Привет! Меня зовут Олег Кретинин, и я разработчик в команде общих компонентов в Яндекс Еде. Сегодня я расскажу о том, как мы смогли успешно снять нагрузку с нашей базы данных, а также уменьшить её размер.

Помимо сервисов, написанных на C++, Go и Python, у нас есть монолит, он же «кора», на PHP, который всё ещё представляет огромную кодовую базу, хранит кучу логики и предоставляет данные по API для 120 сервисов.

После обновления фреймворка и версии PHP мы принялись за решение другой проблемы, которая всё чаще и чаще давала о себе знать. В тот период у нас возросло количество инцидентов, связанных с базой данных, и нам нужно было что‑то придумать, чтобы стабилизировать проект максимально быстро. Случалось, что всё сыпалось во время праздничных дней, когда количество заказов увеличивалось на 30–40%, или во время разовых массовых операций, например когда однажды в большую сеть ресторанов добавлялся бесплатный соус к каждой позиции меню.

Исходные данные

1. Монолит на PHP 8.1, Symfony 6, 930 эндпоинтов (~400 из них — внешние ручки API), 216 консюмеров для обработки очередей и 209 прочих фоновых задач.

2. Основная БД — MySQL 5.7 в виде MariaDB Galera Cluster размером 4 ТБ+ и 400+ таблиц. Общий RPS ~100K. Базируется в трёх дата‑центрах, в каждом по связке master‑slave + несколько слейвов для аналитики.

3. HAProxy — отслеживает работоспособность мастера в ДЦ и при выходе из строя переключает на рабочий.

4. ProxySQL — по описанным правилам в виде регулярок распределяет, какие запросы пойдут в master, какие — в slave.

Задачи, которые нам предстояло решить

1. Повышенный CPU и ОЗУ на MySQL. В монолите полно легаси; добавленные ранее запросы не были рассчитаны на текущую высокую нагрузку и объём данных. К тому же никто не занимался анализом запросов в общей картине.

2. Из‑за того, что база содержит 400+ таблиц, сложно контролировать вносимые изменения в структуру при работе большого количества команд.

3. Flow Control (FC) — фича Galera Cluster, которая включается тогда, когда кластер понимает, что данных на изменение приходит очень много и они не успевают переливаться в другие реплики (упёрлись в диск, CPU, закончились воркеры и т. д.). Чтобы все реплики смогли догнать мастер и применить накопившиеся транзакции, flow control останавливает запись в мастере вовсе, что порождает следующие проблемы:

   1. Ожидание записи в кластер, что ведёт к полному исчерпанию php‑fpm‑воркеров и, следовательно, к росту таймингов.

   2. Из‑за отсутствия в реплике ожидаемых данных падают воркеры очередей и срабатывают ретраи, вследствие чего дополнительно повышается нагрузка на БД, а сама очередь начинает забиваться.

   3. В коре есть использование Galera‑специфичной функциональности — wsrep‑sync‑wait. Она позволяет ходить в слейв с гарантией синхронизации. То есть если вы делаете запрос, а у слейва из‑за FC статус != "Synced", то мы будем ждать, пока он не синхронизируется. Из‑за этого мы получаем последствия в виде тех же растущих таймингов, забитых php‑fpm‑воркеров и очередей.

Как мы решали проблемы

Сначала мы распределили ответственность за таблицы по командам. Таблицы, которые не подходили ни под какой стрим, перешли к нам как к общим компонентам. В рамках активностей по закрытию техдолга мы стали еженедельно собирать slowqueries‑логи с помощью утилиты pt‑query‑digest и распределять между ответственными.

Для этого мы написали свой парсер логов, который позволял нам легко анализировать выгрузки и быстро находить самые плохие запросы. Основные проблемы мы поправили довольно быстро, и общий график стал выглядеть вот так.

Основная проблема была в модифицирующих запросах: любой резкий рост приводил к растущему Flow Control, а следовательно, и к остановке записи в мастер. Нам необходимо было определить самые проблемные зоны и начать с них. Для этого мы написали в приложении код, который парсил каждый SQL‑запрос, собирал информацию об участвующих в запросе таблицах, их типе, используемых полях и отправлял это в метрику. Так у нас появилось понимание самых используемых таблиц, их связей и типов нагрузок на них.

Очевидно, что функциональность с этими таблицами нужно выносить в отдельный сервис, но это занимает немало времени в виде прохождения ревью, выделения квот в инфраструктуре, назначения ответственных и т. д. А для нашей цели по ослаблению нагрузки на базу требуется более быстрый результат с использованием ограниченных ресурсов. Мы подумали: а почему бы просто не унести некоторые таблицы в отдельные базы, подменяя коннекшен в приложении? Так мы написали свой инструмент и назвали его TableSwitcher (TS).

