Applicable scope
- ApsaraDB RDS for PostgreSQL
When you use the ApsaraDB RDS for PostgreSQL, you may encounter high or even 100% CPU utilization . This article describes the common causes and solutions by analyzing typical scenarios in which the CPU utilization is 100%.
Take note of the following items:
When you encounter a 100% CPU utilization , check whether the active connection spikes during your business peak hours and the reserved resources of the database are insufficient. You must verify that the number of active connections is much greater than usual when the problem occurs. For an ApsaraDB RDS for PostgreSQL instance, you can view the connection change to the database in the monitoring and alerts section in the ApsaraDB RDS console. For the current active connection, you can connect to the database and run the following statement to query:
select count( * ) from pg_stat_activity where state not like '%idle';
Track slow SQL statements
If the number of active connections changes in a normal range, the cause may be a large number of slow SQL statements. In this case, to further analyze the cause, you can use slow query logs of ApsaraDB RDS to identify time-consuming statements. However, when this problem occurs, the entire system stands still and all SQL statements slow down. Therefore, it is difficult to identify the slow statements that cause the problem because lots of slow statements may be recorded in the logs. The following section describes methods for tracking slow SQL statements:
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
select * from pg_stat_statements order by total_time desc limit 5;
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
select datname,
usename,
client_addr,
application_name,
state,
backend_start,
xact_start,
xact_stay,
query_start,
query_stay,
replace(query, chr(10), ' ') as query
from
(select pgsa.datname as datname,
pgsa.usename as usename,
pgsa.client_addr client_addr,
pgsa.application_name as application_name,
pgsa.state as state,
pgsa.backend_start as backend_start,
pgsa.xact_start as xact_start,
extract(epoch
from (now() - pgsa.xact_start)) as xact_stay,
pgsa.query_start as query_start,
extract(epoch
from (now() - pgsa.query_start)) as query_stay,
pgsa.query as query
from pg_stat_activity as pgsa
where pgsa.state != 'idle'
and pgsa.state != 'idle in transaction'
and pgsa.state != 'idle in transaction (aborted)') idleconnections
order by query_stay desc
limit 5;
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';
Note: You can also run the following statement to locate queries that are related to the tables by using the pg_stat_statements plug-in:
select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;
Handle slow SQL statements
For the slow SQL statements obtained by using the preceding methods as shown in the following examples, you must terminate them to restore your business.
select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
If the slow SQL statements are necessary for your business, you must optimize them by using the following methods:
ANALYZE [$Table]
or VACUUM ANZLYZE [$Table]
statement in the table that is related to the obtained slow SQL statements to update the statistics of the tables. This way, query execution plans can be more accurate. To minimize the impact on your business, we recommend that your run the statements during off-peak hours of the business.
Note: [$Table] represents the table that is related to the slow SQL statements.
explain [$Query_Text]
explain (buffers true, analyze true, verbose true) [$Query_Text]
Note: [$Query_Text] represents SQL files or statements.