PostgreSQL in Azure loading high CPU lately

Question

Our system was running fine until the last few days it has been very slow. I checked and noticed that PostgreSQL has been getting full CPU load lately. Can you help me what happened, is there something running in the background that I don't know about?

Answer 1

Hi @Hong Phuc Nguyen

Thanks for the Question and using Microsoft Q&A

As per my understanding, you are facing performance issues, PostgreSQL has been getting full CPU usage.

Common Causes of High CPU Usage in PostgreSQL

Some transactions were getting stuck and running since long time. Hence CPU utilization got increased high usage. Following command helped to find out the connections running for the longest time:

SELECT max(now() - xact_start) FROM pg_stat_activity
                           WHERE state IN ('idle in transaction', 'active');

This command shows the amount of time a connection has been running.

Here are some cases met that cause high CPU usage of Postgres.

Incorrect indexes are used in the query

Check the query plan - Through EXPLAIN, we could check the query plan, if the index is used in the query, the Index Scan could be found in the query plan result.

Solution: add the corresponding index for the query SQL to reduce CPU usage

Check EXPLAIN (analyze, buffers) - If the memory is insufficient to do the sorting operation, the temporary file could be used to do the sorting, and high CPU usage comes up.

Note: DO NOT "EXPLAIN (analyze)" in a busy production system as it actually executes the query behind the scenes to provide more accurate planner information and its impact is significant

Solution: Tune up the work_mem and sorting operations

Long-running transactions

SELECT  pid
, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) >  interval '2 minutes';

Inefficient Queries: Complex or poorly optimized queries can consume significant CPU resources. For instance, a query that leads to a full table scan instead of using an index can lead to high CPU usage.

High Traffic Volume: High numbers of simultaneous connections can lead to increased CPU usage. Each connection requires certain CPU resources to manage, and more connections mean more CPU usage.

Database Design Issues: Incorrectly structured databases can cause excessive CPU usage. Issues could include poorly distributed data, improper use of indexes, sub-optimal data or lack of partitioning.

Configuration Missteps: PostgreSQL is highly configurable, but incorrect settings can lead to high CPU usage. This might include inappropriate memory settings or lack of connection limits.

Troubleshooting and Resolving High CPU Usage in PostgreSQL

Detecting High CPU Usage in PostgreSQL:

High CPU usage can be detected by understanding and using tools such as ‘pg_stat_activity’ and ‘pg_stat_user_tables’. External monitoring tools can also be used to help identify high CPU usage issues.

Example: To find currently running queries, use the pg_stat_activity view:

SELECT * FROM pg_stat_activity;

Resolving High CPU Usage: Strategies and Tools

There are several strategies and tools available for resolving high CPU usage in PostgreSQL. These include:

Query Optimization Techniques: Improving the efficiency of queries can significantly reduce CPU usage. This may include rewriting queries, using prepared statements, or using proper indexing.

Database Tuning: PostgreSQL is highly configurable, and adjusting settings appropriately can reduce CPU usage.

Using Indexes: Properly utilizing indexes can make data retrieval more efficient, reducing the need for expensive full table scans.

Partitioning: Partitioning larger into smaller, more manageable pieces can improve query performance and reduce CPU usage.

Hardware Upgrade: In some cases, upgrading hardware or adding additional resources may be the best way to handle high CPU usage.

Please refer to the documentation link: PostgreSQL High CPU Usage: Causes and Solutions

Hope this helps. Do let us know if you have any further queries