«Сайт тормозит» почти всегда упирается в базу данных. Проверка занимает несколько минут, если делать её в правильном порядке: сначала то, что видно прямо во время замедления, потом то, что копилось неделями.

Смотреть надо в момент проблемы

Главное правило: самая ценная информация доступна ровно тогда, когда всё медленно. Первая команда для MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Для PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Вы увидите, что выполняется прямо сейчас и сколько времени. Обычно ответ виден сразу: один тяжёлый запрос держит остальные, либо сотня одинаковых запросов ждёт освобождения блокировки.

Соединения

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Если Max_used_connections вплотную подходит к max_connections, приложение периодически получает отказ «слишком много соединений». Поднимать лимит — не первое, что нужно делать: каждое соединение занимает память, и увеличение лимита на сервере, которому не хватает памяти, ускоряет переход к swap. Сначала стоит понять, почему соединения не освобождаются: обычно виноват долгий запрос или отсутствие пула на стороне приложения.

Здесь же полезно посмотреть на ситуацию с другой стороны: резкий рост числа соединений может быть следствием не роста популярности, а массового обращения ботов к тяжёлой странице — поиску, фильтру каталога, генератору отчётов.

Медленные запросы

Без журнала медленных запросов дальше двигаться некуда. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Через сутки:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, в postgresql.conf:

log_min_duration_statement = 1000

Дальше — расширение pg_stat_statements, которое сразу показывает суммарное время по каждому запросу:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Обратите внимание: важен не самый долгий запрос, а тот, у которого больше суммарное время. Запрос на 50 миллисекунд, выполняемый тысячу раз в минуту, вредит больше, чем отчёт на десять секунд раз в сутки.

Настройка, которую чаще всего забывают

Для MySQL и MariaDB это размер буферного пула InnoDB. Значение по умолчанию — 128 мегабайт, и оно не менялось десятилетиями. На сервере, где база занимает несколько гигабайт, это означает постоянное чтение с диска вместо памяти.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Разумный ориентир для выделенного сервера баз — половина оперативной памяти; для сервера, где рядом работают веб-сервер и PHP, — четверть, с оглядкой на то, чтобы не начался swap. Одна эта настройка обычно даёт больше, чем неделя оптимизации запросов.

Две вещи, которые незаметно съедают диск

Двоичные журналы MySQL. Они нужны для репликации и восстановления на момент времени, растут постоянно и по умолчанию иногда не удаляются вовсе. Проверка:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Если репликации у вас нет и восстановление на момент времени не используется, срок хранения ставится в разумные несколько суток.

Журналы WAL в PostgreSQL при забытом слоте репликации. Случай менее известный и более опасный: если слот репликации создан, а потребитель к нему больше не подключается, PostgreSQL обязан хранить все журналы с момента отключения — и будет хранить их, пока не кончится диск.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

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

Размер и рост

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Полезно не столько текущее число, сколько скорость его изменения. База, выросшая вдвое за месяц без соответствующего роста посещаемости, обычно означает, что какая-то таблица журналирования пишется и не чистится — сессии, история задач, лог событий плагина.

Безопасность в двух строках

Раз уж вы открыли консоль базы, стоит проверить два момента. Первый — доступна ли база снаружи (ss -tulpn | grep 3306): наружу она смотреть не должна почти никогда. Второй — учётные записи с обращением с любого адреса:

mysql -e "SELECT user, host FROM mysql.user"

Записи с host = '%' означают «подключаться можно откуда угодно». Если при этом порт открыт, у вас есть только пароль между базой и интернетом.

Резервные копии

Последнее, о чём стоит сказать. Наличие файла резервной копии ничего не гарантирует — гарантирует только проверенное восстановление. Дамп, который не разворачивали ни разу, с равной вероятностью окажется усечённым, снятым с ошибкой блокировки или содержащим не ту базу. Проверять это надо заранее и на отдельной машине, а не в тот день, когда копия понадобилась.

Из повседневного наблюдения довольно трёх цифр: число соединений, количество медленных запросов за сутки и размер базы. Все три меняются медленно и предсказуемо, и любое резкое изменение стоит того, чтобы на него посмотреть. Как они выглядят на одной странице — на демо ниже.