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.

Sven
  • 97,248
  • 13
  • 177
  • 225
user17268
  • 21
  • 1
  • 2
  • 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 `SELECT`s 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: http://www.postgresql.org/support/professional_support/ – Craig Ringer Jan 02 '15 at 12:33

2 Answers2

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.

Rohini Choudhary
  • 159
  • 6
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
Alexander Mitenko
  • 1
  • 1