Шаблон решения задачи
Отчетность для анализа
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.
Графики операционной скорости и ожиданий по инцидентам снижения скорости
Ось X - точка наблюдения. Ось Y - значение операционной скорости.
Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.
Ось X - точка наблюдения. Ось Y - значение операционной скорости.
Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.
Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.
Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr
2. Установить причины снижения скорости СУБД
SQL запросы, имеющие наибольшую долю ожидания заданного типа
Статистика вызовов и ожидания по запросам имеющим ожидания типа 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.
Статистика вызовов и ожидания по запросам имеющим ожидания типа 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.
Текст запроса
План выполнения запроса
События ожидания при выполнении запроса 2092406791392746781
Статистика выполнения и событий ожидания по запросу 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.
Статистика выполнения и событий ожидания по запросу 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.
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.
- Проверьте, можно ли оптимизировать сами запросы, например, добавив дополнительные условия в WHERE-clause или используя более эффективные подзапросы.
Примеры команд для настройки параметров:
-- Отключить параллельные запросы
SET max_parallel_workers_per_gather = 0;
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 может помочь уменьшить количество операций ввода-вывода, так как больше данных будет храниться в памяти.
- Проверьте и оптимизируйте ваши запросы, чтобы уменьшить количество операций ввода-вывода. Используйте индексы и другие методы оптимизации для уменьшения количества данных, которые нужно считывать из диска.
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 позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.
