MySQL Slow Query Log

What is the Slow Query Log?

The slow query log is one of the complex components in the optimization of the performance of the database. It captures SQL statements that are taking longer time to execute than the mentioned duration and is named 'long_query_time'. It also works on 'min_examined_row_limit'. Database administrators can find the bottlenecks by using the 'mysqldumpslow' command, which is helpful for monitoring long, slow query logs.

How to enable Slow Query Log?

Slow queries can impact the performance of databases and servers as a whole. You can log queries in MySQL that take longer than a predetermined amount of time by using the slow query log function. This makes it much easier to identify time-consuming or ineffective searches.

Use these methods to make the slow query enabled.

Step 1: First, Log in to your server.
Step 2: Type the below command at the command line.

Step 3: Then, type the MySQL Root Password.
Step 4: Enter the following command at the mysql> prompt to enable the slow query log:

Step 5: The slow query log feature automatically logs queries taking over 10 seconds, which can be adjusted by changing the interval using the command X.

The default slow query log file is located in /var/lib/mysql/hostname-slow.log. Modify the log path or filename using the following command:

Step 6: To ensure the slow query log is functioning properly, exit and return to the MySQL software, reload session variables, and change X to a higher number than the long query time parameter.

Step 7: To find out which queries take a long time to execute, keep an eye on the slow query log file.
Step 8: After completing the debugging, turn off the slow query log. To accomplish this, launch the MySQL software once more and then enter the following command:

What are Slow Query Parameters and Contents?

Slow Query Parameters

For extended query time, the minimum and default values are 0 and 10, respectively. You can specify the value down to the microsecond level of precision.

Query types that do not require index lookups are not reported by default, nor are administrative statements. As will be discussed later, log_slow_admin_statements and log_queries_not_using_indexes can be used to modify this behavior.
The slow query log is disabled by default but can be set to 0 or 1 using the command --slow_query_log[={0|1}], with the log file name specified using the log_output system variable.
The server defaults to host_name-slow.log for slow query log files, and unless an absolute path name is provided, the file is created in the data directory.
Use global slow_query_log and slow_query_log_file system variables to enable or disable the slow query log and modify its file name during runtime. To define the log file name, set slow_query_log_file. The new file is opened, and any open log files are closed.
If you set the --log-short-format option, the server writes less data to the sluggish query log.
Enable the log_slow_admin_statements system variable to add slow administrative statements to the query log, including alter table, analyze table, check table, create index, drop index, optimize table, and repair table.
Enable the log_queries_not_using_indexes system variable to include queries that do not use indexes for row lookups in the sluggish query log statements. This setting prevents the server from recording searches with fewer than two rows, where an index is not useful.
Slow query logs can get very large when requests without an index are reported. By modifying the log_throttle_queries_not_using_indexes system variable, it is feasible to impose a rate limit on certain queries. Since this variable has a value of 0 by default, there is no limit. Positive numbers set a restriction on the number of queries that can be logged per minute without using indexes.
The server logs inquiries up to the specified limit during the first 60 seconds after receiving this type of query, after which it suppresses further searches. When the window closes, the server logs a summary indicating the number of suppressed queries and the total amount of time spent on them. When the server logs the subsequent inquiry that does, the subsequent 60-second window starts.
When deciding whether to submit a query to the sluggish query log, the server considers the following controlling parameters in order:
1. Either log_slow_admin_statements must be enabled, or the query cannot be an administrative statement.
2. In order for log_queries_not_using_indexes to be enabled and for the query to utilize no indexes for row lookups, the query must have taken at least long_query_time seconds.
3. At least min_examined_row_limit rows had to be examined by the query.
4. According to the log_throttle_queries_not_using_indexes option, the query cannot be silenced.
The log_timestamps system variable manages the time zone of timestamps in messages sent to the slow query log file, general query log file, and error log. The general query log and slow query log messages written to log tables are unaffected by it in terms of time zone; however, rows received from such tables can be converted using CONVERT_TZ() or by altering the session time_zone system variable from the local system time zone to any desired time zone.
Replicated queries are not automatically written to the slow query log by a replica. You can modify this by setting the log_slow_slave_statements (before MySQL 8.0.26) or log_slow_replica_statements (from MySQL 8.0.26) system variable. It should be noted that these system variables have no effect if row-based replication is enabled (binlog_format=ROW).
The replica's slow query log is only included when a query is logged in statement format in the binary log, either when binlog_format=STATEMENT or binlog_format=MIXED is set or when slow queries are logged in row format when binlog_format=MIXED or binlog_format=ROW is set.

Slow Query Log Contents

The server writes output to specified destinations when a slow query log is enabled, opening the log file and writing startup messages when activated. But unless the FILE log destination is chosen, no more queries are logged to the file. Even with the slow query log enabled, the server does not write any queries if the target is NONE. If FILE is not chosen as an output destination, changing the log file name has no impact on logging.
If the slow query log is enabled and FILE is selected as the output destination, each statement in the log contains the specified fields on a single line.
- uery_time: duration
  The above statement is executed in second.
- Lock_time: duration
  This is used to acquire locks in seconds.
- Rows_sent: N
  This denotes the number of rows locked in seconds.
- Rows_examined
  The row numbers are examined by the server layer.
The server writes the additional fields described below to FILE output in addition to the previously mentioned ones when the log_slow_extra system variable (available as of MySQL 8.0.14) is enabled (TABLE output is unaffected). Some field descriptions make use of the names of status variables. For further details, see the explanations of the status variables. Nevertheless, rather than being cumulative per-session values, the counters in the slow query log are per-statement data.
Enabling log_slow_extra can result in a slow query log file that has a mixture of lines with and without additional information. By counting the fields, log file analyzers can tell if a line has more fields on it.
A SET statement with a timestamp comes before every statement that is written to the slow-paced query log file. The timestamp shows the start of the slow statement execution as of MySQL 8.0.14. Before version 8.0.14, the timestamp records the moment the slow statement (which happens after the statement completes its execution) was reported.
To prevent passwords from appearing in plain text, the server rewrites them in statements posted to the slow query log.