Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
The most important information is missing: what issues do you have? Timeouts, lost connections, increased response times or what? –  dezso Nov 21 '12 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 '12 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 '12 at 14:44
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.