Thread: heavy load-high cpu itilization
Dear all first of all congratulations on your greak work here since from time to time i 've found many answers to my problems. unfortunately for this specific problem i didnt find much relevant information, so i would ask for your guidance dealing with the following situation: we have a dedicated server (8.4.4, redhat) with 24 cpus and 36 GB or RAM. i would say that the traffic in the server is huge and the cpu utilization is pretty high too (avg ~ 75% except during the nights when is it much lower). i am trying to tune the server a little bit to handle this problem. the incoming data in the database are about 30-40 GB /day. at first the checkpoint_segments were set to 50, the checkpoint_timeout at 15 min and the checkpoint_completion_target was 0.5 sec. i noticed that the utilization of the server was higher when it was close to making a checkpoint and since the parameter of full_page_writes is ON , i changed the parameters mentioned above to (i did that after reading a lot of stuff online): checkpoint_segments->250 checkpoint_timeout->40min checkpoint_completion_target -> 0.8 but the cpu utilization is not significantly lower. another parameter i will certainly change is the wal_buffers which is now set at 64KB and i plan to make it 16MB. can this parameter cause a significant percentage of the problem? are there any suggestions what i can do to tune better the server? i can provide any information you find relevant for the configuration of the server, the OS, the storage etc thank you in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/heavy-load-high-cpu-itilization-tp4635696p4635696.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/26/2011 01:47 PM, Filippos wrote: > we have a dedicated server (8.4.4, redhat) with 24 cpus and 36 GB or RAM. i > would say that the traffic in the server is huge and the cpu utilization is > pretty high too (avg ~ 75% except during the nights when is it much lower). > i am trying to tune the server a little bit to handle this problem. the > incoming data in the database are about 30-40 GB /day. > Well, the first question is where the CPU usage is coming from. There are two basic schools of thought here: 1) Use real-time monitoring utilities like "top -c" and see what is gobbling time up. It's possible to miss what's happening, but if you're at 75% a large chunk of the day that doesn't seem likely. 2) Set log_min_duration_statement and the other logging parameters; analyze the resulting log files to see where the CPU time is going. You seem to be focused on the background writer and its checkpoint process right now. That cannot be the source for high CPU usage; at most it could fully use one of your 24 cores. You should fix wal_buffers to a reasonable value regardless, but your problem is not in that area. Importing 30-40 GB/day is extremely difficult to do in PostgreSQL. My guess is that most of the server time is spent running the data import process itself--even COPY, the most efficient way to get data in, is very CPU intensive. The second layer of problems here that can increase CPU usage come from autovacuum taking up a lot of resources to run, which it will do all the time given this volume of activity. And there's always the possibility that the queries you're running against the data are just taking a long time to execute. Another layer of problems in this scenario you'll hit eventually is that you'll need to have your tables partitioned in order to prune old data out efficiently. Presumably you can't keep up with that rate for very long before you have to start dropping older data, and that's really hard to do efficiently unless you've used partitions. P.S. You should upgrade to PostgreSQL 8.4.8 as soon as possible. There is a bug in autovacuum that's been resolved as of 8.4.6 that you are very likely to run into: http://www.postgresql.org/docs/8.4/static/release-8-4-6.html P.P.S. The pgsql-performance list would be a more appropriate place to have this discussion at. Some of the people who provide good input over there on topics like this don't read pgsql-general, too many messages on this list for them. > are there any suggestions what i can do to tune better the server? i can > provide any information you find relevant for the configuration of the > server, the OS, the storage etc > There's a chapter on each of these in my PostgreSQL performance book, and I'm not aware of any other resource that takes on all of these topics usefully. If you're trying to keep up with this volume of data, buying a copy of that should repay itself in time savings--where you can look something up rather than trying to figure it out from scratch--about once every week. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
thx a lot for your answer. i have already bought your book and have read most of the chapters. (in the near future i will study it more thoroughly) i asked here for tips in case there was something i had missed and there was a "magic" way to improve the perfomance even a little. as far as the cpu usage is concerned, i have monitored the situation closely (using top and other tools), and it comes from postgres. you also told me about the logs and i am aware of this "technique" but i didnt use it so far, to avoid the extra load of data, but i will do it soon. i will also schedule an update deal with the bug for autovacuum. thx again :) P.S i will send a message to the admins, to ask them to move the topic to the sub-forum of perfomance -- View this message in context: http://postgresql.1045698.n5.nabble.com/heavy-load-high-cpu-itilization-tp4635696p4637997.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/27/2011 04:37 AM, Filippos wrote: > P.S i will send a message to the admins, to ask them to move the topic to > the sub-forum of perfomance > Don't do that; will just waste their time. pgsql-general is a mailing list, and the "forum" view you're seeing at Nabble is just a web interface to it. They can't move things around there because they don't really own the list; they just make a copy of all its messages. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us