none
~20s query every 15 minutes on Azure Postgres instance? RRS feed

  • Question

  • Hey folks,

    I've got an Azure Database for PostgreSQL resource set up for a webapp, and I've been seeing errors from the app that the database connections time out a couple times a day.  I checked the logs, and every 15 minutes, a long query is logged that appears to be querying for the size of the whole database.  This query takes around 20 seconds to complete every time, so I suspect it may be to blame for the intermittent timeouts.

    The query is always:

    SELECT
    	      pg_database.datname AS database_name,
    	      pg_database.oid AS database_oid,
    	      pg_database_size(pg_database.datname) AS database_size_bytes
    	    FROM pg_database
    And when I try to run it myself, I also see it take a very long time to run.

    I'm trying to find out what might be doing this (it's not the webapp), and what I can do to stop it.  Has anyone seen this before?  I'm not terribly experienced debugging things like this, so I'm not sure what to investigate next, so any guidance on that would also be very appreciated.

    Thanks!

    Mike Hoyle Senior Platform Engineer @ WE.org

    Wednesday, December 12, 2018 6:34 PM

Answers

  • Hi Mike,

    This query is an administrative query that is intended to be a background process. Can you enable Query Store so that we can get a historical record of what is occuring: Monitor performance with the Query Store

    Azure would like to improve Open Source database services and your detailed feedback is vital in improving the experience. Do you have an Azure Support Plan? If not, I can have a one-time incident ticket created to have this specific issue investigated. Please send your Azure Subscription GUID to AzCommunity at Microsoft.com and instructions will be sent back to you with next steps.

    In the meantime:

    The current approach is to implement application retry logic: Application retry logic is essential

    As you said, you are not experiencing a connection issue but in cases where there is a long running query, retry logic will address this issue. Scaling up to a higher tier during the day might also be an option but you will experience a very minor connection outage during the operation but, could allow for more processing power during higher volume activity. At night, you could scale back down to keep costs in check.

    Regards,

    Mike

    • Marked as answer by hoylemd Tuesday, December 18, 2018 4:45 PM
    Monday, December 17, 2018 5:36 PM
    Moderator

All replies

  • Hi Mike,

    Thanks for your feedback. Could you please go through the Troubleshoot connection issues to Azure Database for PostgreSQL to see if it helps.

    Ref link : https://docs.microsoft.com/en-us/azure/postgresql/howto-troubleshoot-common-connection-issues

    If that doesn't help we can take it further from there.

    Thanks

    Thursday, December 13, 2018 2:58 PM
    Moderator
  • Thanks for the suggestion, but this is not a connection issue.

    Mike Hoyle Senior Platform Engineer @ WE.org

    Thursday, December 13, 2018 3:16 PM
  • Hi Mike,

    This query is an administrative query that is intended to be a background process. Can you enable Query Store so that we can get a historical record of what is occuring: Monitor performance with the Query Store

    Azure would like to improve Open Source database services and your detailed feedback is vital in improving the experience. Do you have an Azure Support Plan? If not, I can have a one-time incident ticket created to have this specific issue investigated. Please send your Azure Subscription GUID to AzCommunity at Microsoft.com and instructions will be sent back to you with next steps.

    In the meantime:

    The current approach is to implement application retry logic: Application retry logic is essential

    As you said, you are not experiencing a connection issue but in cases where there is a long running query, retry logic will address this issue. Scaling up to a higher tier during the day might also be an option but you will experience a very minor connection outage during the operation but, could allow for more processing power during higher volume activity. At night, you could scale back down to keep costs in check.

    Regards,

    Mike

    • Marked as answer by hoylemd Tuesday, December 18, 2018 4:45 PM
    Monday, December 17, 2018 5:36 PM
    Moderator
  • Thanks for the suggestions!

    After further investigation, we determined that these long queries probably aren't to blame for the timeouts.  My intent was mainly to find out where these queries were coming from, and you've answered that.  I did turn on the query store though, that's a feature I was unaware of and will be very helpful.  Retry logic is also a great suggestion.

    We don't have a support plan, but it seems like the query is expected behaviour, and it doesn't appear to be negatively impacting anything so I don't think there's any need for further investigation.  Thanks again for your help!


    Mike Hoyle Senior Platform Engineer @ WE.org

    Tuesday, December 18, 2018 4:45 PM