I am trying to fix a high PostgreSQL CPU use problem. We are using PostgreSQL 8.0.9 and when our JEE web application (in JBoss) is used in certain load increase conditions, top shows a slow increase in processes for PostgreSQL. When the problem occurs, there are approximately 12-15 PostgreSQL processes all showing SELECT on the far right of the process information and approximately 6-7% CPU usage each and then the app slows down a lot.
JBoss version: JBoss (MX MicroKernel) 4.0.3
Operating system: CentOS Linux 5.5
Kernel and CPU: Linux 2.6.18-194.26.1.el5 on x86_64
Processor information: 2 x Intel(R) Xeon(R) CPU E5420 @ 2.50GHz, 8 cores
Currently, our thought is to throw more hardware at it. If we do this, would the best option be something like Option A below or Option B?
Option A: 4 x AMD Opteron™ 6100 Series Processors each with 12 Cores
Option B: 4 x Intel® Xeon® 7500 series Processors each with 8 Cores
Is it correct to assume that CentOS Linux 5.5 with PostgreSQL 8.0.9 will scale proportionately with the addition of this many processors and cores (Ex. 4 processors each with 12 cores)? Is there something else I should consider in terms of throwing more hardware at it?
Best Answer
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.