Understanding MySQL Slow Query Logs for Database Performance

2. You may have heard that the poor performance of databases is mostly due to slow queries. Determining if a query is slow is not a static process. Instead of applying rules, standards, or industry metrics, we use business requirements and defining metrics that suits our needs. MySQL is one of the most widely used databases today. They have built some standard interfaces to help businesses apply their custom metrics and identify the slow queries. The result of such an interface is the MySQL slow query logs.  These logs help us DBAs and database developers during MySQL performance tuning to identify and analyze the slow queries. We then recommend changes or optimizations to make the query run much faster. In this PPT, we will look into what the MySQL slow query logs show us. We will also look into configuring it and ways to use it. 

3. What is a Slow query?  A slow query is a relative measure that depends on many factors. So, how can we define a slow query? A SQL query being slow has definitions that differ from one application to another. For any given application, we have to look at the business requirements to define a threshold beyond which all queries will be considered slow. Let’s take an example and determine what a slow query will be for that use case.

4. Assume that you are dealing with a news website with hundreds of thousands of viewers each month. To determine what a slow query would mean, we can first look at the industry guidelines for page load times. Here, a webpage must load within 3 seconds if online readers are to be retained. If rendering the UI components on the webpage takes 1.5 seconds, and it takes 0.5 seconds to send the data over the internet, then the query must take no more than 1 second to execute to stay within the 3-seconds limit. Based on this calculation, we can conclude that a query execution time above 1 second is considered slow for our news website application.  However, it doesn’t stop there. We can also look at the competitors’ websites. Now, let’s assume that the top news websites have a load time of 2.5 seconds. To stay competitive on the top, our news website must load within 2.5 seconds as well, the query should execute within 0.5 seconds. This becomes a business decision. If the business decides it wants to stay competitive with other news websites and not just meet industry standards, the slow query threshold needs to be reduced from 1 second to 0.5 seconds.  We can use these methods and work with business requirements to define what a slow query means for the business to function optimally. 

5. Introducing The MySQL Slow Query Logs The log file is a simple text file containing details about slow running SQL queries. The file itself is the output based on the configuration we have provided to the MySQL server. 

6. The image above shows a sample MySQL slow query log file. As we can see, it contains some key metrics like time taken, the number of rows searched and returned, and also the number of rows edited. The metadata about the query is required to identify why it’s slow and find out what we can do to make it run faster. In the next sections, we will look at configuring and generating log files. 

8. Getting the log file setup involves a few steps. First, we configure it and then we enable it. MySQL provides straightforward ways to customize the log file so we can get the information quickly.  Configuration involves setting the required fields and parameters.  Step #1 Understanding The Default Behaviour Before we start with the configurations, we need to understand what MySQL does by default. It will help us to decide on those configurations we want to change.  By default, logging slow queries is disabled, and so you won’t find any files that provide details about queries and its execution time. Now, let’s assume that you have enabled logging, but you haven’t configured anything. In that case, the following default behavior can be expected: 

9. The default time for a query to be considered slow is 10 seconds.  Queries that do not use indexes for lookups are not logged. Administrative commands are not logged irrespective of how slow they run.  If a file name is not given, the MySQL server creates a file with syntax “hostName-slow.log.” It is stored in the data directory by default. 

10. Main Configurations For The Log File To add configurations, we can use two methods. Provide the options while starting the MySQL server, or we can provide the options in a .cnffile. The .cnf file is a simple text file that the database uses to store config in it. In this article, we will use the my.cnf file.

11. The following options can be configured in the my.ncf file:  slow_query_log - It is used to enable or disable the MySQL slow query logs. A value of 0 is used to disable, while 1 is used to enable the logs. long_query_time - The long_query_time option is the threshold above which a query is considered to be slow. It can be configured to microseconds precision. slow_query_log_file - The path to the log file can be set using this option. min_examined_row_limit - If the query under investigation needs to consider the number of rows it should scan, this option can be used. log_slow_admin_statements & log_queries_not_using_indexes - These two options are used when admin statements such as ALTER_TABLE, ANALYZE TABLE, etc. need to be analyzed for slow running.  MySQL provides many other parameters to determine if a query is slow or not. For more details, you can refer to the MySQL official documentation. The image below shows how a sample my.cnf file would look like:

12. Contents Of The Log File The following parameters are added to the query log file for every query that has been detected to be slow.    Query Time - Time in seconds it took to run that particular query.  Rows Sent - The total number of rows returned by the query Lock Time - The time it took to acquire the required locks before performing the operations Rows Examined - The total number of rows that the query searched. These parameters can be examined by database administrators to explore the slow queries and recommend approaches to rectify them. 

13. Conclusion MySQL slow query logs are among the most widely used tools for manual and automated SQL query tuning processes. DBAs, developers, and even automated tools use the log files to analyze the queries and extract necessary information.  We have seen how we can configure and enable the slow query file. We have also seen what we can expect as an output, and it can be used. These logs are structured, too, making it easier to parse and analyze.  We recommend enabling these for applications and regularly monitoring them as a proactive measure to keep the database running efficiently.