TableSwitcher

TableSwitcher — обёртка над методами Doctrine, которая содержит в себе стратегии, позволяющие плавно переключить запись и чтение отдельных таблиц на новую БД. Стратегии переключаются последовательно, где финальная будет означать, что мы пишем и читаем данные только из табличек новой базы.

Принцип основан на оперировании первичным ключом (PK) конкретной записи. Из параметров эксперимента мы узнаём, какая база на данный момент является ведущей. Затем мы сохраняем строку в ведущую базу, получаем её PK (если он инкрементальный) и делаем запись во вторую базу с этим же PK. Когда данные в таблицах у нас идентичны, мы переключаем стратегию и делаем ведущей уже нашу новую БД, которая будет генерировать PK, а последовательная запись будет происходить в старую базу.

Полный флоу выглядит следующим образом:

1. Создание новой БД и добавление её коннекшена в проект.

2. Разрыв в коде всех джоинов с таблицами старой БД в виде замены отдельными запросами.

3. Замена всех обращений в БД в коде на TableSwitcher.

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

5. Переливка отсутствующих данных из старой БД в новую.

6. Включение стратегии, где генерация ID и первоначальное сохранение происходит в новой базе, а затем в старой.

7. Если проблем не наблюдается, переходим на последнюю стратегию, где перестаём писать в старую БД.

8. Выпиливание TS и развилок с фича‑флагами.

Таблица возможных стратегий TS:

Чтобы использовать такой подход, в выносимых таблицах не должно быть джоинов с таблицами основной БД и зависимостей от транзакций. Следовательно, не все нагруженные таблицы могут нам подойти из‑за большого количества связей и использований.

Поиск данных для переноса

Чтобы найти таблицы, которые можно легко вынести, мы написали скрипт. Он собрал все связи между сущностям Doctrine. Затем соединили это с метриками реальных запросов и загрузили данные в OrientDB. У нас получился граф, который помог визуально определить, какие логические группы и в какие новые БД мы можем унести.

Далее мы провели анализ получившихся групп и определили целесообразность переноса. Рассматривались следующие критерии:

1. Нагрузка. Таблицы должны быть в топе по нагруженности.

2. Использование в коде. Например, в нашем случае трудозатраты на вынесение таблицы orders, которая связана почти с каждым сервисом в коде, были бы колоссальные, поэтому данная и подобные ей таблицы не рассматривались.

3. Связанность с другими таблицами. Это таблицы явно выделенные в логические группы: например, группа таблиц логистики, логи, таблицы по информации о компенсациях или же одиночные таблицы без связей.

4. Актуальность работ. Важно быть на связи с другими командами: возможно, кто‑то уже в процессе реализации отдельного сервиса на основе этих таблиц и перенос для них неактуален.

5. Специфика запросов. Актуально, когда переезд происходит с одного типа БД на другой (в нашем случае с MySQL на PostgreSQL). Например, в raw‑запросах нужно поискать вещи типа ON DUPLICATE KEY UPDATE, GROUP_CONCAT(), DATE_ADD, SHOW VARIABLES LIKE wsrep_sync_wait, функции по работе с JSON и т. д. То есть найти всё то, что не сможет выполняться одинаково на старой и новой базе, провести ресёрч и понять, возможно ли будет малой кровью повторить идентичное поведение.

Базой для переноса мы выбрали PostgreSQL, потому что это основная база для всех наших сервисов. В будущем это без проблем позволит прикрепить указанную базу на новый сервис. На основе наших критериев мы отобрали 13 таблиц, в которые преимущественно записывали данные и которые подходили под одну логическую группу, а конкретно — группу логов.

Старт переноса и решение возникших проблем

Начать мы решили с таблицы history. Она содержит историю изменений заказов, ресторанов, смены логистических параметров и пр. Эти данные отображаются в админке, используются в основном службой поддержки для разбора пользовательских обращений и не участвуют в важной логике. Средний RPS на запись в эту таблицу — 2к+, а весит она около 200 ГБ.

Первым делом мы удалили немногочисленные джоины и переписали на отдельные подзапросы. Потом реализовали классы TableSwitcher и написали скрипт по переносу данных. Но почти сразу столкнулись с проблемами.

Сразу же, на этапе сборки, из‑за отсутствия в окружении новой базы у нас посыпались функциональные тесты. Пришлось актуализировать окружение и добавлять PostgreSQL, миграции, фичафлаги, параметры, править envs.

