Re: advise on performance issues please

On 11/30/2010 4:34 AM, Gregory Machin wrote:
> Hi
> this is the first time I'm working with Posgresql other than a defualt install.
>
> I have a CentOS 5.5 virtual machine with -
> 4 virtual cpus , 8 Gig RAM , resource pool set to High (8000)
>
> running on a vmware ESXi 4.1 host -
> 4 x 2.4 GHz cpus AMD 6 cores each, 96 Gig ram, storage is provided by
> HP Left hand SAN iSCSI.
>
> I'm running post Postgresql 8.4.4 rpm from the postgresql repo.
>
> What I need to clarify is wether the performance issue the website
> that uses database is experiencing is related to postgresql miss
> configuration or bad code in the site.
>
> Customisations in the postgresql.conf
>
> max_connections = 1000
> shared_buffers = 4096MB
> temp_buffers = 512MB
> work_mem = 10MB # min 64kB
> maintenance_work_mem = 160MB # min 1MB
> fsync = off
> synchronous_commit = on
> wal_sync_method = open_sync
> full_page_writes = off
> effective_cache_size = 32MB
> join_collapse_limit = 1
> autovacuum_vacuum_cost_delay = 100ms
> checkpoint_segments = 60
>
> this configuration gives the following pgbench results
>
> [macgre(at)topnz15209-linux ~]$ pgbench -h drvppgs01 -U postgres -c 40 -T 120 test
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 40
> duration: 120 s
> number of transactions actually processed: 47070
> tps = 391.660580 (including connections establishing)
> tps = 392.523468 (excluding connections establishing)
>
> pgbench was setup with pgbench -h drvppgs01 -U postgres -c 40 -T 120
> -i test -F 100 -S 15
>
> mpstat every 5 seconds gives the following:
>

pgbench needs its scale (-s) and #connections (-c) to be close. You
have a scale of 1 and 40 connections, so this is a bad test. Init with
-s 40, then run with -c at 40 or less.

Your mpstat output is hard to read because of the word wrapping. Not
sure if iostat would be more readable (its usually the one I look at
(I'd never even heard of mpstat before now)).

> fsync = off

Thats a bad idea if you care about your data.

> effective_cache_size = 32MB

That looks wrong, you better read up on that one in the docs.

-Andy