It is important to know which queries are consuming your resources, that will help you to tune your system for the workload needed. In this article we will discuss how to find high CPU utilization query in PostgreSQL?
High CPU utilization also causes other problems as well. High CPU utilization means one or more queries are blocking other queries because they consume high amount of resources on the system.
Other problems high CPU utilization in Postgresql can cause: If you hit 100% on all cores even your SSH / RDP connection might fail. Also operating system core tasks might not end up successfully.
How to find high cpu utilization query in PostgreSQL?
PostgreSQL’s design is really simple, and made for users not for computers. In this case, we will leverage Postgresql’s design to understand how to find high cpu utilization query in PostgreSQL?
How to Find PostgreSQL Connection?
Each PostgreSQL connection spawns a process on the server side. That means, you if a connection is running a query with high cpu utilization must be owned by a process, which is a connection.
Let’s try to show you what i mean. First of all, i run ps -ef | grep postgres command and here is the output:
Processes above are the standard PostgreSQL processes. Let’s run dbeaver (as a PostgreSQL client) and re-check the list:
Take a look to process 133, 134 and 135, they have a source ip adress and source port information. Process ID on OS level, matches the process id in PostgreSQL.
Finding Activity of a Process
As said, each connection spawns a process on OS level. Process ID of that process matches with the process id in PostgreSQL. Previous screenshot show us 133, 134 and 135, let’s query them from PostgreSQL.
select * from pg_catalog.pg_stat_activity where pid in (133, 134,135);
And output is:
If i wrote this query as:
select pid, query from pg_catalog.pg_stat_activity where pid in (133, 134,135);
I would end up finding queries run by those processes.
High CPU Utilization Queries
Until here, we discussed how to find a connection, process id and how to search it’s activity from pg_stat_activity table. My test server doesn’t contain enough data, connection and queries but i will try to explain.
On Linux/Unix systems we have the command called top, which show most resource consuming processes. Here is a simple output for the top command:
PID 195 is consuming 22% of the CPU resources. Let’s query this from pg_stat_activity view.
select * from pg_catalog.pg_stat_activity where pid in (195);
If you scroll to right and check the query column, that will show our query:
select sqrt(generate_series(1, 10000000000000)), generate_series(1, 10000000000000)
And that is answer of the question how to find high cpu utilization query in PostgreSQL. As you can see, we found PID and searched it from pg_stat_activity view.
Terminating High CPU Utilization Queries
Since each connection is a operating system we can send SIGINT or SIGTERM
PostgreSQL has a table about server signalling functions.
PostgreSQL Documentation
pg_cancel_backendandpg_terminate_backendsend signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.
As you can see you can use PostgreSQL’s functions pg_cancel_backend and pg_terminate_backed to top the running queries processes.
Read More About High CPU Utilization Query
Please check my article about Postgres Monitor Query Performance to learn more about monitoring and maintaining query performance in PostgreSQL.
Monitoring high cpu utilization queries is important for all database engines, high CPU utilization means there is a blockage.
Hi! I’m an IT Specialist
I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.
Get in touch with me.
Leave a Reply