Производительность базы данных — это не то, что вы измеряете, когда приходят жалобы. Это то, что вы предвосхищаете, чтобы жалоб не было вовсе.
В мире, где простои баз данных измеряются в упущенной выгоде, проактивный мониторинг из роскоши превращается в необходимость. В статье описывается как pg_expecto позволяет не ждать проблем, а предсказывать и предотвращать их.
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic
Расчет и формирование файлов метрик оценки производительности СУБД с использованием расширения pg_expecto, для применения в системах мониторинга при необходимости реализация проактивного мониторинга производительности СУБД .
Предыдущие работы по теме:
Условие начала инцидента снижения скорости СУБД:
Если угол наклона линии регрессии операционной скорости < 0 ,
И
угол наклона линии регрессии ожиданий > 0
ТО
Создать оповещение мониторинга "Инцидент деградации производительности".
В качестве уровня важности оповещения , можно использовать абсолютное значение коэффициента корреляции :
Практическая реализация мониторинга производительности СУБД с использованием расширения pg_expecto
Порядок формирования метрик оценки производительности СУБД
Рассчитанные значения операционной скорости и ожидания СУБД сохраняются в текстовых файлах для использования системой мониторинга Zabbix
По результатам расчета угла наклона линий регрессии операционной скорости и ожиданий СУБД - формируется текстовый файл для формирования метрики индикатора деградации производительности СУБД.
Ненулевое значение индикатора деградации производительности СУБД является стартовым событием для начала процесса решения инцидента производительности СУБД.
Пример использования метрик оценки производительности СУБД и индикатора деградации производительности СУБД в системе мониторинга Zabbix.
Для лучшей скорости необходима настройка под конкретные условия трассы .
Задача
Определить качественное и количественное влияние на производительность тестовой СУБД изменения параметра checkpoint_timeout для сценария нагрузки "Mix".
checkpoint_timeout (integer)
Максимальное время между автоматическими контрольными точками в WAL. Если это значение задаётся без единиц измерения, оно считается заданным в секундах. Допускаются значения от 30 секунд до одного дня. Значение по умолчанию — пять минут (5min).
Ось X - общая нагрузка на СУБД. Ось Y - апроксимированные значения операционной скорости.
Ось X - общая нагрузка на СУБД. Ось Y - операционная скорость.
Итог:
Для данной СУБД в сценарии смешанной нагрузки "Mix":
Максимальная скорость СУБД достигается при значении параметра checkpoint_timeout = 60 при общей нагрузке 18 соединений.
Максимальная нагрузка , после которой скорость СУБД начинает снижаться достигается при значении параметра checkpoint_timeout = 300 при общей нагрузке 26 соединений.
При предельной общей нагрузке 111 соединений наибольшая скорость СУБД достигается при значении параметра checkpoint_timeout = 900.
Ось X - инцидент производительности СУБД . Ось Y - приоритет инцидента
Ось X - инцидент производительности СУБД . Ось Y - приоритет инцидента
Результат
свыше 80% инцидентов производительности имеют приоритет 4
Количество SQL запросов по инцидентам
Ось X - queryid запроса. Ось Y - количество инцидентов в которых участвовал запрос.
Ось X - queryid запроса. Ось Y - количество инцидентов в которых участвовал запрос
SQL запросы участвующие в более 80% инцидентов
Ось X - queryid запроса. Ось Y - количество инцидентов в которых участвовал запрос.
Количество SQL запросов участвующих во всех инцидентах = 5
Количество SQL запросов участвующих в 80% инцидентов = 29
Ожидания СУБД
wait_event_type
Ось X - тип ожидания СУБД . Ось Y - количество ожиданий
wait_event
Ось X - событие ожидания. Ось Y - количество событий ожиданий
События ожидания составляющие 80% от общего числа ожиданий.
SQL запросы для оптимизации
Список SQL запросов участувующих в инцидентах
queryid = 1214551160677155501
План выполнения запроса
Статистика ожиданий по типу IO
События ожидания по типу IO
История выполнения и событий ожидания по типу IO для queryid = 1214551160677155501
Ось X - точка наблюдения. Ось Y - количество выполнений запроса.
Ось X - точка наблюдения. Ось Y - количество событий ожидания DSMFillZeroWrite
Статистика ожиданий по типу IPC
События ожидания по типу IPC
История выполнения и событий ожидания по типу IPC для queryid = 1214551160677155501
Ось X - точка наблюдения. Ось Y - количество выполнений запроса.
Ось X - точка наблюдения. Ось Y - количество событий ожидания BgWorkerShutdown
Ось X - точка наблюдения. Ось Y - количество событий ожидания ExecuteGather
Ось X - точка наблюдения. Ось Y - количество событий ожидания ParallelFinish
Результаты анализа по SQL queryid = 1214551160677155501
1. Событий ожидания типа IPC существенно больше чем событий по типу IO.
2. На основании результатов проведенных ранее экспериментов , принято решение добавить индекс в таблицу для решения проблемы большого количества ожиданий типа IPC.
3. После добавления индексов , провести анализ результатов .
G.3.11.2. Load distribution (Распределение нагрузки)
Этот раздел отчёта pgpro_pwr основан на представлении pgpro_stats_totals расширения pgpro_stats, если оно было доступно в течение отчётного интервала. Каждая таблица в данном разделе предоставляет данные за отчётный интервал о распределении нагрузки для определённого типа объектов, для которых собирается агрегированная статистика, например, баз данных, приложений, узлов или пользователей. Каждая таблица содержит по одной строке для каждого из ресурсов (таких, как общее время или общее число записанных разделяемых блоков), где распределение нагрузки показано на графике в виде линейчатой диаграммы с накоплением для объектов с наибольшей нагрузкой по этому ресурсу. Если область диаграммы, соответствующая объекту, слишком узка для включения заголовков, наведите указатель на эту область, чтобы получить подсказку с заголовком, значением и процентом. Таблицы «Load distribution among heavily loaded databases», «Load distribution among heavily loaded applications», «Load distribution among heavily loaded hosts» и «Load distribution among heavily loaded users» показывают распределение нагрузки для соответствующих объектов.
Наибольшую нагрузку создает DB-1
Статистика утилизации CPU
G.3.11.4.1. rusage statistics (Статистика использования ресурсов)
Этот раздел добавляется в отчёт, только если в отчётном интервале было доступно расширение pgpro_stats или pg_stat_kcache.
Таблица отчёта «Top SQL by system and user time» показывает запросы с наибольшей суммой значений полей user_time и system_time в представлении pg_stat_kcache или pgpro_stats_totals.
SQL запрос с наибольшим потреблением CPU
Наиболее длительные SQL
Таблица отчёта «Top SQL by execution time» показывает запросы с наибольшей длительностью выполнения, определяемой по значению поля total_time представления pgpro_stats_statements или pg_stat_statements.
SQL запрос с наибольшей длительностью выполнения
Причина инцидента и проблемный запрос
Проблемный запрос
Причина аномальной утилизации CPU и снижения операционной скорости СУБД является массовый вызов хранимой функции, требующей для выполнения высоких вычислительных ресурсов.
Вопрос : какой мониторинг выгоднее использовать для оптимизации производительности субд postgresql - реактивный или проактивный ?
Ответ YandexGPT
Нельзя однозначно сказать, какой мониторинг выгоднее для оптимизации производительности СУБД PostgreSQL — реактивный или проактивный. Выбор зависит от конкретных задач и требований к системе.
Для исследования проблем с производительностью PostgreSQL важен систематический мониторинг. Некоторые инструменты позволяют получать информацию в режиме реального времени. Например, htop, iostat и vmstat. 3
Также для мониторинга PostgreSQL можно использовать внутренний сбор статистики. Например, pg_stat_activity помогает определить активные запросы к базе и кто их выполняет, а pg_stat_statements отображает популярные запросы с наибольшим временем выполнения. 3
Ещё для мониторинга можно применять системы, такие как Percona. Она позволяет быстро оценить динамику нагрузки на сервер и показывает главные показатели работы сервера.
Вопрос: Какой мониторинг выгоднее использовать для оптимизации производительности СУБД PostgreSQL - реактивный или проактивный ?
Ответ DeepSeek
Для оптимизации производительности PostgreSQL **проактивный мониторинг** предпочтительнее, так как он позволяет предотвращать проблемы до их возникновения, минимизируя простои и улучшая стабильность системы. Однако оптимальный результат достигается при **комбинации обоих подходов**.
### Почему проактивный мониторинг выгоднее: 1. **Предупреждение проблем**: - Отслеживание метрик (CPU, память, I/O, индексы, блокировки) помогает выявить узкие места до их критического влияния. - Пример: мониторинг роста таблиц или замедления запросов через `pg_stat_statements` позволяет заранее оптимизировать индексы или переписать запросы.
2. **Снижение простоев**: - Анализ трендов (например, заполнение диска или увеличение времени ответа) позволяет планировать масштабирование и обновления.
3. **Экономия ресурсов**: - Проактивное устранение проблем дешевле, чем аварийное восстановление после сбоев.
4. **Инструменты**: - Prometheus + Grafana для сбора метрик. - Настройка алертов на аномалии (например, через Alertmanager). - Использование расширений PostgreSQL: `pg_stat_activity`, `pg_stat_bgwriter`, `pg_stat_statements`.
### Когда полезен реактивный мониторинг: - Для анализа уже возникших инцидентов (например, внезапные блокировки или деградация запросов). - Использование логов и инструментов вроде `pgBadger` для постфактум-анализа. - Оптимизация запросов, которые не были выявлены проактивно.
### Итог: - **Проактивный мониторинг** — основа для долгосрочной стабильности и оптимизации. - **Реактивный** — дополнение для работы с непредвиденными сценариями. - Для максимальной эффективности объедините оба подхода, но фокусируйтесь на проактивных методах.
1.Проанализировать инциденты снижения скорости СУБД
Ожидания и корреляция по инцидентам снижения скорости СУБД
Фрагмент таблицы инцидентов снижения скорости СУБД
Столбцы таблицы:
ID : идентификатор инцидента снижения скорости СУБД
START TIME : время начала инцидента
FINISH TIME : время окончания инцидента
№ : порядковый номер
IO : количество ожидания типа IO на время начала инцидента
IO CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями IO за отрезок [ START TIME - 1 ЧАС ; START TIME ]
LWLock : количество ожидания типа LWLock на время начала инцидента
LWLock CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями LWLock за отрезок [ START TIME - 1 ЧАС ; START TIME ]
Количество ожидания типа IO , LWLock по инцидентам
Ось X - ID инцидента. Ось Y - количество ожидания типа IO на начало инцидента
Ось X - ID инцидента. Ось Y - количество ожидания типа LWLock на начало инцидента
Ось X - ID инцидента. Ось Y - коэффициент корреляции между всеми ожиданиями и ожиданиями типа IO , LWLock на начало инцидента
Особенности инцидентов 34 , 36 :
Коэффициент корреляции между ожиданиями СУБД в целом и ожиданиями типа LWLock больше , чем между ожиданиями СУБД в целом и ожиданиями типа IO.
Количество ожидания типа LWLockменьше чем количество ожидания типа IO.
Графики операционной скорости и ожиданий по инцидентам снижения скорости
Инцидент 34
Ось X - точка наблюдения. Ось Y - значение операционной скорости.
Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.
Инцидент 36
Ось X - точка наблюдения. Ось Y - значение операционной скорости.
Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.
Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr
Инцидент 34
Инцидент 36
2. Установить причины снижения скорости СУБД
SQL запросы, имеющие наибольшую долю ожидания заданного типа
Инцидент 34
Ожидания типа IO
Статистика вызовов и ожидания по запросам имеющим ожидания типа IO
Ожидания типа LWLock
Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock
Столбцы таблицы:
QUERYID : queryid SQL выражения , из представления pgpro_stats.
PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.
CALLS : количество выполнений SQL выражения
WAITINGS : количество ожиданий
WAITINGS TO CALLS : количество ожиданий на одно выполнение
WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.
Результат:
Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.
Инцидент 36
Ожидания типа IO
Статистика вызовов и ожидания по запросам имеющим ожидания типа IO
Ожидания типа LWLock
Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock
Столбцы таблицы:
QUERYID : queryid SQL выражения , из представления pgpro_stats.
PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.
CALLS : количество выполнений SQL выражения
WAITINGS : количество ожиданий
WAITINGS TO CALLS : количество ожиданий на одно выполнение
WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.
Результат:
Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.
Главная причина снижения скорости СУБД
Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.
Текст запроса
Доступен в pgpro_pwr
План выполнения запроса
Доступен в pgpro_pwr
События ожидания при выполнении запроса 2092406791392746781
Инцидент 34
Статистика выполнения и событий ожидания по запросу 2092406791392746781
Столбцы таблицы:
timestamp : точка времени сбора статистических данных уровня SQL.
datname : База данных, в которой выполнялся SQL запрос.
rolname : Роль, под которой выполнялся SQL запрос.
CALLS : Количество выполнений запроса .
WAITINGS : Количество ожиданий типа IO , LWLock .
WAITINGS TO CALLS : количество ожиданий на одно выполнение.
WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .
SQL : текст SQL запроса (не приведен).
События ожидания возникающие при выполнении SQL запроса:
IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.
LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.
Инцидент 36
Статистика выполнения и событий ожидания по запросу 2092406791392746781
Cтолбцы таблицы:
timestamp : точка времени сбора статистических данных уровня SQL.
datname : База данных, в которой выполнялся SQL запрос.
rolname : Роль, под которой выполнялся SQL запрос.
CALLS : Количество выполнений запроса .
WAITINGS : Количество ожиданий типа IO , LWLock .
WAITINGS TO CALLS : количество ожиданий на одно выполнение.
WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .
SQL : текст SQL запроса (не приведен).
События ожидания возникающие при выполнении SQL запроса:
IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.
LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.
LWLock / BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.
LWLock / ProcArray : Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
3. Cписок мероприятий для устранения причин снижения скорости СУБД .
Мероприятия для снижения ожиданий DSMFillZeroWrite
События ожидания DSMFillZeroWrite в PostgreSQL связаны с операциями записи в разделяемую память (shared memory), где необходимо заполнить область нулями перед использованием.
Это может происходить при работе с большими объектами или при высокой нагрузке на систему.
Чтобы снизить количество таких событий, можно рассмотреть следующие шаги:
1. Оптимизация запросов и транзакций:
- Убедитесь, что ваши запросы оптимизированы и не выполняют избыточных операций.
- Сократите длительность транзакций, чтобы уменьшить нагрузку на разделяемую память.
2. Настройка параметров конфигурации:
- Увеличьте размер разделяемой памяти (shared_buffers), чтобы уменьшить частоту операций записи в разделяемую память.
- Настройте параметры, связанные с кэшированием и буферизацией, чтобы уменьшить количество операций записи.
3. Оптимизация использования разделяемой памяти:
- Убедитесь, что ваши приложения и расширения эффективно используют разделяемую память.
- Избегайте создания большого количества временных объектов, которые могут приводить к увеличению операций записи в разделяемую память.
Мероприятия для снижения ожиданий ParallelHashJoin
Ожидания ParallelHashJoin могут возникать из-за того, что PostgreSQL использует параллельные запросы для выполнения операций, таких как Hash Join. Это может привести к увеличению количества ожиданий, особенно если у вас много одновременных запросов или ограниченные ресурсы.
1. Отключить параллельные запросы:
- Вы можете отключить параллельные запросы, установив параметр max_parallel_workers_per_gather в 0. Это отключит использование параллельных рабочих процессов для операций, таких как Hash Join.
2. Оптимизировать индексы:
- Убедитесь, что у вас есть правильные индексы на таблицах, участвующих в запросе. Индексы могут помочь ускорить выполнение запросов и уменьшить необходимость в параллельных операциях.
3. Анализ и вакуумизация таблиц:
- Периодически выполняйте команды ANALYZE и VACUUM для обновления статистики и очистки мертвых строк. Это поможет оптимизатору запросов выбрать более эффективные планы выполнения.
4. Настройка параметров планировщика:
- Настройте параметры, такие как random_page_cost и cpu_tuple_cost, чтобы повлиять на выбор плана выполнения запроса. Например, уменьшение random_page_cost может сделать индексные сканирования более привлекательными.
5. Использование правильных операторов JOIN:
- Попробуйте использовать другие типы соединений, такие как Nested Loop или Merge Join, если они подходят для вашего запроса. Вы можете временно отключить Hash Join, установив параметр enable_hashjoin в off.
6. Оптимизация запросов:
- Проверьте, можно ли оптимизировать сами запросы, например, добавив дополнительные условия в WHERE-clause или используя более эффективные подзапросы.
Примеры команд для настройки параметров:
-- Отключить параллельные запросы
SET max_parallel_workers_per_gather = 0;
-- Отключить Hash Join
SET enable_hashjoin = off;
-- Установить параметры планировщика
SET random_page_cost = 1.1;
SET cpu_tuple_cost = 0.01;
Мероприятия для снижения ожиданий BufferMapping
Ожидания на BufferMapping в PostgreSQL могут возникать из-за интенсивных операций чтения, когда база данных часто обращается к данным на диске вместо кэша. Это может происходить, когда рабочий набор данных превышает доступную память, что приводит к частым операциям ввода-вывода (I/O).
1. Увеличение shared_buffers:
- Увеличение параметра shared_buffers может помочь уменьшить количество операций ввода-вывода, так как больше данных будет храниться в памяти.
2. Оптимизация запросов:
- Проверьте и оптимизируйте ваши запросы, чтобы уменьшить количество операций ввода-вывода. Используйте индексы и другие методы оптимизации для уменьшения количества данных, которые нужно считывать из диска.
3. Увеличение effective_cache_size:
- Параметр effective_cache_size помогает PostgreSQL лучше оценивать доступную память для кэширования данных. Увеличение этого параметра может улучшить планирование запросов.
4. Увеличение work_mem и maintenance_work_mem:
- Увеличение параметров work_mem и maintenance_work_mem может помочь уменьшить количество операций ввода-вывода, особенно при выполнении операций сортировки и хранения данных.
5. Анализ и оптимизация индексов:
- Убедитесь, что у вас есть правильные индексы для ваших запросов. Индексы могут значительно уменьшить количество операций ввода-вывода.
6. Обновление аппаратного обеспечения:
- Если возможно, обновите аппаратное обеспечение, особенно увеличьте объем оперативной памяти и используйте более быстрые диски (например, SSD).
7. Распределение нагрузки:
- Анализируйте и оптимизируйте распределение нагрузки между сессиями, чтобы уменьшить конкуренцию за ресурсы.
Мероприятия для снижения ожиданий ProcArrayLock
Задержки, связанные с блокировкой ProcArrayLock, могут возникать из-за интенсивной активности рабочих процессов, которые создают конкуренцию за доступ к ProcArray. Это особенно актуально при выполнении параллельных запросов и операций, таких как walsender.
Для уменьшения задержек ProcArray можно рассмотреть следующие шаги:
1. Оптимизация рабочих процессов:
- Уменьшите количество одновременно выполняемых рабочих процессов, чтобы снизить нагрузку на ProcArrayLock.
- Оптимизируйте параллельные запросы, чтобы уменьшить их длительность и уменьшить время блокировки.
2. Настройка параметров конфигурации:
- Уменьшите значение параметра max_standby_streaming_delay, чтобы уменьшить задержку репликации.
- Настройте параметры, связанные с параллелизмом, такие как max_parallel_workers_per_gather и max_worker_processes, чтобы управлять количеством рабочих процессов.
3. Оптимизация хранения данных:
- Убедитесь, что у вас используется оптимальное хранилище данных, например, AWS EBS GP3, для уменьшения задержек ввода-вывода.
4. Итог
Использование корреляционного анализа ожиданий с помощью оперативно-тактического комплекса pg_hazel позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.
