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