Начало

https://dzen.ru/a/Z_ZU4LQXlnxVJEBt

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

Корреляционный анализ ожиданий при проведении тестового нагрузочного тестирование по сценарию "HeavyWeight" - повышенная утилизация CPU.

Уточнить характерные признаки возникающие при нехватке вычислительной мощности CPU , при минимальном использовании IO.

Реализация теста

Виртуальная машина

cat /proc/cpuinfoprocessor  : 0model name  : Intel Xeon Processor (Skylake, IBRS, no TSX)cpu MHz  : 2693.670processor  : 1model name  : Intel Xeon Processor (Skylake, IBRS, no TSX)cpu MHz  : 2693.670

Тестовый запрос

FOR counter IN 1..10000LOOPSELECTgcd( ((random()*100.0) * (random()*100.0))::numeric , ((random()*100.0) * (random()*100.0))::numeric ) *lcm( ((random()*100.0) * (random()*100.0))::numeric , ((random()*100.0) * (random()*100.0))::numeric )*power(gcd( ((random()*100.0) * (random()*100.0))::numeric , ((random()*100.0) * (random()*100.0))::numeric ) ,lcm( ((random()*100.0) * (random()*100.0))::numeric , ((random()*100.0) * (random()*100.0))::numeric ))END LOOP;

План выполнения тестового запроса

explain ( analyze , buffers , summary ) select custom_test4();QUERY PLAN----------------------------------------------------------------------------------------Result  (cost=0.00..0.26 rows=1 width=4) (actual time=459.522..459.522 rows=1 loops=1)Buffers: shared hit=160Planning Time: 0.066 msExecution Time: 461.071 ms(4 rows)

Нагрузка на чтение с диска - отсутствует.

Операционная скорость и ожидания СУБД в ходе выполнения теста

Ожидания СУБД

Коэффициенты корреляции

Сильная корреляция между ожиданиями и ожиданиями типа LWLock свидетельствует о том, что все ожидания вызваны типом LWLock.

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

События ожиданий (wait_event) по тестовому запросу

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

• timestamp : точка времени сбора статистических данных уровня SQL.

• datname : База данных, в которой выполнялся SQL запрос.

• rolname : Роль, под которой выполнялся SQL запрос.

• CALLS : Количество выполнений запроса .

• WAITINGS : Количество ожиданий типа IO , LWLock .

• WAITINGS TO CALLS : количество ожиданий на одно выполнение.

• WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

• SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

• ProcArray : Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

• LockManager : Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

Итог

Характерным признаком недостатка вычислительных ресурсов CPU является корреляция между снижением операционной скорости и ростом ожиданий ProcArray .

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

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

Корреляционный анализ ожиданий при проведении тестового нагрузочного тестирование по сценарию "HeavyWeight" - повышенная утилизация CPU.

Уточнить характерные признаки недостаточного размера shared_buffer при повышенной утилизации CPU и большом объеме чтений данных .

Реализация теста

Виртуальная машина

cat /proc/cpuinfo

processor  : 0

model name  : Intel Xeon Processor (Skylake, IBRS, no TSX)

cpu MHz  : 2693.670

processor  : 1

model name  : Intel Xeon Processor (Skylake, IBRS, no TSX)

cpu MHz  : 2693.670

Тестовый запрос

SELECTSUM(gcd( abalance::numeric , ((random()*100.0) * (random()*100.0))::numeric ) *lcm( abalance::numeric , ((random()*100.0) * (random()*100.0))::numeric )*power(gcd( abalance::numeric , ((random()*100.0) * (random()*100.0))::numeric ) ,lcm( abalance::numeric , ((random()*100.0) * (random()*100.0))::numeric )))INTO current_resultFROM pgbench_accounts  ;

План выполнения тестового запроса

QUERY PLAN---------------------------------------------------------------------------------------------Aggregate  (cost=232786.90..232786.91 rows=1 width=32) (actual time=14435.176..14435.177 rows=1 loops=1)

Buffers: shared hit=16235 read=16552

I/O Timings: shared read=973.889

->  Seq Scan on pgbench_accounts  (cost=0.00..52786.99 rows=1999999 width=4) (actual time=0.039..3764.889 rows=2000000 loops=1)

Buffers: shared hit=16235 read=16552

I/O Timings: shared read=973.889

Planning:Buffers: shared hit=63

Planning Time: 15.128 ms

Execution Time: 14435.289 ms

Нагрузка в ходе выполнения теста

Длительность итерации = 10 минут.

Рост нагрузки - экспоненциально.

Операционная скорость и время выполнения тестового запроса

Тест был принудительно завершен после потери работоспособности СУБД после выполнении итерации №16.

Операционная скорость и ожидания СУБД в ходе выполнения теста

Время выполнения с итерации №6 до итерации №16.

Периодичность сбора данных = 1 минута.

Операционная скорость

Ожидания СУБД

Линия регрессии операционной скорости и ожиданий СУБД

Коэффициенты корреляции

Ожидания типа IO, IPC , LWLock

События ожиданий (wait_event) по тестовому запросу

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

• timestamp : точка времени сбора статистических данных уровня SQL.

• datname : База данных, в которой выполнялся SQL запрос.

• rolname : Роль, под которой выполнялся SQL запрос.

• CALLS : Количество выполнений запроса .

• WAITINGS : Количество ожиданий типа IO , LWLock .

• WAITINGS TO CALLS : количество ожиданий на одно выполнение.

• WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

• SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

• DataFileRead : Ожидание чтения из файла данных отношения.

• BufferIO : Ожидание завершения буферного ввода/вывода.

• BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.

Итог

Характерным признаком недостаточного размера общей области памяти shared_buffers является корреляция между снижением операционной скорости и ростом ожиданий BufferMapping/BufferIO.

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

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