что такое оптимизация запросов

Оптимизация сложных запросов MySQL

Введение

MySQL — весьма противоречивый продукт. С одной стороны, он имеет несравненное преимущество в скорости перед другими базами данных на простейших операциях/запросах. С другой стороны, он имеет настолько неразвитый (если не сказать недоразвитый) оптимизатор, что на сложных запросах проигрывает вчистую.

Прежде всего хотелось бы ограничить круг рассматриваемых проблем оптимизации «широкими» и большими таблицами. Скажем до 10m записей и размером до 20Gb, с большим количеством изменяемых запросов к ним. Если в вашей в таблице много миллионов записей, каждая размером по 100 байт, и пять несложных возможных запросов к ней — это статья не для Вас. NB: Рассматривается движок MySQL innodb/percona — в дальнейшем просто MySQL.

Большинство запросов не являются очень сложными. Поэтому очень важно знать как построить индекс для использования нужным запросом и/или модифицировать запрос таким образом, чтобы он использовал уже имеющиеся индексы. Мы рассмотрим работу оптимизатора для выбора индекса обычных запросов (select_type=simple), без джойнов, подзапросов и объединений.

Отбросим простейшие случаи для очень небольших таблиц, для которых оптимизатор зачастую использует type=all (полный просмотр) вне зависимости от наличия индексов — к примеру, классификатор с 40-ка записями. MySQL имеет алгоритм использования нескольких индексов (index merge), но работает этот алгоритм не очень часто, и только без order by. Единственный разумный способ пытаться использовать index merge — случаи выборки по разным столбцам с OR.

Еще одно отступление: подразумевается что читатель уже знаком с explain. Часто сам запрос немного модифицируется оптимизатором, поэтому для того, чтобы понять, почему использовался или нет тот или иной индекс, следует вызвать а затем который и покажет измененный оптимизатором запрос.

Покрывающий индекс — от толстых таблиц к индексам

Итак задача: пусть у нас есть довольно простой запрос, который выполняется довольно часто, но для такого частого вызова относительно медленно. Рассмотрим стратегию приведения нашего запроса к using index, как к наиболее быстрому выбору.

Следует указать на разницу в кешировании запросов в разных базах. Если PostgreSQL/Oracle кешируют планы запросов (как бы prepare for some timeout), то MySQL просто кеширует СТРОКУ запроса (включая значение параметров) и сохраняет результат запроса. То есть если последовательно селектировать несколько раз — то, если DDD не содержит изменяющихся функций, и таблица AAA не изменилась (в смысле используемой изоляции), результат будет взят прямо из кеша. Довольно спорное улучшение.

Источник

Spark SQL. Немного об оптимизаторе запросов

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

До того как встретиться с Big Data и Spark, в частности, мне довелось много и часто оптимизировать SQL запросы, сначала для MSSQL, потом для Oracle, и вот теперь я столкнулся со SparkSQL.

И если для СУБД уже существует множество хороших книг, описывающих методологию и «ручки», которые можно покрутить для получения оптимального плана запроса, то для Spark такого рода книг я не встречал. На глаза попадались больше статьи и наборы практик, причем больше относящиеся к работе через RDD/Dataset API, а не чистому SQL. Для меня одной из эталонных книг на тему оптимизации SQL является книга Дж. Льюис «Oracle. Основы стоимостной оптимизации». Что-то подобное по глубине проработки я и искал. Почему предметом исследования стал именно SparkSQL, а не API, лежащий в основе? Тут интерес был вызван особенностями проекта, над которым я работаю.

Для одного из заказчиков наша компания разрабатывает хранилище данных, детальный слой которого и часть витрин находится в кластере Hadoop, а итоговые витрины — в Oracle. Данный проект подразумевает обширный слой преобразования данных, который реализован на Spark. Для ускорения разработки и возможности подключения разработчиков ETL, не знакомых с тонкостями технологий Big Data, но хорошо знакомых с SQL и ETL средствами, был разработан инструмент, напоминающий по идеологии прочие ETL средства, например, Informatica, и позволяющий визуально конструировать ETL процессы с последующей генерацией кода для Spark. В силу сложности алгоритмов и большого количества преобразований разработчики в основном используют SparkSQL запросы.

Вот с этого места и начинается история, так как пришлось отвечать на большое количество вопросов вида «А почему запрос не работает/работает медленно/работает не так как в Oracle?». Наиболее интересной частью для меня оказался именно этот: «Почему медленно работает?». Тем более что в отличие от СУБД, с которыми я работал до этого, можно залезть в исходный код и получить ответ на интересующие вопросы.

