Re: pgbench results on a new server

On Mon, Jun 28, 2010 at 1:12 PM, Craig James <craig_james(at)emolecules(dot)com> wrote:
> On 6/25/10 12:03 PM, Greg Smith wrote:
>>
>> Craig James wrote:
>>>
>>> I've got a new server and want to make sure it's running well.
>>
>> Any changes to the postgresql.conf file? Generally you need at least a
>> moderate shared_buffers (1GB or so at a minimum) and checkpoint_segments
>> (32 or higher) in order for the standard pgbench test to give good
>> results.
>
> max_connections = 500
> shared_buffers = 1000MB
> work_mem = 128MB
> synchronous_commit = off
> full_page_writes = off
> wal_buffers = 256kB
> checkpoint_segments = 30
> effective_cache_size = 4GB
>
> For fun I ran it with the installation defaults, and it never got above 1475
> TPS.
>
>>> pgbench -c20 -t 5000 -U test
>>> tps = 5789
>>> pgbench -c30 -t 3333 -U test
>>> tps = 6961
>>> pgbench -c40 -t 2500 -U test
>>> tps = 2945
>>
>> General numbers are OK, the major drop going from 30 to 40 clients is
>> larger than it should be. I'd suggest running the 40 client count one
>> again to see if that's consistent.
>
> It is consistent.  When I run pgbench from a different server, I get this:
>
>   pgbench -c40 -t 2500 -U test
>   tps = 7999
>
>   pgbench -c100 -t 1000 -U test
>   tps = 6693

6k tps over 8 7200 rpm disks is quite good imo. synchronous_commit
setting is making that possible. building a server that could handle
that much was insanely expensive just a few years ago, on relatively
cheap sorage. that's 21m transactions an hour or ~ half a billion
transactions a day (!). running this kind of load 24x7 on postgres
7.x would have been an enormous headache. how quickly we forget! :-)

your 'real' tps write, 1475 tps, spread over 4 disks doing the actual
writing, is giving you ~ 370 tps/device. not bad at all -- the raid
controller is doing a good job (the raw drive might get 200 or so). I
bet performance will be somewhat worse with a higher scaling factor
(say, 500) because there is less locality of writes -- something to
consider if you expect your database to get really big.

merlin