Command to find CPU Spike for PostgreSQL single server.

Question

I am seeking a particular command or query that can furnish me with details about the query responsible for causing 100% CPU usage in Azure Database for PostgreSQL single server.

The query store is already enabled on the server and also aware of Query Performance Insight  which can give me a Query ID but my requirement is to find which queries are causing 100% CPU.

Answer 1

@KT Thank you for reaching out.

My understanding is that you are trying to identify the query causing the 100% CPU spike. Please note that High CPU utilization can be because of several reasons which includes increased workload, slow running queries, increased number of connections on server, sub optimal statistics in tables because auto vacuum is not catching with workload.

If you are looking to find the top queries or query causing the high CPU. For single server, you can get the query ID and query query_store.query_texts_view view as described in the document I shared below, for flexible the same applies to single server as well.

 https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-high-cpu-utilization?tabs=postgres-13

How to get the query Text for the Queryid

1. You can connect to the server using database azure_sys (here Query Store is installed), example psql -h ServerName.postgres.database.azure.com -U AdminUsername -d azure_sys
2. Use the generated GetQueryText column to retrieve the Query Text

Use below SQL to retrieve the query text for given Queryid

Get query_text from QueryId

Example: SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id=16950.

Please let me know that helps.

Regards,

Oury