MySQL на маленьком VPS: почему база съедает всю память

Часть 10 из 14 · Серия «Сервер в бою»

Прошлая статья закончилась на кассирах, которые звонят на склад: PHP-воркеры не работают, а ждут базу. Сегодня идём на склад. У MySQL на маленьком VPS две вечные претензии от владельца: «съедает всю память» и «медленно отвечает». Обычно их считают двумя разными проблемами. На самом деле это одна проблема, рассказанная с двух сторон, — и понимание этой связи важнее любого конкретного параметра.

Начнём с реабилитации обвиняемого, потому что тут прячется главное непонимание: база данных и должна есть память. Это не утечка — это её работа. А вот сколько ей положено на общем VPS и почему при неправильной цифре первым в три ночи умирает именно mysqld (наш старый знакомый сценарий с OOM killer из первой статьи) — это уже настройка, и ей займёмся. А потом — второй стороной медали: slow query log, EXPLAIN и индексы, которые превращают трёхсекундные запросы в трёхмиллисекундные. И только в самом конце — честный ответ на вопрос из заголовка серии тарифов: когда оперативки действительно пора добавить, а когда это способ дорого замаскировать отсутствие индекса.

Почему база жрёт память — и почему это хорошо

Диски медленные. Даже быстрый NVMe медленнее оперативной памяти на порядки, а на VPS диск ещё и общий с соседями. Поэтому вся архитектура InnoDB (движок MySQL по умолчанию) построена вокруг одной идеи: держать рабочие данные в RAM и ходить на диск как можно реже. Кусок памяти, где живут горячие данные и индексы, называется buffer pool — и это сердце базы.

Отсюда переворот в голове, который стоит совершить один раз: когда htop показывает, что mysqld занял полтора гигабайта, — это не «раздулся», это «прогрелся». Запрос, чьи данные лежат в buffer pool, отвечает из памяти за микросекунды; запрос, за данными которого пришлось идти на диск, — в сотни раз медленнее. Память, «съеденная» базой, — это и есть её скорость. Пустая память на сервере с базой — вот что на самом деле должно смущать.

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

Провал первый: пул больше, чем сервер может себе позволить. Начитавшись советов «ставьте buffer pool в 70–80% RAM» (совет правильный — но для выделенного сервера БД!), владелец VPS на 4 ГБ ставит 3 ГБ пула. Рядом живут PHP-FPM с его воркерами, nginx, система. В спокойный день всё помещается; в день наплыва PHP-воркеры разворачиваются до своих max_children — и памяти физически не хватает. Приходит OOM killer, выбирает самый жирный процесс — а это, конечно, mysqld — и сайт ложится целиком. Знакомая ночь из первой статьи имеет вот такую предысторию.

Провал второй: пул по умолчанию — 128 МБ. Дефолт MySQL трогательно консервативен. База в 2 ГБ при пуле 128 МБ живёт в режиме вечного дискового чёса: данные не помещаются в память, каждый второй запрос идёт на диск, всё «тормозит», хотя память сервера простаивает. Владелец делает вывод «MySQL медленный» и... идёт покупать тариф побольше, где дефолтные 128 МБ продолжат тормозить точно так же.

Считаем buffer pool по-взрослому

Правильная цифра выводится из двух чисел: сколько у вас данных и сколько у вас бюджета. Оба легко узнать.

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

SELECT engine,
       ROUND(SUM(data_length + index_length)/1024/1024) AS size_mb
FROM information_schema.tables
GROUP BY engine;

Допустим, вышло 1200 МБ. В идеальном мире buffer pool вмещает всё — но на практике горячая часть (то, что реально читается каждый день) обычно заметно меньше полного объёма: старые заказы и логи за 2022 год лежат мёртвым грузом.

Сколько бюджета. Достаём салфетку из восьмой и девятой статей — она сводится к одному неравенству, которое обязано сходиться при пиковой нагрузке:

buffer pool + (Σ max_children × RAM воркера) + nginx + система + запас ≤ RAM.

На VPS 4 ГБ с посчитанными в прошлый раз ~34 воркерами по 60 МБ (≈2 ГБ) разумный пул — 1–1.5 ГБ. Ставим в /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
innodb_buffer_pool_size = 1200M

и перезапускаем MySQL (не в час пик — помним про шестую статью). Проверить, что пул попал в размер, — через пару дней работы:

SHOW ENGINE INNODB STATUS\G

В секции BUFFER POOL AND MEMORY смотрим на соотношение прочитанного из памяти и с диска — если хиты идут из памяти в подавляющем большинстве, пул справляется. Косвенный, но наглядный признак со стороны системы: у Netdata из четвёртой статьи стихает график дисковых чтений.

И вторая, менее известная статья расходов — соединения. Каждое подключение к MySQL несёт свои персональные буферы: сортировки, джойны, чтение. По мелочи — по несколько мегабайт, но дефолтный max_connections = 151 означает потенциально ещё сотни мегабайт сверх пула в плохой день. На маленьком VPS с посчитанными PHP-воркерами больше соединений, чем воркеров, взяться неоткуда:

