How to Best Manage High PostgreSQL CPU Use?

Question

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?

Answer 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.

Answer 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.

Answer 3

I think you'll benefit from the book PostgreSQL 9.0 High Performance. It's available in PDF (instant download) as well as in dead tree format.

We've just rebuilt our database using the advice in this book. Our new database box blows the old one away, and we didn't have to spend a ton of money doing it. There are chapters that specifically address each of your questions. There are answers, but better yet, there are also methods (how do you measure your hardware to know how fast it is?)

I'm no Postgresql expert, but I'll tell you what I've learned about hardware and Postgresql. Your mileage may vary.

In general, for the databases I've had experience with, what matters more than number and speed of CPUs is:

• Adequate RAM. Databases drink memory like a wino drinks rotgut.
• I/O bandwidth. Databases love I/O.

You get I/O bandwidth with RAID. RAID10 does well for the bulk of Postgresql data. The more drives, the better for performance. Put xlog on a separate device, if you can. That one can be RAID1. The use of a hardware RAID card with battery-backed cache will give you the best performance.

Answer 4

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.

12x6=72%, so even at the lowest point, the CPUs are rather busy. Throw in everything else, and it's pretty clear why you're running flat-out. (This is assuming you're looking at the CPUs as an aggregate; when you look at process time in top, are you pressing the 1 key to see all the individual CPU time, or just looking at the number it presents, which is all the CPUs combined?)

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

More cores. PostgreSQL will use a process-per-core model, so the more the better. I would look at maybe 2x AMD CPU at 12 each for 24 cores total, and then purchase the remaining 2 CPUs over time, allowing you to budget them in.

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)?

Yes. I may be mistaken but I believe that older kernel compiles used a fixed number in a C header file to determine the max number of CPUs to look for, which usually had an upper bound of 32 at compile time. If you had a "big" machine you would just bump the number to something higher and recompile. Not entirely sure but I think they removed that constant in the 2.6 series so you should be fine.

Is there something else I should consider in terms of throwing more hardware at it?

You may wish to look at tuning the software a bit more before throwing hardware at it (or tune it and still get the new hardware).

If it is a SELECT statement, any chance you can log it and then use EXPLAIN to find out where it is spending its time? Use PgAdmin to run and tune the query by hand until you can get the execution time down a bit. If the SELECT statement is programmatic, you could still look at the impact of using a new index.

How much memory have you allocated to PostgreSQL? How much on a per-process basis? How much shared memory is allocated? All of these can have an adverse impact on how the database runs.

Are there any other processes or services that could be disabled (to free up memory) or re-niced (to reduce CPU consumption)?

Answer 5

I've been recently experiencing similar issues on a small database (7 tables, 30 MB) with queries having lots of joins. The machine is a VM with 2GB RAM and always seem to use less than 160MB of it. It worked out very fast until we added about 1M of new data. Then the server (8.4.5) started to hit 100% CPU for anywhere between 5 seconds to 30 minutes with the same queries that were sub-second.

We managed to fix the problem by server upgrade. Tests with 8.4.9 and 8.4.12 did not show the bad behaviour (but 8.4.8 did).