question

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

Alert for Long running/blocking queries for azure database for postgresql

Hello Experts.

We have a azure database for postgresql hosted and we would like to have an alert email sent for
long running queries and blocking happening on PAAS instance. can you please suggest with queries and steps? . We have already enabled diagnostic logging on PAAS instance and logs are getting stored on log analytics workspace.




azure-database-postgresql
· 4
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

Have you checked the concept of Query Store in Azure Database for Postgresql. Please go through this document for more information and let us know if you have any questions.

Regards
Navtej S


0 Votes 0 ·

@sqluser-0880 Please let us know if you need further help.

0 Votes 0 ·

yes we do have query store enabled by default and we can get the data from query store. We are looking for a way to send an email to users on daily basis on list of bloacking queries and long running queries.Is there a way to satisfy this requirement with output of long running queries from through query store feature? .

0 Votes 0 ·
Show more comments

1 Answer

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered sqluser-0880 commented

@sqluser-0880

Here is the answer from our team.

"We don't have a native notification service tied to Intelligent Performance/ Query Store but we do expose APIs that will allow you to query the data periodically. They could also query the azure_sys database directly using sql. Building a workflow on top of either is obviously one choice. The cons here is it will put load on top of your OLTP workload for the duration your Query Store query runs.
The second way to achieve this is to integrate with Log analytics. Query Store data is published to Log Analytics so that customers can integrate easily with the different popular third party monitoring apps. If your Cx uses one of these apps and they have a notification service, that's another way to go about this. This has zero impact on your core OLTP workload.
If the Cx has a read replica set up, realize that QS data is replicated to the replica as-is from the primary. The cons here is: load on replica and this will have as much lag as replication lag."

Here are three options to achieve this. Please check and let us know if any questions.

Regards
Navtej S


· 4
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.

Thanks Navtej for the response. Appreciate.

I have one question on Log analystics part. When we check the azurediagnostics table, we just see query_id column and cannot see Query name (actual querries) to see the queries which ran on the PAAS instance. Just seeing query id, one cannot make out what are those queries running?. How do we get the query text in Azurediagnostics table..

0 Votes 0 ·

@sqluser-0880 We don’t stream query text to LA b/c it’s PII data. You can find the query text corresponding to a query id by querying the azure_sys database directly. The view query_store.query_texts_view has the mapping.

82980-1.png


Please let us know if any other question.

Regards
Navtej S


0 Votes 0 ·
1.png (5.6 KiB)
1.png (5.6 KiB)

@sqluser-0880 Please let us know if you need any further help with this.

0 Votes 0 ·

Hi Navtej,

Is there a way to integrate query store (for query text) and azurediagnostics table in log analytics. bcoz when we would like to see query text as when sending an alert through LA. or any other ideas to get a complete workflow/solution?

Also, for the below comment. what are those APIs..

"We don't have a native notification service tied to Intelligent Performance/ Query Store but we do expose APIs that will allow you to query the data periodically"

0 Votes 0 ·