Как спасти проект от закрытия, разобравшись с MySQL
Отправная точка
По мере развития игры игровых объектов становится все больше и больше, компании растут и обсчитывать игровую ситуацию становится все сложнее и сложнее. Транзакции повисали по таймауту и игровые объекты сохраняли свое состояние с ошибками, что приводило в свою очередь к другим ошибкам. В логах сервера с завидной регулярностью писалось о следующей проблеме: Lock wait timeout exceeded; try restarting transaction.
Google явного решения не давал, общая рекомендация заключалась в прочесывании бизнес-логики.
Ночные звонки о проблемах, бессонные ночи, сорванные выходные. В какой-то момент мы дошли до состояния перманентной тревоги, и перестали удивляться происходящим ошибкам. Также на некоторые действия игроков сервер реагировал непозволительно долго.
Данная ситуация провоцировало логичное негодование игроков, это приводило к постепенному оттоку игроков и падению выручки.
В общем — ситуацию надо было спасать. Засучив рукава, мы начали с чистого листа искать решение.
Обновляем инструментарий
Для этого пришлось проапгрейдить MySQL до версии 5.7, чтобы полноценно работала performance_schema, она позволяет в реальном времени собирать статистику и оперативно отслеживать влияние изменений. Не могу сказать, что апгрейд прошел гладко, требуется терпение. Кстати с 5.5. на 5.7 сразу перейти нельзя, сначала надо 5.6 поставить. Далее устанавливаем MySQL Workbench — удобный интерфейс для анализа БД. Затем инсталлируем performance_schema в разделе Performance
Первая настройка
Смотрим данные и проверяем, насколько работа базы данных соответствует бизнес-логике, нет ли повышенной нагрузки там, где ее быть не должно, проверяем потенциально узкие места. Используя отчет Top File I/O File by Time было открыто, что розничная статистика каждый раз запрашивается из базы. Т.к. данные не меняются в игровой день и одинаковы для всех игроков необходимо эти данные закэшировать. Эти запросы выпали из статистики самых частотных, аналитика стала выдаваться многовенно и вообще все вроде бы должно стать ок.
Все равно медленно
Нагрузка снизилась, но по какой-то причине интерфейс работает с трудом. Вероятно возникает Lock на MyISAM таблицах, пытаемся поймать по show processlist —, но безуспешно. Ситуация возникает несколько раз в день, без серьезных симптомов. Пишем простой код для того, чтобы логировать возникающие Lock:
Таблица для сохранения данных.
create table processlist_stat like information_schema.processlist
alter table processlist_stat add column datetime datetime;
Делаем Thread, который будет постоянно писать в базу о проблемах.
public static class ProcessListThread extends Thread {
private long timeout;
public ProcessListThread(long timeout) {
super("ProcessListThread");
this.timeout = timeout;
}
@Override
public void run() {
if (timeout > 0) {
while (!interrupted()) {
try {
sleep(timeout);
} catch (InterruptedException ie) {
System.out.println("Interrupted!!!");
return;
}
if (processListThreadStopped) {
Thread.currentThread().interrupt();
System.out.println("SelfDestruction");
}
try {
saveProcessListStat();
} catch (Throwable e) {
Logger.error(e);
}
}
}
}
}
Сохраняем данные в базу:
private static void saveProcessListStat() throws ru.plazma.db.DBException {
Executor e = GameObject.getExecutor();
Query rs = e.getConnection().newQuery();
rs.select("id,user,host,db,command,time,state,info", "information_schema.processlist", "info is not null and info not like '%info is not null%'");
rs.calculate("sysdate() as datetime");
Statement st = e.newStatement();
st.insert("processlist_stat", "id,user,host,db,command,time,state,info,datetime", rs);
st.execute();
e.commit();
}
Пуск/Запуск:
public static void runProcesslistLog() throws DBException {
if (processListThread == null) {
processListThread = new ProcessListThread(5 * 1000);
}
processListThreadStopped = false;
processListThread.run();
}
public static void stopProcesslistLog() {
if (processListThread != null) {
processListThreadStopped = true;
processListThread.interrupt();
}
}
Данную идею можно реализовать на триггерах самой базы данных, но при его запуске у меня напрочь завалился сервер, так что выбран был именно описанный выше путь.
Бинго
Смотрим в таблицу, созданную выше, куда каждый 5 сек. пишется состояние запросов. Вот же они… Waiting for table level lock длительностью по 60 секунд… оказалось что ключевая таблица sales запирается из-за того, что подозрительно долго идут запросы к ней. Попутно всплыли еще какие-то запросы, которые тоже идут слишком долго.
Решаемся на двойной удар — очистить таблицы от старых данных (стирали недостаточно), прочесать бизнес-логику и убрать ненужные обращения к таблице. Стало лучше, но не сильно. В чем же причина… Индексы стоят, вроде все должно быть ок —, но нет. Долго.
Ох уж эти индексы
Оказалась, что висящие запросы делают full scan…, а Primary Key не задействуют… все дело в конструкции between. Фильтр по составному PK, если используется between почему-то не активируется. Делаем дополнительные индекс… и вауля — все полетело.
Итоги
Эта переделка вдохнула новую жизнь в проект, и дала жизненно важное время на разработку нового функционала, который мы через несколько месяцев запустили, не растеряв аудиторию.
Комментарии (23)
VMichael
19 февраля 2017 в 21:05
+1↑
↓
Хм.
Есть ведь мнение, что БД это ерунда и ОРМ наше все.19 февраля 2017 в 23:24 (комментарий был изменён)
0↑
↓
А есть такие ОРМ, которые могут анализировать запросы и давать рекомендации по добавлению индексов и всего такого?
-
VMichael
19 февраля 2017 в 23:29
0↑
↓
Вряд ли.
Хотя я не спец по ОРМ.19 февраля 2017 в 23:33 (комментарий был изменён)
0↑
↓
Пока таких нет — БД таки наше всё. И, даже, если они появятся — выбор СУБД будет много значить, потому что в некоторых из них физически нет фич, которые в других — в наличии.
19 февраля 2017 в 23:31
0↑
↓
Ради интереса почитал что такое ОРМ… Напоминает сферического коня в вакууме. Прекрасные риски получить неработающий продукт, в котором ни один программист не сможет разобраться. Видел я один замечательный проект, где к базе данных запрос был длиной кажется в 20КБ…-
VMichael
19 февраля 2017 в 23:34
0↑
↓
ОРМ это такой мейнстрим сейчас.
Серебрянная пуля.
Недавно на хабре видел, что пишут, что и нормализация БД уже никому не нужна.
Что спецов по БД найти сейчас трудно, поэтому и заморачиваться БД не нужно.
Есть отличные инструменты в виде ОРМ.
Это так, ирония с моей строны конечно.19 февраля 2017 в 23:39
0↑
↓
Существует дикое количество способ как гарантированно завалить проект. Спору нет :)
Когда таблиц становится хотя бы 100+ начинается веселая жизнь…19 февраля 2017 в 23:40 (комментарий был изменён)
+1↑
↓
ОРМ и спецы по БД это вещи ортогональные. Если вы используете ОРМ — специалисты по БД всё равно будут нужны. И заморачиваться всё равно придётся. Ну и нормализация БД — это тоже никак не связано с ОРМ.
19 февраля 2017 в 23:38
0↑
↓
ОРМ позволяет сократить количество бойлерплейта за счёт автоматической генерации джойнов и маппинга таблиц в объекты. Если использовать грамотно, то риск получить неработающий продукт невелик.
20 февраля 2017 в 06:14
0↑
↓
Прекрасные риски получить неработающий продукт, в котором ни один программист не сможет разобраться.
Странное у вас впечатление. ОРМ создаются, по сути, чтобы упростить продукт, вынеся процесс работы с СУБД в отдельный слой, единственная ответственность которого преобразование объектов в запросы и наоборот.
diamond_nsk
19 февраля 2017 в 21:07
+1↑
↓
Какой у вас размер базы и кол-во запросов в секунду в прайм-тайм, если не секрет? Чем держите нагрузку?19 февраля 2017 в 21:15
0↑
↓
Сейчас после рестарта бэкап весит около 700МБ. А год назад — 2.5 ГБ. Это только значимые данные без логов операций, развернутое — в три раза больше + индексы. Ну и каждый пересчет (7 раз в сутки) наверно по 50–100МБ пишем данных, а потом ночью стираем хвосты.
Мы показываем игрокам 300 тыс. страниц в сутки. Счетчики в публичном доступе. Нагрузку держим за счет хитрющей системы кэширования.
GarretUA
19 февраля 2017 в 23:46
0↑
↓
А на каком железе работает база? Перевели бы на InnoDB/XtraDB и с вашим объемом данных вообще все в память загнали. Пара часов времени и можно спать ночью.19 февраля 2017 в 23:49
0↑
↓
Работает RAID на 5 дисках.
Загнать всю базу в память… сейчас с трудом все залезет, но она же растет…
Может часть имеет смысл попробовать положить. Спасибо за идейку, подумаю на досуге.-
VMichael
19 февраля 2017 в 23:52
0↑
↓
Лучше загнать все в память, чем парится с индексами?
m0Ray
19 февраля 2017 в 21:16
0↑
↓
А почему используется движок MyISAM, который как раз таки славится своими проблемами с блокировкой таблиц?19 февраля 2017 в 21:22
0↑
↓
Тут два аспекта. Пишет быстрее данные, чем InnoDB. Хотя я в курсе, что намного правильнее там использовать транзакции. Во-первых он там появился давным давно, я пробовал менять —, но не понравилось как работает. А сейчас — работает, не трожь :)20 февраля 2017 в 01:26
+1↑
↓
у вас очень старая информация.
19 февраля 2017 в 21:21
+1↑
↓
Зачем, если не секрет, вам понадобилась реляицонная БД в экономической игре?19 февраля 2017 в 21:23
0↑
↓
Какие есть варианты? В чем хранить?20 февраля 2017 в 01:14
0↑
↓
Объекты игрового мира — в памяти, в структурах специализированных, наиболее подходящих под вашу конкретную игру, это вы расскажите что именно вы там храните и зачем.
youlose
20 февраля 2017 в 00:19
0↑
↓
Не понравился ваш костыль, во-первых логгируйте медленные запросы, там данные о долгих заблокированных запросах есть. А во-вторых поставьте MariaDB или Percona мускуль, там в логах медленных запросов больше информации, даже можно сразу explain туда выводить, если совсем всё грустно.20 февраля 2017 в 01:04
+1↑
↓
По абзацу «Ох уж эти индексы» хотелось бы подробностей.
С DDL таблиц, тесткейсом, планом запроса до и после.
