Оптимизация базы данных: измерение, приоритет и проверка

Прямой ответ

Оптимизация базы данных начинается с измерения доли времени по запросам, поэтому работа выполняется против запроса, занимающего основную долю, а не против каждого запроса по очереди.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Сортировка по суммарному времени показывает запрос, занимающий основную долю нагрузки, поэтому изменение этого запроса даёт наибольший эффект. Об оптимизации отдельного запроса — в статье про оптимизацию запросов БД.

Поиск узкого места по доле времени

Статистика запросов показывает суммарное время и число вызовов, поэтому узкое место определяется по доле общего времени.

Суммарное время. Запрос с большим суммарным временем занимает основную долю нагрузки, поэтому изменение даёт наибольший эффект.

Число вызовов. Запрос с большим числом вызовов и малым временем одного вызова даёт заметную долю за счёт частоты.

Среднее время. Запрос с большим средним временем заметен при малом числе вызовов, поэтому доля определяется произведением.

Практическое следствие: оптимизация запроса с малой долей общего времени не сокращает общую нагрузку заметно, поэтому работа выполняется против запроса с основной долей.

Отдельный аспект — частые запросы в цикле. Обращение к базе для каждой строки результата даёт большое число вызовов, поэтому суммарное время растёт даже при малом времени одного вызова. Объединение обращений сокращает число вызовов и суммарное время.

Настройки сервера и обслуживание

Настройки сервера определяют объём ресурсов, доступных запросу, поэтому изменение настройки влияет на все запросы сразу.

Объём памяти для операций. Настройка определяет объём данных, обрабатываемых в памяти, поэтому превышение приводит к обмену с диском.

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

Сбор статистики. Статистика таблицы обновляется при обслуживании, поэтому планировщик оценивает объём по актуальным данным.

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

Проверка результата после изменения

Изменение проверяется повторной статистикой, поэтому эффект фиксируется по измерению, а не по впечатлению.

Повторная статистика. Суммарное время запроса после изменения сравнивается с временем до изменения, поэтому сокращение подтверждается.

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

Поведение при росте объёма. Проверка на данных, близких к рабочему объёму, показывает, сохраняется ли эффект при росте таблицы.

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

О разборе плана выполнения — в статье про EXPLAIN PLAN; о метриках запросов — в статье про pg_stat_statements.

Практика: настройка оптимизации базы данных

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

2. Работайте против запроса с основной долей времени. Изменение этого запроса даёт наибольший эффект на общую нагрузку.

3. Проверяйте частые запросы в цикле. Большое число вызовов даёт заметную долю даже при малом времени одного вызова.

4. Обновляйте статистику таблиц. Планировщик оценивает объём по актуальным данным, поэтому план выбирается верно.

5. Изменяйте настройки сервера по измерению. Изменение без измерения может ухудшить другие запросы.

6. Проверяйте результат повторной статистикой. Сокращение времени подтверждается измерением после изменения.

7. Фиксируйте время запросов в метриках. Рост времени указывает на изменение объёма данных либо на деградацию плана.

Рекомендации:

1. Не оптимизируйте запросы по очереди без измерения. Запрос с малой долей времени не даёт заметного эффекта на общую нагрузку.
2. Учитывайте число вызовов при выборе приоритета. Частый запрос с малым временем одного вызова даёт заметную долю.
3. Сопоставляйте изменение настройки с влиянием на все запросы. Изменение настройки сервера затрагивает все запросы сразу.
4. Фиксируйте суммарное время в метриках. Рост суммарного времени указывает на необходимость изменения.
5. Пересматриваем приоритет при изменении нагрузки. Рост объёма данных меняет долю времени по запросам.

Практический сценарий: база нагружена, но медленный запрос не очевиден

Контекст: нагрузка на базу была высокой, при этом отдельный медленный запрос не выделялся, поэтому причина не определялась.

1. Диагностика. Статистика запросов показала запрос с малым средним временем, но с числом вызовов в десятки тысяч за интервал. Суммарное время этого запроса занимало основную долю нагрузки, поэтому доля определялась частотой, а не временем одного вызова.
2. Причина. Запрос выполнялся в цикле для каждой строки результата, поэтому число вызовов умножалось на объём выборки.
3. Действия. (а) Объединили обращения в один запрос с соединением, чтобы число вызовов не умножалось на число строк; (б) сняли повторную статистику и зафиксировали сокращение числа вызовов; (в) сравнили суммарное время до и после изменения и убедились в сокращении доли; (г) проверили статистику остальных запросов и убедились в отсутствии ухудшения; (д) настроили метрику суммарного времени, чтобы рост обнаруживался.
4. Результат. Число вызовов сократилось, поэтому нагрузка на базу уменьшилась без изменения времени одного запроса.

Вывод: доля нагрузки определяется произведением числа вызовов на время одного вызова, поэтому частый запрос занимает основную долю даже при малом времени. Статистика запросов по суммарному времени показывает запрос, изменение которого даёт наибольший эффект.

FAQ

С чего начинать оптимизацию базы данных? Со статистики запросов с сортировкой по суммарному времени. Запрос с основной долей общего времени даёт наибольший эффект от изменения.

Почему частый запрос важнее медленного? Доля нагрузки определяется произведением числа вызовов на время одного вызова, поэтому частый запрос занимает основную долю.

Как влияет устаревшая статистика таблицы? Планировщик оценивает объём по неактуальным данным, поэтому план выбирается против неверной оценки и запрос замедляется.

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

Источники: PostgreSQL query tuning, pg_stat_statements, routine vacuuming — ссылки в шапке.