question

sqluser-0880 avatar image
0 Votes"
sqluser-0880 asked AnuragSharma-MSFT commented

High CPU driving query in azure database for postgresql

Team,

i have azure database for postgresql and CPU is gradually increasing and i have query store enabled and i cannot increase the resources due to cost limitation.

How to get list of queries responsible for consuming CPU from during a specific period?. I'm looking for exact Query store queries which i can directly run in 'azure_sys' database. the below link doesn't have any QS queries.

https://docs.microsoft.com/en-us/azure/postgresql/concepts-query-store

The query performance insights just gives high duration queries and doesn't give which query is driving the CPU..




Appreciate the help. thanks

azure-database-postgresql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sqluser-0880, welcome to Microsoft Q&A forum.

We are looking into this and will get back at the earliest.

0 Votes 0 ·

Hi @sqluser-0880, thanks for your patience. As such there is directly no way I could find where in we can get the exact query for CPU utilization. However we can first use the pg_stat_statements to check which queries are taking longer to execute. We can thus optimize those queries and check the CPU usage.

You can also run the below query to check the long running transaction as per the article.

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


0 Votes 0 ·

0 Answers