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

Начало :

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск потенциально проблемных SQL запросов при продуктивной нагрузке .

Продолжение тестирования методологии использования корреляционного анализа для поиска проблемных SQL запросов при продуктивной нагрузке на СУБД .

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

Проанализировать причины снижения скорости СУБД и найти проблемные запросы:

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Метрика относительной доли ожиданий - исключена из анализа.

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

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

Ожидания на уровне кластера

Корреляционный анализ ожиданий и определение потенциально проблемных SQL запросов

1. Сильная отрицательная корреляция между скоростью и ожиданиями .

2. Наиболее сильная положительная корреляция между всеми ожиданиями и ожиданиями типа IPC.

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

Корреляционный анализ на уровне запросов SQL по ожиданию типа IPC

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

• QUERYID : id SQL запроса

• PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

• CORRELATION : коэффициент корреляции между ожиданиями типа IPC по всем SQL запросам и ожиданиям типа IPC по конкретному запросу.

• CALLS : общее количество выполнений запроса за анализируемый период.

• WAITINGS : Ожидания типа IPC по конкретному запросу.

• WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

• WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IPC для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 3985919093425059746

События ожидания:

• BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

• ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

• ExecuteGather Ожидание активности дочернего процесса при выполнении узла плана Gather.

Корреляционный анализ на уровне запросов SQL по ожиданию типа LWLock

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

• QUERYID : id SQL запроса

• PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

• CORRELATION : коэффициент корреляции между ожиданиями типа LWLock по всем SQL запросам и ожиданиям типа LWLock по конкретному запросу.

• CALLS : общее количество выполнений запроса за анализируемый период.

• WAITINGS : Ожидания типа LWLock по конкретному запросу.

• WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

• WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа LWLock для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 2092406791392746781

События ожидания:

• ParallelHashJoin Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Корреляционный анализ на уровне запросов SQL по ожиданию типа IO

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

• QUERYID : id SQL запроса

• PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

• CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

• CALLS : общее количество выполнений запроса за анализируемый период.

• WAITINGS : Ожидания типа IO по конкретному запросу.

• WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

• WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IO для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 5680299967307342186

События ожидания:

• DataFileExtend Ожидание расширения файла данных отношения.

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

Итог

Корреляционный анализ ожиданий СУБД может быть использован для поиска проблемных SQL запросов и первоначального анализа проблемы и путей оптимизации SQL запросов.

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

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

Проанализировать причины снижения скорости СУБД и найти проблемные запросы

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Порядок проведения корреляционного анализа

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

Ожидания на уровне кластера

Корреляционный анализ на уровне кластера

1. Средняя отрицательная корреляция между операционной скоростью и ожиданиями.

2. Тип ожидания имеющий наибольшую корреляция с общим количеством ожиданий - IO.

Статистика выполнений и ожиданий по отдельным SQL запросам по типу ожидания IO

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

• QUERYID : id SQL запроса

• CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

• CALLS : общее количество выполнений запроса за анализируемый период.

• WAITINGS : Ожидания типа IO по конкретному запросу.

• WAITINGS TO CALL : Отношение количество ожиданий к количество выполнений. Среднее количество ожидания за одно выполнение.

Таблица отсортирована по столбцам QUERYID / WAITINGS TO CALL .

Статистика выполнений и ожиданий по выбранным SQL-запросам

-7843470278038126227

События ожидания:

• DataFilePrefetch: Ожидание асинхронной предвыборки из файла данных отношения.

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

• DataFileWrite : Ожидание записи в файл данных отношения.

• WALSync : Ожидание помещения файла WAL в надёжное хранилище.

• WALWrite : Ожидание записи в файл WAL.

Результат анализа по запросу -7843470278038126227:

Выполнение процедуры ANALYZE во время продуктивной нагрузки на СУБД приводит к ожиданиям. Необходимо пересмотреть порядок проведения регламентных работ на СУБД.

-8198400089192679786

