1

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had this issue for a couple of weeks. At first we got problems like

FATAL: remaining connection slots are reserved for non-replication superuser connections

But after optimising the code by using memcached to let of some of the load from postgres as well as making som smarter queries, we got rid of this problem. The postgresql log of slow queries looks a lot nicer now, but we are still experiencing a lot of timed out requests during peak hours. So the question is: Where is the bottleneck?

We’ve installed munin to log a lot of parameters to check the health status but acctually, we don’t get any closure out of this.

  1. Is there anything that look strange in the graphs?
  2. What should we focus on gettings more efficient?
  3. Is there any indications on what may cause the issues during peak hours? (19:00 – 23:00)

Look at the benchmark here: http://bit.ly/WwJPKA

Thanks!

Some info about our current setup:

  • 8 CORES
  • 16GB RAM
  • 100 GB, RAID-10 * SAS 15.000 RPM
  • PostgreSQL 9.1.5

For more info about our PostgreSQL.conf checkout this

Improve this question
3
  • The most important information is missing: what issues do you have? Timeouts, lost connections, increased response times or what?
    – dezso
    Nov 21, 2012 at 11:09
  • 1
    You are absolutely right. I will add this information immediately. I am very impressed about the people in this forum, everybody comes with constructive criticism. That is unlike many other forums. Thanks @dezso
    – lorgartzor
    Nov 21, 2012 at 11:14
  • 3
    Your system load is quite high during peak, but it is not reflected in the cpu graph. However, the disk seems quite busy so I'm guessing I/O waits. You have only allocated a small portion of the RAM to PostgreSQL and seem to have a lot of free memory. You may have some luck allocating more memory to the database.
    – Roy
    Nov 21, 2012 at 14:44

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.