Переключив TS на вторую стратегию, где мы начали писать параллельно в новую базу, мы сразу увидели растущие тайминги и забитые php‑fpm‑воркеры, из‑за чего пришлось откатить наш эксперимент. Получилось, что, выбирая параметры сервера для нашей БД, мы опирались только на ожидаемую нагрузку, но не учли одну вещь. Оказывается, у каждого доступного к выбору инстанса в Yandex Cloud своё количество коннектов к БД, пропорциональное мощности выбранного сервера. Эта проблема была решена простым увеличением флейвора БД. А чтобы не попасть в подобную ситуацию ещё раз, мы добавили в нашу функциональность эксперимента попроцентную раскатку.

Потом мы снова начали параллельно писать в обе базы, перенесли данные нашим скриптом и переключились на стратегию, где уже читали из новой БД и где PostgreSQL генерировал ID. В таком режиме мы работали несколько дней до тех пор, пока не произошло страшное и наш master не переключился на другой дата‑центр...

Так как в Yandex Cloud по умолчанию используется схема «один master + по одному slave в каждом ДЦ», то возникает потребность выяснить, в каком ДЦ сейчас находится мастер и какой хост использовать. Когда мы добавляли параметры подключения БД в сервис, то указали специальный единый FQDN‑хост, который умеет определять активного мастера. И всё работало хорошо, пока не выяснилось, что использование этого единого хоста не рекомендуется для использования в продакшене, потому что обновление CNAME‑записи при смене ДЦ мастера может составлять от пары секунд до 10 минут.

Как итог: из‑за долгой смены CNAME мы несколько минут пытались писать в slave, вследствие чего возросли тайминги и забились fpm‑воркеры. Естественно, последовал очередной откат эксперимента, и нужно было сделать всё заново.

Для решения данной проблемы мы решили использовать связку HAProxy + PgBouncer. Мы уже давно определяли активного мастера в MySQL с помощью HAProxy, и проблем с ним на нашем трафике никогда не возникало. Для определения мастера в PostgreSQL, помимо новых правил HAProxy, мы добавили свой кастомный.sh‑чекер. PgBouncer же нужен, чтобы сократить время на установку коннектов к нашим новым БД.

#объявляем точку входа, указываем порт к которому будет подключаться pgbouncer
frontend history_master_rw
    mode tcp
    bind :::6433,127.0.0.1:6433
    default_backend history_master_rw

backend history_master_rw
    mode tcp
    #нативная проверка доступности хоста
    option pgsql-check
    option external-check
    #кастомный чекер, который определяет мастер среди хостов
    external-check command /path/check_pg_master.sh
	        
	#тут сами хосты и политики реконнектов
    server history1 hostname1.db:5432 check inter 1s rise 2 fall 3 on-marked-down shutdown-sessions
	server history2 hostname2.db:5432 check inter 1s rise 2 fall 3 on-marked-down shutdown-sessions
    server history3 hostname3.db:5432 check inter 1s rise 2 fall 3 on-marked-down shutdown-sessions

#!/bin/bash

PG_PSQL=/usr/bin/psql

# переменные которые прокидывает сюда haproxy
VIP=$1
VPT=$2
RIP=$3

if [ "$4" == "" ]; then
  RPT=$VPT
else
  RPT=$4
fi

STATUS=$(PGPASSWORD="password" $PG_PSQL "host=$RIP port=$RPT sslmode=prefer dbname=db_name user=username" -qtAX -c "select pg_is_in_recovery()" )

if [ "$STATUS" == "f" ]; then
  # Master
  exit 0
else
  exit 1
fi

[databases]
#перечисляем все базы, с портами в haproxy
history=host=localhost port=6433 dbname=dbname_history user=user password=password pool_mode=transaction
orders=host=localhost port=6434 dbname=dbname_orders user=user password=password

[pgbouncer]
#порт к которому будет подключаться наше приложение
listen_port=6455
listen_addr=127.0.0.1
pool_mode=session
log_connections=0
log_disconnections=0
log_stats=0
log_pooler_errors=1
syslog=1
ignore_startup_parameters = extra_float_digits

Таким образом мы решили проблему отказа мастера при переезде в другой ДЦ.

Далее нужно было восстановиться после отката эксперимента. Раз данные в MySQL снова ушли вперёд PostgreSQL, нужно было не только перенести отсутствующие записи, но и обновить уже существующие. Наш скрипт по переносу данных в первой версии умел делать только вставку отсутствующих строк, но не мог обновлять уже существующие записи, поэтому пришлось потратить ещё какое‑то время на доработку.