Ограничения и допущения

Для запуска примеров и анализа исходного кода используется Spark 2.3.0.
Предполагается, что читатель знаком с архитектурой Spark, и общими принципами действия оптимизатора запросов для одной из СУБД. Как минимум, фраза «план запроса» точно не должна вызывать удивления.

Также данная статья пытается не стать переводом кода оптимизатора Spark на русский язык, поэтому для вещей, весьма интересных с точки зрения работы оптимизатора, но которые можно прочитать в исходном коде, тут будут просто кратко упомянуты со ссылками на соответствующие классы.

Переходим к изучению

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

Основной модуль, отвечающий за разбор SQL, и оптимизацию плана выполнения запроса — Spark Catalyst.

Расширенный вывод при описании плана запроса (df.explain(true)) позволяет отследить все стадии, которые проходит запрос:

Следующие стадии оптимизации и выполнения (например, WholeStageCodegen) выходят за рамки данной статьи, но весьма подробно (равно как и описанные выше стадии) описаны в Mastering Spark Sql.

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

Виды оптимизаторов запросов

Можно выделить два вида оптимизаторов запросов:

Первые заточены на применении набора фиксированных правил, например, применение условий фильтраций из where на более ранних этапах, если это возможно, предвычисление констант и т.д.

CBO оптимизатор для оценки качества полученного плана используют стоимостную функцию, которая обычно зависит от объема обрабатываемых данных, количества строк, попадающих под фильтры, стоимости выполнения тех или иных операций.

Ознакомиться детально с дизайн-спецификацией на CBO для Apache Spark можно по ссылкам: спецификация и основная JIRA задача для реализации.

Отправной точкой для изучения полного набора существующих оптимизаций может послужить код Optimizer.scala.

Вот небольшая выдержка из длинного списка доступных оптимизаций:

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

Особенностью CBO является то, что для корректной работы ему необходимо знать и хранить информацию по статистике данных, используемых в запросе — количество записей, размер записи, гистограммы распределения данных в столбцах таблиц.

Для сбора статистики используется набор SQL команд ANALYZE TABLE… COMPUTE STATISTICS, кроме того, необходим набор таблиц для хранения информации, API предоставляется через ExternalCatalog, точнее через HiveExternalCatalog.

Так как в настоящий момент CBO по умолчанию отключен, то основной упор будет сделан на исследовании доступных оптимизация и нюансов RBO.

Виды и выбор стратегии join

На стадии формирования физического плана выполнения запроса производится выбор стратегии join. На настоящий момент в Spark доступны следующие варианты (изучение кода можно начать с кода в SparkStrategies.scala).

Broadcast hash join

Наилучший вариант в случае если одна из сторон join достаточно мала (критерий достаточности задается параметром spark.sql.autoBroadcastJoinThreshold в SQLConf). В этом случае данная сторона целиком копируется на все executor’ы, где и происходит hash join с основной таблицей. Кроме размера следует учитывать, что в случае outer join можно копировать только outer сторону, поэтому по возможности в качестве ведущей таблицы в случае outer join необходимо использовать таблицу с наибольшим объемом данных.

Sort merge join

С включенной по умолчанию настройкой spark.sql.join.preferSortMergeJoin данный способ применяется по умолчанию, если ключи для join можно отсортировать.
Из особенностей можно отметить, что в отличие от предыдущего способа, оптимизация по кодогенерации для выполнения операции доступна только для inner join.

Shuffle hash join

В случае если ключи не поддаются сортировке, либо отключена настройка выбора sort merge join по умолчанию, Catalyst пытается применить shuffle hash join. Помимо проверки на настройки, проверяется также, что Spark хватит памяти, чтобы построить локальный hash map для одной партиции (общее количество партиций задается настройкой spark.sql.shuffle.partitions)

BroadcastNestedLoopJoin и CartesianProduct

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

Порядок указания таблиц в join’ах

В любом случае для работы join необходим shuffle таблиц по ключу. Поэтому в настоящий момент порядок указания таблиц, особенно в случае выполнению подряд нескольких join важен (если быть занудой, то в том случае, если не включен CBO, и не включена настройка JOIN_REORDER_ENABLED).

По возможности порядок соединения таблиц должен минимизировать количество shuffle операций для больших таблиц, для чего соединения по одному и тому же ключу должны идти последовательно. Также не стоит забывать про минимизацию данных для join, для возможности включения Broadcast Hash Join.

