Postgresql – How to find resource intensive SQL operations in Postgresql? High CPU usage spike

performancepostgresqlpostgresql-9.2query-performance

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. 

SELECT * FROM pg_stat_activity;

From the collected data, observed that there is one frequently accessed table with UPDATE operation. From further investigation I came to know that the table is the most frequently accessed one in the DB and there is nothing possibly resource intensive.

Following are the details I got from EXPLAIN for that query,

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Update on foo_table  (cost=0.00..1271.14 rows=1 width=55)
   ->  Seq Scan on foo_table  (cost=0.00..1271.14 rows=1 width=55)
         Filter: (id = 2::bigint)
(3 rows)

So far the only certain data I have is the time period when this high CPU usage triggering operation is happening. Though unable to figure out which query is taking too much time to process.

I have tried following queries, I couldn't find convincing stats.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads;

SELECT * FROM pg_stat_all_tables ORDER BY seq_scan DESC;

I am helpless as there is no way for me to figure out what is happening, like is it normal because the work load or is it indexing related issue or query optimization would solve it,etc.

DB spec:
Size: 150+ GB
CPU: 8 core
RAM: 16 GB
Storage: SSD with no swap.

Best Answer

The problem was with the indexing of few tables. I was able to identify those tables with Read stats by performing the following query.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads desc LIMIT 10;
Related Question