High CPU usage in the processes related to postgres in ubuntu 12.04

Question

My Ubuntu server 12.04 hangs quite oftenly (say once in a day), after hard reboot it starts running normally.

The server is Intel Xeon having 2 CPUs with 8GB RAM.There is high CPU usage in the processes related to postgres in ubuntu 12.04 (80%, 90% and sometimes more than 100%).

Solution 1

This post saved my hours. check for the query running for the longest time and check if the query is stuck and running since more an hour. Run the following command.

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

If this is more than an hour, than this is the issue. Kill the long running connection and limit the max age of the connection from application side.

Solution 2

I googled a very helpful link. http://frosty-postgres.blogspot.in/2012/08/postgresql-numa-and-zone-reclaim-mode.html After setting zone_reclaim_mode to 0 ,performace of server has improved. Queries are taking less time. Server is still under close observation.