Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример)⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Постановка задачи

1. Проанализировать инциденты снижения скорости СУБД.

2. Установить причины снижения скорости СУБД.

3. Сформировать список мероприятий для устранения причин снижения скорости СУБД.

4. Итог.

Шаблон решения задачи

https://dzen.ru/a/Z-4mhu9oFCnB-jp9

Отчетность для анализа

https://dzen.ru/a/Z-6YLjZRni5hLvtD

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 по инцидентам

Особенности инцидентов 34 , 36 :

1. Коэффициент корреляции между ожиданиями СУБД в целом и ожиданиями типа LWLock больше , чем между ожиданиями СУБД в целом и ожиданиями типа IO.

2. Количество ожидания типа LWLock меньше чем количество ожидания типа IO.

Графики операционной скорости и ожиданий по инцидентам снижения скорости

Инцидент 34

Инцидент 36

Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr

Инцидент 34

Инцидент 36

2. Установить причины снижения скорости СУБД

SQL запросы, имеющие наибольшую долю ожидания заданного типа

Инцидент 34

Ожидания типа IO

Ожидания типа 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

Ожидания типа 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

Столбцы таблицы:

• 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

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 позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.