Robert Haas wrote:
On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott.otis@xxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
