Отправная точка
По мере развития игры игровых объектов становится все больше и больше, компании растут и обсчитывать игровую ситуацию становится все сложнее и сложнее. Транзакции повисали по таймауту и игровые объекты сохраняли свое состояние с ошибками, что приводило в свою очередь к другим ошибкам. В логах сервера с завидной регулярностью писалось о следующей проблеме: 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 почему-то не активируется. Делаем дополнительные индекс… и вауля — все полетело.
Итоги
Эта переделка вдохнула новую жизнь в проект, и дала жизненно важное время на разработку нового функционала, который мы через несколько месяцев запустили, не растеряв аудиторию.
Комментарии (116)
diamond_nsk
19.02.2017 21:07+1Какой у вас размер базы и кол-во запросов в секунду в прайм-тайм, если не секрет? Чем держите нагрузку?
qdreadknight
19.02.2017 21:15Сейчас после рестарта бэкап весит около 700МБ. А год назад — 2.5 ГБ. Это только значимые данные без логов операций, развернутое — в три раза больше + индексы. Ну и каждый пересчет(7 раз в сутки) наверно по 50-100МБ пишем данных, а потом ночью стираем хвосты.
Мы показываем игрокам 300 тыс. страниц в сутки. Счетчики в публичном доступе. Нагрузку держим за счет хитрющей системы кэширования.
GarretUA
19.02.2017 23:46+2А на каком железе работает база? Перевели бы на InnoDB/XtraDB и с вашим объемом данных вообще все в память загнали. Пара часов времени и можно спать ночью.
qdreadknight
19.02.2017 23:49Работает RAID на 5 дисках.
Загнать всю базу в память… сейчас с трудом все залезет, но она же растет…
Может часть имеет смысл попробовать положить. Спасибо за идейку, подумаю на досуге.
VMichael
19.02.2017 23:52-2Лучше загнать все в память, чем парится с индексами?
wladyspb
20.02.2017 12:59-1Индекс тоже хранится в памяти, и если на одну таблицу весом 2гб приходится 1.5гб индексов в памяти — намного выгоднее перенести в память саму таблицу.
poxvuibr
20.02.2017 13:10+2Индекс тоже хранится в памяти, и если на одну таблицу весом 2гб приходится 1.5гб индексов в памяти — намного выгоднее перенести в память саму таблицу.
А как перенос таблицы в память решит проблему, которую решают индексы? Перенос в память, ведь не сделает её волшебным образом отсортированной по всем комбинациями полей, на которые делаются индексы. Операции, конечно побыстрее будут проходить, чем когда таблица без индексов хранится на диске, но full scan то не денется никуда.
wert_lex
20.02.2017 07:18+1300 000 страниц в сутки это 3.5 запроса в секунду.
Парни, вам есть еще куда расти во-первых, а во-вторых положите уже всё в память, она по-прежнему быстрее любого SSDqdreadknight
20.02.2017 11:10Нам есть куда расти, это факт.
SSD мы пытались воткнуть, но наш сервер через месяц стабильной работы пришел в полную негодность. Проект два дня не работал, еле вернули пациента к жизни, убрав новый диск. Видимо наше железо не смогло подружится с ним.
diamond_nsk
20.02.2017 12:57+2Как уже писали ниже:
1. Базу в оперативку;
2. Может всё же ИнноДБ? Даже просто из соображений той же целостности данных, игра всё-таки «БизнесМания», экономика, все дела;
3. Рэид 5? Он в произвольной записи порой проседает на четверть;
4. Что за «хитрющее кэширование»? Сфинкс штоле?
5. Основная масса запросов к базе — чтение?
m0Ray
19.02.2017 21:16+5А почему используется движок MyISAM, который как раз таки славится своими проблемами с блокировкой таблиц?
qdreadknight
19.02.2017 21:22-3Тут два аспекта. Пишет быстрее данные, чем InnoDB. Хотя я в курсе, что намного правильнее там использовать транзакции. Во-первых он там появился давным давно, я пробовал менять — но не понравилось как работает. А сейчас — работает, не трожь :)
darkmind
20.02.2017 11:40+3Ну перестаньте уже эту городскую легенду таскать, тем более что у вас уже 5.7. Innodb прекрасно в нем работает, а недостатков у MyISAM столько, что я уже не знаю зачем его можно использовать.
В вашем случае переезд на Innodb как минимум даст
а) неблокирующее чтение вообще, т.к. данные читаются из снапшота
б) локи только на нужные строки
Это гарантированно улучшит общий перфоманс базы.qdreadknight
20.02.2017 11:43MyISAM там появился во времена, когда InnoDB только набирал обороты. Проекту не 2 и на 5 лет :)
В следующей итерации попробуем все-все все-таки перенести на Inno, главное чтобы ничего не порушилось.
В лоб смена движка не прошла в свое время.
parpalak
20.02.2017 18:10Иногда MyISAM бывает полезен. Была в проекте на прошлой работе одна InnoDB-табличка на несколько сотен записей. Она пополнялась в триггере при изменении записей в другой таблице. Но иногда вставки по непонятным причинам не происходило. Я долго не мог докопаться до настоящей причины, пока не сделал добавление записей из кода. Оказалось, что вставка иногда не срабатывает из-за ошибки типа unique constraint violation на автоинкрементном первичном ключе. Админы подтвердили, что это известная проблема в MySQL 5.6, но быстро перевести production на 5.7 они не могли. Пришлось переключить тип таблицы с InnoDB на MyISAM. Проблема исчезла.
darkmind
21.02.2017 14:56+1Была в проекте на прошлой работе одна InnoDB-табличка на несколько сотен записей. Она пополнялась в триггере при изменении записей в другой таблице.
Т.е. по сути это логгер таблица. Вообщем да, это пожалуй единственный полезный кейс MyISAM т.к. нетранзакционная, а в mysql нет autonomous transaction. Если надо заллогировать фейл транзакции то выбора по сути нет.
Но такая лог таблица обычно а) не содержит критикал данных б) нет требований по производительности, так что по сути в вашем случае можно было взять любой другой движок на выбор.
robert_ayrapetyan
19.02.2017 21:21+1Зачем, если не секрет, вам понадобилась реляицонная БД в экономической игре?
qdreadknight
19.02.2017 21:23Какие есть варианты? В чем хранить?
robert_ayrapetyan
20.02.2017 01:14-1Объекты игрового мира — в памяти, в структурах специализированных, наиболее подходящих под вашу конкретную игру, это вы расскажите что именно вы там храните и зачем.
wladyspb
20.02.2017 13:03+1+1, разверну мысль robert_ayrapetyan, на мой взгляд его недопоняли.
Сам сейчас обдумываю как хранить данные, и реляционная таблица подходит для сырых данных, однако в игре нужно будет работать с обработанными данными, взятыми из многих таблиц, над которыми производятся рассчёты. Делать это на лету каждый раз не очень разумно, поэтому имеет смысл сформировать «игровое состояние» конкретного игрока, и хранить его в КВ или документарной базе, обновляя сырые данные в бд по мере необходимости.robert_ayrapetyan
20.02.2017 19:04Тут в соседней статье по Hello world на PHP (https://habrahabr.ru/post/322170/) забавно написано: «Но прежде, чем его [фреймворк] устанавливать, надо бы создать базу данных. Зачем базу данных? Ну не хардкодить же строку «hello, world» прямо в тексте программы!»
youlose
20.02.2017 00:19Не понравился ваш костыль, во-первых логгируйте медленные запросы, там данные о долгих заблокированных запросах есть. А во-вторых поставьте MariaDB или Percona мускуль, там в логах медленных запросов больше информации, даже можно сразу explain туда выводить, если совсем всё грустно.
miksoft
20.02.2017 01:04+3По абзацу «Ох уж эти индексы» хотелось бы подробностей.
С DDL таблиц, тесткейсом, планом запроса до и после.
Maiami
20.02.2017 06:38+9В общем — ситуацию надо было спасать. Засучив рукава, мы начали с чистого листа искать решение.
Молодцы, что решение всё таки нашли. Но так как статья с меткой «tutorial», то стоит отметить, что в mysql для таких целей уже есть всё необходимое (youlose уже упоминал это). Достаточно добавить в my.cnf несколько строчек:
slow-query-log = 1 slow-query-log-file = /var/log/mysql/slow.log long_query_time = 1 log-queries-not-using-indexes
В файл /var/log/mysql/slow.log будут попадать запросы которые выполняются медленнее 1 секунды, а так же запросы которые не используют индексы. Кроме самих запросов там много сопутствующей информации. В mariaDB или percona информации даже больше будетVolCh
20.02.2017 09:20-1Плохо работает, когда есть запросы, штатно выполняющиеся минуты (аналитика, например).
qdreadknight
20.02.2017 11:46Мне показалось, что метка уместна. Возможно я не прав.
Но вдруг моя идея кому-то поможет.
Лог медленных запросов в моем случае ничего не показал.
Мой метод дал мне полную картину, когда и в какой момент времени у меня идет зависание, связано это с обсчетом игровой ситуации, или это повышенная активность людей.
jehy
20.02.2017 07:53+4Кстати с 5.5. на 5.7 сразу перейти нельзя, сначала надо 5.6 поставить.
Что-то?
Про использование MyISAM и про то, что профилировние длинных запросов делается настройками my.ini, здесь уже сказали.
Так что в целом я тут вижу только отсутствие внятного специалиста по СУБД, и метка «tutorial» смущает.
customtema
20.02.2017 08:18+2InnoDB, Percona, не?
Полчаса работы, и все ваши проблемы яйца выеденного не стоят. Неужели гуглеж не подсказал?..
gmaker
20.02.2017 08:43+2processListThread.run();
Вы уверены, что запускали потоки?
romamo
20.02.2017 09:07Да ничего, норм. Пацаны только в школе учатся. Надо самому ощутить всю боль, прежде чем начнешь сразу строить правильную архитектуру.
qdreadknight
20.02.2017 11:18С архитектурой у нас все более чем в порядке.
Проблема-то от чего возникла — писали в быстром темпе какие-то вещи, и до поры до времени проблем не было. А потом бах — работает медленно. А никто не помнит, почему так написано. Откуда копать. Это хорошо назад рассуждать о том, что блин — ставь индексы правильно или кэшируй запросы. А когда ты считаешь, что все описанные вещи уже итак давно решены — что делать, с чего начинать копать.romamo
20.02.2017 11:23Архитектура — это не нормализация таблиц, а описание всей схемы приложения в целом и взаимодействия всех его компонентов. И политика работы с бд в том числе (джойны нельзя, какие ключи можно и т.д.).
Kwisatz
20.02.2017 11:18+1Имхо слишком большие проблемы для такого малого количества данных. Нужно тщательно анализировать архитектуру бд целиком.
Но на вскидку несколько советов:
1. Если используете MyIsam старайтесь организовать логику так, чтобы бОльшая часть запроса была одного типа (insert/select/update)
2. в where столбцы должны идти ровно в том порядке, в каком указаны в составном ключе
3. Включите логирование запросов со временем выполнения более 1с. Если таковых много или очень встречаются часто — явно что-то делаете не так.
4. Прогоните explain всех запросов. У MySQL очень плохой оптимизатор. Есть тысяча и один способ сделать запрос быстрее и легче.
5. В идеале — перейдите на postgesql.
6. Наймите крутого специалиста по БД.qdreadknight
20.02.2017 11:23-1Спасибо.
1. Так и сделано. Проблема возникла из-за того, что бизнес-логика работала не по тем данным.
2. Вроде это никогда не влияло.
3. Show slow query мне влоб в свое время не помог, может быть я неверно что-то там делал.
4. Все-все запросы, это проще повесится. Самые активно используемые — смотрел, с ними все более менее, кроме описанного в статье.
5. Это слишком дорого.
6. Пока у нас все проблемы относительно решены. Скорее купим более мощный и современный сервер.
TheRaven
20.02.2017 14:42+1InnoDB + SSD и на таких объемах вы еще долго просидите на текущем железе.
MyISAM с его table-level локами в 2017 году это нонсенс.
Kwisatz
21.02.2017 00:192. Всегда влияло. Возможно за последний год и поправили но я сомневаюсь. На большой таблице сделайте индекс (fieldA, fieldB, fieldC) а затем explain select * from table where fieldC=1 and fieldB=2 and fieldA=3
3. Если он не пуст — у вас проблемы. За исключением отчетов ни один запрос не должен выполняться дольше 0.1с.
4. Для начала всех что попадают в slow query log.
5. Если уж покупать новый сервер то и на postgre неплохо бы. SQL почти тот же, только специалист нужен. Поставить как можно больше оперативной памяти и отдать ее всю для бд.
6. Тоже когда-то думал так же, после покупки второго сервера подряд одумался.
Касательно «проблемы относительно решены». Нужно оперативно мониторить. Ко мне очень часто обращаются люди как раз по таким проблемам и я каждый раз, после того как их сервисы начинают летать а не ползать, рекомендую полный аудит и выдаю бесплатно десяток рекомендаций. Каждый раз потом тянут до последнего и возвращаются уже с лежащим проектом, полудохлой базой и «нужно оченьсрочнотеряемденьги».qdreadknight
21.02.2017 09:05Я думаю да, следующий апгрейд будет именно железячный, когда потребуется. Надеюсь с деньгами к тому моменту проблем не будет :)) я писал где-то, SSD в текущей конфигурации сервера сработал как троянский конь завалив вообще все.
miksoft
21.02.2017 13:152. Всегда влияло. Возможно за последний год и поправили но я сомневаюсь. На большой таблице сделайте индекс (fieldA, fieldB, fieldC) а затем explain select * from table where fieldC=1 and fieldB=2 and fieldA=3
Именно в такой форме я такого не встречал, как минимум, с версии 4.1. Более ранние версии не застал.
Вероятно речь идет о случае, когда один из предикатов содержит не строгое равнство, а диапазон.
Вот тогда порядок полей в индексе имеет значение.
Для упомянутого индекса запрос where fieldC>1 and fieldB=2 and fieldA=3 сможет использовать индекс, а запрос where fieldC=1 and fieldB=2 and fieldA>3 — скорее всего нет.
darkmind
20.02.2017 11:431. Не поможет. Вот к примеру идет поток insert'ов на MyISAM таблицу и каждый делает лок/инсерт/анлок. Т.е. происходит такая себе конвертация в serializable что все равно никак не ускорит процесс.
2. В MySQL оптимизатор конечно не идеальный, но такую оптимизацию даже он сделает.
4. На самом деле в 5.7 (да даже с 5.6) его неплохо так пилят и он уже вполне неплох.Kwisatz
21.02.2017 00:10я очень много работал с Mysql (myisam/innodb): порядка 500млн записей на таблицу, 1.1 млн пользователей, 100 тысяч уникальных посещений в сутки. Вариантов для экспериментов было масса.
Способов руками сделать лучше чем оптимизатор для mysql я знаю не менее полусотни: это и танцы с параметрами и вложенные запросы там где они не нужны и много других «приятных» вещей. Одним из наиболее часто встречающихся сценариев является замена join с таблицей на join с подзапросом, такие вещи сразу видны по explan
Как кто-то умный сказал: «Oracle это база данных, а mysql это хер с гвоздями». Перед тем как перейти на postgesql я 12 часов баловался с различными запросами, начиная от однострочных и заканчивая такими что на распечатке займут страниц 10. Итог: хуже сделать получилось, лучше — нет.darkmind
21.02.2017 15:07Ну скажем так — оно то может и можно подпинать оптимизатор в сторону более идеального запроса, но разница обычно небольшая ~1%. Я говорю про случаи когда запрос написан вдумчиво и правильно, а не абы как, тогда и подсказывать ненадо.
является замена join с таблицей на join с подзапросом,
Сколько не видел — всегда появление подзапроса портило план выполнения, вот уж где оптимизатор Mysql тупит вовсю. Из последних случаев — наличие подзапроса вызывало лок всех строк в таблице, как по выводу explain так и по факту. Выкидывание подзапроса и join таблицы на себя — получаем идеальный план выполнения.
У оптимизатора Oracle свои причуды. Например он очень котирует индексы по временным колонкам в партиционированной по времени таблицы и игнорит другой очень селективный индекс.
Kwisatz
21.02.2017 00:21Я дико извиняюсь, но меня мучает один вопрос: а зачем mysql то? Простой пример: как вы решаете проблемы с отсутствием рекурсивных запросов?
VolCh
21.02.2017 07:52Есть мнение, что для разработчиков хорошо знающих MySQL это не проблема. Я вот за много лет работы с MySQL ни разу с этой проблемой не сталкивался. Просто не приходило в голову решать какие-то задачи с помощью рекурсивных запросов. Собственно толком и не знаю что это, не смотря на то, что уже с полгода в продакшене крутится база на Постгрес.
Kwisatz
21.02.2017 08:05Без проблем: вывод одним списком трех уровней форума. Конечно решается через nested sets но подобных вещей непридумывать и посложней не проблема.
Есть и другие доводы: fts, функциональные индексы, схемы, instead of триггеры, нормальный оптимизатор и так далее.VolCh
21.02.2017 09:06В нашем случае моим доводом перед руководством за переход были функциональные и частичные индексы, чтобы избавиться от триггеров, вычисляющих выражения (хотя бы банальное взятие даты от датывремени) или null если под условие не попадает и заносящие их в служебный столбец. WITH и WINDOW — бесплатный бонус для меня к индексам. Но админы меня потихоньку проклинают — для них в мускуле было всё понятно, да и я их практически не беспокоил, а теперь пользователей создают они.
А схемы в мускуле есть, просто их обычно называют базами данных, но нет того, что в постгресе называют базой данных.
Kwisatz
21.02.2017 11:16Кстати да, with вещ просто нереально удобная. А вот про window спасибо, все забывал прочесть про них.
Про админов немного не понял. На каждом углу лежат гайды с рекомендуемыми настройками postgesql и pgbouncer.
Схемы нереально удобны когда в них помещаешь только view или like таблицы и спокойно выставляешь овнером пользователя.poxvuibr
21.02.2017 11:42+1Кстати да, with вещ просто нереально удобная.
Вы с этим осторожнее, в postgres with это optimization fence. Производительность может неслабо убить. Но, за счёт материализации, может и увеличить.
VolCh
21.02.2017 15:40В мускуле я мог сам пользователей для приложений добавлять через SQL, в постгресе требуется как минимум править hg_hba.conf и перезагружать сам постгрес. Ну и ещё там, типа из коробки только сервер целиком реплицируется, одну базу, не говор яо таблице нельзя
miksoft
21.02.2017 13:11Без проблем: вывод одним списком трех уровней форума.
Для фиксированного количества уровней можно обойтись JOIN-ами без иерархических запросов.
И, кстати, иерархические запросы в MySQL теоретически возможны с использованием переменных, но сложны для построения и сопровождения и очень медленны. Так что их практическая ценность стремится к нулю.poxvuibr
21.02.2017 16:25Тут даже джойны не особо нужны. Если хранить иерархию в виде 1.2.3.45 и уровень вложенности каждого сообщения.
И запрос с
where path like '1.%' and level < 4
qdreadknight
21.02.2017 09:03MySQL появился там исторически. Проекту много лет. Зачем мне нужны рекурсивные запросы? Потребностей пока не было.
miragenn
20.02.2017 12:43Есть утилита pt-query-digest она может снимать данные с логов(slow_log/binlog), сервера или из перехваченного(tcpdump) трафика. Строит различные отчеты, в том числе по медленным запросам.
Duss
21.02.2017 17:50Не перечитывал все комменты, может уже писали, но добавлю от себя:
1) 5.5 можно обновить до 5.7 без 5.6 и это может быть даже проще.
2)Делаем дополнительные индекс…
Существует force index (название индека) позволяющий заставить базу использовать индекс, даже если оптимизатор запросов решил этого не делать. Может помочь в части ситуаций, но с ключевым полем может и не сработать. Но все-таки доп индекс это лишняя нагрузка.
А вообще нужно внимательно смотреть все запросы к базе (не только тяжелые), изучать эксплейн. Смотреть триггеры и индексы.
Триггеры вообще больное место, любая мало мальски не оптимальная директива выльется в большую нагрузку. Иногда очень хорошо помогают составные индексы, зачастую результат запроса можно рассчитать из того же индекса, в котором хранятся условия фильтрации.
Была ли оптимизация конфига базы? Про это правда не мало написано мануалов. И т.д. и т.п. вы описали только вершину айсбегра под названием бесконечная оптимизация.
darkmind
22.02.2017 10:23Оптимизация конфига MySQL это дать побольше под innodb_buffer_pool_size и включить innodb_numa_interleave.
Все, остальной тюнинг на данном этапе (когда нужно править запросы и индексы и переезжать на InnoDB) ничего не даст.
Нет, можно конечно остаться на MyISAM и тюнить уже его, но это будет время потраченное зря.
VMichael
Хм.
Есть ведь мнение, что БД это ерунда и ОРМ наше все.
poxvuibr
А есть такие ОРМ, которые могут анализировать запросы и давать рекомендации по добавлению индексов и всего такого?
VMichael
Вряд ли.
Хотя я не спец по ОРМ.
poxvuibr
Пока таких нет — БД таки наше всё. И, даже, если они появятся — выбор СУБД будет много значить, потому что в некоторых из них физически нет фич, которые в других — в наличии.
VolCh
Есть такие ОРМ, которые автоматически добавляют (вернее генерируют миграции и т. п.) индексы, первичные и внешние ключи в типовых случаях типа классических связей. Есть и такие, которые позволяют дополнительно описать какое-то подмножество индексов в схеме маппинга объектов на базу. Но вообще это дополнительная фича, лишь помогающая выстроить процесс работы с базой единообразно.
oxidmod
ORM отвечают за объекты, а не за бд. Но, к примкеру, дебаг компонент симфони позволяет смотреть сгенерированные запросы и их експлейны. А миграции доктрины позволяют создавать индексы без лишних телодвижений
poxvuibr
ORM отвечают за преобразование записей из БД в объекты. Соответственно генерация оптимальных запросов для вытаскивания объектов тоже входит в задачу ORM. В связи с этим, как совершенно справедливо отметил VolCh, генерация первичных и внешних ключей в схеме тоже входит в задачи ORM.
Хотелось бы увидеть такой ORM, который посмотрел бы на запрос, собранный с помощью предлагаемых им средств, и выдал бы рекомендации про простановке аннотаций на поля замапленных объектов.
oxidmod
за формирование запросов отвечает DBAL, а не ORM.
poxvuibr
Если внутри ORM есть этот самый DBAL, то он, конечно отвечает. Но это часть ORM, без которой он работать не смог бы.
oxidmod
DBAL — самостоятельный компонент, и это его обязанность. Нормальная ОРМ скорей всего позволит заменить DBALЮ, если вас не устраивает существующий.
poxvuibr
Если я правильно понимаю, обязанность DBAL — обеспечивать единообразный доступ к разным СУБД. Как PDO в php примерно. Плюс, видимо, генерация запросов с помощью, сюрприз, построителя запросов :).
DBAL не занимается генерированием запросов, он просто транслирует их из одной формы в другую. Генерирует запросы компонент, который берёт описание сущностей и на его основании строит запрос, который вытащит, например, сущность по ID.
Я таких не знаю, если вы видели, подскажите, будет интересно.
mayorovp
Для того, чтобы заменить DBAL, нужна абстракция над DBAL. Но что делать когда понадобится заменить эту абстракцию?
qdreadknight
Ради интереса почитал что такое ОРМ… Напоминает сферического коня в вакууме. Прекрасные риски получить неработающий продукт, в котором ни один программист не сможет разобраться. Видел я один замечательный проект, где к базе данных запрос был длиной кажется в 20КБ…
VMichael
ОРМ это такой мейнстрим сейчас.
Серебрянная пуля.
Недавно на хабре видел, что пишут, что и нормализация БД уже никому не нужна.
Что спецов по БД найти сейчас трудно, поэтому и заморачиваться БД не нужно.
Есть отличные инструменты в виде ОРМ.
Это так, ирония с моей строны конечно.
qdreadknight
Существует дикое количество способ как гарантированно завалить проект. Спору нет :)
Когда таблиц становится хотя бы 100+ начинается веселая жизнь…
poxvuibr
ОРМ и спецы по БД это вещи ортогональные. Если вы используете ОРМ — специалисты по БД всё равно будут нужны. И заморачиваться всё равно придётся. Ну и нормализация БД — это тоже никак не связано с ОРМ.
immaculate
Никто никогда не заявлял, что ORM — это серебряная пуля, по-моему. Я такого утверждения ни разу не видел. С ORM получается понятный читаемый код, гораздо лучше, чем простыни SQL разбросанные тут и там.
Ну а если возникают проблемы с производительностью, то приходится оптимизировать. Можно оптимизировать как на уровне ORM, так и на уровне SQL: посмотреть, какие запросы генерирует ORM и добавить/изменить индексы, настройки базы.
Конечно, когда неграмотные программисты с оплатой за еду пишут код, у них будет и 1000+ запросов на страницу, и запросы в мегабайт размером. Но так они не только с ORM плохой код пишут. Они и на чистом SQL и на любом другом языке сделают ровно то же самое. Потому что нет знаний, опыта и мотивации делать иначе.
poxvuibr
ОРМ позволяет сократить количество бойлерплейта за счёт автоматической генерации джойнов и маппинга таблиц в объекты. Если использовать грамотно, то риск получить неработающий продукт невелик.
knekrasov
Ключевой момент «грамотно». Бойлерплейт — это вопрос удобочитаемости кода, а не производительности.
poxvuibr
Ну да, ОРМ к производительности достаточно косвенно относится. Хотя его наличие, иногда, позволяет увеличить производительность за счёт кеширования.
VolCh
Странное у вас впечатление. ОРМ создаются, по сути, чтобы упростить продукт, вынеся процесс работы с СУБД в отдельный слой, единственная ответственность которого преобразование объектов в запросы и наоборот.
remzalp
Проблема в том, что не всё так уж хорошо в построенных с помощью ОРМ запросах. Руками запиленный запрос, который тыкает носом тот же MySQL, какие индексы ему именно сейчас надо использовать — способен ускорить работу в сотни раз. Правильно использованный подзапрос в качестве значения поля или в операции сравнения позволяет очень сильно сократить нагрузку.
Да, неправильно выполненный запрос DROP DATABASE поломает всё :)))
VolCh
Нормальные универсальные ORM позволяют писать любые raw SQL запросы для получения/обновления нужных данных. На самом деле автоматическая генерация SQL-запросов (включая DDL запросы при генерации миграций и т. п.) это фичи конкретных реализаций универсальных ORM, а не обязательная черта ORM как паттерна в целом. Автогенерация — лишь средство ускорения разработки на начальных этапах. Как только сгенерированный запрос становится узким местом, его можно и нужно оптимизировать, заменяя частично (подзапросы и т. д.) или целиком на вручную написанный SQL.
chumakov-ilya
+1 к VolCh, применение ORM вовсе не означает, что все ваши SQL-запросы автоматически становятся на порядки медленнее. Напротив, во многих случаях падения производительности по сравнению с хранимыми процедурами вообще нет, особенно для простого CRUD. Для сложных составных запросов — да, расхождения возможны, но вам никто не запрещает переписать обнаруженные узкие места на хранимках и view. Воспринимайте ORM как средство быстрого прототипирования, и не забывайте о известном высказывании касательно предварительной оптимизации.
VolCh
Или просто ткнуть СУБД, чтобы использовала индекс, о котором ОРМ либо не знает, либо не сочла нужным тыкать, написав для конкретного кейса запрос на голом SQL ручками.
VMichael
Уверен, что метры, говоря о «Преждевременной оптимизации», имели ввиду «не вылизывай преждевременно», а не «не проектируй вообще».
Мне, к сожалению, не попадались проекты загубленные «преждевременной оптимизацией».
А вот проектов недостаточно продуманных изначально, попадалось много.
mayorovp
Я все еще не понимаю почему наличие ORM вы воспринимаете как отсутствие проектирования.
VMichael
Да не воспринимаю, конечно.
Просто сложилось такое впечатление, что мне в пример приводят ОРМ, люди, считающие главной проблемой создания программных продуктов «преждевременную оптимизацию». Это словосочетание мелькает очень часто.
Поэтому я и привожу ОРМ, как пример.
Я тут специально позвонил коллегам, с которыми приходилось работать.
Все они моложе, так сложилось, лет на 10 — 15 (не мальчики конечно).
Обсудил с ними ОРМ, много базовые приложения и прочие популярные теперь штуки.
Получил очень много примеров, когда приходилось перерабатывать такие штуки после метода «не допусти преждевременную оптимизацию».
Раз ОРМ есть, значит он кому то нужен, конечно.
Возможно, мне не приходилось работать с простыми приложениями, когда максимум в БД нужно форум записать и считать.
Поэтому методы автогенерации структуры БД, кажутся мне игрушками.
poxvuibr
У меня сложилось впечатление, что вы считаете наличие ORM — преджевременной оптимизацией. Я правильно понимаю? Просто, с моей точки зрения, преждевременная оптимизация это его отсутствие.
VolCh
Скорее сознательное избегание из-за потенциальных проблем в будущем.
poxvuibr
Сознательное избегание чего-либо из-за потенциальных проблем в будущем это же и есть преждевременная оптимизация. Если, конечно, вы не избегаете какого-нибудь широко известного антипаттерна.
VolCh
Само отсутствие ещё ни о чём не говорит. Может это своевременная оптимизация, если с самого старта ясно, что ORM будет узким местом. А вот избегания использования без такой ясности, просто из-за предубеждений каких-то — да, преждевременная
VMichael
Я считаю, что БД нужно проектировать и продумывать.
Определение ORM взял отсюда: https://ru.wikipedia.org/wiki/ORM
Из того, что там написано, я понимаю, что проекты, которыми я занимаюсь и занимался при таком подходе «не взлетят».
Вот прямо сейчас мне приходится ковыряться в БД, сделанной не знаю какой технологией.
Разработчики ушли в небытие, меня наняли «поправить БД».
MS SQL Server
Стуктуры БД как таковой нет.
Все напихано в денормализованные таблицы.
Данные дублируются в различных таблицах.
Все текстовые поля nvarchar(max).
На такое поле нельзя индекс повесить, к примеру.
Изменить тип поля я не могу, приложение падает.
И что бы сделать отчеты, которые должны быть по определению простейшими, нужно совершить хренову тучу телодвижений.
И что бы понять запросы из приложения идущие, когда таблицы получают алиасы, типа
Extent1, Extent2, Extent3, по мере подключения в джойне, приходится напрягать мозг.
Запросы были построены в построителе каком то.
А что, главное же не допустить «преждевременной оптимизации».
И тут по сообщениям я вижу, что мало кто пишет: «для простых систем», «может быть», «вероятно», «нужно использовать, где это применимо».
Нет, прямо таки гвоздят уверенностью, что ОРМ (например) все решит.
mayorovp
ORM не виновата что кто-то наговнокодил. И исправить ситуацию проще, чем вы думаете.
При изменении типа поля приложение падает потому что там стоит проверка соответствия схемы БД и объектной схемы. Надо либо исправить вторую (вписать туда те же длины строк), либо отключить проверку (да, так тоже можно делать!).
В целом же, вместо того чтобы писать гневные комментарии на хабре, вам стоило бы сразу признать — задача вышла за рамки вашей компетенции. Варианта тут два — либо изучать C# самому, либо искать специалиста по ORM.
mayorovp
По поводу же преждевременных оптимизаций...
Поймите одну простую вещь. Прошлая команда сэкономила немало времени за счет того что не писала SQL-запросы вручную. И вы теперь это время тратите.
Если бы другая команда тратила время на SQL-запросы — они бы наворотили такого же кошмара на хранимках в БД. С такими же страшными запросами, построенными в конструкторе запросов. С такими же неиндексируемыми полями
nvarchar(max). Вот только даже кое-как работающую программу они бы сделать не успели и разбежались бы раньше.И вам бы никто не поручил эту самую БД доделывать. Потому-то вы такие БД и не видели :)
PS из того, над чем страдаем мы. Очередь сообщений на базе MS SQL. Выборка очередного сообщения из очереди требует двух вложенных вложенных циклов. Виноваты кривые хранимки и отсутствие индексов (вообще!). Переписывать нельзя — код генподрядчика.
mayorovp
UPD: вложенных полных проходов, конечно же
VMichael
Эк вы меня припечатали, то — «задача вышла за рамки вашей компетенции».
У меня другое видение ситуации.
Всех проблем можно было избежать пригласив проконсультировать грамотного базовика в начале проекта, что бы спроектировать БД и избежать элементарных ошибок.
Это гораздо дешевле, чем переделывать и исправлять ошибки потом.
Ничего не могу сказать про вашу очередь, не обладая полнотой информации.
Но вы то конечно, все делаете элементарными движениями новейших технологий и можете посматривать свысока на старых базовиков. Молодец. Рад за вас и вашего работодателя.
mayorovp
Какой смысл "приглашать базовика" в начале проекта, когда еще даже бизнес-аналитик не успел выпытать у заказчика что вообще этот проект будет делать?
Начинать оптимизировать базу имеет смысл только когда у проекта появились все его ответственности, написаны прототипы всех модулей. А до этого момента задача разработчиков — вовремя менять прототип под хотелки заказчика, и при этом не сорваться и никого не покусать. Специалист-базовик в таких условиях будет лишь тормозить команду и сорвется первым...
VMichael
Вы делаете предположение. Потом считаете его аксиомой и на основе его строите дальнейшую цепь рассуждений.
С чего вы решили, что бизнес-аналитик не успел выпытать у заказчика, что этот проект будет делать?
А если он не успел выпытать, что вообще вы собрались собирать с помощью любых технологий? Вы провидец?
Далее, прежде, чем оптимизировать базу, ее неплохо бы создать.
Не, знаю.
Напишите обычный склад.
Приход товаров, размещение, отгрузка, это типовые действия.
Справочники там, товаров. Описание склада.
Отчеты, сперва не сложные.
Показать остаток товара.
Залейте тестовых данных наименований там, тысяч сто.
Знаю не одну команду (даже не отдельных людей), испытавших не малый геморрой, на не сложной по звучанию задаче.
И проблемы начинаются, когда нужно немного доработать решение.
Ввести там партионный учет, например.
mayorovp
Когда внезапно выясняется, что надо ввести партионный учет (чтобы ни означал этот термин) — это и означает, что бизнес-аналитик все еще продолжает пытать заказчика на предмет что тому надо.
poxvuibr
Я думаю тут никто не будет с вами спорить. Единственное, что грамотного базовика хорошо бы иметь под рукой на протяжении всей работы над проектом, а не только в начале.
С вами не соглашаются только в том разрезе, что наличие ОРМ на проекте никак не приводит к отсутствию на нём ДБА.
pnovikov
Ох, я чувствую в этом комментарии взрывной потенциал :)
mayorovp
Вы теперь под каждой статьей о БД будете эту чушь писать? :)
ORM — это механизм формирования запросов к БД, а не индексов
GlukKazan
Есть мнение, что ОРМ, в описанном автором случае, никак не поможет.
ОРМ к этому случаю немного ортогонален.