MySQL под нагрузкой > 40000 QPS, что может пойти не так
Кирилл ЕгоровМой канал: https://t.me/HeadOfWeb
Доклад для: https://t.me/php_nn
Опыт работы с нагруженной БД.
Вводные данные.
Две E-commerce платформы (B2B, B2C), продаем автозапчасти и аксессуары.
Как обычно выглядят такие платформы https://habr.com/ru/post/531908
Сложность в нашей сфере.
Клиент хочет купить запчасть на автомобиль, у этой запчасти кроме основного артикула, могут быть оригинальные замены от производителя и аналоги «не оригинал», предложения запчастей могут быть от многих поставщиков.
Таким образом 1 артикул, который хочет купить клиент, легко превращается в 5-7 тыс артикулов аналогов и замен, по каждому из которых есть десятки предложений. В итоге получаем 140 000 вариантов предложений по одному артикулу в сыром виде, естественно покупателю нет смысла показывать их все, это начальная точка подготовки предложения.
Жесткий SLA и работа 24/7 (час простоя стоит неприлично больших денег, исчисляется в годах работы программиста)
Приблизительные объемы:
~ 80 млн SKU (артикулов) мы знаем
~ 60 млн можем привезти
~ 150 000 SKU лежит на складах
~ 240 млн записей по аналогам
> 10 000 входящих прайс-листов (максимальный размер прайс-листа 8.5 млн строк), частое обновление цен и наличия
Одна из наших самых нагруженных БД:
MySQL (Percona 5.7),
102 ядра CPU,
480 GB RAM, 400 GB Bufer Pull
~ 1.2 TB данных БД
~ 150 GB бинлогов за сутки
~ 40 000 QPS в пике
Master-Master на 2 ДЦ, Master-Slave(s) репликация в рамках одного ДЦ.
Профиль нагрузки: много записи, много чтения.
При таких конфигурациях и объемах нужны крутые DevOps, руками разработчиков проблемы с базой уже не решить.
Для начала нужно оптимизировать настройки сервера БД для работы с таким объемом и железом, обложиться мониторингами.
Немного про интересные настройки MySQL (для общего понимания)
Ускорение применения binlog на реплике
Одна из базовых проблем репликации, которые приходится решать на подобных системах это медленное применение логов репликации на ведомом сервере. Приведены не все настройки, только некоторые для понимания.
(лучше поручить DevOps)
enforce-gtid-consistency = ON
gtid-mode = ON
Используем GTID в репликации, проще говоря, реплика смотрит на идентификатор транзакции, а не на позицию в бинлоге как это было принято раньше.
transaction_write_set_extraction = XXHASH64
binlog-transaction-dependency-tracking = WRITESET
Используем WRITESET для транзакций, это позволяет накатывать binlog реплики в несколько потоков. Если совсем просто, то транзакции которые не затрагивают одни и те же данные могут накатываться на реплику параллельно. Если не использовать, данные реплику идут в один поток, что очень медленно относительно мастера. Количество потоков slave-parallel-workers настраивается в MySQL.
Работа с памятью в БД
У нас настроено:
innodb-numa-interleave = ON
(лучше не трогать без понимания)
Железо свое, на сервере несколько сокетов, у каждого сокета (процессора) свои банки памяти. В этом случае процессор пытается использовать ближайшие к нему банки памяти (не бегать по шине через материнскую плату к другому процу и его банкам).
https://ru.wikipedia.org/wiki/Non-Uniform_Memory_Access
Для пользователей в группе mysql мы резервируем страницы памяти в 2МБ (стандартный размер 4k)
Проще говоря, в системе есть map [номер -> страница], когда в системе много памяти эта мапа большая и медленная, не влезает в кэш.
https://en.wikipedia.org/wiki/Translation_lookaside_buffer
Опыт работы, что может пойти не так
Толстые транзакции
При такой нагрузке и компоновке несколько больших транзакций затрагивающих миллионы строк могут «положить» репликацию. При огромных объемах данных легко промахнуться или не обратить внимание на кол-во записей в транзакции. Реплики начинают сильно отставать. В нашем случае сильно отстающая реплика отстреливается системой. Более того микс большого количества маленьких и больших транзакций вызывают дедлоки и ожидание. Нужно бить на порции.
Неочевидные особенности Master-Master репликации
Два активных мастера это боль грозящая развалом репликации, мы используем один мастер в режиме stand by (другой дц), для быстрого переключения. Т.е физически запись идет в 1 мастер.
Если ваш код завязан на последнй автоинкрементальный индекс, который он обработал, например, выбирает строки по id, обрабатывает при этом запоминая строку которую он обработал последней, то во время переключения на другой master этот скрипт может потерять несколько строк. Строки с меньшим индексом могут появится на сервере позже. Это связано с тем, что при типичной master-master репликации задается шаг автоинкремента, например, один сервер создает только четные id, второй только нечетные. На сервере 2 создалась строка с индексом 2 сохранилась в бинлог, но не успела приехать по репликации на сервер 1, сервер 2 упал. В это время на сервере 1 создается строка с индексом 3, а строка с индексом 2 приедет только при включении сервера 2.
Есть проблемы с репликацией Memory таблиц в MySQL 5.6, при перезапуске одного из серверов происходил двойной drop memory таблицы, что разваливало репликацию, в 5.7 поправили.
https://bugs.mysql.com/bug.php?id=77729 Тикет открыли в 2015 году, наш DevOps написал как воспроизвести в 2017 и тикет получил status: Verified, а закрыли в 2020.
Кончаются primary key
Типичная проблема, primary key INT auto_increment
Достаточно быстро заканчивается на 2 147 483 647
Мы ставим unigned BIGINT
Следующие конструкции увеличивают счетчик автоинкремента независимо от того произошла вставка, обновление или строка была проигнорирована.
- INSERT ON DUPLICATE KEY UPDATE
- INSERT IGNORE
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
Кончается место SSD, RAM
Место на дисках, особенно промышленных SSD не резиновое, данные необходимо все время «подрезать» / переносить в архив. Оперативная память хранит индексы таблиц и тоже не бесконечна.
Настраиваем фоновые задачи по очистке и переносу в архив.
Дедлоки (deadlock) на уровне логики приложения
Примеры логики уже освещал в докладе: Обновляем цену на сайте, что может пойти не так в большом интернет-магазине.
Идеально, когда всего один повод обновления данных в строке
В нагруженной таблице лучше иметь только одну причину обновления записи и она не должна быть конкурентной на уровне диапазонов.
Если в таблице есть две и более колонок, которые обновляются разными источниками данных (кодом/сервисами) по разным событиям, рано или поздно начнут возникать deadlock и конкуренция за обновление строки. Усугубляется это, если эти причины требуют массового обновления строк (блокировка диапазона).
Одна задача обновляет одну колонку у множества строк, вторая задача обновляет вторую колонку у другого множества строк. Как только эти множества пересекаются, начинаются блокировки.
см. п. "Дедлоки на уровне логики приложения".
Обходится это различными методами в зависимости от бизнес логики, разделением по таблицам, версионностью строк, обновлением через очереди событий, шардированием с перебалансировкой и т.п
LOAD DATA IN FILE
Самый быстрый механизм вставки большого объема данных в БД. При необходимости залить в табличку несколько миллионов строк, сохраняем их в csv файл и льем через этот механизм.
Бэкапы и восстановление
Восстановление из дампа занимает неприлично большое время.
Ранее бэкапы делали при помощи XtraBackup от Percona, но на больших объёмах он начал ломаться. Скорее всего из-за того что хранилище данных у нас не локальное, а смонтированная по сети FS (возникли проблемы с локами на уровне лога самого XtraBackup при копировании больших таблиц).
Бэкапы делаем с реплики, 2ух видов:
1) дампы таблиц через mysqldump
2) Через Veeam (снэпшот файловой системы), проще говоря, снэпшот виртуальной машины
- останавливается поток реплики
- в файл записывается номер последней транзакции полученной с мастера
- останавливается mysql
- делается снэпшот файловой системы
- запускается mysql
ALTER больших таблиц
Alter больших таблиц занимает неприлично большое время, при этом нужно понимать, что он сначала проходит на мастере, только потом отправляется в бинлог, применение которого на реплике занимает большее время. Стараемся делать в периоды с самой низкой пользовательской активностью.
Внезапный баг
В MySQL 5.7 подвезли Hash Indexes, штука полезная, но внезапно мы стали получать жесткие тормоза и дедлоки на рандомных запросах.
Долго наблюдали и пытались понять в чем дело, чтобы зарепортить. Причину так и не нашли, нашли как от этого избавиться.
Проблемы начинались в момент очистки больших таблиц, когда удаляли все строки (DELETE) или делали TRUNCATE TABLE, при этом это было каким-то образом связано с наличием MEMORY (Storage engine) таблиц, хотя в них была не особо интенсивная запись.
Предположительно это связано со сбросом Hash Index и какими-то внутренними структурами mysql.
Как исправили - заменили TRUNCATE на DROP table, отказались от Memory (Storage engine), тормоза и дэдлоки пропали
Внезапный баг №2 c зависающими неубиваемыми селектами, пока не знаем как воспроизвести и лечить, появляются не чаще раза в месяц, лечатся только рестартом сервера.
Неоптимальные запросы, индексы таблиц
Тут особо нечего добавить.
Включаем slow log, агрегируем, получаем самые частые и медленные запросы, вооружаемся Explain, оптимизируем запросы, добавляем нужные индексы.
Из неочевидных вещей хотелось бы отметить, что наличие в запросе SELECT колонок с типом text, long text, varchar с большим диапазоном данных вызывает дополнительные обращения к external off-page storage, что самим фактом замедляет выборку, не говоря уж об объеме передаваемых данных. Поэтому больших выборок по таким полям стоит избегать, если в них нет необходимости. Подробности тут https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html
Интересный кейс с селективностью индексов
Кейс, когда пришлось mysql оптимизатору принудительно выставлять Use Index
Упрощенный пример:
Таблица:
part (ID детали) | pricelist (ID прайс-листа) | еще колонки с данными
Всего строк около 1 млн.
Деталей part явно больше, чем прайс-листов pricelist
Строим составной индекс с большей селективностью INDEX(part, pricelist)
Все отлично работает на запросах вида
SELECT * FROM `таблица` WHERE `part` = x AND `pricelist` = y
Индекс становится неоптимальным как только появляется еще один запрос с пересечением множеств вида:
SELECT * FROM `таблица` WHERE `part` IN (1,2,3) AND `pricelist` IN (4,5,6)
При этом в условии IN() может быть:
- нескольких сотен идентификаторов деталей
- десятки идентификаторов прайс-листов
В этом случае лучше работает дополнительный обратный INDEX(pricelist, part),
оптимизатор не выбирает его, так как он, по его мнению, менее селективный (не учитывает соотношение в разных IN()), приходится явно указывать use index, который кратно ускоряет выборку.
Случай редкий, с этим нужно быть очень внимательным.
Doctrine
Ничего не имею против доктрины, одно время она у нас использовалась в паре сервисов, там она создавала больше проблем чем пользы. Около 7 лет назад ошиблись при проектировании системы, выбрав инструмент неподходящий под задачу. Использовалась только для чтения данных, которые вставляются большими объемами (данные прайс-листов), писать доктриной такие вещи не имеет смысла.
При чтении оказалось, что нужно строить сложные запросы, которые нужно еще и долго оптимизировать, тут от доктрины было больше боли чем профита.
Старая версия доктрины, завязка на старый MongoDB драйвер, невозможность перейти на PHP-7, основной «doctrine активист» покинул компанию около 6 лет назад.
Выпилили и вздохнули с облегчением, сервис переписали на Road Runner и другую архитектуру, получили до 5x повышения производительности.
"База все стерпит и сделает быстрее"
Часто программисты злоупотребляют перекладывая ответственность на базу.
Делают тяжелые и ресурсоемкие запросы там, где можно было бы подумать, изменить структуру хранения данных, сделать фоновые расчеты. На малых объемах данных это не так заметно, проблемы начинаются намного позже и часто по всем фронтам при повышении нагрузки.
Необходимо принимать во внимание, что мощности базы данных масштабируются на много сложнее кода. Дополнительный сервер базы данных добавить в систему намного сложнее и дороже чем сервер для исполнения кода.
Очереди в бд
Затея не очень хорошая. Если очень нужно, можно организовать в БД, при условии небольшого количества событий и построчной обработке (не диапазонами). При этом обязательно с повышением уровня изоляции транзакций например до serialized, и обновлением строки признаком, что стоку забрали, иначе воркеры будут выхватывать одни и те же записи. Так же нужно предусмотреть, что воркер может получить try restart transaction.
SELECT FOR UPDATE можно использовать, но совместно со SKIP LOCKED (последнее появилось только в MySQL 8), иначе получим блокировки воркеров.
Опасные запросы с которыми нужно быть внимательнее
Давным-давно был модный трюк с подменой таблиц путем атомарного переименования RENAME TABLE old_table TO backup_table, new_table TO old_table. На больших нагруженных таблицах так лучше не делать, особенно автоматизированно, чревато дедлоками.
INSERT SELECT - опасный запрос, может вызывает блокировки так как чаще всего ставят shared next-key locks на таблицу из которой идет SELECT https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
—————
Бонус:
- Кейс по рефакторингу: Спасаем базу от разрушительной нагрузки.
- В MySQL есть strict режим, почитайте, если до сих пор не слышали про него.
- Отличный доклад Петра Зайцева на Highload fwdays 2021.