On Thu, Sep 3, 2009 at 5:27 PM, Andy Colson<andy@xxxxxxxxxxxxxxx> wrote: > 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 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
