Azure Mysql Flexi server Slow Logs diagnostic settings captures <10s

Question

Hello,

Good day!

In Flexi server, I recently change long_query_time parameter from 1 to 10s, but still my diagnostic settings captures queries which is < 10s.

Why and how to fix this?

Below is the answer from Q&A Assist

there could be a few reasons for this:

1. Parameter Not Applied: The change to the long_query_time parameter may not have been applied to existing connections. It is recommended to reconnect to the Azure Database for MySQL Flexible Server or restart the server to ensure that the new parameter value is applied.
2. Logging Configuration: Check if there are other logging configurations that might be affecting what is being captured. For example, if log_queries_not_using_indexes is enabled, it could log queries regardless of their execution time.
3. Caching: If queries are being cached or if there are other performance optimizations in place, the behavior of query logging might not reflect the expected results immediately.

Would like to understand if #2 is causing this issue.

Thanks for the support.

Answer 1

Hi @Alex,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are experiencing an issue where your diagnostic settings are capturing queries that are less than 10 seconds, even though you have changed the long_query_time parameter to 10 seconds. Based on the information provided, let's focus on the second point regarding logging configuration.

The log_queries_not_using_indexes parameter could indeed be causing this issue. When this parameter is enabled, it logs queries that do not use indexes, regardless of their execution time. This means that even if a query takes less than 10 seconds, it will still be logged if it does not use an index.

Here are some steps you can take to address this issue:

• Check the log_queries_not_using_indexes Parameter: Verify if this parameter is enabled. If it is, you can disable it to prevent logging of queries that do not use indexes.
• Review Other Logging Configurations: Ensure that there are no other logging configurations that might be affecting what is being captured. For example, the log_slow_admin_statements parameter logs administrative statements regardless of their execution time.
• Reconnect or Restart the Server: As mentioned in the Q&A Assist response, the change to the long_query_time parameter may not have been applied to existing connections. Reconnecting to the Azure Database for MySQL Flexible Server or restarting the server can help ensure that the new parameter value is applied.

Please refer to the below mentioned link for more information.

https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-monitor-mysql

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.