События ожидания:

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

Результат анализа по запросу -8198400089192679786

Необходимо провести анализ плана выполнения с целью снижения операций дискового чтения.

Итог

Использование корреляционного анализа позволяет определить степень влияния потенциально проблемных SQL запросов на общую скорость и ожидания СУБД.

Нет, я не против тех, кто плавает медленно, но ты плыви по той дорожке, где твоя скорость сопоставима с другими пловцами! Нет же - там вообще не вариант, я же крутой, что мне с физкультурниками плавать...а начинаешь его обгонять, так он вместо того чтобы к разделителю дорожки прижаться, ускоряется, тебе мешает и сам не плывет... Почитайте уже правила и уважайте соседей. Или еще вариант - проплывет отрезок и стоит посередине, напротив тумбочки, отдыхает, мало того мешает другим разворачиваться, так еще и перед тобой стартует... ну ты же видишь, что подплывают быстро плавающие, ты же стоял все это время, постой еще 3 секунды, дай уплыть и плыви потом за ними спокойно, аж бесит!

Я на своих проектах учу людей - нужно работать так, чтобы с вами можно было комфортно взаимодействовать, не нужно "упираться рогом" и продавливать только то, что вам нужно, это, в конечном итоге, обернется против вас самих. И на тренировках ведь тоже самое, вот ты думаешь только о том, чтобы только тебе было хорошо, а в конечном итоге тебе мешают, ты мешаешь, столкновения, удары под водой, раздражение и тренировка превращается в ад!

Я бы вместо инструктажа по ТБ перед бассейном, их принципам равномерности потока из Бережливого производства учил и уважению к коллегам по цеху

Взято с основного технического канала Postgres DBA

Задача эксперимента

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

Характер нагрузки

Нагрузка создается пользовательским сценарием pgbench.

Сценарий "OLTP" - SELECT + UPDATE.

1)UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

2)SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

3)UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

4) UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

Рост количества подключений pgbench - экспоненциально от 6 до 111.

При проведении сравнительного эксперимента ,таблица pgbench_accounts создается без ограничения первичного ключа.

Версия СУБД и ресурсы ВМ

Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Operating System: Astra Linux

Kernel: Linux 6.1.90-1-generic

processor  : 0

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

• cpu MHz  : 2693.670

• cpu cores  : 1

processor  : 1

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

• cpu MHz  : 2693.670

• cpu cores  : 1

RAM: 1GB

Словарь терминов, используемых при анализе

https://dzen.ru/a/Z7h6qwE1CT3q-E73?share_to=link

Результаты нагрузочного тестирования аналогичного сценария с использованием индекса

https://dzen.ru/a/Z7gXkYKsy2syEMGG?share_to=link

Сравнительные результаты экспериментов

Статистические показатели операционной скорости.

По оси X - точка наблюдения. По оси Y - значение операционной скорости.

Сравнительный анализ операционной скорости

1. Без индекса скорость существенно ниже

2. График изменения скорости , без использования индекса, носит ступенчатый характер.

Ожидания

По оси X - точка наблюдения. По оси Y - количество ожиданий .

Сравнительный анализ ожиданий

1. Характер графика практически не изменился

2. Абсолютные значение количества ожиданий без использования индекса незначительно ниже.

WAITING RATIO

Относительная доля(%), времени ожиданий от времени работы базы данных.

По оси X - точка наблюдения. По оси Y - относительная доля ожиданий .

Сравнительный анализ относительной доли ожиданий

1. Характер графика практически не изменился

WAIT_EVENT_TYPE (Типы ожиданий)

Минимальные и максимальные количества ожиданий при использовании индекса

Минимальные и максимальные количества ожиданий без использования индекса

Анализ WAIT_EVENT_TYPE

1. Характер ожидания , при проведении нагрузочного тестирования без использования индексов принципиально изменился.

