2

We have a Java EE application (a online digital library portal) with Hibernate 4.3 which connects to a PostGres 9.2 database. Upon load testing with a load of 175 concurrent users logging in and accessing the system, the database server CPU utilization goes up to 100% and stays that way.

The hardware for the database server is: 4 Core Pentium 2+ GHz processor. 12 GB RAM Ubuntu The connection pool size is 200. The application server is Apache Tomcat 6.x running on a different 4 Core Pentium 2+ GHz processor. 12 GB RAM Ubuntu machine.

Please help! Need to fix this urgently.

Improve this question
1
  • 1
    This isn't especially surprising. Hibernate tends to drive huge loads because of very inefficient query patterns - huge, unnecessary left join chains and/or the n+1 SELECTs pattern. 200 is also at the very upper end of a desirable pool size even on a bigger machine with a newer PostgreSQL; lowering it may well significantly improve performance. You didn't mention disk activity, disk type, etc, which is critical for a database unless the DB is quit a bit smaller than RAM. As for urgent, you know where to go: postgresql.org/support/professional_support Commented Jan 2, 2015 at 12:33

2 Answers 2

Reset to default
2

I came across with the similar kind of issue. The reason was - some transactions was getting stuck and running since long time. Hence forth CPU utilization got increased to 100%. Following command helped to find out the connections running for the longest time:

SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');

This command shows the time since when a connection is running. This time should not be greater than an hour. So killing the connection which was running from long time or stuck at any point, worked for me. I followed this post for monitoring and solving my issue. Post includes lots of useful commands to monitor this situation.

Improve this answer
0

Do you have a DBA?

Can he answer to questions about database regular maintenance such as a tables analyzing frequency, enough memory for the database?

Can you find the queries that affect the database CPU?

There are existing some ways to help You without hardware extension, such as:

  1. Adequate memory management - it's depend on Your database configuration/load
  2. Appropriate indexes to avoid full table scans - You must do research on queries
  3. Regular tables/indexes analysis - DBA must configure it on a regular basis
  4. Common application database architecture - it's a personal approach
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.