Scott Otis 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.
Scott Otis
CIO / Lead Developer
Intand
www.intand.com
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%.
Yeah, that sounds excessive. But my database gets 20 transactions a DAY, so, I
have no experience with a busy box.
You say its mostly selects, but do you have any triggers or anything that might
update a table? Do you do inserts or updates to track traffic?
What does:
select * from pg_stat_activity
look like? (I think vacuum will show up in there, right?) I'm curious if we
can find the table autovacuum is working on, maybe that'll help pin it down.
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
