Медленные запросы к mysql могут возникать по разным причинам: недостаточная производительность процессора, неоптимизированные запросы, криво настроенный сервер, большое количество «случайных» операций чтения и др. Иногда, во имя спасения всего сервера, легче «заставить» сервер отказаться от выполнения долгах запросов. К сожалению, mysql-сервер не умеет ограничивать максимальное время выполнения запроса. Поэтому приходится искать альтернативные пути. А именно, убивать запросы, которые выполняются дольше, чем мы хотим. Конечно, в таком случае работа веб-приложения завершится с ошибкой и, скорее всего, пользователь увидит HTTP 500, но бывают такие случаи, когда лучше отдать одному пользователю HTTP 500, чем всем остальным показать таймаут.

Итак, встречайте: очередной тул от percona — pt-kill! На FreeBSD ставится вот отсюда: /usr/ports/databases/percona-toolkit . Pt-kill — это такая маленькая, но полезная утилитка, которая умеет опрашивать mysql-сервер раз в n секунд и убивать медленные запросы. В общем-то, на этом ее функционал не ограничивается, но в данный момент нас интересует именно эта часть.

Для начала, можно проверить работу pt-kill в «тестовом» режиме. Вместо завершения соединений, он будет просто показывать те запросы, которые подходят под его условия. Для этого надо запустить его примерно такой командой:

pt-kill --busy-time 60 --print

В этом примере он должен выводить в консоль запросы, которые перешагнули порог в 60 секунд. Таймаут, необходимый вам, лучше подбирать самостоятельно исходя из характера нагрузки на сервер. В общем случае для сервера, на котором крутится не только mysql, но и apache, я бы рекомендовал выставлять его таким, чтобы количество процессов веб-сервера не начало расти из-за того, что mysql не успевает отдавать данные.

И, если вы все же уверены в том, что все эти запросы необходимо убивать, можете запустить его следующим образом:

pt-kill --busy-time 60 --print --kill

В таком режиме он будет выводить на экран и убивать по одному медленному запросу за один раз (самому «старому»). Если надо убивать их все сразу (допустим, одновременно их появилось 10 штук), надо указать ключ —victims all. И, наконец, если вы хотите, чтобы он работал в фоне, смело убирайте —print и добавляйте —daemonize.

pt-kill --busy-time 60 --kill -- victims all --daemonize

Но помните: все действия с базой данных потенциально опасны и могут привести к порче данных.

Иногда бывает полезно посмотреть какие таблицы находятся в innodb-буфере (размер которого задается директивой innodb_buffer_pool_size). Исходя из того, что буфер Innodb работает по алгоритму LRU (least recently used), по содержимому буфера мы можем судить о наиболее часто используемых таблицах. Это может пригодиться, например, для оптимизации его размера (когда у нас не выделенный mysql-сервер). А можно и любопытства ради туда заглядывать :)

Итак, для того, чтобы узнать кто у вас там прописался и сколько процентов этого самого буфера он съедает, необходимо выполнить всего навсего один запрос (пример приведен для mysql 5.6):

USE information_schema;
SET @page_size = @@innodb_page_size;
SET @bp_pages = @@innodb_buffer_pool_size/@page_size;
 
SELECT P.TABLE_NAME, P.PAGE_TYPE,
CASE WHEN P.INDEX_NAME IS NULL THEN NULL WHEN P.TABLE_NAME LIKE '`SYS_%' THEN P.INDEX_NAME WHEN P.INDEX_NAME <> 'PRIMARY' THEN 'SECONDARY' ELSE 'PRIMARY' END AS INDEX_TYPE,
COUNT(DISTINCT P.PAGE_NUMBER) AS PAGES,
ROUND(100*COUNT(DISTINCT P.PAGE_NUMBER)/@bp_pages,2) AS PCT_OF_BUFFER_POOL,
CASE WHEN P.TABLE_NAME IS NULL THEN NULL WHEN P.TABLE_NAME LIKE 'SYS_%' THEN NULL ELSE ROUND(100*COUNT(DISTINCT P.PAGE_NUMBER)/CASE P.INDEX_NAME WHEN 'PRIMARY' THEN TS.DATA_LENGTH/@page_size ELSE TS.INDEX_LENGTH/@page_size END, 2) END AS PCT_OF_INDEX
FROM INNODB_BUFFER_PAGE AS P
JOIN INNODB_SYS_TABLES AS T ON P.SPACE = T.SPACE
JOIN TABLES AS TS ON T.NAME = CONCAT(TS.TABLE_SCHEMA, '/', TS.TABLE_NAME)
WHERE TS.TABLE_SCHEMA <> 'mysql'
GROUP BY TABLE_NAME, PAGE_TYPE, INDEX_TYPE;

На выходе будет примерно такая табличка (в данном случае были выполнены запросы только к таблице test.foo):

+--------------+-------------------+------------+-------+--------------------+--------------+
| TABLE_NAME   | PAGE_TYPE         | INDEX_TYPE | PAGES | PCT_OF_BUFFER_POOL | PCT_OF_INDEX |
+--------------+-------------------+------------+-------+--------------------+--------------+
| NULL         | FILE_SPACE_HEADER | NULL       |     1 |               0.00 |         NULL |
| NULL         | IBUF_BITMAP       | NULL       |     1 |               0.00 |         NULL |
| NULL         | INODE             | NULL       |     1 |               0.00 |         NULL |
| `test`.`foo` | INDEX             | PRIMARY    |  2176 |               3.32 |        98.37 |
| `test`.`foo` | INDEX             | SECONDARY  |  2893 |               4.41 |        88.47 |
+--------------+-------------------+------------+-------+--------------------+--------------+