Re: sniff test on some PG 8.4 numbers

On 3/5/13 10:00 PM, Jon Nelson wrote:
> On Tue, Mar 5, 2013 at 1:35 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
>>
>> pgbench -h BLAH -c 32 -M prepared -t 100000 -S
>> I get 95,000 to 100,000 tps.
>>
>> pgbench -h BLAH -c 32 -M prepared -t 100000
>> seems to hover around 6,200 tps (size 100) to 13,700 (size 400)
>
> Some followup:
> The read test goes (up to) 133K tps, and the read-write test to 22k
> tps when performed over localhost.

All your write numbers are inflated because the test is too short. This
hardware will be lucky to sustain 7500 TPS on writes. But you're only
writing 100,000 transactions, which means the entire test run isn't even
hitting the database--only the WAL writes are. When your test run is
finished, look at /proc/meminfo I'd wager a large sum you'll find
"Dirty:" has hundreds of megabytes, if not gigabytes, of unwritten
information. Basically, 100,000 writes on this sort of server can all
be cached in Linux's write cache, and pgbench won't force them out of
there. So you're not simulating sustained database writes, only how
fast of a burst the server can handle for a little bit.

For a write test, you must run for long enough to start and complete a
checkpoint before the numbers are of any use, and 2 checkpoints are even
better. The minimum useful length is a 10 minute run, so "-T 600"
instead of using -t. If you want something that does every trick
possible to make it hard to cheat at this, as well as letting you graph
size and client data, try my pgbench-tools:
https://github.com/gregs1104/pgbench-tools (Note that there is a bug in
that program right now, it spawns vmstat and iostat processes but they
don't get killed at the end correctly. "killall vmstat iostat" after
running is a good idea until I fix that).

Your read test numbers are similarly inflated, but read test errors
aren't as large. Around 133K TPS on select-only is probably accurate.
For a read test, use "-T 30" to let it run for 30 seconds to get a more
accurate number. The read read bottleneck on your hardware is going to
be the pgbench client itself, which on 8.4 is running as a single
thread. On 9.0+ you can have multiple pgbench workers. It normally
takes 4 to 8 of them to saturate a larger server.

I hope you're not considering deploying a new application with 8.4.
Take a look at http://www.postgresql.org/support/versioning/ and you'll
see 8.4 only has a little over a year before it won't get bug fixes
anymore. Also, your server would really appreciate the performance
gains added to 9.2. If that's a bit too leading edge for you, I don't
recommend deploying at version below 9.1 anymore.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com