max_connections = 60   ; чуть больше суммы max_children — и хватит

Это не только память: это ещё и честный предохранитель — лучше пусть лишний посетитель увидит ошибку подключения, чем сервер уедет в своп целиком.

Вторая сторона медали: медленные запросы

Память посчитана — переходим к жалобе «медленно». И тут снова связь двух сторон: каждый медленный запрос давит на память, каждый недостаток памяти замедляет запросы. Запрос без индекса перелопачивает всю таблицу — вымывает из buffer pool горячие данные холодными — соседние запросы идут на диск — всё медленнее — соединения копятся — память растёт. Один кривой запрос способен испортить жизнь всей базе. Найдём его.

Включаем протокол медленных запросов — в тот же mysqld.cnf:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1        ; всё, что дольше секунды, — в протокол

Через день-два нагрузки — смотрим улов. Сырой лог многословен, поэтому сразу агрегируем штатной утилитой:

mysqldumpslow -s t -t 5 /var/log/mysql/slow.log

-s t — сортировать по суммарному времени, -t 5 — топ-5. На выходе — рейтинг настоящих виновников: не «самый долгий запрос однажды», а «какие запросы съели больше всего времени суммарно». Классика жанра — в топе один запрос, выполняющийся 400 раз в час по 2 секунды.

Дальше — допрос с пристрастием. Берём запрос из топа и спрашиваем базу, как она его выполняет:

EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]';

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 480213 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

Читать EXPLAIN можно долго и глубоко, но для 90% случаев хватает двух колонок. type = ALL — «полное сканирование»: база перебирает все строки подряд. rows = 480213 — сколько именно перебирает, на каждый запрос, по 400 раз в час. Диагноз: нет индекса по колонке из WHERE. Лечение — одна строка:

ALTER TABLE orders ADD INDEX idx_customer_email (customer_email);

Повторяем EXPLAIN: type = ref, rows = 3. Вместо 480 тысяч строк — три. Запрос с двух секунд падает до миллисекунд — в тысячу раз, бесплатно, без единого нового гигабайта. Ни один тариф в мире не даёт ускорение×1000 за минуту работы; индекс — даёт. Именно поэтому в иерархии из прошлой статьи база стоит на первом месте.

Тот же метод — по всему топу mysqldumpslow: EXPLAIN, смотрим type и rows, ставим индексы по колонкам из WHERE и JOIN. Пара примечаний в дорогу: индексы не бесплатны для записи (каждый INSERT их обновляет), так что вешать их на всё подряд не надо — только по следам slow log; а если в топе сотни одинаковых быстрых запросов, отличающихся только ID, — поздоровайтесь с проблемой N+1 из вашей ORM: чинится в коде (жадная загрузка), а не в базе.

Так когда же пора добавить оперативки?

Теперь можно ответить честно, потому что появились критерии. Память — правильный ответ, когда данные переросли сервер, и неправильный, когда она маскирует отсутствие индексов. Чек-лист «пора», все пункты сразу:

1. Slow log чист — топ запросов разобран, EXPLAIN нигде не показывает ALL по большим таблицам. Оптимизировано всё, что оптимизируется.
2. Горячие данные объективно больше пула — таблицы выросли, SHOW ENGINE INNODB STATUS показывает постоянные дисковые чтения, график диска у Netdata не стихает.
3. Бюджет памяти сведён честно — воркеры посчитаны, соединения ограничены, лишнего не раздано, а неравенство всё равно не сходится.
4. Своп используется постоянно (не «занят», а именно шуршит — ненулевые si/so в vmstat 1).

Все четыре — да? Поздравляю: ваш проект дорос до апгрейда, и новые гигабайты уйдут в buffer pool и дадут реальный эффект. Хотя бы один пункт — нет? Новый тариф купит вам пару месяцев тишины, после чего всё вернётся: неиндексированный запрос прекрасно сканирует полмиллиона строк в любом объёме памяти.

И маленькое смежное: своп на VPS с базой держите, но прикрутите рвение — vm.swappiness = 10 в sysctl. Совсем без свопа первый же пик памяти закончится OOM-убийством; со свопом по умолчанию (60) система начинает выгружать страницы слишком охотно, и база «тормозит» на ровном месте.

Грабли: сводная таблица

Что в итоге

База перестаёт быть чёрным ящиком, когда усвоены две вещи. Первая: память для базы — топливо, а не потери; вопрос не «почему ест», а «по бюджету ли». Вторая: у «медленно» есть протокол с фамилиями — slow log и EXPLAIN называют конкретные запросы и конкретные недостающие индексы, и лечение там измеряется не гигабайтами, а тысячами раз.

Но даже идеально проиндексированный запрос — это работа: базу спросили, она ответила. А теперь вопрос на подумать: зачем вообще спрашивать базу о списке товаров, который не менялся с прошлого вторника, — по триста раз в минуту? Самый быстрый запрос — тот, который не выполнился. В следующей статье выстроим всю иерархию кэшей — от заголовков браузера до Redis, — разберёмся, на каком этаже живёт FastCGI cache, и почему начинать модно с Redis, а правильно — с двух строчек в nginx.