PG高CPU查询语句收集

最近看了一个视频，介绍相关的SQL语句，记录如下

Troubleshooting high cpu utilization in PostgreSQL databases
High CPU usage is often connected to
-long running times
-poor performance
-unexcepted crashes
-bad end-customer experience
-limited scalability of app
-wrong architecture
-unplanned and uncontrolled increase in cost

目录

DB Connections

Distribution of non-idle connections per database

Distribution of non-idle connections per database

---

DB Connections

One of the patterns of PostgreSQL DB leading to high CPU utilizaiton is a high number of active connections.
"Connection Storm" is a very painful pattern for DB 
The following SQL query lists the 
-total number of connections.
-number of non-idle connections.
-number of maximum available connections.
-connections utilization percentage

select 
  A.total_connections,
  A.non_idle_connections,
  B.max_connections,
  round((100 * A.total_connections::numeric / B.max_connections::numeric),
  2) connections_utilization_pctg 
from 
  (select count(1) as total_connections, sum(case when state!='idle' then 1 
  else 0 end) as non_idle_connections from pg_stat_activity) A,
  (select setting as max_connections from pg_settings where 
  name='max_connections') B;

Here's an example of the SQL output:
total_connections | non_idle_connections | max_connections | conn_utilization_pctg
    3457                          3            9057                38.17

To tune the parameter max_connections,
and other key PostgreSQL DB instance parameters 
I recommand using free online tools, like, for example,
PostgreSQL configuration builder at:
https://www.pgconfig.org

Distribution of non-idle connections per database

The following query allows to check 
the distribution of non-idle connections per databases,
sorted in descending order:

select datname as db_name, count(1) as num_non_idle_connections 
from pg_stat_activity 
where state!='idle' 
group by 1 
order by 2 desc;

Here's an exmaple of the SQL output:
db_name    num_non_idle_connections
my_db_1    133
my_db_2      6
my_db_3      3

Distribution of non-idle connections per database

The following sql checks the distribution of non-idle connections 
per database and per query, sorted in descending order 
as seen in the example below:

select datname as db_name, query, count(1) as num_non_idle_connections 
from pg_stat_activity 
where state!='idel' 
group by 1,2 
order by 3 desc;

Here's an example of the SQL output
db_name   |   short_query   |  num_non_idle_connections
db_1        select * from table_1   40
db_1        select * from table_2    1