Re: Seeking performance advice and explanation for high I/O on 8.3

On Thu, Sep 3, 2009 at 5:27 PM, Andy Colson<andy(at)squeakycode(dot)net> wrote:
> Robert Haas wrote:
>>
>> On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott(dot)otis(at)intand(dot)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 sequential vs 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 is doing 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 or down with user activity on
>>> the server - which makes me wonder a little bit.  I have a feeling that if I
>>> just turned off Apache that the I/O stats wouldn't change.  Which leads me
>>> to believe that the I/O is not query related - its stats collecting and
>>> 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 using that much CPU)  - therefore
>>> something in my configuration must be messed up or could be changed somehow.
>>>  But maybe I'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 feedback to see if am setting
>>> things up right.  I am also looking out into the future and seeing how much
>>> load I can put on this server before getting another one.  If I can reduce
>>> the I/O and CPU that the stats collector and autovacuum are using without
>>> 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

You're right - I missed that. But I still want to see pg_stat_all_tables.

I wonder if it would be worth attaching strace to the stats collector
and trying to get some idea what it's doing (if FreeBSD has
strace...).

....Robert