🌱 Quentin Pleplé

Debugging Long MySQL queries cheatsheet

Apr 18th, 2014

See running queries

To see list of running queries use the command SHOW FULL PROCESSLIST.

mysql> SHOW FULL PROCESSLIST;
+-------+------+-----------------+-------+---------+------+-------+-----------------------+
| Id    | User | Host            | db    | Command | Time | State | Info                  |
+-------+------+-----------------+-------+---------+------+-------+-----------------------+
| 37723 | root | localhost       | NULL  | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
+-------+------+-----------------+-------+---------+------+-------+-----------------------+

Column Info shows the MySQL query.

Column Time shows the running time of the query.

Column State shows the state of the query. Here are some possible values:

  • NULL: query terminated
  • copy to tmp table: copying the table in memory to do an operation on it
  • Locked: waiting to have access to the table

Locking and Mutex Information

To get a some useful information about locks, use the command SHOW ENGINE INNODB STATUS.

mysql> SHOW ENGINE INNODB STATUS;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
110514 19:44:14 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9014315, signal count 7805377
Mutex spin waits 0, rounds 11487096053, OS waits 7756855
RW-shared spins 722142, OS waits 211221; RW-excl spins 787046, OS waits 39353
...

Timeout value

Here is how you inspect the value for query timeout:

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120   |
+--------------------------+-------+

You can change this value in /etc/mysql/my.cnf by adding or editing the line:

innodb_lock_wait_timeout = 200

Comments