PostgreSQL – CPU Utilization and Index

One of the Production Aurora PostgreSQL instance running on db.r4.16xlarge instance (64 vCPU and 488 GB ) was reporting high CPU Utilization spiking upto 100%.

Screen Shot 2018-12-09 at 11.14.49 AM

With such issues, one of the first thing is to look for the SQLs with high buffers shared hit. I have built a small tool called pgsnap which is something similar to AWR respostory in Oracle maintaining the SQL stat history.  So, with pg_stat_statements and hist_pg_stat_statements(that’s what I call it), I was able to identify the SQL.

select col1, col2, col3, col4, col5 from cltn_errs redis0_ where redis0_.sbmn_id=123456;

Lets look at the execution plan

Gather  (cost=1000.00..2070031.26 rows=2696 width=262) (actual time=17475.126..18771.216 rows=1 loops=1)                                                       
   Output: col1, col2, col3, col4, col5                                   
   Workers Planned: 7                                                                                                                                           
   Workers Launched: 0                                                                                                                                          
   Buffers: shared hit=3945515                                                                                                                            
   ->  Parallel Seq Scan on demo.cltn_errs redis0_  (cost=0.00..2068761.66 rows=385 width=262) (actual time=17474.807..18770.895 rows=1 loops=1) 
         Output: col1, col2, col3, col4, col5                              
         Filter: (redis0_.sbmn_id = '123456'::numeric)                                                                                        
         Rows Removed by Filter: 52390761                                                                                                                       
         Buffers: shared hit=3945515                                                                                                                            
 Planning time: 0.652 ms  
 Execution time: 18771.384 ms      

The problem was obvious!! Full table scan of table cltn_errs (~15Gb table). So, I restored the latest snapshot, created an index on “sbmn_id” column and the execution plan changed to

Index Scan using idx_sbmn_id on demo.cltn_errs redis0_  (cost=0.57..182.80 rows=3086 width=272) (actual time=0.031..0.032 rows=1 loops=1) 
   Output: col1, col2, col3, col4, col5                                           
   Index Cond: (redis0_.sbmn_id = '123456'::numeric)                                                                                                 
   Buffers: shared hit=5                                                                                                                                 
 Planning time: 0.573 ms                                                                                                                                               
 Execution time: 0.085 ms        

Wow!! After the index, Buffers: Shared hit and the total execution time has magnificent improvement, more than 100x. So, with this testing, I created the index on Prod and after this little change CPU Utilization graph had to say it all.

Screen Shot 2018-12-09 at 11.58.06 AM

3 thoughts on “PostgreSQL – CPU Utilization and Index

  1. Hi Anand,

    Awesome !!!.

    Can you share the all the script you have used for trouble shooting the performance issue. (Pgsnap & hist_pg_stat_statements)

    1. Hi Jitendra,

      Thank you for visiting the blog. I will try and post the scripts on github and share the link. It might take some time though.

      Regards,
      Anand

      1. Hi Anand,

        Came across your post while troubleshooting similar issue. Though your scripts are not attached here but i got some hint on how to resolve it. Would love to have these scripts if you could put them on github. Thanks in advance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s