Стоило помнить, что при откатах с третьей стратегии, когда уже PostgreSQL генерирует ID, нужно обновлять sequence отдельным запросом. Потому что делая запись вместе с существующим ID, PostgreSQL, естественно, не делает nextval(), и инкремент остаётся в том же значении, на котором вы остановились.

С третьей попытки у нас всё‑таки получилось переключиться на последнюю стратегию и полностью перейти на PostgreSQL для данной таблицы.

Переключение остальных таблиц происходило тоже далеко не гладко. Вот пара интересных проблем, с которыми мы столкнулись.

• При отвязке джоинов основное внимание мы акцентировали на запросах, сделанных через query builder, и в целом проблем не возникало. Но в огромной кодовой базе мы несколько раз пропускали разрыв связей в raw‑запросах вида: "SELECT * FROM place_log pl JOIN places p ON p.id = pl.place_id". Получалось, что после полного переключения коннекта мы пытались притянуть таблицу places, которой в новой базе не существует.

• Были случаи, когда в основной БД произошел rollback, который, конечно же, никак не мог повлиять на rollback в новой базе. Следовательно, приходилось дописывать логику, вынося изменение записей новой БД в пост‑ивенты или оборачивая транзакцией из другого коннекшена.

• Воркеры очередей у нас представлены демонами, в которых есть специальная стратегия восстановления подключения для MySQL, если переключился дата‑центр или база на какое‑то время моргнула. Для новых postgres‑подключений такой стратегии не было добавлено. Так что в один прекрасный день у нас посыпалась обработка задач в очередях, потому что подключение с базой было разорвано, а скрипт‑демон продолжал обрабатывать приходящие задачи. Быстро поправить это получилось только ручным перезапуском воркеров, но были потери в заказах.

Несколько выводов о подходе

1. Чтобы использовать TableSwitcher‑подход, требуется много подготовки. На слабосвязанных таблицах он показал хороший результат. Однако работа по написанию кода и правке тестов для вынесения таблиц в отдельный кластер равна работе по переходу на обращение в сервис по API.

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

3. TableSwitcher‑подход позволяет быстрее разгрузить базу. Но для полноценного распила монолита всё равно придётся написать код перехода на эндпоинты сервиса и поправить тесты, поэтому см. п. 2.

4. Система с бо́льшим количеством узлов менее устойчива, и есть определённые риски при отказе какой‑то из БД.

5. Всегда нужно ставить задачу на удаление развилок с экспериментами. Нередки случаи, когда из‑за дефолтного состояния фича‑флага тесты запускаются на старой функциональности.

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

7. Когда у вас больше одной базы данных, вы рискуете словить проблемы с транзакциями. Разработчики могут не понять, что внутри метода используется два разных коннекта, и обернуть в транзакцию в надежде, что всё откатится в случае необходимости.

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

9. Обязательно нужно предусмотреть постепенную раскатку функциональности и держать под рукой достаточно графиков, так как проблемы в одной базе могут затронуть весь сервис (как в нашем случае при переполнении пула коннектов).

Дополнительные работы 

Наша база всё ещё живёт на железных выделенных серверах, а не в облаке. У неё нет общих унифицированных инструментов диагностики, мониторинга, восстановления и обновления, как в Yandex Cloud, поэтому возникает сложность в поддержке со стороны отдела эксплуатации. Также у нас нет возможности масштабировать эти железки, поэтому изначально, с запасом, было заложено очень много ресурсов. В совокупности это 6 ТБ ОЗУ и 1536 ядер, что очень нерационально и дорого. Поэтому следующее вложение в стабильность — перенос базы монолита в YC.

В отделе эксплуатации подсчитали: чтобы перенести базу с наименьшими рисками, она должна весить не более 750 ГБ против 4 ТБ на тот момент. То есть не распиливая монолит полностью, нам нужно было сократить количество данных в таблицах более чем в пять раз.

Частично наша задача начала продвигаться уже на этапе вынесения таблиц, но уменьшение базы до нужного размера таким подходом требует огромных трудозатрат. Да и невозможно отлинковать все таблицы, не распилив монолит полностью.

Мы стали пользоваться более простыми подходами. Сначала при помощи запроса…

select * from (select table_name, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `GB_size`, update_time from information_schema.tables where table_schema = "eda") sub order by sub.GB_size desc

