Комментарии 116
А есть такие ОРМ, которые могут анализировать запросы и давать рекомендации по добавлению индексов и всего такого?
Есть такие ОРМ, которые автоматически добавляют (вернее генерируют миграции и т. п.) индексы, первичные и внешние ключи в типовых случаях типа классических связей. Есть и такие, которые позволяют дополнительно описать какое-то подмножество индексов в схеме маппинга объектов на базу. Но вообще это дополнительная фича, лишь помогающая выстроить процесс работы с базой единообразно.
ORM отвечают за объекты, а не за бд.
ORM отвечают за преобразование записей из БД в объекты. Соответственно генерация оптимальных запросов для вытаскивания объектов тоже входит в задачу ORM. В связи с этим, как совершенно справедливо отметил VolCh, генерация первичных и внешних ключей в схеме тоже входит в задачи ORM.
Хотелось бы увидеть такой ORM, который посмотрел бы на запрос, собранный с помощью предлагаемых им средств, и выдал бы рекомендации про простановке аннотаций на поля замапленных объектов.
Если внутри ORM есть этот самый DBAL, то он, конечно отвечает. Но это часть ORM, без которой он работать не смог бы.
DBAL — самостоятельный компонент, и это его обязанность.
Если я правильно понимаю, обязанность DBAL — обеспечивать единообразный доступ к разным СУБД. Как PDO в php примерно. Плюс, видимо, генерация запросов с помощью, сюрприз, построителя запросов :).
DBAL не занимается генерированием запросов, он просто транслирует их из одной формы в другую. Генерирует запросы компонент, который берёт описание сущностей и на его основании строит запрос, который вытащит, например, сущность по ID.
Нормальная ОРМ скорей всего позволит заменить DBAL, если вас не устраивает существующий.
Я таких не знаю, если вы видели, подскажите, будет интересно.
Для того, чтобы заменить DBAL, нужна абстракция над DBAL. Но что делать когда понадобится заменить эту абстракцию?
Когда таблиц становится хотя бы 100+ начинается веселая жизнь…
ОРМ и спецы по БД это вещи ортогональные. Если вы используете ОРМ — специалисты по БД всё равно будут нужны. И заморачиваться всё равно придётся. Ну и нормализация БД — это тоже никак не связано с ОРМ.
Никто никогда не заявлял, что ORM — это серебряная пуля, по-моему. Я такого утверждения ни разу не видел. С ORM получается понятный читаемый код, гораздо лучше, чем простыни SQL разбросанные тут и там.
Ну а если возникают проблемы с производительностью, то приходится оптимизировать. Можно оптимизировать как на уровне ORM, так и на уровне SQL: посмотреть, какие запросы генерирует ORM и добавить/изменить индексы, настройки базы.
Конечно, когда неграмотные программисты с оплатой за еду пишут код, у них будет и 1000+ запросов на страницу, и запросы в мегабайт размером. Но так они не только с ORM плохой код пишут. Они и на чистом SQL и на любом другом языке сделают ровно то же самое. Потому что нет знаний, опыта и мотивации делать иначе.
ОРМ позволяет сократить количество бойлерплейта за счёт автоматической генерации джойнов и маппинга таблиц в объекты. Если использовать грамотно, то риск получить неработающий продукт невелик.
Прекрасные риски получить неработающий продукт, в котором ни один программист не сможет разобраться.
Странное у вас впечатление. ОРМ создаются, по сути, чтобы упростить продукт, вынеся процесс работы с СУБД в отдельный слой, единственная ответственность которого преобразование объектов в запросы и наоборот.
Проблема в том, что не всё так уж хорошо в построенных с помощью ОРМ запросах. Руками запиленный запрос, который тыкает носом тот же MySQL, какие индексы ему именно сейчас надо использовать — способен ускорить работу в сотни раз. Правильно использованный подзапрос в качестве значения поля или в операции сравнения позволяет очень сильно сократить нагрузку.
Да, неправильно выполненный запрос DROP DATABASE поломает всё :)))
Нормальные универсальные ORM позволяют писать любые raw SQL запросы для получения/обновления нужных данных. На самом деле автоматическая генерация SQL-запросов (включая DDL запросы при генерации миграций и т. п.) это фичи конкретных реализаций универсальных ORM, а не обязательная черта ORM как паттерна в целом. Автогенерация — лишь средство ускорения разработки на начальных этапах. Как только сгенерированный запрос становится узким местом, его можно и нужно оптимизировать, заменяя частично (подзапросы и т. д.) или целиком на вручную написанный SQL.
+1 к VolCh, применение ORM вовсе не означает, что все ваши SQL-запросы автоматически становятся на порядки медленнее. Напротив, во многих случаях падения производительности по сравнению с хранимыми процедурами вообще нет, особенно для простого CRUD. Для сложных составных запросов — да, расхождения возможны, но вам никто не запрещает переписать обнаруженные узкие места на хранимках и view. Воспринимайте ORM как средство быстрого прототипирования, и не забывайте о известном высказывании касательно предварительной оптимизации.
но вам никто не запрещает переписать обнаруженные узкие места на хранимках и view.
Или просто ткнуть СУБД, чтобы использовала индекс, о котором ОРМ либо не знает, либо не сочла нужным тыкать, написав для конкретного кейса запрос на голом SQL ручками.
Я все еще не понимаю почему наличие ORM вы воспринимаете как отсутствие проектирования.
У меня сложилось впечатление, что вы считаете наличие ORM — преджевременной оптимизацией. Я правильно понимаю? Просто, с моей точки зрения, преждевременная оптимизация это его отсутствие.
Скорее сознательное избегание из-за потенциальных проблем в будущем.
Сознательное избегание чего-либо из-за потенциальных проблем в будущем это же и есть преждевременная оптимизация. Если, конечно, вы не избегаете какого-нибудь широко известного антипаттерна.
ORM не виновата что кто-то наговнокодил. И исправить ситуацию проще, чем вы думаете.
При изменении типа поля приложение падает потому что там стоит проверка соответствия схемы БД и объектной схемы. Надо либо исправить вторую (вписать туда те же длины строк), либо отключить проверку (да, так тоже можно делать!).
- Страшный вид запросов — не повод для паники. Надо смотреть не на SQL, а на получившийся план запроса. Ну и на исходный (включаю телепатию) linq-запрос. Индексам все эти Extent1, Extent2, Extent3 — не помеха. И исходный linq-запрос, из которого все это было сгенерировано, точно так же поддается переписыванию. Только не вами.
В целом же, вместо того чтобы писать гневные комментарии на хабре, вам стоило бы сразу признать — задача вышла за рамки вашей компетенции. Варианта тут два — либо изучать C# самому, либо искать специалиста по ORM.
По поводу же преждевременных оптимизаций...
Поймите одну простую вещь. Прошлая команда сэкономила немало времени за счет того что не писала SQL-запросы вручную. И вы теперь это время тратите.
Если бы другая команда тратила время на SQL-запросы — они бы наворотили такого же кошмара на хранимках в БД. С такими же страшными запросами, построенными в конструкторе запросов. С такими же неиндексируемыми полями nvarchar(max)
. Вот только даже кое-как работающую программу они бы сделать не успели и разбежались бы раньше.
И вам бы никто не поручил эту самую БД доделывать. Потому-то вы такие БД и не видели :)
PS из того, над чем страдаем мы. Очередь сообщений на базе MS SQL. Выборка очередного сообщения из очереди требует двух вложенных вложенных циклов. Виноваты кривые хранимки и отсутствие индексов (вообще!). Переписывать нельзя — код генподрядчика.
UPD: вложенных полных проходов, конечно же
Какой смысл "приглашать базовика" в начале проекта, когда еще даже бизнес-аналитик не успел выпытать у заказчика что вообще этот проект будет делать?
Начинать оптимизировать базу имеет смысл только когда у проекта появились все его ответственности, написаны прототипы всех модулей. А до этого момента задача разработчиков — вовремя менять прототип под хотелки заказчика, и при этом не сорваться и никого не покусать. Специалист-базовик в таких условиях будет лишь тормозить команду и сорвется первым...
Всех проблем можно было избежать пригласив проконсультировать грамотного базовика в начале проекта, что бы спроектировать БД и избежать элементарных ошибок.
Я думаю тут никто не будет с вами спорить. Единственное, что грамотного базовика хорошо бы иметь под рукой на протяжении всей работы над проектом, а не только в начале.
С вами не соглашаются только в том разрезе, что наличие ОРМ на проекте никак не приводит к отсутствию на нём ДБА.
Вы теперь под каждой статьей о БД будете эту чушь писать? :)
ORM — это механизм формирования запросов к БД, а не индексов
ОРМ к этому случаю немного ортогонален.
Мы показываем игрокам 300 тыс. страниц в сутки. Счетчики в публичном доступе. Нагрузку держим за счет хитрющей системы кэширования.
Загнать всю базу в память… сейчас с трудом все залезет, но она же растет…
Может часть имеет смысл попробовать положить. Спасибо за идейку, подумаю на досуге.
С чего бы индекс постоянно хранился в памяти целиком? Или это в MySQL такое поведение?
Индекс тоже хранится в памяти, и если на одну таблицу весом 2гб приходится 1.5гб индексов в памяти — намного выгоднее перенести в память саму таблицу.
А как перенос таблицы в память решит проблему, которую решают индексы? Перенос в память, ведь не сделает её волшебным образом отсортированной по всем комбинациями полей, на которые делаются индексы. Операции, конечно побыстрее будут проходить, чем когда таблица без индексов хранится на диске, но full scan то не денется никуда.
300 000 страниц в сутки это 3.5 запроса в секунду.
Парни, вам есть еще куда расти во-первых, а во-вторых положите уже всё в память, она по-прежнему быстрее любого SSD
1. Базу в оперативку;
2. Может всё же ИнноДБ? Даже просто из соображений той же целостности данных, игра всё-таки «БизнесМания», экономика, все дела;
3. Рэид 5? Он в произвольной записи порой проседает на четверть;
4. Что за «хитрющее кэширование»? Сфинкс штоле?
5. Основная масса запросов к базе — чтение?
В вашем случае переезд на Innodb как минимум даст
а) неблокирующее чтение вообще, т.к. данные читаются из снапшота
б) локи только на нужные строки
Это гарантированно улучшит общий перфоманс базы.
В следующей итерации попробуем все-все все-таки перенести на Inno, главное чтобы ничего не порушилось.
В лоб смена движка не прошла в свое время.
Иногда MyISAM бывает полезен. Была в проекте на прошлой работе одна InnoDB-табличка на несколько сотен записей. Она пополнялась в триггере при изменении записей в другой таблице. Но иногда вставки по непонятным причинам не происходило. Я долго не мог докопаться до настоящей причины, пока не сделал добавление записей из кода. Оказалось, что вставка иногда не срабатывает из-за ошибки типа unique constraint violation на автоинкрементном первичном ключе. Админы подтвердили, что это известная проблема в MySQL 5.6, но быстро перевести production на 5.7 они не могли. Пришлось переключить тип таблицы с InnoDB на MyISAM. Проблема исчезла.
это известная проблема в MySQL 5.6А можно ссылочку на баг на bugs.mysql.com?
Была в проекте на прошлой работе одна InnoDB-табличка на несколько сотен записей. Она пополнялась в триггере при изменении записей в другой таблице.
Т.е. по сути это логгер таблица. Вообщем да, это пожалуй единственный полезный кейс MyISAM т.к. нетранзакционная, а в mysql нет autonomous transaction. Если надо заллогировать фейл транзакции то выбора по сути нет.
Но такая лог таблица обычно а) не содержит критикал данных б) нет требований по производительности, так что по сути в вашем случае можно было взять любой другой движок на выбор.
Сам сейчас обдумываю как хранить данные, и реляционная таблица подходит для сырых данных, однако в игре нужно будет работать с обработанными данными, взятыми из многих таблиц, над которыми производятся рассчёты. Делать это на лету каждый раз не очень разумно, поэтому имеет смысл сформировать «игровое состояние» конкретного игрока, и хранить его в КВ или документарной базе, обновляя сырые данные в бд по мере необходимости.
С DDL таблиц, тесткейсом, планом запроса до и после.
В общем — ситуацию надо было спасать. Засучив рукава, мы начали с чистого листа искать решение.Молодцы, что решение всё таки нашли. Но так как статья с меткой «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 информации даже больше будет
Плохо работает, когда есть запросы, штатно выполняющиеся минуты (аналитика, например).
Но вдруг моя идея кому-то поможет.
Лог медленных запросов в моем случае ничего не показал.
Мой метод дал мне полную картину, когда и в какой момент времени у меня идет зависание, связано это с обсчетом игровой ситуации, или это повышенная активность людей.
Кстати с 5.5. на 5.7 сразу перейти нельзя, сначала надо 5.6 поставить.
Что-то?
Про использование MyISAM и про то, что профилировние длинных запросов делается настройками my.ini, здесь уже сказали.
Так что в целом я тут вижу только отсутствие внятного специалиста по СУБД, и метка «tutorial» смущает.
Полчаса работы, и все ваши проблемы яйца выеденного не стоят. Неужели гуглеж не подсказал?..
processListThread.run();
Вы уверены, что запускали потоки?
А потом они открыли для себя ssd диски, MariaDB и slow-query-log...
Проблема-то от чего возникла — писали в быстром темпе какие-то вещи, и до поры до времени проблем не было. А потом бах — работает медленно. А никто не помнит, почему так написано. Откуда копать. Это хорошо назад рассуждать о том, что блин — ставь индексы правильно или кэшируй запросы. А когда ты считаешь, что все описанные вещи уже итак давно решены — что делать, с чего начинать копать.
Но на вскидку несколько советов:
1. Если используете MyIsam старайтесь организовать логику так, чтобы бОльшая часть запроса была одного типа (insert/select/update)
2. в where столбцы должны идти ровно в том порядке, в каком указаны в составном ключе
3. Включите логирование запросов со временем выполнения более 1с. Если таковых много или очень встречаются часто — явно что-то делаете не так.
4. Прогоните explain всех запросов. У MySQL очень плохой оптимизатор. Есть тысяча и один способ сделать запрос быстрее и легче.
5. В идеале — перейдите на postgesql.
6. Наймите крутого специалиста по БД.
1. Так и сделано. Проблема возникла из-за того, что бизнес-логика работала не по тем данным.
2. Вроде это никогда не влияло.
3. Show slow query мне влоб в свое время не помог, может быть я неверно что-то там делал.
4. Все-все запросы, это проще повесится. Самые активно используемые — смотрел, с ними все более менее, кроме описанного в статье.
5. Это слишком дорого.
6. Пока у нас все проблемы относительно решены. Скорее купим более мощный и современный сервер.
MyISAM с его table-level локами в 2017 году это нонсенс.
3. Если он не пуст — у вас проблемы. За исключением отчетов ни один запрос не должен выполняться дольше 0.1с.
4. Для начала всех что попадают в slow query log.
5. Если уж покупать новый сервер то и на postgre неплохо бы. SQL почти тот же, только специалист нужен. Поставить как можно больше оперативной памяти и отдать ее всю для бд.
6. Тоже когда-то думал так же, после покупки второго сервера подряд одумался.
Касательно «проблемы относительно решены». Нужно оперативно мониторить. Ко мне очень часто обращаются люди как раз по таким проблемам и я каждый раз, после того как их сервисы начинают летать а не ползать, рекомендую полный аудит и выдаю бесплатно десяток рекомендаций. Каждый раз потом тянут до последнего и возвращаются уже с лежащим проектом, полудохлой базой и «нужно оченьсрочнотеряемденьги».
2. Всегда влияло. Возможно за последний год и поправили но я сомневаюсь. На большой таблице сделайте индекс (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 — скорее всего нет.
2. В MySQL оптимизатор конечно не идеальный, но такую оптимизацию даже он сделает.
4. На самом деле в 5.7 (да даже с 5.6) его неплохо так пилят и он уже вполне неплох.
Способов руками сделать лучше чем оптимизатор для mysql я знаю не менее полусотни: это и танцы с параметрами и вложенные запросы там где они не нужны и много других «приятных» вещей. Одним из наиболее часто встречающихся сценариев является замена join с таблицей на join с подзапросом, такие вещи сразу видны по explan
Как кто-то умный сказал: «Oracle это база данных, а mysql это хер с гвоздями». Перед тем как перейти на postgesql я 12 часов баловался с различными запросами, начиная от однострочных и заканчивая такими что на распечатке займут страниц 10. Итог: хуже сделать получилось, лучше — нет.
является замена join с таблицей на join с подзапросом,
Сколько не видел — всегда появление подзапроса портило план выполнения, вот уж где оптимизатор Mysql тупит вовсю. Из последних случаев — наличие подзапроса вызывало лок всех строк в таблице, как по выводу explain так и по факту. Выкидывание подзапроса и join таблицы на себя — получаем идеальный план выполнения.
У оптимизатора Oracle свои причуды. Например он очень котирует индексы по временным колонкам в партиционированной по времени таблицы и игнорит другой очень селективный индекс.
Есть мнение, что для разработчиков хорошо знающих MySQL это не проблема. Я вот за много лет работы с MySQL ни разу с этой проблемой не сталкивался. Просто не приходило в голову решать какие-то задачи с помощью рекурсивных запросов. Собственно толком и не знаю что это, не смотря на то, что уже с полгода в продакшене крутится база на Постгрес.
Есть и другие доводы: fts, функциональные индексы, схемы, instead of триггеры, нормальный оптимизатор и так далее.
В нашем случае моим доводом перед руководством за переход были функциональные и частичные индексы, чтобы избавиться от триггеров, вычисляющих выражения (хотя бы банальное взятие даты от датывремени) или null если под условие не попадает и заносящие их в служебный столбец. WITH и WINDOW — бесплатный бонус для меня к индексам. Но админы меня потихоньку проклинают — для них в мускуле было всё понятно, да и я их практически не беспокоил, а теперь пользователей создают они.
А схемы в мускуле есть, просто их обычно называют базами данных, но нет того, что в постгресе называют базой данных.
Про админов немного не понял. На каждом углу лежат гайды с рекомендуемыми настройками postgesql и pgbouncer.
Схемы нереально удобны когда в них помещаешь только view или like таблицы и спокойно выставляешь овнером пользователя.
Кстати да, with вещ просто нереально удобная.
Вы с этим осторожнее, в postgres with это optimization fence. Производительность может неслабо убить. Но, за счёт материализации, может и увеличить.
В мускуле я мог сам пользователей для приложений добавлять через SQL, в постгресе требуется как минимум править hg_hba.conf и перезагружать сам постгрес. Ну и ещё там, типа из коробки только сервер целиком реплицируется, одну базу, не говор яо таблице нельзя
Без проблем: вывод одним списком трех уровней форума.Для фиксированного количества уровней можно обойтись JOIN-ами без иерархических запросов.
И, кстати, иерархические запросы в MySQL теоретически возможны с использованием переменных, но сложны для построения и сопровождения и очень медленны. Так что их практическая ценность стремится к нулю.
1) 5.5 можно обновить до 5.7 без 5.6 и это может быть даже проще.
2)
Делаем дополнительные индекс…Существует force index (название индека) позволяющий заставить базу использовать индекс, даже если оптимизатор запросов решил этого не делать. Может помочь в части ситуаций, но с ключевым полем может и не сработать. Но все-таки доп индекс это лишняя нагрузка.
А вообще нужно внимательно смотреть все запросы к базе (не только тяжелые), изучать эксплейн. Смотреть триггеры и индексы.
Триггеры вообще больное место, любая мало мальски не оптимальная директива выльется в большую нагрузку. Иногда очень хорошо помогают составные индексы, зачастую результат запроса можно рассчитать из того же индекса, в котором хранятся условия фильтрации.
Была ли оптимизация конфига базы? Про это правда не мало написано мануалов. И т.д. и т.п. вы описали только вершину айсбегра под названием бесконечная оптимизация.
Все, остальной тюнинг на данном этапе (когда нужно править запросы и индексы и переезжать на InnoDB) ничего не даст.
Нет, можно конечно остаться на MyISAM и тюнить уже его, но это будет время потраченное зря.
Как спасти проект от закрытия, разобравшись с MySQL