Re: Benchmark: Dell/Perc 6, 8 disk RAID 10 - Mailing list pgsql-performance

Greg Smith wrote:

On Fri, 14 Mar 2008, Justin wrote:

I played with shared_buffer and never saw much of an improvement from
100 all the way up to 800 megs  moved the checkpoints from 3 to 30 and
still never saw no movement in the numbers.

Increasing shared_buffers normally improves performance as the size of the database goes up, but since the pgbench workload is so simple the operating system will cache it pretty well even if you don't give the memory directly to PostgreSQL.  Also, on Windows large settings for shared_buffers don't work very well, you might as well keep it in the 100MB range.

wal_sync_method=fsync

You might get a decent boost in resuls that write data (not the SELECT ones) by changing

wal_sync_method = open_datasync

which is the default on Windows.  The way you've got your RAID controller setup, this is no more or less safe than using fsync.

I moved the window server back to fsync a long time ago.  Around here we are super paranoid about making sure the data does not become corrupt, performance is secondary.  The new server along with the old server is way over built for the load it will ever see.  I will be making the old server a slony replicator located in the manufacturing building.

Also *note* tried setting the value open_datasync and get invalid parameter.  instead i use open_sync

i agree with you, those numbers are terrible i realized after posting i had the option -C turned on if i read the option -C correctly it is disconnecting and reconnecting between transactions. The way read -C option creates the worst case.

In addition to being an odd testing mode, there's an outstanding bug in how -C results are computed that someone submitted a fix for, but it hasn't been applied yet.  I would suggest forgetting you ever ran that test.

Why is the -C option odd?

Note how the total number of transactions goes up here, because it's actually doing clients x requested transcations in total.  The 40 client case is actually doing 4X as many total operations.  That also means you can expect 4X as many checkpoints during that run.  It's a longer run like this second one that you might see some impact by increasing checkpoint_segments.

To keep comparisons like this more fair, I like to keep the total transactions constant and just divide that number by the number of clients to figure out what to set the -t parameter to.  400000 is a good medium length test, so for that case you'd get

-c 10 -t 40000
-c 40 -t 10000

as the two to compare.

---- retested with fsync turned on -----

C:\Program Files\PostgreSQL\8.3\bin>pgbench -c 10 -t 40000 -v -h 192.168.1.9 -U
postgres empro
Password:
starting vacuum...end.
starting vacuum accounts...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 10
number of transactions per client: 40000
number of transactions actually processed: 400000/400000
tps = 767.040279 (including connections establishing)
tps = 767.707166 (excluding connections establishing)


C:\Program Files\PostgreSQL\8.3\bin>pgbench -c 40 -t 10000 -v -h 192.168.1.9 -U
postgres empro
Password:
starting vacuum...end.
starting vacuum accounts...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 40
number of transactions per client: 10000
number of transactions actually processed: 400000/400000
tps = 648.988227 (including connections establishing)
tps = 650.935720 (excluding connections establishing)


-------open_sync------------

C:\Program Files\PostgreSQL\8.3\bin>pgbench -c 10 -t 40000 -v -h 192.168.1.9 -U
postgres empro
Password:
starting vacuum...end.
starting vacuum accounts...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 10
number of transactions per client: 40000
number of transactions actually processed: 400000/400000
tps = 798.030461 (including connections establishing)
tps = 798.752349 (excluding connections establishing)

C:\Program Files\PostgreSQL\8.3\bin>pgbench -c 40 -t 10000 -v -h 192.168.1.9 -U
postgres empro
Password:
starting vacuum...end.
starting vacuum accounts...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 40
number of transactions per client: 10000
number of transactions actually processed: 400000/400000
tps = 613.879195 (including connections establishing)
tps = 615.592023 (excluding connections establishing)