Чехол для AppBooster Я считаю, что почти каждый проект, использующий Ruby on Rails и Postgres в качестве основного оружия серверной части, находится в постоянной борьбе между скоростью разработки, читаемостью/удобством сопровождения кода и скоростью проекта в производстве.
Расскажу о своем опыте балансирования между этими тремя столпами в случае, когда на входе страдала читабельность и скорость работы, но на выходе мне удалось сделать то, что до меня безуспешно пытались сделать несколько талантливых инженеров.
Вся история будет состоять из нескольких частей.
Это первый, где я расскажу о том, что такое PMDSC для оптимизации SQL-запросов, поделюсь полезными инструментами для измерения эффективности запросов в postgres и напомню одну полезную старую шпаргалку, которая актуальна до сих пор.
Сейчас, спустя некоторое время, «задним числом» я понимаю, что на входе в это дело я совершенно не ожидал, что у меня все получится.
Поэтому этот пост будет полезен скорее смелым и не самым опытным разработчикам, чем супер-сениторам, повидавшим веревки с голым SQL.
Входные данные
Мы в Appbooster продвигаем мобильные приложения.Чтобы легко выдвигать и проверять гипотезы, мы разрабатываем несколько собственных приложений.
Бэкэнд большинства из них — Rails API и Postgresql. Герой этой публикации разрабатывался с конца 2013 года — тогда только вышел рельс 4.1.0.beta1. С тех пор проект превратился в полностью загруженное веб-приложение, работающее на нескольких серверах Amazon EC2 с отдельным экземпляром базы данных в Amazon RDS (db.t3.xlarge с 4 виртуальными ЦП и 16 ГБ ОЗУ).
Пиковые нагрузки достигают 25 тысяч об/мин, средняя нагрузка в течение дня — 8-10 тысяч об/мин.
Эта история началась с экземпляра базы данных, а точнее с его кредитного баланса.
Как работает экземпляр Postgres типа «t» в Amazon RDS: если ваша база данных работает со средним потреблением ЦП ниже определенного значения, то в вашем аккаунте накапливаются кредиты, которые экземпляр может потратить на потребление ЦП в часы пик – это позволяет вам чтобы не переплачивать за мощности сервера и справляться с высокой нагрузкой.
Вы можете узнать больше о том, за что и сколько вы платите за использование AWS. читайте в статье нашего технического директора .
В определенный момент остаток кредита был исчерпан.
Некоторое время этому не придавали особого значения, поскольку баланс кредитов можно пополнить деньгами — это обходилось нам примерно в 20 долларов в месяц, что не очень существенно для общей стоимости аренды вычислительных мощностей.
При разработке продукта принято в первую очередь уделять внимание задачам, сформулированным из требований бизнеса.
Повышенное потребление ЦП сервера базы данных включено в технический долг и покрывается за счет небольших затрат на приобретение остатка кредитов.
В один прекрасный день я написал в ежедневной сводке, что очень устал тушить «пожары», периодически возникавшие в разных местах проекта.
Если так будет продолжаться, то выгоревший разработчик будет уделять время бизнес-задачам.
В тот же день я подошел к главному руководителю проекта, объяснил ситуацию и попросил время для выяснения причин периодических возгораний и проведения ремонтных работ. Получив добро, я начал собирать данные с различных систем мониторинга.
Мы используем Newrelic для отслеживания общего времени ответа в день.
Картина выглядела так: 
Часть времени ответа, которую занимает Postgres, на графике выделена желтым цветом.
Как видите, иногда время ответа достигало 1000 мс и большую часть времени об ответе думала база данных.
Это означает, что нам нужно посмотреть, что происходит с SQL-запросами.
PMDSC — простая и понятная практика для любой скучной работы по оптимизации SQL-запросов.
Сыграй!
Измерьте это!
Нарисуй это!
Предположим!
Проверь это!
Сыграй!
Пожалуй, самая важная часть всей практики.Когда кто-то произносит фразу «Оптимизация SQL-запросов», скорее всего, у подавляющего большинства людей это вызывает приступ зевоты и скуки.
Когда вы говорите «Детективное расследование и поиск опасных злодеев», это становится более увлекательным и настраивает на нужный лад. Поэтому важно войти в игру.
Мне нравилось играть детектива.
Я представлял, что проблемы с базой данных — это либо опасные преступники, либо редкие заболевания.
И он представлял себя в роли Шерлока Холмса, лейтенанта Коломбо или доктора Хауса.
Выбирайте героя на свой вкус и вперед!
Измерьте это!
Для анализа статистики запросов я установил PgHero .
Это очень удобный способ чтения данных из расширения pg_stat_statements для Postgres. Заходим в /queries и смотрим статистику всех запросов за последние 24 часа.
Сортировка запросов по умолчанию по столбцу «Общее время» (процент общего времени, затрачиваемого базой данных на обработку запроса) является ценным ресурсом при поиске подозреваемых.
Среднее время – как долго в среднем выполняется запрос.
Звонки – сколько было запросов за выбранное время.
PgHero считает запросы медленными, если они выполнялись более 100 раз в день и занимали в среднем более 20 миллисекунд. Список медленных запросов на первой странице, сразу после списка повторяющихся индексов.
Берем первый в списке и смотрим детали запроса, сразу видно его поясняющий анализ.
Если время планирования намного меньше времени выполнения, значит, с этим запросом что-то не так и мы фокусируем свое внимание на этом подозреваемом.
У PgHero есть свой метод визуализации, но мне он понравился больше объяснение.
depesz.com копирование данных из объяснения и анализа туда.
Один из подозрительных запросов использует индексное сканирование.
На визуализации видно, что данный индекс не эффективен и является слабым местом – выделено красным.
Большой! Мы исследовали следы подозреваемого и нашли важные улики! Справедливость неизбежна!
Нарисуй это!
Нарисуем набор данных, который используется в проблемной части запроса.Будет полезно сравнить, с какими данными охватывается индекс.
Немного контекста.
Мы протестировали один из способов удержания аудитории в приложении — что-то вроде лотереи, в которой можно выиграть какую-то внутреннюю валюту.
Вы делаете ставку, угадываете число от 0 до 100 и забираете весь банк, если ваше число ближе всего к тому, что получил генератор случайных чисел.
Мы назвали ее «Арена», а розыгрыши — «Битвы».
На момент расследования в базе данных содержалось около пятисот тысяч боевых записей.
В проблемной части запроса ищем бои, в которых ставка не превышает баланс пользователя и статус боя - жду игроков.
Мы видим, что пересечение множеств (выделено оранжевым цветом) имеет очень небольшое количество записей.
Индекс, используемый в подозрительной части запроса, охватывает все созданные бои в поле Create_at. Запрос обрабатывает 505330 записей, из которых он выбирает 40 и удаляет 505290. Выглядит очень расточительно.
Предположим!
Выдвинем гипотезу.Что поможет базе данных найти сорок записей из пятисот тысяч? Попробуем сделать индекс, охватывающий поле ставок, только для боев со статусом «ожидание игроков» — парсальный индекс.
Парсиальный индекс — существует только для тех записей, которые соответствуют условию: поле статуса равно «ожидание_игроков» и индексирует поле ставки — именно то, что есть в условии запроса.add_index :arena_battles, :bet, where: "status = 'waiting_for_players'", name: "index_arena_battles_on_bet_partial_status"
Использовать именно этот индекс очень выгодно: он занимает всего 40 килобайт и не охватывает те бои, которые уже были сыграны и не нужны нам для получения выборки.
Для сравнения, index_arena_battles_on_created_at, которым пользовался подозреваемый, занимает около 40 МБ, а таблица с боями — около 70 МБ.
Этот индекс можно безопасно удалить, если другие запросы его не используют.
Проверь это!
Выкатываем миграцию с новым индексом в продакшн и наблюдаем, как изменился отклик боевой конечной точки.
На графике показано, в какое время мы развернули миграцию.
Вечером 6 декабря время отклика уменьшилось примерно в 10 раз с ~500 мс до ~50 мс.
Подозреваемый получил в суде статус заключенного и сейчас находится в тюрьме.
Большой!
Побег из тюрьмы
Через несколько дней мы поняли, что радоваться нам рано.Судя по всему, заключенный нашел сообщников, разработал и осуществил план побега.
Утром 11 декабря планировщик запросов postgres решил, что использование свежего парсального индекса ему уже невыгодно и снова начал использовать старый.
Мы вернулись на этап «Предположим!» Давайте поставим дифференциальный диагноз, в духе доктора Хауса: Возможно, вам нужно оптимизировать настройки Postgres; возможно незначительное обновление postgres до новой версии (9.6.11 -> 9.6.15); А может быть еще раз внимательно изучить, какие SQL-запросы генерирует Rails? Мы проверили все три гипотезы.
Последний вывел нас на след сообщника.
SELECT "arena_battles".
* FROM "arena_battles" WHERE "arena_battles".
"status" = 'waiting_for_players' AND (arena_battles.bet <= 98.13) AND (NOT EXISTS ( SELECT 1 FROM arena_participations WHERE arena_battle_id = arena_battles.id AND (arena_profile_id = 46809) )) ORDER BY "arena_battles".
"created_at" ASC
LIMIT 10 OFFSET 0
Давайте вместе пройдемся по этому SQL. Из таблицы боев выбираем все поля боя, у которых статус «ожидает игроков» и ставка меньше или равна определенному числу.
Пока все ясно.
Следующий член условия выглядит жутковато.
NOT EXISTS (
SELECT 1 FROM arena_participations
WHERE arena_battle_id = arena_battles.id
AND (arena_profile_id = 46809)
)
Мы ищем результат подзапроса, которого не существует. Получите первое поле из таблицы участия в бою, где ID боя совпадает и профиль участника принадлежит нашему игроку.
Попробую нарисовать набор, описанный в подзапросе.
Это сложно понять, но в итоге этим подзапросом мы постарались исключить те бои, в которых игрок уже участвует. Смотрим общее объяснение запроса и видим Время планирования: 0,180 мс, Время выполнения: 12,119 мс.
Мы нашли сообщника! Пришло время моей любимой шпаргалки, которая гуляет по Интернету с 2008 года.
Вот она: 
Да! Как только в запросе есть что-то, что должно исключить определенное количество записей на основе данных из другой таблицы, в памяти должен всплывать этот мем с бородой и кудряшками.
Собственно, это то, что нам нужно: 
Сохраните эту картинку себе, а еще лучше распечатайте и повесьте в нескольких местах своего офиса.
Переписываем подзапрос на LEFT JOIN WHERE B.key IS NULL, получаем: SELECT "arena_battles".
* FROM "arena_battles" LEFT JOIN arena_participations ON arena_participations.arena_battle_id = arena_battles.id AND (arena_participations.arena_profile_id = 46809) WHERE "arena_battles".
"status" = 'waiting_for_players' AND (arena_battles.bet <= 98.13) AND (arena_participations.id IS NULL) ORDER BY "arena_battles".
"created_at" ASC
LIMIT 10 OFFSET 0
Исправленный запрос выполняется одновременно по двум таблицам.
Мы прикрепили слева таблицу с записями участия пользователя в боях и добавили условие, что идентификатор участия не существует. Давайте посмотрим на анализ полученного запроса: Время планирования: 0,185 мс, Время выполнения: 0,337 мс.
Большой! Теперь планировщик запросов не будет сомневаться в том, что ему следует использовать парсальный индекс, а воспользуется самым быстрым вариантом.
Сбежавший заключенный и его сообщник приговорены к пожизненному заключению в колонии строгого режима.
Им будет сложнее спастись.
Резюме краткое.
Используйте Newrelic или другой подобный сервис, чтобы найти подсказки.
Мы поняли, что проблема именно в запросах к базе данных.
Используйте практику PMDSC — она работает и в любом случае хороша для вовлечения.
Используйте PgHero, чтобы найти подозреваемых и изучить улики в статистике SQL-запросов.
Использовать объяснение.
depesz.com — там удобно читать запросы объяснения и анализа.
Попробуйте нарисовать много данных, когда вы не понимаете, что именно делает запрос.
Вспомните сурового парня с локонами в разных местах головы, когда увидите подзапрос, который ищет что-то, чего нет в другой таблице.
Играя в детектива, возможно, вы даже получите значок.
Теги: #Разработка мобильных приложений #postgresql #ruby onrails
-
3D-Эффект В Фотошопе
19 Oct, 24 -
О Видеомагнитофонах И Программировании
19 Oct, 24 -
Пассивный Доход Для Студии. Поддержка Сайта.
19 Oct, 24 -
Семейство Хеш-Функций Google Cityhash
19 Oct, 24
