Re: Seeking performance advice and explanation for high I/O on 8.3 - Mailing list pgsql-performance

Robert Haas wrote:
> On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott.otis@intand.com> wrote:
>> Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.
>>
>> Would providing more information about the size and complexities of the databases help?
>>
>> I measure I/O stats with iostat - here is the command I use:
>>
>> iostat -d -x mfid0 -t 290 2
>>
>> I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for
sequentialvs random - any help there? 
>>
>> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.
>>
>> When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.
>>
>> Is that normal?  It seems to me that the stats collector is doing all the I/O (which would mean the stats collector
isdoing 46.1 megabytes /sec). 
>>
>> Also, the I/O stats don't change hardly at all (except at night during backups which makes sense).  They don't go up
ordown with user activity on the server - which makes me wonder a little bit.  I have a feeling that if I just turned
offApache that the I/O stats wouldn't change.  Which leads me to believe that the I/O is not query related - its stats
collectingand autovacuuming related.  Is that expected? 
>>
>> It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be
usingthat much CPU)  - therefore something in my configuration must be messed up or could be changed somehow.  But
maybeI'm wrong - please let me know. 
>>
>> I don't think my setup is necessarily slow.  I just want to make it as efficient as possible and wanted to get some
feedbackto see if am setting things up right.  I am also looking out into the future and seeing how much load I can put
onthis server before getting another one.  If I can reduce the I/O and CPU that the stats collector and autovacuum are
usingwithout losing any functionality then I can put more load on the server. 
>>
>> Again thanks for all the help.
>
> Can you post to the list all the uncommented lines from your
> postgresql.conf file and attach the results of "select * from
> pg_stat_all_tables" as an attachment?
>
> ...Robert
>

The first message he posted had this, and other info... Which is funny, because I almost asked the exact same question
:-)


FreeBSD 6.4
Apache 2.2
PostgreSQL 8.3.6
PHP 5.2.9


~1500 databases w/ ~60 tables each


Conf settings:

listen_addresses = '*'
max_connections = 600
ssl = on
password_encryption = on
shared_buffers = 1GB
work_mem = 5MB
maintenance_work_mem = 256MB
max_fsm_pages = 2800000
max_fsm_relations = 160000
synchronous_commit = off
checkpoint_segments = 6
checkpoint_warning = 30s
effective_cache_size = 1GB


pg_stat_bgwriter:

checkpoints_timed: 16660
checkpoints_req: 1309
buffers_checkpoint: 656346
buffers_clean: 120922
maxwritten_clean: 1
buffers_backend: 167623
buffers_alloc: 472802349