2. К ожиданиям тяжеловесных и легковесных блокировок добавились ожидания подсистемы ввода вывода и ожидания взаимодействия с серверными процессами.

Ожидания Lock

По оси X - точка наблюдения. По оси Y - количество ожиданий типа Lock.

Ожидания LWLock

По оси X - точка наблюдения. По оси Y - количество ожиданий типа LWLock.

Сравнительный анализ ожиданий Lock , LWLock

1. Характер графиков и количества ожиданий практически не изменились и не зависят от отсутствия индекса.

Ожидания IO

По оси X - точка наблюдения. По оси Y - количество ожиданий типа IO.

Ожидания IPC

По оси X - точка наблюдения. По оси Y - количество ожиданий типа IPC.

Сравнительный общий корреляционный анализ ожиданий

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

https://dzen.ru/a/Z6zzzMzWGmYuR-bG?share_to=link

Корреляция операционной скорости и ожиданий и событий ожидания при использовании индекса

Корреляция операционной скорости и ожиданий и событий ожидания без использовании индекса

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

• SPEED CORR: коэффициент корреляции между количеством активных сессий к БД и операционной скоростью.

• BUFFERPIN CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Bufferpin.

• EXTENSION CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Extension.

• IO CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IO.

• IPC CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IPC.

• LOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Lock.

• LWLOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа LWLock.

Сравнительный анализ корреляций

1. Коэффициент корреляции с событиями ожидания тяжеловесных и легковесных блокировок практически не изменился.

2. При проведении нагрузочного тестирования без использования индексов, возникает сильная отрицательная корреляция с ожиданиями взаимодействия с другим процессом и слабая отрицательная корреляция с ожиданиями подсистемы ввода-вывода.

Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event) - при использовании индекса

Средняя и очень сильная корреляция с событиями ожидания:

1. Lock / transactionid: Ожидание завершения транзакции.

2. Lock / tuple: Ожидание при запросе блокировки для кортежа.

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

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

5. LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.

6. LWLock / WALWrite: Ожидание при записи буферов WAL на диск.

Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event) - без использования индекса

От слабой до сильной корреляции с событиями ожидания:

1. IO / RelationMapSync Ожидание помещения файла отображений отношений в надёжное хранилище.

2. IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

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

4. IO / DataFileImmediateSync Ожидание немедленной синхронизации файла данных отношения с надёжным хранилищем.

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

6. IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

7. IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

8. Lock / transactionid: Ожидание завершения транзакции.

9. Lock / tuple: Ожидание при запросе блокировки для кортежа.

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

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

12. LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.

13. LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.

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

Корреляция между событием ожидания(wait_event) и SQL запросами

При использовании индекса

Без использования индекса

Пользовательский запрос и события ожидания оказывающий наибольшее влияние на снижение производительности БД.

С использованием индекса

select custom_test( $1 )

События ожидания, оказывающие наибольшее влияние на снижение производительности БД

1. Lock / transactionid: Ожидание завершения транзакции.

2. Lock / tuple: Ожидание при запросе блокировки для кортежа.

Без использования индекса

select custom_test( $1 )

События ожидания, оказывающие наибольшее влияние на снижение производительности БД

1. IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

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

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

4. IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

5. IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

6. Lock / transactionid: Ожидание завершения транзакции.

7. Lock / tuple: Ожидание при запросе блокировки для кортежа.

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

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

10. LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.

11. LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.

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

Итог и практическое применение результатов корреляционного анализа

1. Результаты корреляционного анализа нагрузочного тестирования с использованием индексов являются нормальной картиной штатной работы СУБД в условиях массовых обновлений данных .

2. Корреляция с ожиданиями IO и IPC - с высокой долей уверенности , свидетельствует о неоптимальном плане выполнения SQL запроса.

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

Я в железе не разбираюсь. Может тут подскажут в какое значение выставить режим производительности. Или не трогать и оставить как есть.

На верхнем скрине по умолчанию так сказать стоит CURRENT. А частоты низкие.