Корреляция + Тестирование

Взято с основного технического канала 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 запроса.

математическая статистика в целом не подходит для общего анализа и сравнения производительности СУБД.

Эпиграф

Чем же может оказаться полезной математическая статистика или комментарий к комментарию.

Для сглаживания данных используется медианное сглаживание:

• Долгая скользящая: 1 час(красная линия).

• Короткая скользящая: 10 минут(синяя линия).

• Активные соединения и утилизация CPU: стандартные метрики Zabbix.

Как видно из графика - имеет место деградация производительности СУБД:

1. Количество активных сессий растет, но производительность падает

2. Утилизация CPU растет , но производительность падает

Ситуация, принципиально отличается от описанной в казалось бы похожих кейсах:

1. CPU Utilization = 100%. Это проблема СУБД?

2. CPU Utilization = 100%. Это проблема СУБД?

Поэтому и решаться данный инцидент будет по другому.

Использование статистического анализа

1.Выделение трендов на графике производительности

Выполняется тривиально, дополнительных инструментов не требуется.

• 13:00 - 13:28 : Горизонтальный тренд - высокая производительность

• 13:28 - 13:47 : Деградация производительности

• 13:57 - 14:05 : Горизонтальный тренд - низкая производительность. Нагрузка на СУБД уменьшилась.

13:00 - 13:28 : Горизонтальный тренд - высокая производительность

Статистические показатели производительности СУБД

Прямая корреляция между количество активных сессий и производительностью СУБД . Или другими словами - чем выше нагрузка на СУБД , тем выше производительность.

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

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

13:28 - 13:47 : Деградация производительности

Статистические показатели производительности СУБД

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

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

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

2.Определение наиболее значимой причины деградации производительности СУБД

Из Рис.4 видно, что наибольшая обратная корреляция между событиями ожидания и снижением производительности СУБД имеется для события LWLock / BufferMapping

Как видно - количество ожиданий менее чем за 20 минут - весьма существенно.

Итак, первый результат

Первой( но конечно не единственной) причиной деградации производительности СУБД в период 13:28 - 13:47 является - большое количество ожиданий LWLock / BufferMapping при выполнении пользовательских запросов.

Чуть подробнее об ожидании BufferMapping

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

Postgres Pro Enterprise : Документация: 16: 27.2. Система накопительной статистики : Компания Postgres Professional

LWLock - buffer_mapping

This event occurs when a session is waiting to associate a data block with a buffer in the shared buffer pool.

Context

The shared buffer pool is an PostgreSQL memory area that holds all pages that are or were being used by processes. When a process needs a page, it reads the page into the shared buffer pool. The shared_buffers parameter sets the shared buffer size and reserves a memory area to store the table and index pages. If you change this parameter, make sure to restart the database. For more information, see Shared Buffer Area.

The buffer_mapping wait event occurs in the following scenarios:

• A process searches the buffer table for a page and acquires a shared buffer mapping lock.

• A process loads a page into the buffer pool and acquires an exclusive buffer mapping lock.

• A process removes a page from the pool and acquires an exclusive buffer mapping lock.

LWLock - buffer_mapping | Redrock Postgres Documentation (rockdata.net)

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

Далее, дело техники, используя утилиту pgpro_pwr по queryid, находим проблемный запрос за период 13:30 - 13:50(снимки pgpro_pwr формируются каждые 10 минут).

Запрос передается разработчикам , для анализа .

Дальнейшие события ожидания анализируются схожим образом. Если отсортировать таблицу Рис.4. по количеству пользовательских запросов(более 100) , то можно и нужно сформировать список проблемных запросов для передачи группе разработки на оптимизацию и доработку.

Итог

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

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

P.S.

В настоящее время ведутся работы по разработке и тестированию новой версии инструментария по мониторингу и анализу производительности СУБД PostgreSQL - "Орешник".

Методология статистического анализа производительности СУБД PostgreSQL будет довольно существенно дополнена и доработана.