Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 25 '11 at 12:21

This question came from our site for professional and enthusiast programmers.

    
It would be nice to know more about your architecture. Do you have some kind of caching in place? –  Vitor Feb 24 '11 at 23:47
    
When under load, What does iostat -xm 4 show for the %util of the devices hosting $pg_data and $pg_data/xlog? –  Wayne Conrad Feb 25 '11 at 0:03
    
Regarding the comment "It would be nice to know more about your architecture. Do you have some kind of caching in place?", I don't think there is a specific use of caching in place other than standard EJB 3.0 session and entity bean standard optimization. What kind of caching options are available for something like this? Which one do you think is most likely to help? –  Anonymous Feb 25 '11 at 0:32
    
Regarding the comment "When under load, What does iostat -xm 4 show for the %util of the devices hosting $pg_data and $pg_data/xlog?", we did run an iostat during some load but did not capture stats from the exact time period where the problem occured. Please tell me more about running iostat in a way that would specify which devices are hosting "$pg_data and $pg_data/xlog"? How do we know which devices to focus on for those? –  Anonymous Feb 25 '11 at 0:35
    
I want to confirm the assumption that the box is CPU bound, and not IO bound. When you run iostat, it will show you each block device. You're looking for the devices which host your postgres data (e.g. /var/lib/postgresql). The mount command will help you map the path to the device. –  Wayne Conrad Feb 25 '11 at 3:20

5 Answers 5

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.

share|improve this answer
1  
Thank you for your quick reply. Regarding your comment "My initial gut feeling however is that your disks can not keep up." and your comment "There's also the possibility that you are I/O bound", what can be done about slow disks that cannot keep up? What are the available approaches to fixing this type of problem? –  Anonymous Feb 25 '11 at 0:29
    
@user633286 I doubt that IO caused that, anyway you can always try to increase the memory cache, read westnet.com/~gsmith/content/postgresql/pg-5minute.htm or better wiki.postgresql.org/wiki/Performance_Optimization –  arthurprs Feb 25 '11 at 0:36
    
Thank you for your quick reply. Regarding your comment "I doubt that IO caused that, anyway you can always try to increase the memory cache, read westnet.com/~gsmith/content/postgresql/pg-5minute.htm or better wiki.postgresql.org/wiki", please specify which parameter or parameters in postgresql.conf I should try increasing and to what degree. Which parameters and how much for a server with 17.38 GB total real memory on which JBoss is allocated a significant portion (Xms=1024m and Xmx=6144m but usually uses less than 3072m)? –  Anonymous Feb 25 '11 at 1:02
    
Thank you again for your quick reply. Regarding your comment "I would highly encourage upgrading to the latest version. There have been some massive performance improvements since 8.0", we are interested in migrating to a more recent release of PostgreSQL but are not ready to do so for another month and this problem is urgent. For our planning purposes, please elaborate on what the massive performance improvements that have been made since 8.0 are. –  Anonymous Feb 25 '11 at 1:06
    
Thank you for your quick reply. Regarding your comment "My initial gut feeling however is that your disks can not keep up." and your comment "There's also the possibility that you are I/O bound", the drives are 10K RPM server drives - is this helpful or do you have any additional thoughts? –  Anonymous Feb 25 '11 at 1:39

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.

share|improve this answer
    
Hello, thank you for your quick reply. This database is very small (the tar archive backup for this database is less than 30MB). I have read that a small fully cached database usually doesn't have CPU problems. Would throwing a lot of hardware (ex. 4 x 12 core CPUs and RAID 10 with 8 x 15k RPM drives) at it help significantly if the queries were unchanged and it was still small and fully cached? How would the extra processor resources help in these circumstances? How would the RAID 10 with RAID 10 with 8 x 15k RPM drives help in these circumstances? –  Anonymous Feb 25 '11 at 6:44
    
I've never had the luxury of a database that fit entirely in RAM, so I don't know much much I/O bandwidth would help you. Even things that fit in RAM do get written to disk, though, as they change. –  Wayne Conrad Feb 25 '11 at 13:26

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.