Комментарии 14
У нас скорее реактивный подход — раз в месяц примерно мы вытаскиваем самые тяжелые запросы с группировкой по execution plan и занимаемся ими. Чаще всего бывает что есть 2-3 проблемы, которые требуют ресурсов на порядок больше чем все остальное.
Для Oracle использовались встроенные средства, которые анализировали работу сервера и выдавали подробные отчеты по проблемным местам как в запросах и структурах данных, так и в настройках самого сервера. После чего проверялись и изменялись соответствующие SQL-запросы. Но все это делалось только тогда, когда на сервере появлялись какие-либо проблемы, либо когда пользователи жаловались на медленную работу приложений.
Для MySQL использовался MONyog, который подключался к серверу, собирал статистику по его работе и составлял отчеты с рекомендациями по оптимизации.
Для MySQL использовался MONyog, который подключался к серверу, собирал статистику по его работе и составлял отчеты с рекомендациями по оптимизации.
У меня MS SQL.
Я использую: Отчет — производительность – запросы с наибольшим общим временем цп.
И там думаю, оптимизировать запросы, кешировать данные и прочее. Просто, но эффективно.
Я использую: Отчет — производительность – запросы с наибольшим общим временем цп.
И там думаю, оптимизировать запросы, кешировать данные и прочее. Просто, но эффективно.
Мы смотрим запросы, которые долго выполняются и оптимизируем их. Обычно это 2-3 самых «тяжёлых» запроса, а вообще их количество для исправления зависит от сложности запроса и выделенного времени. Бывало и по 5-7 запросов в день оптимизировали.
Чуток ИМХО. О наболевшем...
Я пишу на PHP, в качестве СУБД используем PostgreSQL.
ORM — не помощник в оптимизации, скорее наоборот. Не знаю как дела обстоят в других языках, но, если взять пару-тройку популярных фреймворков для PHP с реализованным ORM, то замечаем, что они заставляют нас делать лишние запросы! На сколько я понял, причина — заточенность этих самых ORM в основном под MySQL.
Нет поддержки RETURNING. Иногда работает (не на MySQL) метод аля-getLastInsertId, но работает только в случае если в качестве PK используется счётчик (тип данных INTEGER). Поэтому, если вы используете что-то отличное от счётчика (например, UUID), то прощай getLastInsertId и здравствуй дополнительный запрос перед нужным запросом аля SELECT uuid_generate_v4() и предопределённый id вместо автогенерируемого в запросе с INSERT. А если у вас несколько автогенерируемых полей, которые вам нужно вернуть (RETURNING field1, field2), то понадобится ещё один запрос после INSERT аля-SELECT field1, field2 FROM table WHERE id=:id
Итого 3 запроса вместо 1-го…
Нельзя реализовать непопулярное решение для отличных СУБД от MySQL. В PG при использовании ORM, например, нельзя реализовать запрос:
Я конечно, понимаю, что данный пример не отражает всех нюансов и его можно абсолютно безболезненно заменить на:
И даже ещё лучше:
Но всё же бывают случаи, когда тебе приходится делать выборку из таблицы store несколько раз в запросе, тут WITH приходится как нельзя кстати. У нас были очень тяжёлые запросы, которые выполнялись секунд по 10-15, после подобной оптимизации они стали выполняться значительно быстрее.
А заточенных ORM под PG я ещё не встречал, думаю, что иначе в них просто теряется смысл, ибо теряется эта независимость от выбранной СУБД…
Поэтому ORM — это штука только для простых запросов, если вам нужно что-то реально хорошо оптимизировать, то приходится работать с «голыми» запросами, мимо ORM. Либо, есть ещё вариант, инкапуслировать все нужные вам запросы в хранимые процедуры и оптимизировать запросы не со стороны кода и запросов, которые генерирует вам ORM, а со стороны самой СУБД. Но последний вариант, ИМХО, не очень кошерный…
Но и не всегда проблема кроется в том как построен запрос, иногда проблема в отсутствии/не оптимальности какой-то мелочи или индекса.
Например, LIKE работает значительно быстрее, чем ILIKE в PG. Поэтому для полнотекстового поиска лучше использовать LIKE + функциональный индекс:
И соответствующий функциональный индекс аля-USING btree («name» pg_table.text_pattern_ops).
В итоге видим значительное увеличение скорости выполнения запроса.
Спасибо за внимение.
P.S.: давно не работал с MySQL, поэтому некоторые сравнения могут быть не актуальными… к сожалению…
ORM — не помощник в оптимизации, скорее наоборот. Не знаю как дела обстоят в других языках, но, если взять пару-тройку популярных фреймворков для PHP с реализованным ORM, то замечаем, что они заставляют нас делать лишние запросы! На сколько я понял, причина — заточенность этих самых ORM в основном под MySQL.
Нет поддержки RETURNING. Иногда работает (не на MySQL) метод аля-getLastInsertId, но работает только в случае если в качестве PK используется счётчик (тип данных INTEGER). Поэтому, если вы используете что-то отличное от счётчика (например, UUID), то прощай getLastInsertId и здравствуй дополнительный запрос перед нужным запросом аля SELECT uuid_generate_v4() и предопределённый id вместо автогенерируемого в запросе с INSERT. А если у вас несколько автогенерируемых полей, которые вам нужно вернуть (RETURNING field1, field2), то понадобится ещё один запрос после INSERT аля-SELECT field1, field2 FROM table WHERE id=:id
Итого 3 запроса вместо 1-го…
Нельзя реализовать непопулярное решение для отличных СУБД от MySQL. В PG при использовании ORM, например, нельзя реализовать запрос:
WITH sel AS (SELECT item_id FROM store WHERE category = 'mobile')
SELECT * FROM items WHERE id IN (SELECT item_id FROM sel) AND price BETWEEN 100 AND 200Я конечно, понимаю, что данный пример не отражает всех нюансов и его можно абсолютно безболезненно заменить на:
SELECT * FROM items WHERE id IN (SELECT item_id FROM store WHERE category = 'mobile') AND price BETWEEN 100 AND 200И даже ещё лучше:
SELECT i.* FROM items AS i INNER JOIN store AS s ON s.item_id = i.id AND s.category = 'mobile' AND i.price BETWEEN 100 AND 200Но всё же бывают случаи, когда тебе приходится делать выборку из таблицы store несколько раз в запросе, тут WITH приходится как нельзя кстати. У нас были очень тяжёлые запросы, которые выполнялись секунд по 10-15, после подобной оптимизации они стали выполняться значительно быстрее.
А заточенных ORM под PG я ещё не встречал, думаю, что иначе в них просто теряется смысл, ибо теряется эта независимость от выбранной СУБД…
Поэтому ORM — это штука только для простых запросов, если вам нужно что-то реально хорошо оптимизировать, то приходится работать с «голыми» запросами, мимо ORM. Либо, есть ещё вариант, инкапуслировать все нужные вам запросы в хранимые процедуры и оптимизировать запросы не со стороны кода и запросов, которые генерирует вам ORM, а со стороны самой СУБД. Но последний вариант, ИМХО, не очень кошерный…
Но и не всегда проблема кроется в том как построен запрос, иногда проблема в отсутствии/не оптимальности какой-то мелочи или индекса.
Например, LIKE работает значительно быстрее, чем ILIKE в PG. Поэтому для полнотекстового поиска лучше использовать LIKE + функциональный индекс:
LOWER("name") LIKE LOWER(:name)И соответствующий функциональный индекс аля-USING btree («name» pg_table.text_pattern_ops).
В итоге видим значительное увеличение скорости выполнения запроса.
Спасибо за внимение.
P.S.: давно не работал с MySQL, поэтому некоторые сравнения могут быть не актуальными… к сожалению…
Мне кажется, в данной статье немного смешаны 2 проблемы.
1-я, техническая на стороне кода — дублирование кода SQL-запросов в приложении.
Сразу же возникает запрос — действительно ли нужен сырой SQL в этих местах? На моей практике он был нужен для действительно тяжелых мест (получение первичных данных для расчета ЗП по подразделению, например), ну и для отчетов. И тех и тех мест мало, они известны, и с ними можно разбираться индивидуально.
Если принять, что в остальных местах SQL генерируется через ORM, то можно избежать дублирования, на мой взгляд. Попробуйте посмотреть в сторону паттерна Specification применительно к построению DAO/Repository слоя — он позволит, в целом, вынести «атомарные» бизнес-условия для сущностей в отдельные спецификации, компонуемые и реюзабельные далее везде в коде. Разумеется, это идеализированно, но тем не менее, может серьезно сократить дублирование кода.
В случае все-таки ручного написания SQL — проблему с повторением кода в целом никак не решить. Равно как и то, что каждый будет писать так, как привык. Остается принять это как данное, и перейти ко второй проблеме :)
2-я проблема, административная, на стороне СУБД — получение и анализ медленных запросов. Поможет slow-лог, очевидно, в той или иной реинкарнации для вашей СУБД. Кто-то (MS) умеет получать эти данные на лету с хорошей разверткой, и выдавать подсказки по индексам, для PostgreSQL/MySQL нужно смотреть именно slow log и далее анализировать запросы поштучно. В общем, зависит от специфики и удобства тулз для СУБД.
1-я, техническая на стороне кода — дублирование кода SQL-запросов в приложении.
Сразу же возникает запрос — действительно ли нужен сырой SQL в этих местах? На моей практике он был нужен для действительно тяжелых мест (получение первичных данных для расчета ЗП по подразделению, например), ну и для отчетов. И тех и тех мест мало, они известны, и с ними можно разбираться индивидуально.
Если принять, что в остальных местах SQL генерируется через ORM, то можно избежать дублирования, на мой взгляд. Попробуйте посмотреть в сторону паттерна Specification применительно к построению DAO/Repository слоя — он позволит, в целом, вынести «атомарные» бизнес-условия для сущностей в отдельные спецификации, компонуемые и реюзабельные далее везде в коде. Разумеется, это идеализированно, но тем не менее, может серьезно сократить дублирование кода.
В случае все-таки ручного написания SQL — проблему с повторением кода в целом никак не решить. Равно как и то, что каждый будет писать так, как привык. Остается принять это как данное, и перейти ко второй проблеме :)
2-я проблема, административная, на стороне СУБД — получение и анализ медленных запросов. Поможет slow-лог, очевидно, в той или иной реинкарнации для вашей СУБД. Кто-то (MS) умеет получать эти данные на лету с хорошей разверткой, и выдавать подсказки по индексам, для PostgreSQL/MySQL нужно смотреть именно slow log и далее анализировать запросы поштучно. В общем, зависит от специфики и удобства тулз для СУБД.
В статье я обсуждаю вопрос более концептуальный — анализ всех запросов в целом и структуры СУБД, которое может использоваться для оптимизации, рефакторинга и исследований. Дублирование кода и анализ медленных запросов — это некоторые из подзадач этой более глобальной задачи. Решая только локальные задачи оптимизации можно через пару итераций столкнуться с тем что сколько запрос не оптимищируй все равно будт медленно потому что проблема в другом, например в уже неправильной структуре базы.
В целом рассматривая какой-то такой общий анализатор будет уже все равно написан код вручную или с помощью ORM. ORM-ы многие генерируют код далеко не оптимальный. Вы привели очень правильный пример расчета ЗП. Во всех системах ERP-класса или похожих подобных запросов очень много и современные ORM непомогут. Нужны более интеллектуальные ORM, которые бы могли включать како-йто такой универсальный алгоритм анализа и оптимизации или хотя бы СППР.
В целом рассматривая какой-то такой общий анализатор будет уже все равно написан код вручную или с помощью ORM. ORM-ы многие генерируют код далеко не оптимальный. Вы привели очень правильный пример расчета ЗП. Во всех системах ERP-класса или похожих подобных запросов очень много и современные ORM непомогут. Нужны более интеллектуальные ORM, которые бы могли включать како-йто такой универсальный алгоритм анализа и оптимизации или хотя бы СППР.
Полностью поддерживаю тезис о том, что «наличие средства такого анализа позволило бы значительно упростить работу многих программистов и некоторых ученых, работающих над алгоритмами оптимизации в СУБД».
Для средств разработки приложений, подобные средства уже есть и, в умелых руках, очень помогают разработчикам. Например — Pascal Analyzer для проектов написанных на Delphi.
В работе не раз сталкивались с необходимостью оптимизации быстродействия работы приложения, узким местом которого было взаимодействие с БД. Достаточно часто это было приложение написанное другими разработчиками.
Как правило, использовали аналитические методы и действовали по алгоритму:
— с помощью трейсеров, консоли Оракл и т.п. инструментальных средств, определяем самые «медленные» запросы, которые с точки зрения пользователей мешают работе системе;
— максимально оптимизируем запросы и структуру БД;
— проводим нагрузочное тестирование;
— если результат все еще не устраивает возвращаемся на шаг 1 :-)
Типовые причины проблем с быстродействием с которыми сталкивались:
— неоптимальная структура БД.
Т.е. БД может быть спроектирована в 3НФ, «по классике», однако не учитывать популяцию запросов приложения.
Варианты решения — изменение структуры (с или без нарушением 3НФ), создание объектов material view или таблиц обновляемых триггерами специально под определенные задачи.
— множество точечных запросов вместо одного/двух.
При этом основное время уходит не на сам запрос, а на взаимодействие приложения и БД по каждому запросу.
Самый простой пример — много запросов вида select… where id =…
Вариант решения — замена на один запрос вида select… where id in ( ..., ..., ...).
В случае, если каждый следующий запрос требует данные предыдущего (например «вытягиваем» дерево узлов из БД) — можно использовать иерархические запросы или заранее предсохраненные, обновляемые триггерами данные.
— неоптимальный план запроса.
Либо изначально, либо в какой-то момент СУБД меняет план и запрос начинает работать в несколько раз медленнее.
Варианты решения — создание/удаление индексов, ручное изменение плана запроса средствами СУБД (например, консоли Оракл), изменение текста запроса так, чтобы СУБД «поняла как лучше» выполнять этот запрос (последний вариант — на грани шаманства :-) ).
На практике также встречаются ошибки даже в таких СУБД как Оракл, которые могут не только менять план запроса, но и приводить к некорректным данным, возвращаемым таким запросом. Здесь может помочь только чтение документации, переписка с саппортом самого СУБД, эксперименты.
— неоптимально написанный запрос (часто для профессионала явно неоптимально), который на небольшом количестве данных работал нормально, а в реальной системе внезапно (!) начал тормозить.
Вариант решения — книги «Основы SQL-для чайников/проффесионалов» для разработчика, который написал этот запрос, и «Почему плохо давать задачи по написанию запросов новичкам, а потом ленится их почитать» ведущему разработчику/тим-лиду/ПМ по проекту. Может помочь еще книга «Зачем нужен Code Review».
— «слишком много» данных в таблицах и предыдущие варианты решений не актуальны.
Варианты решения — технический: партирование, структурый: отделение актуальных данных от архивных.
Думаю, для разработчиков было бы полезно, чтобы средство автоматического анализа:
— отрабатывало все перечисленные и много других подобных ситуаций;
— предоставляло возможность разработчику выделять «приоритетные» запросы, поскольку только одним автоанализом неэффективные, с точки зрения пользователя, запросы не выявить. Например, для какого-то запроса, который часто выполняется, нормальным временем может быть 1000 секунд (если это регламентная задача или часть задачи по печати отчета занимающего 1000 страниц). В то же время, для более «редкого» запроса 2 секунды может быть уже недопустимым временем (например, кассовые операции, работа с банкоматом);
— как дополнение содержало инструкцию с описанием как отрабатывать рекомендации и детальным разбором «анти-патернов», их примерами.
Как первый шаг к написанию такого средства, возможно, полезно было бы хорошую книгу или Интернет ресурс по SQL где описываются патерны/анти-патерны наиболее часто встречающихся ситуаций в приложениях, работающих с БД.
Если будут желающие – было бы интересно поучаствовать в написании такой книги, возможно, в зависимости от загрузки по работе – разработке такого средства.
Для средств разработки приложений, подобные средства уже есть и, в умелых руках, очень помогают разработчикам. Например — Pascal Analyzer для проектов написанных на Delphi.
В работе не раз сталкивались с необходимостью оптимизации быстродействия работы приложения, узким местом которого было взаимодействие с БД. Достаточно часто это было приложение написанное другими разработчиками.
Как правило, использовали аналитические методы и действовали по алгоритму:
— с помощью трейсеров, консоли Оракл и т.п. инструментальных средств, определяем самые «медленные» запросы, которые с точки зрения пользователей мешают работе системе;
— максимально оптимизируем запросы и структуру БД;
— проводим нагрузочное тестирование;
— если результат все еще не устраивает возвращаемся на шаг 1 :-)
Типовые причины проблем с быстродействием с которыми сталкивались:
— неоптимальная структура БД.
Т.е. БД может быть спроектирована в 3НФ, «по классике», однако не учитывать популяцию запросов приложения.
Варианты решения — изменение структуры (с или без нарушением 3НФ), создание объектов material view или таблиц обновляемых триггерами специально под определенные задачи.
— множество точечных запросов вместо одного/двух.
При этом основное время уходит не на сам запрос, а на взаимодействие приложения и БД по каждому запросу.
Самый простой пример — много запросов вида select… where id =…
Вариант решения — замена на один запрос вида select… where id in ( ..., ..., ...).
В случае, если каждый следующий запрос требует данные предыдущего (например «вытягиваем» дерево узлов из БД) — можно использовать иерархические запросы или заранее предсохраненные, обновляемые триггерами данные.
— неоптимальный план запроса.
Либо изначально, либо в какой-то момент СУБД меняет план и запрос начинает работать в несколько раз медленнее.
Варианты решения — создание/удаление индексов, ручное изменение плана запроса средствами СУБД (например, консоли Оракл), изменение текста запроса так, чтобы СУБД «поняла как лучше» выполнять этот запрос (последний вариант — на грани шаманства :-) ).
На практике также встречаются ошибки даже в таких СУБД как Оракл, которые могут не только менять план запроса, но и приводить к некорректным данным, возвращаемым таким запросом. Здесь может помочь только чтение документации, переписка с саппортом самого СУБД, эксперименты.
— неоптимально написанный запрос (часто для профессионала явно неоптимально), который на небольшом количестве данных работал нормально, а в реальной системе внезапно (!) начал тормозить.
Вариант решения — книги «Основы SQL-для чайников/проффесионалов» для разработчика, который написал этот запрос, и «Почему плохо давать задачи по написанию запросов новичкам, а потом ленится их почитать» ведущему разработчику/тим-лиду/ПМ по проекту. Может помочь еще книга «Зачем нужен Code Review».
— «слишком много» данных в таблицах и предыдущие варианты решений не актуальны.
Варианты решения — технический: партирование, структурый: отделение актуальных данных от архивных.
Думаю, для разработчиков было бы полезно, чтобы средство автоматического анализа:
— отрабатывало все перечисленные и много других подобных ситуаций;
— предоставляло возможность разработчику выделять «приоритетные» запросы, поскольку только одним автоанализом неэффективные, с точки зрения пользователя, запросы не выявить. Например, для какого-то запроса, который часто выполняется, нормальным временем может быть 1000 секунд (если это регламентная задача или часть задачи по печати отчета занимающего 1000 страниц). В то же время, для более «редкого» запроса 2 секунды может быть уже недопустимым временем (например, кассовые операции, работа с банкоматом);
— как дополнение содержало инструкцию с описанием как отрабатывать рекомендации и детальным разбором «анти-патернов», их примерами.
Как первый шаг к написанию такого средства, возможно, полезно было бы хорошую книгу или Интернет ресурс по SQL где описываются патерны/анти-патерны наиболее часто встречающихся ситуаций в приложениях, работающих с БД.
Если будут желающие – было бы интересно поучаствовать в написании такой книги, возможно, в зависимости от загрузки по работе – разработке такого средства.
www.mysqlperformanceblog.com/2012/08/31/visualization-tools-for-pt-query-digest-tables/
только написал и оказывается есть целый пласт инструментов! надо пробовать…
только написал и оказывается есть целый пласт инструментов! надо пробовать…
Работаем с Oracle. Сталкивались с некоторым анализом популяции запросов при апгрейде с версии 10.2 до 11.2. Перед миграцией были сохранены baseline всех запросов, а теперь решаем что с ними сделать. Теперь их планы сравниваем с тем, что предлагает новая версия Oracle — с Oracle это делается без особого труда. Честно говоря, не слежу за этой работой, хотя тема интересная.
Обычно «лечим» все тормоза по месту. Но такие средства, как Oracle Tuning Advisor и Oracle Partition Advisor, наверное, за ростом объема и изменения структуры данных придется пощупать.
Обычно «лечим» все тормоза по месту. Но такие средства, как Oracle Tuning Advisor и Oracle Partition Advisor, наверное, за ростом объема и изменения структуры данных придется пощупать.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Вы встречались с анализом леса популяции запросов SQL промышленного приложения (например, для оптимизации)?