Транзитивное применение условий фильтрации

Рассмотрим следующий запрос:

Здесь мы соединяем две таблицы, которые партицированы одинаково, по полю actual_date и накладываем явный фильтр только на партицию по таблице balance.

Как видно из оптимизированного плана запроса, фильтр по дате применяется также и к customer, причем на моменте чтения данных с диска определяется, что необходима ровно одна партиция.

Но стоит только заменить в запросе inner join на left outer, как тут же отваливается push predicate для таблицы customer, и происходит полное сканирование, что является нежелательным эффектом.

Конвертация типов

Рассмотрим простой пример отбора из таблицы с фильтрацией по типу клиента, в схеме тип поля party_type — string.

И сравним два получившихся плана, первый — когда обращаемся по некорректному типу (будет неявное приведение int to string), второй — когда по типу, соответствующему схеме.

Аналогичная проблема наблюдается и для случая сравнения даты со строкой, будет фильтр на сравнение строк. Пример:

Направление дальнейших исследований

Много любопытной информации о «ручках», которые можно использовать для тонкой настройки Catalyst, а также о возможностях (настоящих и будущих) оптимизатора можно почерпнуть из SQLConf.scala.

В частности, как можно увидеть по умолчанию стоимостной оптимизатор на текущий момент все еще выключен.

Равно как и зависящие от него оптимизации, связанные с переупорядочиванием join’ов.

Краткие итоги

Удалось затронуть только небольшую часть существующих оптимизаций, впереди ждут эксперименты со стоимостной оптимизацией, которая может дать намного больше простора для преобразования запросов. Также отдельный интересный вопрос — сопоставление набора оптимизаций при чтении из Parquet и Orc файлов, судя по jira проекта дело идет к паритету, но так ли это на самом деле?

Источник

Оптимизация SQL запросов или розыск опасных преступников

Кейс компании Appbooster

Полагаю, практически каждый проект, использующий Ruby on Rails и Postgres в качестве основного вооружения на бэкенде находится в перманентной борьбе между скоростью разработки, читаемостью/поддерживаемостью кода и скоростью работы проекта в продакшене. Я расскажу о своем опыте балансирования между этими тремя китами в кейсе, где на входе страдали читаемость и скорость работы, а на выходе получилось сделать то, что до меня безуспешно пытались сделать несколько талантливых инженеров.

Полностью вся история займёт несколько частей. Это первая, где я расскажу о том что такое PMDSC для оптимизации SQL-запросов, поделюсь полезными инструментами измерения эффективности запросов в postgres и напомню об одной полезной старой шпаргалке, которая до сих пор актуальна.

Сейчас, спустя какое-то время, “задним умом” я понимаю, что на входе в этот кейс совершенно не ожидал что у меня всё получится. Поэтому этот пост будет полезен скорее для смелых и не самых опытных разработчиков, чем для супер-сеньоров видавших рельсы с голым SQL.

Вводные данные

Мы в Appbooster занимаемся продвижением мобильных приложений. Чтобы легко выдвигать и проверять гипотезы мы разрабатываем несколько своих приложений. Бэкенд большинства из них это Rails API и Postgresql.

Герой этой публикации разрабатывается с конца 2013 года – тогда только-только вышел rails 4.1.0.beta1. С тех пор проект вырос в полноценно нагруженное веб-приложение, которое крутится на нескольких серверах в Amazon EC2 c отдельным инстансом базы данных в Amazon RDS (db.t3.xlarge с 4 vCPU и 16 GB RAM). Пиковые нагрузки доходят до 25k RPM, средняя нагрузка днём 8-10k RPM.

С инстанса базы данных, точнее с её кредитного баланса и началась эта история.

Как работает инстанс Postgres типа “t” в Амазон RDS: если ваша база данных работает со средним потреблением процессорного времени ниже определенного значения, то у вас на счету накапливаются кредиты, которые инстанс может тратить на потребление процессора в часы высокой нагрузки – это позволяет не переплачивать за серверные мощности и справляться с высокой нагрузкой. Более подробно о том за что и сколько платят, используя AWS можно прочитать в статье нашего CTO.

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

Мы используем Newrelic для отслеживания общего времени отклика за сутки. Картина выглядела так:

Желтым на графике выделена часть времени ответа, которую занимает Postgres. Как видно, иногда время ответа доходило до 1000 ms и большую часть времени именно база данных размышляла над ответом. Значит надо смотреть что происходит с SQL запросами.

