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?