How to Best Manage High PostgreSQL CPU Use?

Solution 1:

The question is impossible to answer, we have no idea what is going on. You're talking about 12-15 connections, that's next to nothing. But, when executing very complex queries, or using a bad database schema, lack of indexes, etc. cpu usage can go up any time.

Version 8.0.9 is serious problem, 8.0 is EOL as of october 2010 and the lastest fix is version 8.0.26 (4 years of bugfixes after 8.0.9). You should at least update to this version, to fix many bugs in 8.0.

Start logging the queries, use EXPLAIN to see the queryplan, take a look at VACUUM and you might need a REINDEX as well. Your hardware looks fine for now, you first have to find the source of the problems.

Consider to hire a PostgreSQL dba for a couple of days.

Solution 2:

If you're showing high CPU usage, it could be due to slow queries. I would suggest enabling the slow query logging features in postmaster.conf and checking for queries that take longer than they should.

There's also the possibility that you are I/O bound, as slow disks can easily cause queries to start backing up. I would suggest installing htop and checking what percentage of your CPU wait time is attributed to iowait.

Aside from that, I would highly encourage upgrading to the latest version. There have been some massive performance improvements since 8.0, and the current stable version (9.0.x at the time of writing) offers more information when EXPLAIN VERBOSE ANALYZEing queries.

Generally speaking (and all other conditions being equal), PostgreSQL scales really well as you add cores (each additional core adds approximately a 96% gain in performance (out of a theoretical 100% performance gain possible per additional core)).

My initial gut feeling however is that your disks can not keep up.