Сайт гальмує: на що дивитися в MySQL і PostgreSQL

«Сайт гальмує» майже завжди зводиться до бази даних. Перевірка займає кілька хвилин, якщо робити її в правильному порядку: спершу те, що видно під час самого гальмування, потім те, що накопичувалося тижнями.

Дивіться, поки проблема триває

Головне правило: найцінніша інформація доступна саме тоді, коли все повільно. Перша команда для 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, застосунок періодично отримує відмову «too many 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'"

Розумний орієнтир для виділеного сервера бази — половина RAM; для сервера, на якому ще й вебсервер із 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 = '%' означають «з’єднання звідусіль». Якщо при цьому відкрито й порт, між базою та інтернетом стоїть лише пароль.

Резервні копії

Останнє. Наявність файла резервної копії не гарантує нічого; гарантує лише перевірене відновлення. Дамп, який ніколи не відновлювали, з рівною ймовірністю виявиться обрізаним, знятим із помилкою блокування або з не тією базою. Перевіряти це треба заздалегідь і на окремій машині, а не в день, коли копія знадобилася.

Для повсякденного спостереження вистачить трьох цифр: кількість з’єднань, кількість повільних запитів за добу й розмір бази. Усі три змінюються повільно й передбачувано, і будь-який різкий рух однієї з них вартий погляду. Який вигляд вони мають на одній сторінці, показує демо нижче.