/
Pack Hosting Panel

Locks & slow queries

How can you recognize MySQL locks and how to deal with it?

In this article, we assume you configured your MySQL command as we explained in the article ‘MySQL’ under “Commandline”.

Locks

For viewing innodb locks, the query SHOW ENGINE INNODB STATUS is used. For security reasons, this is not directly accessible but via an alias command.

innodbstatus

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-02-06 19:55:44 7f62cab21700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 30 seconds
-----------------
BACKGROUND THREAD
-----------------
...
...
...
----------------------------
END OF INNODB MONITOR OUTPUT
============================

This command is useful to view current locks and the queries where these locks come from. In case of a cluster, you need to run this command on the database server. You can do this by creating an ordinary SSH on that server and log in on it.

Running queries

MySQL has multiple purposes to lock a row, table or even a whole database. These locks will always be caused by running queries.

You can view the running queries with the MySQL command and can be stopped if desired.

For example:

mysql -e 'SHOW PROCESSLIST'
+----------+-----------+-------------------+---------------+---------+------+-------+------------------+-----------+---------------+
| Id       | User      | Host              | db            | Command | Time | State | Info             | Rows_sent | Rows_examined |
+----------+-----------+-------------------+---------------+---------+------+-------+------------------+-----------+---------------+
| 70569602 | shop_root | 192.168.0.2:45016 | shop_database | Sleep   |  186 |       | NULL             |      1239 |          6782 |
| 70600067 | shop_root | 192.168.0.2:17014 | shop_database | Sleep   |   61 |       | NULL             |         6 |            12 |
| 70600999 | shop_root | 192.168.0.4:39104 | shop_database | Sleep   |   58 |       | NULL             |         0 |             0 |
| 70601002 | shop_root | 192.168.0.5:44738 | shop_database | Sleep   |   32 |       | NULL             |         0 |             0 |
| 70601003 | shop_root | 192.168.0.5:44748 | shop_database | Sleep   |   32 |       | NULL             |         0 |             0 |
| 70601019 | shop_root | 192.168.0.4:39184 | shop_database | Sleep   |    9 |       | NULL             |         0 |             0 |
| 70601044 | shop_root | 192.168.0.2:20268 | shop_database | Sleep   |    8 |       | NULL             |         0 |             0 |
| 70601053 | shop_root | 192.168.0.2:20308 | shop_database | Sleep   |    8 |       | NULL             |         0 |             0 |
| 70601073 | shop_root | 192.168.0.4:39322 | shop_database | Sleep   |    7 |       | NULL             |         0 |             0 |
| 70601097 | shop_root | 192.168.0.5:45000 | shop_database | Sleep   |    7 |       | NULL             |         0 |             0 |
| 70601114 | shop_root | 192.168.0.2:20522 | shop_database | Sleep   |    6 |       | NULL             |         0 |             0 |
| 70601154 | shop_root | 192.168.0.5:45190 | shop_database | Sleep   |    5 |       | NULL             |         0 |             0 |
| 70601173 | shop_root | 192.168.0.4:39504 | shop_database | Sleep   |    5 |       | NULL             |         0 |             0 |
+----------+-----------+-------------------+---------------+---------+------+-------+------------------+-----------+---------------+
38 rows in set (0.00 sec)

Here we see that the query with id 70569602 has been running for 186 seconds. Of course, that is too long and because there are a few queries that have been going on for a relatively long time, it seems that they are being delayed by that query.

killing a query is done with a KILL query:

mysql -e 'KILL 70569602'

Kill all

If it is needed to stop everything and you do not have time to look what query is the malicious one, you can make use of the command below:

mysql -e 'SHOW PROCESSLIST' | awk '$1 ~ /^[0-9]/ {print "KILL "$1";"}' | mysql

Auto kill

Automatic killing of long running processes kan be achieved by combining the supervisor feature with pt-kill.

For this you will need to create a MySQL config file and a pt-kill supervisor process file.

[client]
user = username
password = *************
[program:pt-kill]
command=/usr/bin/pt-kill --busy-time=25s --ignore-command "INSERT INTO" --kill --print
autorestart=true
stdout_logfile=/home/user/domains/domain.nl/var/log/pt-kill.log
redirect_stderr=true
environment=HOME=/home/user,PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
directory=/home/user

(Do not forget to change the /home/user and /home/user/domains/domain.nl paths)

After restarting supervisor

supervisorctl reread
supervisorctl update

All queries running longer then 25 seconds will be killed and printed in var/log/pt-kill.log.