If CPU utilization increases but the number of active connections is within the normal range, multiple slow queries with low performance may occur. These slow queries may consume much CPU for a long period of time, resulting in an increase in CPU utilization.
Discover slow queries of long execution duration
The pg_stat_statements extension can record the statistics of all SQL statements on the database server during the optimization and execution phases.
Because the extension uses the shared memory, the extension name is added to the shared_preload_libraries parameter.
Note If the pg_stat_statements extension is not created in the current database, execute the following statement to create the extension. The functions and views provided by the extension are also registered in this process:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
The pg_stat_statements extension and the database continue accumulating statistics. To troubleshoot high CPU utilization, you must clear all existing statistics in the database and extension, and then start to collect statistics from now.
-- Clear all existing statistics in the current database.
SELECT pg_stat_reset();
-- Clear all existing statistics collected by the pg_stat_statements extension.
SELECT pg_stat_statements_reset();
Wait one or two minutes for the database and extension to collect sufficient statistics.
After the statistics are collected, execute the following statements to query the top five SQL statements in terms of execution duration.
SELECT * FROM pg_stat_statements ORDER BY total_plan_time DESC LIMIT 5;
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
Discover slow queries of high buffer reads
If no index is created for a table and point queries are often performed on the table, full scans are required. All records are filtered in memory by using specified conditions, which significantly increases CPU utilization.
Execute the following statement to query the top five SQL statements in terms of buffer reads based on the statistics of the pg_stat_statements extension:
SELECT * FROM pg_stat_statements
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 5;
You can also query the tables with high full scans based on the statistics of the built-in system view pg_stat_user_tables in PolarDB for PostgreSQL.
Execute the following statement to query the top five tables in terms of tuples obtained in full scans from tables that have about 100,000 tuples:
SELECT * FROM pg_stat_user_tables
WHERE n_live_tup > 100000 AND seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 5;
Discover slow queries that do not end after a very long period
You can use the built-in view pg_stat_activity to query SQL statements that do not end after a very long period. These SQL statements may cause high CPU utilization.
Execute the following statement to query the top five SQL statements that last a long period and do not end.
SELECT
*,
extract(epoch FROM (NOW() - xact_start)) AS xact_stay,
extract(epoch FROM (NOW() - query_start)) AS query_stay
FROM pg_stat_activity
WHERE state NOT LIKE 'idle%'
ORDER BY query_stay DESC
LIMIT 5;
Execute the following statement and consider the aforementioned tables with high full scans to obtain the slow queries that last more than 10 seconds.
SELECT * FROM pg_stat_activity
WHERE
state NOT LIKE 'idle%' AND
query ILIKE '%Table name%' AND
NOW() - query_start > interval '10s';