Identifying slow queries in MySQL

Showing the full process list

To display all current operations that MySQL's processing threads are executing, type:

SHOW FULL PROCESSLIST;

 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 | Id | User            | Host      | db   | Command | Time    | State                  | Info                  |
 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 |  5 | event_scheduler | localhost | NULL | Daemon  | 1834441 | Waiting on empty queue | NULL                  |
 | 50 | root            | localhost | NULL | Query   |       0 | init                   | show full processlist |
 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 2 rows in set (0.00 sec)

The output above shows an idle server with only our own query as well as a long running event listener. An active server would show many more processes, some of which might be long running. Without the FULL modifier, this command will only show the first 100 processes, which may or may not truncate your results depending on your server activity.

Some of the important parts to take a look at are the Time and State columns. The Time column counts the number of seconds that the thread has been in the State mentioned. If you find processes with a Time value that doesn't match your expectations for the given operation, it might be time to investigate further.

Checking storage engine status

Another place to check is the actual storage engine's status.

You can find the storage engine associated with a given table by typing:

SHOW CREATE TABLE <database>.<table>\G

For instance, to show the storage engine that the mysql.time_zone table uses, type:

SHOW CREATE TABLE mysql.time_zone`\G

*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Time zones'
1 row in set (0.01 sec)

The ENGINE=InnoDB indicates that the table is using the InnoDB storage engine. This is the default storage engine in most configurations, so you will likely want to check it's status.

You can show the InnoDB engine's status by typing:

SHOW ENGINE INNODB STATUS\G

 *************************** 1. row ***************************
   Type: InnoDB
   Name:
 Status:
 =====================================
 2022-02-26 11:55:52 139992513984256 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 16 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1835091 srv_idle
 srv_master_thread log flush and writes: 0
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 555
 OS WAIT ARRAY INFO: signal count 457
 RW-shared spins 0, rounds 0, OS waits 0
 RW-excl spins 0, rounds 0, OS waits 0
 RW-sx spins 0, rounds 0, OS waits 0
 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 8778
 Purge done for trx's n:o < 8713 undo n:o < 0 state: running but idle
 History list length 0
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 421467955133656, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 ---TRANSACTION 421467955132848, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 ---TRANSACTION 421467955132040, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
 I/O thread 1 state: waiting for completed aio requests (log thread)
 I/O thread 2 state: waiting for completed aio requests (read thread)
 I/O thread 3 state: waiting for completed aio requests (read thread)
 I/O thread 4 state: waiting for completed aio requests (read thread)
 I/O thread 5 state: waiting for completed aio requests (read thread)
 I/O thread 6 state: waiting for completed aio requests (write thread)
 I/O thread 7 state: waiting for completed aio requests (write thread)
 I/O thread 8 state: waiting for completed aio requests (write thread)
 I/O thread 9 state: waiting for completed aio requests (write thread)
 Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  ibuf aio reads:, log i/o's:, sync i/o's:
 Pending flushes (fsync) log: 0; buffer pool: 0
 1779 OS file reads, 3384 OS file writes, 1870 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf: size 1, free list len 0, seg size 2, 0 merges
 merged operations:
  insert 0, delete mark 0, delete 0
 discarded operations:
  insert 0, delete mark 0, delete 0
 Hash table size 34679, node heap has 2 buffer(s)
 Hash table size 34679, node heap has 9 buffer(s)
 Hash table size 34679, node heap has 9 buffer(s)
 Hash table size 34679, node heap has 15 buffer(s)
 Hash table size 34679, node heap has 7 buffer(s)
 Hash table size 34679, node heap has 3 buffer(s)
 Hash table size 34679, node heap has 4 buffer(s)
 Hash table size 34679, node heap has 6 buffer(s)
 0.00 hash searches/s, 0.00 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number          38540718
 Log buffer assigned up to    38540718
 Log buffer completed up to   38540718
 Log written up to            38540718
 Log flushed up to            38540718
 Added dirty pages up to      38540718
 Pages flushed up to          38540718
 Last checkpoint at           38540718
 2197 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total large memory allocated 0
 Dictionary memory allocated 473011
 Buffer pool size   8192
 Free buffers       6241
 Database pages     1896
 Old database pages 719
 Modified db pages  0
 Pending reads      0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 8, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 1754, created 142, written 926
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the last printout
 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 LRU len: 1896, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Process ID=59529, Main thread ID=139992560166656 , state=sleeping
 Number of rows inserted 0, updated 0, deleted 0, read 0
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 Number of system rows inserted 0, updated 317, deleted 0, read 6128
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================
 
 1 row in set (0.00 sec)

The output will contain a large amount of information about the resources the engine is using, the processes being executed, and more. You can use this to get an idea of whether there is a bottleneck in execution or whether the number of processes in contention are causing performance issues.

Check if MySQL is logging slow queries

The first thing you should do is verify the current state of slow query logging. If slow query logging is already enabled, you don't have to do anything.

You can check if slow query logging is enabled by typing:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

 +---------------------+------------------------------------+
 | Variable_name       | Value                              |
 +---------------------+------------------------------------+
 | slow_query_log      | OFF                                |
 | slow_query_log_file | /var/lib/mysql/mysqlutils-slow.log |
 +---------------------+------------------------------------+
 2 rows in set (0.01 sec)

The above output indicates that slow queries are currently not being logged because the functionality is switched off.

If slow query logging is on, your output will look something like this instead:

 +---------------------+-------------------------------+
 | Variable_name       | Value                         |
 +---------------------+-------------------------------+
 | slow_query_log      | ON                            |
 | slow_query_log_file | /var/log/mysql/mysql-slow.log |
 +---------------------+-------------------------------+
 2 rows in set (0.02 sec)

Now that you know the current state, you can change it as necessary.

Configure MySQL to log slow queries

Before we move on, it is important to note that while slow query logging is incredibly useful, it can potentially have an additional performance impact. MySQL must perform additional operations to time each query and to record the results to a log. This can impact performance and fill up hard drive space unexpectedly.

It may not be a good idea to log slow queries at all times. Instead, enable the functionality when you are actively investigating an issue and disable it when you are finished.

With that in mind, you can configure slow query logging by modifying the MySQL server's configuration file. You can also modify these values interactively, but setting good defaults in the configuration will make it easier to tweak interactively later.

Open MySQL's configuration file. On most Debian Linux-based systems, the configuration file will be located at /etc/mysql/mysql.conf.d/mysqld.conf:

sudo vim /etc/mysql/mysql.conf.d/mysqld.conf

We will want to modify or potentially add the following settings:

So, for example, if we wanted to turn all of the optional logging on and log any statement that examines at least 100 rows and takes 2 seconds or longer to execute, we could use these settings:

; enable MySQL to log slow queries
slow_query_log                  = ON
; query log location
slow_query_log_file             = /var/log/mysql/mysql-slow.log
; amount of seconds that a query must surpass to be logged
long_query_time                 = 2
; minimum number of rows affected for a query to be logged
min_examined_row_limit          = 100

; include administrative commands in the logs
log_slow_admin_statements       = ON
; also log queries that are not using indexes
log_queries_not_using_indexes   = ON
; for MySQL servers version 8.0.14 or later, we can
; get extra information by setting this
log_slow_extra                  = ON
; Also log slow statements that have executed on
; the replica
; This only will log statements if `binlog_format` is
; set to `STATEMENT` or `MIXED` and the statement is
; logged in statement format
; RE: https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_log_slow_replica_statements
; This option name depends on your MySQL version:
;   versions >= 8.0.26: log_slow_replica_statements
;   versions < 8.0.26: log_slow_slave_statements
; log_slow_slave_statements   = ON
log_slow_replica_statements   = ON

After saving and closing the file, you can validate your configuration changes by typing:

sudo mysqld --validate-config

If no errors are returned, your MySQL server configuration file is syntactically valid. You can restart the MySQL server process by typing:

sudo systemctl restart mysql

You can validate that slow querying is enabled now by re-running the original discovery query:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

Once you have slow querying configured how you want, you can enable and disable it as needed within MySQL itself. The syntax for adjusting the values looks like this:

SET GLOBAL slow_query_log = 'OFF';