PMDSC – простая и понятная практика для любой скучной работы оптимизации SQL запросов

Play it!
Measure it!
Draw it!
Suppose it!
Check it!

Play it!

Пожалуй, самая важная часть всей практики. Когда кто-то произносит фразу «Оптимизация SQL запросов» – это скорее вызывает приступ зевоты и скуку у абсолютного большинства людей. Когда ты произносишь «Детективное расследование и розыск опасных злодеев» – это сильнее вовлекает и настраивает тебя самого на нужный лад. Поэтому важно войти в игру. Мне понравилось играть в детектива. Я представлял себе что проблемы с базой данных либо опасные преступники, либо редкие болезни. А себя представлял в роли Шерлока Холмса, Лейтенанта Коломбо или Доктора Хауса. Выбирай героя на свой вкус и вперед!

Measure It!

Для анализа статистики запросов, я установил PgHero. Это очень удобный способ читать данные из расширения pg_stat_statements для Postgres. Заходим в /queries и смотрим на статистику всех запросов за последние сутки. Сортировка запросов по умолчанию по колонке Total Time – доля общего времени которое база данных обрабатывает запрос – ценный источник в поиске подозреваемых. Average Time – сколько в среднем запрос выполняется. Calls – сколько запросов было за выбранное время. PgHero считает медленными запросы, которые выполнялись более 100 раз за сутки и занимали в среднем более 20 миллисекунд. Список медленных запросов на первой странице, сразу после списка дублирующихся индексов.

Берём первый в списке и смотрим детали запроса, тут же можно посмотреть его explain analyze. Eсли planning time сильно меньше execution time, значит с этим запросом что-то не так и мы концентрируем внимание на этом подозреваемом.

В PgHero есть свой способ визуализации, но мне больше понравилось использовать explain.depesz.com копируя туда данные из explain analyze.

Один из подозреваемых запросов использует Index Scan. На визуализации видно что этот индекс не эффективен и является слабым местом – выделено красным. Отлично! Мы изучили следы подозреваемого и нашли важную улику! Правосудие неизбежно!

Draw it!

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

Немного контекста. Мы тестировали один из способов удержания аудитории в приложении – что-то вроде лотереи, в которой можно выиграть немного внутренней валюты. Делаешь ставку, загадываешь число от 0 до 100 и забираешь весь банк, если твое число оказалось ближе всех к тому что получил генератор случайных чисел. Мы назвали это “Арена”, а розыгрыши назвали “Битвами”.

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

Индекс, используемый в подозреваемой части запроса покрывает все созданные битвы по полю created_at. Запрос пробегает по 505330 записям из которых выбирает 40, а 505290 отсеивает. Выглядит очень расточительно.

Suppose it!

Выдвигаем гипотезу. Что поможет базе данных найти сорок записей из пятисот тысяч? Попробуем сделать индекс который покрывает поле ставка, только для битв со статусом “жду игроков” – паршиал индекс.

Паршиал индекс – существует только для тех записей, которые подходят под условие: поле статус равно “жду_игроков” и индексирует поле ставка – ровно то что в условии запроса. Очень выгодно использовать именно этот индекс: он занимает всего 40 килобайт и не покрывает те битвы которые уже сыграны и не нужны нам для получения выборки. Для сравнения – индекс index_arena_battles_on_created_at, который использовался подозреваемым занимает около 40 Мб, а таблица с битвами около 70 Мб. Этот индекс можно смело удалить, если его не используют другие запросы.

Check it!

Выкатываем миграцию с новым индексом в продакшен и наблюдаем за тем как изменился отклик эндпоинта с битвами.

На графике видно во сколько мы выкатили миграцию. Вечером 6 декабря время отклика уменьшилось примерно в 10 раз с

50ms. Подозреваемый в суде получил статус заключенного и теперь сидит в тюрьме. Отлично!

Prison Break

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

Утром 11 декабря планировщик запросов postgres решил что использовать свежий паршиал индекс, ему больше не выгодно и стал снова использовать старый.

Мы снова на этапе Suppose it! Собираем дифференциальный диагноз, в духе доктора Хауса:

Давай вместе пройдемся по этому SQL. Выбираем все поля битвы из таблицы битв статус которых равен “жду игроков” и ставка меньше или равна некоему числу. Пока все понятно. Следующее слагаемое условия выглядит жутко.

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

