CPU bound
I have a 48M PostgreSQL database on a 2GB dedicated Linux server with one CPU. It is taking a pounding and I've seeing very sluggish performance. Due to the importance of the load (it's the run up to a live event and that's the reason for the traffic), I want to do as limited experimentation as possible.
It's not too bad here...
top - 23:26:38 up 144 days, 10:26, 4 users, load average: 1.35, 1.21, 1.10
Tasks: 75 total, 2 running, 73 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.3% hi, 0.0% si
Mem: 3631740k total, 1969348k used, 1662392k free, 92852k buffers
Swap: 4008120k total, 0k used, 4008120k free, 1757768k cached
...but I've seen those load averages get up to > 5.0 for long periods, and yes it *is* postmaster munching the mips :-(
The postgresql.conf file has been set up with defaults, and I'm planning to apply the following changes:
#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
shared_buffers = 8192 # Rob S. 2006-11-17 - it is only a 50M data set, but we can spare 64M of RAM
#vacuum_mem = 8192 # min 1024, size in KB
vacuum_mem = 16384 # Rob S. 2006-11-17 - 16MB
#effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 10240 # Rob S. This is 80M, which is plenty for our 50M database!
The database is: PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)
I have never vacuumed. I'm assume that PostgreSQL does that for me now.
Here are my questions:
(1) Are these sane changes and in any way likely to reduce the CPU load?
(2) Would explicit VACUUMing be worth it?
It's not too bad here...
top - 23:26:38 up 144 days, 10:26, 4 users, load average: 1.35, 1.21, 1.10
Tasks: 75 total, 2 running, 73 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.3% hi, 0.0% si
Mem: 3631740k total, 1969348k used, 1662392k free, 92852k buffers
Swap: 4008120k total, 0k used, 4008120k free, 1757768k cached
...but I've seen those load averages get up to > 5.0 for long periods, and yes it *is* postmaster munching the mips :-(
The postgresql.conf file has been set up with defaults, and I'm planning to apply the following changes:
#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
shared_buffers = 8192 # Rob S. 2006-11-17 - it is only a 50M data set, but we can spare 64M of RAM
#vacuum_mem = 8192 # min 1024, size in KB
vacuum_mem = 16384 # Rob S. 2006-11-17 - 16MB
#effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 10240 # Rob S. This is 80M, which is plenty for our 50M database!
The database is: PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)
I have never vacuumed. I'm assume that PostgreSQL does that for me now.
Here are my questions:
(1) Are these sane changes and in any way likely to reduce the CPU load?
(2) Would explicit VACUUMing be worth it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks earthman2. I've been picking through all the queries with explain and have found some indexing that improves matters. There's some horrible code in there.
Annoyingly I can't seem to see any commands despite setting stats_command_string = true. It can't stop it from saying ' <command string not enabled>'.
Annoyingly I can't seem to see any commands despite setting stats_command_string = true. It can't stop it from saying ' <command string not enabled>'.
Are you accessing the database as the postgres super user ? see
http://www.postgresql.org/docs/8.1/static/monitoring-stats.html
http://www.postgresql.org/docs/8.1/static/monitoring-stats.html
ASKER
Yes as user postgres.
My fiddling around with indexing has paid dividends you know! My load averages have come plumetting down :-) :-)
My fiddling around with indexing has paid dividends you know! My load averages have come plumetting down :-) :-)
ASKER
earthman2, you've answered my question. I'll open a separate one about the stats_command_string. Many thanks.
see
http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum