Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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?
  • 3
  • 3
1 Solution
earth man2Commented:
You have to enable auto vacuuming daemon it is normally off by default.  Look see in the postgresql.conf file for the autovacuum setting
earth man2Commented:
Since your database is probably now in memory the bottleneck is the CPU/memory !

You need to establish what statements are consuming resource and create indexes to speed up those critical hogs.
rstaveleyAuthor Commented:
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>'.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

earth man2Commented:
Are you accessing the database as the postgres super user ? see
rstaveleyAuthor Commented:
Yes as user postgres.

My fiddling around with indexing has paid dividends you know! My load averages have come plumetting down :-) :-)
rstaveleyAuthor Commented:
earthman2, you've answered my question. I'll open a separate one about the stats_command_string. Many thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now