Сложно осмыслить, но в итоге этим подзапросом мы пробовали исключить те битвы в которых игрок уже участвует. Смотрим общий explain запроса и видим Planning time: 0.180 ms, Execution time: 12.119 ms. Мы нашли сообщника!

Настало время моей любимой шпаргалки, которая гуляет по интернетам с 2008 года. Вот она:

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

На самом деле вот что нам нужно:

Сохрани себе эту картинку, а еще лучше распечатай и повесь в нескольких местах в офисе.

Переписываем подзапрос на LEFT JOIN WHERE B.key IS NULL, получаем:

Исправленный запрос бежит сразу по двум таблицам. Мы присоединили “слева” таблицу с записями участий в битвах пользователя и добавили условие что идентификатор участия не существует. Смотрим explain analyze полученного запроса: Planning time: 0.185 ms, Execution time: 0.337 ms. Отлично! Теперь планировщик запросов не будет сомневаться что ему стоит использовать паршиал индекс, а будет использовать самый быстрый вариант. Сбежавший заключенный и его сообщник приговорены на пожизненное заключение в заведении строгого режима. Сбежать им будет сложнее.

Источник

Оптимизация запросов базы данных на примере B2B сервиса для строителей

Как вырасти в 10 раз под количеству запросов к БД не переезжая на более производительный сервер и сохранить работоспособность системы? Я расскажу, как мы боролись с падением производительности нашей базы данных, как оптимизировали SQL запросы, чтобы обслуживать как можно больше пользователей и не повышать расходы на вычислительные ресурсы.

Я делаю сервис для управления бизнес процессами в строительных компаниях. С нами работает около 3 тысяч компаний. Более 10 тысяч человек каждый день работают с нашей системой по 4-10 часов. Она решает разные задачи планирования, оповещения, предупреждения, валидации… Мы используем PostgreSQL 9.6. В базе данных у нас около 300 таблиц и каждые сутки в нее поступает до 200 млн запросов (10 тысяч различных). В среднем у нас 3-4 тысяч запросов в секунду, в самые активные моменты более 10 тысяч запросов в секунду. Большая часть запросов — OLAP. Добавлений, модификаций и удалений намного меньше, то есть OLTP нагрузка относительно небольшая. Все эти цифры я привел, чтобы вы могли оценить масштаб нашего проекта и понять насколько наш опыт может быть полезен для вас.

Картина первая. Лирическая

Картина вторая. Статистическая

Итак у нас есть около 10 тысяч различных запросов, которые выполняются на нашей БД за сутки. Из этих 10 тысяч есть монстры, которые выполняются по 2-3 млн раз со средним временем выполнения 0.1-0.3 мс и есть запросы со средним временем выполнения 30 секунд, которые вызываются 100 раз в сутки.

Оптимизировать все 10 тысяч запросов не представлялось возможным, поэтому мы решили разобраться с тем, куда направлять усилия, чтобы повышать производительность БД правильно. После нескольких итераций мы стали делить запросы на типы.

TOP запросы

Это самые тяжелые запросы, которые занимают больше всего времени (total time). Это запросы, которые либо очень часто вызываются либо запросы, которые очень долго выполняются (долгие и частые запросы были оптимизированы еще на первых итерациях борьбы за скорость). В итоге суммарно на их исполнение сервер тратит больше всего времени. Причем важно отделять топ запросы по общему времени исполнения и отдельно по IO time. Способы оптимизации таких запросов немного разные.

Обычная практика всех компаний- работать с TOP запросами. Их немного, оптимизация даже одного запроса может освободить 5-10% ресурсов. Однако, по мере “взросления” проекта оптимизация TOP запросов становится все более нетривиальной задачей. Все простые способы уже отработаны, да и самый “тяжелый” запрос отнимает “всего” 3-5% ресурсов. Если TOP запросы в сумме занимают менее 30-40% времени, то скорее всего вы уже приложили усилия, чтобы они работали быстро и пришла пора переходить к оптимизации запросов из следующей группы.
Остается ответить на вопрос сколько верхних запросов включить в эту группу. Я обычно беру не меньше 10, но не больше 20. Стараюсь, чтобы время первого и последнего в TOP группе отличалось не более чем в 10 раз. То есть если время исполнения запросов резко падает с 1 места до 10, то беру TOP-10, если падение более плавное, то увеличиваю размер группы до 15 или 20.

Середняки (medium)

Это все запросы, которые идут сразу за TOP, за исключением последних 5-10%. Обычно в оптимизации именно этих запросов кроется возможность сильно поднять производительность сервера. Эти запросы могут “весить” до 80%. Но даже если их доля перевалила за 50%, значит пора на них взглянуть более внимательно.

