Understanding MySQL Slow Query Logs for Database Performance

1
(No Transcript)
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. Lets 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 doesnt stop there.
We can also look at the competitors websites.
Now, lets 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 its 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.   
7
(No Transcript)
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 wont find any files that provide details
about queries and its execution time. Now, lets
assume that you have enabled logging, but you
havent configured anything. In that case, the
following default behavior can be expected 
9

1. The default time for a query to be considered
   slow is 10 seconds. 
2. Queries that do not use indexes for lookups are
   not logged.
3. Administrative commands are not logged
   irrespective of how slow they run. 
4. 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 .cnf file. 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. 
14
(No Transcript)