High CPU utilization adversely affects the performance of your instance.Any activity that's performed on the instance uses CPU. Hence, if there's ahigh CPU utilization notice, you should first identify the root cause of the issue,whether that's poorly written queries, long running transactions,or any other database activity.

This document describes the ways to identify CPU bottlenecks in an instance andmitigate the CPU utilization issues in the instance.

Identify the CPU bottlenecks

The following sections discuss different CPU scenarios.

Use query insights to identify queries that have high CPU consumption

Query insights helps you detect, diagnose, andprevent query performance problems for Cloud SQL databases.

Use thepg_proctab extension

Use thepg_proctab extension with the combination of thepg_top utilityto get operating system outputs that give per process CPU utilization information.

Use queries

The following sections discuss different queries you can use.

Identify the active connections by state

Each active connection to the database takes some amount of CPU, so if the instance has a high number ofconnections, cumulative utilization could be high. Use the following query to getthe information about the number of connections by state.

SELECT  state,  usename,  count(1)FROM  pg_stat_activityWHERE  pid <> pg_backend_pid()group by  state,  usenameorder by  1;

The output looks similar to the following:

        state        |    usename    | count---------------------+---------------+------- active              | ltest         |   318 active              | sbtest        |    95 active              |               |     2 idle                | cloudsqladmin |     2 idle in transaction | ltest         |    32 idle in transaction | sbtest        |     5                     | cloudsqladmin |     3                     |               |     4(8 rows)

If the active connections count is high, check for long running queries or wait eventsthat are blocking the queries from executing.

If the idle connection count is high, execute the following query to terminate the connections,after taking the necessary approvals.

SELECT  pg_terminate_backend(pid)FROM  pg_stat_activityWHERE  usename = 'sbtest'  and pid <> pg_backend_pid()  and state in ('idle');

You can also terminate the connections individually withpg_terminate_backend using the following query:

SELECT pg_terminate_backend (<pid>);

Here, you can obtain the PID frompg_stat_activity.

Identify the long running connections

Here's an example of a query that returns long running queries.In this case, you can identify the queries that have been active for more than 5 minutes.

SELECT  pid,  query_start,  xact_start,  now() - pg_stat_activity.query_start AS duration,  query,  stateFROM  pg_stat_activityWHERE  (    now() - pg_stat_activity.query_start  ) > interval '5 minutes' order by 4 desc;

Review the explain plan to identify poorly written queries

Use the EXPLAIN PLAN to investigate a poorly written query and rewrite the query, if necessary.Optionally, consider cancelling the long running query with the following command with necessary approvals.

SELECT pg_cancel_backend(<pid>);

Monitor VACUUM activity

The AUTOVACUUM activity that clears the dead tuples is a CPU-intensive operation. If your instance uses PostgreSQL version 11 or later,use the following query to check if there's any active AUTOVACUUM or VACUUM activity in progress.

SELECT  relid :: regclass,  pid,  phase,  heap_blks_total,  heap_blks_scanned,  heap_blks_vacuumed,  index_vacuum_count,  max_dead_tuples,  num_dead_tuplesFROM  pg_stat_progress_vacuum;

Check if there's an ongoing VACUUM activity in an instance using the following query:

SELECT  pid,  datname,  usename,  queryFROM  pg_stat_activityWHERE  query like '%vacuum%';

Additonally, you canoptimize and troubleshoot VACUUM operations in PostgreSQL.

Add pg_stat_statements extension

Set up thepg_stat_statements extension to get enhanced dictionary information about instance activity.

Frequent checkpoints

Frequent checkpoints degrade performance. Consider adjusting thecheckpoint_timeout flag if the PostgreSQL alert log reports thecheckpoint occurring too frequently warning.

Gather statistics

Ensure that the query planner has the latest statistics about tables to choose the best plan for queries.The ANALYZE operation collects statistics about the contents of tables in the database, andstores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.The AUTOVACUUM process automatically analyzes the tables periodically, so run the following command to check if all the tables have been analyzed and have the latest metadata available to the planner.

SELECT  relname,  last_autovacuum,  last_autoanalyzeFROM  pg_stat_user_tables;

Inadequate system settings

There are other factors and flag settings or system factors that influence theperformance of your query. Run the following query to check the wait events andwait event type to get the insights about the performance of other system settings.

SELECT  datname,  usename,  (    case when usename is not null then state else query end  ) AS what,  wait_event_type,  wait_event,  backend_type,  count(*)FROM  pg_stat_activityGROUP BY  1,  2,  3,  4,  5,  6ORDER BY  1,  2,  3,  4 nulls first,  5,  6;

The output looks similar to this:

 ..  | .. | what           | wait_event_type |      wait_event      | ..    | count-..--+-..-+----------------+-----------------+----------------------+-..----+------ .. ..  | .. | active         | IO              | CommitWaitFlush      | ..    |   750 ..  | .. | idle           | IO              | CommitWaitFlush      | ..    |   360 ..  | .. | active         | LWLock          | BufferMapping        | ..    |   191

Monitor sequential scans

Frequent sequential scans over tables of more than a few tens of rows usuallyindicate a missing index. When the scans touch thousands or even hundreds ofthousands of rows, it can cause excessive CPU usage.

Frequent sequential scans on tables with hundreds of thousands of rows can causeexcessive CPU usage. Avoid sequential scans on such tables by creating necessaryindexes.

Run the following query to check the number of times sequential scans areinitiated on any table.

SELECT  relname,  idx_scan,  seq_scan,  n_live_tupFROM  pg_stat_user_tablesWHERE  seq_scan > 0ORDER BY  n_live_tup desc;

Lastly, if the CPU is still high and you feel those queries are legitimate traffic then consider increasing the CPU resources in your instance to avoid database crash or downtime.

What's next

• Google Cloud recommenders