Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов
Результат известен заранее. Но детали , могут быть интересны.
Задача эксперимента
Определить характерные события ожиданий СУБД вызванные отсутствием индекса в таблице.
Характер нагрузки
Нагрузка создается пользовательским сценарием 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
Словарь терминов, используемых при анализе
Результаты нагрузочного тестирования аналогичного сценария с использованием индекса
Сравнительные результаты экспериментов
Статистические показатели операционной скорости.
По оси X - точка наблюдения. По оси Y - значение операционной скорости.
График операционной скорости при использовании индекса.
График операционной скорости без использования индекса.
Сравнительный анализ операционной скорости
Без индекса скорость существенно ниже
График изменения скорости , без использования индекса, носит ступенчатый характер.
Ожидания
По оси X - точка наблюдения. По оси Y - количество ожиданий .
График количества ожиданий при использовании индекса
График количества ожиданий без использования индекса
Сравнительный анализ ожиданий
Характер графика практически не изменился
Абсолютные значение количества ожиданий без использования индекса незначительно ниже.
WAITING RATIO
Относительная доля(%), времени ожиданий от времени работы базы данных.
По оси X - точка наблюдения. По оси Y - относительная доля ожиданий .
График относительной доли ожиданий при использовании индекса.
График относительной доли ожиданий без использования индекса.
Сравнительный анализ относительной доли ожиданий
Характер графика практически не изменился
WAIT_EVENT_TYPE (Типы ожиданий)
Минимальные и максимальные количества ожиданий при использовании индекса
Минимальные и максимальные количества ожиданий без использования индекса
Анализ WAIT_EVENT_TYPE
Характер ожидания , при проведении нагрузочного тестирования без использования индексов принципиально изменился.
К ожиданиям тяжеловесных и легковесных блокировок добавились ожидания подсистемы ввода вывода и ожидания взаимодействия с серверными процессами.
Ожидания Lock
По оси X - точка наблюдения. По оси Y - количество ожиданий типа Lock.
График количество ожидания при использовании индекса.
График количество ожидания без использования индекса.
Ожидания LWLock
По оси X - точка наблюдения. По оси Y - количество ожиданий типа LWLock.
График количество ожидания при использовании индекса.
График количество ожидания без использования индекса.
Сравнительный анализ ожиданий Lock , LWLock
Характер графиков и количества ожиданий практически не изменились и не зависят от отсутствия индекса.
Ожидания IO
По оси X - точка наблюдения. По оси Y - количество ожиданий типа IO.
График количество ожидания без использования индекса.
Ожидания IPC
По оси X - точка наблюдения. По оси Y - количество ожиданий типа IPC.
График количество ожидания без использования индекса.
Сравнительный общий корреляционный анализ ожиданий
Основная гипотеза корреляционного анализа ожиданий СУБД
Корреляция операционной скорости и ожиданий и событий ожидания при использовании индекса
Корреляция операционной скорости и ожиданий и событий ожидания без использовании индекса
Коэффициенты корреляции
SPEED CORR: коэффициент корреляции между количеством активных сессий к БД и операционной скоростью.
BUFFERPIN CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Bufferpin.
EXTENSION CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Extension.
IO CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IO.
IPC CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IPC.
LOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Lock.
LWLOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа LWLock.
Сравнительный анализ корреляций
Коэффициент корреляции с событиями ожидания тяжеловесных и легковесных блокировок практически не изменился.
При проведении нагрузочного тестирования без использования индексов, возникает сильная отрицательная корреляция с ожиданиями взаимодействия с другим процессом и слабая отрицательная корреляция с ожиданиями подсистемы ввода-вывода.
Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event) - при использовании индекса
Средняя и очень сильная корреляция с событиями ожидания:
Lock / transactionid: Ожидание завершения транзакции.
Lock / tuple: Ожидание при запросе блокировки для кортежа.
LWLock / LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
LWLock / ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.
LWLock / WALWrite: Ожидание при записи буферов WAL на диск.
Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event) - без использования индекса
От слабой до сильной корреляции с событиями ожидания:
IO / RelationMapSync Ожидание помещения файла отображений отношений в надёжное хранилище.
IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.
IO / DataFileRead Ожидание чтения из файла данных отношения.
IO / DataFileImmediateSync Ожидание немедленной синхронизации файла данных отношения с надёжным хранилищем.
IPC / BufferIO Ожидание завершения буферного ввода/вывода.
IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.
IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.
Lock / transactionid: Ожидание завершения транзакции.
Lock / tuple: Ожидание при запросе блокировки для кортежа.
LWLock / BufferMapping Ожидание при связывании блока данных с буфером в пуле буферов.
LWLock / LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.
LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.
LWLock / ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
Корреляция между событием ожидания(wait_event) и SQL запросами
При использовании индекса
Без использования индекса
Пользовательский запрос и события ожидания оказывающий наибольшее влияние на снижение производительности БД.
С использованием индекса
select custom_test( $1 )
События ожидания, оказывающие наибольшее влияние на снижение производительности БД
Lock / transactionid: Ожидание завершения транзакции.
Lock / tuple: Ожидание при запросе блокировки для кортежа.
Без использования индекса
select custom_test( $1 )
События ожидания, оказывающие наибольшее влияние на снижение производительности БД
IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.
IO / DataFileRead Ожидание чтения из файла данных отношения.
IPC / BufferIO Ожидание завершения буферного ввода/вывода.
IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.
IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.
Lock / transactionid: Ожидание завершения транзакции.
Lock / tuple: Ожидание при запросе блокировки для кортежа.
LWLock / BufferMapping Ожидание при связывании блока данных с буфером в пуле буферов.
LWLock / LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.
LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.
LWLock / ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
Итог и практическое применение результатов корреляционного анализа
Результаты корреляционного анализа нагрузочного тестирования с использованием индексов являются нормальной картиной штатной работы СУБД в условиях массовых обновлений данных .
Корреляция с ожиданиями IO и IPC - с высокой долей уверенности , свидетельствует о неоптимальном плане выполнения SQL запроса.