Хвост (tail)

Как было сказано, эти запросы идут в конце и на них уходит 5-10% времени. Про них можно забыть, только если вы не используете автоматические средства анализа запросов, тогда их оптимизация тоже может дешево обойтись.

Как оценить каждую группу?

Я использую SQL запрос, который помогает сделать такую оценку для PostgreSQL (уверен что для многих других СУБД можно написать похожий запрос)

Результат запроса- три столбца, каждый из которых содержит процент времени, который уходит на обработку запросов из этой группы. Внутри запроса есть два числа (в моем случае это 20 и 800), которые отделяет запросы одной группы от другой.

Вот так примерно соотносятся доли запросов на момент начала работ по оптимизации и сейчас.

Из диаграммы видно, что доля TOP запросов резко снизилась, зато выросли “середняки”.
Поначалу в TOP запросы попадали откровенные ляпы. Со временем детские болезни исчезли, доля TOP запросов сокращалась, приходилось прилагать все больше усилий, чтобы ускорить тяжелые запросы.

Вот список самых часто используемых приемов, которые помогали нам ускорять TOP запросы:

Тогда мы обратили внимание на вторую группу запросов- группу середняков. В ней намного больше запросов и казалось, что на анализ всей группы уйдет очень много времени. Однако большинство запросов оказались очень просты для оптимизации, а многие проблемы повторялись десятки раз в разнличных вариациях. Вот примеры некоторых типовых оптимизаций, который мы применяли к десяткам похожих запросов и каждая группа оптимизированных запросов разгружала БД на 3-5%.

Например, вместо запроса для выборки всех водителей по большой таблице доставок (DELIVERY)

сделали запрос по сравнительно небольшой таблице PERSON

Каждый конкретный запрос удавалось ускорить порой в 3-1000 раз. Несмотря на впечатляющие показатели, поначалу нам казалось, что нет смысла в оптимизации запроса, который выполняется 10 мс, входит в 3-ю сотню самых тяжелых запросов и в общем времени нагрузки на БД занимает сотые доли процента. Но применяя один и тот же рецепт к группе однотипных запросов мы отыгрывали по несколько процентов. Чтобы не тратить время на ручной просмотр всех сотен запросов мы написали несколько простых скриптов, которые с помощью регулярных выражений находили однотипные запросы. В итоге автоматический поиск групп запросов позволил нам еще больше улучшить нашу производительность, затратив скромные усилия.

В итоге мы уже три года работаем на одном и том же железе. Среднесуточная нагрузка около 30%, в пиках доходит до 70%. Количество запросов как и количество пользователей выросло примерно в 10 раз. И все это благодаря постоянному мониторингу этих самых групп запросов TOP-MEDIUM. Как только какой-то новый запрос появляется в группе TOP, мы его тут же анализируем и пытаемся ускорить. Группу MEDIUM мы раз в неделю просматриваем с помощью скриптов анализа запросов. Если попадаются новые запросы, которые мы уже знаем как оптимизировать, то мы их быстро меняем. Иногда находим новые способы оптимизации, которые можно применить сразу к нескольким запросам.

По нашим прогнозам текущий сервер выдержит увеличение количества пользователей еще в 3-5 раз. Правда у нас есть еще один козырь в рукаве- мы до сих пор не перевели SELECT- запросы на зеркало, как рекомендуется делать. Но мы этого не делаем осознанно, так как хотим сначала до конца исчерпать возможности «умной» оптимизации, прежде чем включать «тяжелую артиллерию».
Критический взгляд на проделанную работу может подсказать использовать вертикальное масштабирование. Купить более мощный сервер, вместо того, чтобы тратить время специалистов. Сервер может стоить не так дорого, тем более что лимиты вертикального масштабирования у нас еще не исчерпаны. Однако в 10 раз выросло лишь количество запросов. За несколько лет, увеличился функционал системы и сейчас разновидностей запросов стало больше. Тот функционал, который был, за счет кеширования выполняется меньшим количеством запросов, к тому же более эффективных запросов. Значит можно смело умножить еще на 5, чтобы получить реальный коэффициент ускорения. Итак по самым скромным подсчетам можно сказать, что ускорение составило 50 и более раз. Вертикально раскачать сервер в 50 раз обошлось бы дороже. Особенно учитывая, что однажды проведенная оптимизация работает все время, а счет за арендованный сервер приходит каждый месяц.

Источник