Каталог российского интернет-магазина светильников и светотехники перестал думать над фильтрами и падать на распродажах. Базу данных ускорил Евгений Росляков: разобрал медленные запросы, пересобрал индексы, перевёл сервер на MariaDB и закрыл открытый в интернет phpMyAdmin. За серверной частью сайтов он следит с 2016 года. Он самозанятый, работает по договору и выдаёт юрлицам закрывающие документы. Живёт в Москве, заказы берёт и удалённо.
В магазине около 60 тысяч товаров, у каждого десятки характеристик: цоколь, мощность, цветовая температура, степень защиты IP. Сайт написан на собственном PHP-движке. База работала на Oracle MySQL 5.7 на одном сервере вместе с сайтом. Производитель прекратил поддержку этой версии в 2023 году, исправления безопасности для неё больше не выходят.
Фильтры каталога открывались по несколько секунд. Покупатель выбирал цоколь и мощность, ждал и уходил. В дни распродаж база упиралась в диск, и сайт падал.
Отдельная история — phpMyAdmin. Менеджеры правили через него цены и остатки. Он был открыт в интернет, и заходили все под одним общим логином, так что кто и что поменял, выяснить было нельзя. Резервная копия лежала на том же сервере, и никто не проверял, что из неё вообще можно восстановиться.
Администрирование баз данных в магазине годами никто не вёл: версию не обновляли, права были общие, копии не проверялись. От меня ждали три вещи: быстрый каталог, сайт, переживающий распродажу, и уход с версии без поддержки.
Начал с измерений. Включил журнал медленных запросов, неделю собирал статистику и разобрал самые тяжёлые запросы через EXPLAIN. Почти все тормоза давали фильтры каталога. Индексы стояли по одному полю, а покупатели выбирают сразу несколько. Я сделал составные индексы под реальные комбинации фильтров. Запросы с OR и LIKE по характеристикам переписал: поиск по подстроке заменил точным сравнением, условия через OR разложил на отдельные выборки.
Затем настроил InnoDB под память сервера: размер буферного пула, журнал транзакций, параметры записи на диск.
После этого перевёл базу с MySQL 5.7 на MariaDB 10.11 LTS. Выбрал её потому, что она почти без изменений заменяет MySQL: те же драйверы, тот же протокол, движок сайта остаётся прежним. У LTS-версии несколько лет исправлений. Совместимость проверял на копии базы: режимы SQL, функции JSON, кодировки. Нашлись два расхождения в строгом режиме, их поправили в запросах.
Рядом поднял реплику. С неё строятся отчёты и снимаются резервные копии, так что боевая база не отвлекается. Ежедневную копию восстанавливаю на отдельном сервере, чтобы убедиться, что она рабочая.
phpMyAdmin закрыл. Зайти можно только из сети офиса, у каждого менеджера свой пользователь с минимальными правами, вход с двухфакторной проверкой. В конце написал регламент обслуживания на две страницы.
Фильтры каталога отвечают сразу, даже когда выбрано несколько характеристик. Первая распродажа после работ прошла без падений: диск больше не упирается в предел, а отчёты не мешают покупателям, потому что считаются на реплике.
База работает на версии, для которой выходят обновления безопасности. Следующий переход назван в регламенте с ориентиром по времени, а не отложен до аварии.
Менеджеры правят цены в привычном интерфейсе. Разница в том, что каждое изменение теперь связано с конкретным человеком. Если в карточке окажется ошибочная цена, видно, кто её менял, и значение возвращается за несколько минут.
Есть и оборотная сторона. Индексы ускоряют чтение, но слегка тормозят запись, поэтому массовую загрузку прайса лучше запускать ночью. И чем больше появится новых фильтров, тем чаще индексы придётся пересматривать, поэтому такая проверка записана в регламент как плановая.
Сайт и основная база по-прежнему живут на одном сервере, реплика только страхует. Если магазин вырастет, базу стоит вынести на отдельную машину, и это можно сделать без смены движка.
Тормозит каталог или сайт ложится в сезон — пришлите мне на freelance.ru версию базы и примерное число товаров. По этим двум цифрам я скажу, с чего начинать.