…опираясь на поле updated_time, мы отсеяли забытые таблицы со старыми данными, которые, например, забыли почистить после переноса логики на отдельный сервис. Тем самым сократили больше 150 ГБ.

Просмотрев записи самых тяжелых таблиц, мы увидели, что есть данные, датированные ещё 2015 годом. Для аналитиков у нас используются другие хранилища, которые ежечасно обновляются, а значит, такое долгое хранение в основной БД нам не требуется. Эти таблицы мы добавили в ежедневные чистки по расписанию и освободили больше 300 ГБ.

Затем мы организовали встречу с ответственными командами и решили изучить детальнее контент таблиц и их потребителей. Мы смогли избавиться от таблиц, в которые давно ничего не пишем. Но при этом задумались: что, если есть таблицы, в которые мы продолжаем до сих пор что‑то писать, но эти данные никому не нужны?

Встал вопрос, как определить такие таблицы. На тот момент монолит содержал 900+ эндпоинтов. Мы знали, что далеко не все ручки актуальны, поэтому собрали по access log nginx все обращения в монолит и отсеяли неиспользуемые. Далее выгребли из кода все репозитории из этих эндпоинтов и получили названия таблиц, по которым ещё раз прошлись в коде и убедились, что больше нигде на чтение они не используются.

Казалось бы, сомнительный подход, но он позволил нам полностью отказаться от трёх таблиц общим весом 150 ГБ.

Некоторые индексы на таблицы добавлялись разными разработчиками и под разные задачи. Возможно, некоторые индексы уже не используются. Это мы проверяем посредством такого запроса:

SELECT DISTINCT stat.table_name,
    indx.index_name,
    cntRows,
    round((stat_value * @@innodb_page_size) / 1024 / 1024 / 1024, 2) `SizeinGB`
FROM sys.schema_unused_indexes indx
    INNER JOIN mysql.innodb_index_stats stat on stat.index_name = indx.index_name AND stat.stat_name = 'size'
    LEFT JOIN (
        SELECT TABLE_NAME, SUM(TABLE_ROWS) as cntRows
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'eda'
        GROUP by TABLE_NAME
    ) cntTable ON cntTable.TABLE_NAME = stat.table_name

В нашем случае такие индексы нашлись, но занимали совсем немного места, поэтому данный подход для нас был не сильно продуктивным. Важно помнить, что данный запрос нужно смотреть на всех хостах, потому что есть запросы, которые ходят только на мастер, а значит, на слейве данной статистики по индексу не будет, хотя по факту он используется.

Такими нехитрыми способами мы смогли срезать ~600 ГБ данных. Но до требуемого размера в 750 ГБ ещё далеко, и надо решать, что делать с остальным данными.

Исторически сложилось, что информация о заказе в Яндекс Еде хранилась несколько месяцев. Некоторые данные, например таблицы с составом заказа, мета‑информацией, историей логистики, составляют львиную долю от всего хранимого в базе. Связанность у сущностей очень крепкая, отделить и унести пул таблиц не представляется возможным. Поэтому мы стали разбираться: а для каких процессов вообще требуется долгое хранение заказа?

Обойдя все группы, мы выяснили, что старые данные о заказе используются только командой поддержки клиентов, конкретно — для выдачи компенсаций, а также для выборочных сверок, которые могут происходить спустя месяц после завершения заказа. Мы собрали информацию об используемых данных и смогли составить список сервисов, в которых эта информация уже есть. В течение нескольких недель мы перевели походы в БД «на ручки» этих сервисов, а следовательно, уже было не нужно хранить данные о заказах в монолите. Мы добавили ежедневные чистки и оставили хранение информации по заказу только на месяц. Именно этот шаг помог нам дойти до заветного размера базы в 750 ГБ.

Итоги

С помощью нашего инструмента TableSwitcher у нас получилось унести 27 из 400+ таблиц в пять разных postgres‑кластеров. Общий вес вынесенных таблиц составил ~600 ГБ, а RPS сократился на 7K (в основном пишущего трафика). Проблема с Flow Control на Galera Cluster нас перестала беспокоить, а проблема с ресурсами теперь не стоит так остро из‑за тенденции к снижению нагрузки.

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

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

Главная идея этой статьи — не предоставление пошагового гайда, а подсветка проблем, с которыми столкнулись мы и с которыми может столкнуться читатель при решении такой типовой задачи, как уменьшение размера и нагрузки БД. Само собой, используемые подходы зависят как от специфики проекта, так и от технических требований, поэтому может быть придумано огромное количество реализаций. Было бы интересно узнать, а какими подходами пользовались вы? Делитесь своим опытом в комментариях.