Re: New to PostgreSQL, performance considerations

On Dec 14, 2006, at 16:00 , Greg Smith wrote:

> On Wed, 13 Dec 2006, Ron wrote:
>
>> The slowest results, Michael's, are on the system with what
>> appears to be the slowest CPU of the bunch; and the ranking of the
>> rest of the results seem to similarly depend on relative CPU
>> performance.  This is not what one would naively expect when
>> benching a IO intensive app like a DBMS.
>
> pgbench with 3000 total transactions and fsync off is barely doing
> I/O to disk; it's writing a bunch of data to the filesystem cache
> and ending the benchmark before the data even makes it to the hard
> drive.  This is why his results become completely different as soon
> as the number of transactions increases. With little or no actual
> disk writes, you should expect results to be ranked by CPU speed.

I also second your suggestion that pgbench should be run with -S to
disable updates. As far as I can see, nobody has reported numbers for
this setting, so here goes. I also increased the buffer size, which I
found was needed to avoid hitting the disk for block reads, and
increased the memory settings.

My PostgreSQL config overrides, then, are:

shared_buffers = 1024MB
work_mem = 1MB
maintenance_work_mem = 16MB
fsync = off

Environment: Linux 2.6.15-23-amd64-generic on Ubuntu. Dual-core AMD
Opteron 280 with 4GB of RAM. LSI PCI-X Fusion-MPT SAS.

Running with: pgbench -S -v -n -t 5000 -c 5.

Results as a graph: http://purefiction.net/paste/pgbench.pdf

Stats for CFLAGS="-O0": 18440.181894 19207.882300 19894.432185
19635.625622 19876.858884 20032.597042 19683.597973 20370.166669
19989.157881 20207.343510 19993.745956 20081.353580 20356.416424
20047.810017 20319.834190 19417.807528 19906.788454 20536.039929
19491.308046 20002.144230

Stats for CFLAGS="-O3 -msse2 -mfpmath=sse -funroll-loops -m64 -
march=opteron -pipe": 23830.358351 26162.203569 25569.091264
26762.755665 26590.822550 26864.908197 26608.029665 26796.116921
26323.742015 26692.576261 26878.859132 26106.770425 26328.371664
26755.595130 25488.304946 26635.527959 26377.485023 24817.590708
26480.245737 26223.427801

Alexander.