Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример)
Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).
Если правильно и вовремя обслуживать - СУБД будет летать !
Постановка задачи
Проанализировать инциденты снижения скорости СУБД.
Установить причины снижения скорости СУБД.
Сформировать список мероприятий для устранения причин снижения скорости СУБД.
Итог.
Шаблон решения задачи
Отчетность для анализа
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 позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.
