PG_HAZEL : Влияние изменения параметра slru_buffers_scale_size на скорость и ожидания СУБД.

Любой результат эксперимента это повод для дальнейших исследований.

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

1. Продолжение тестирования PG_HAZEL на стандартных сценариях нагрузочного тестирования.

Начало - сценарий "Select-only"

Продолжение - сценарий "Select-Update"

Текущий эксперимент - Сценарий "Insert-Only"

2.Проверка рекомендаций ChatPPG

Чтобы уменьшить количество ожиданий SLRIWrite в PostgreSQL, можно увеличить размер буферов для конкретных SLRU (Shared Local Recovery Unit)

...

Чтобы уменьшить количество ожиданий MultixactMemberSLRU в PostgreSQL, можно предпринять несколько шагов:

-Увеличение размера буферов SLRU:

...

Ожидания MultixactOffsetSLRU в PostgreSQL могут возникать из-за интенсивного использования многократных транзакций (multixacts), которые используются для отслеживания совместного владения строками несколькими транзакциями. Для уменьшения количества таких ожиданий можно предпринять следующие шаги:

-Увеличение размера буферов SLRU

Цитаты из ответов ChatPPG.

Версия СУБД

Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu

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

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

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

SELECT MAX(aid) INTO current_aid FROM pgbench_accounts ;

SELECT MAX(tid) INTO current_tid FROM pgbench_tellers ;

SELECT MAX(bid) INTO current_bid FROM pgbench_branches ;

FOR counter IN 1..1000

LOOP

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)

VALUES (  current_tid , current_bid , current_aid , 10 , CURRENT_TIMESTAMP );

END LOOP;

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

Эксперимент-1 : slru_buffers_scale_size = 2

Эксперимент-2 : slru_buffers_scale_size = 5

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

Ось X - номер итерации. Ось Y - значение операционной скорости.

Ось X - номер итерации. Ось Y - значение медианного времени выполнения тестового SQL запроса.

Ось X - номер итерации. Ось Y - относительная разница операционной скорости в эксперименте-1 и эксперименте-2

Ось X - номер итерации. Ось Y - относительная разница медианного времени тестового запроса в эксперименте-1 и эксперименте-2

Результат

Скорость выполнения тестового запроса в Эксперименте 2 - менялась разнонаправлено от -1.5% до 1.5%.

Время выполнения тестового запроса в Эксперименте 2 - менялась разнонаправлено от -1% до 1%.

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

Эксперимент-1

Эксперимент-2

Результаты

1. Снижение скорости - снизилось с 133% до 81%.

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

Эксперимент-1

Эксперимент-2

Результаты

1. Количество SQL запросов, имеющих ожидания увеличилось: c 164 до 174

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

IO

Статистика выполнений и ожиданий по типу ожидания IO

Lock

Статистика выполнений и ожиданий по типу ожидания Lock

LWLock

Статистика выполнений и ожиданий по типу ожидания LWLock

Результаты

1. Отношение ожидания к количеству запросов по типу ожидания IO - уменьшилось на 8%
2. Отношение ожидания к количеству запросов по типу ожидания Lock - уменьшилось на 27%
3. Отношение ожидания к количеству запросов по типу ожидания LWLock - уменьшилось на 36%

Ожидания по тестовому SQL запросу

Табличные значения

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

Результаты

1. В эксперименте-2 события ожидания SLRUWrite - отсутствуют.
2. В эксперименте-2 события ожидания DataFileRead - отсутствуют.
3. В эксперименте-2 появились события XidGen.

Событие ожидания XidGen

• XidGen Ожидание при выделении нового идентификатора транзакции.

Рекомендация ChatPPG по снижению количества ожиданий XidGen

Ожидание XidGen в PostgreSQL возникает, когда процессу требуется уникальный идентификатор транзакции (XID), но он не может получить его немедленно из-за конкуренции с другими процессами. Это может произойти в следующих случаях:

1. Конкуренция за XID: Когда множество транзакций одновременно запрашивают новые XID, они могут столкнуться с блокировкой на уровне XidGenLock. Это LW-блокировка (Lightweight Lock) используется для синхронизации доступа к генератору XID.

2. Высокая нагрузка: При высокой нагрузке на базу данных, когда множество транзакций одновременно выполняются и требуют новые XID, может возникнуть ожидание XidGen.

3. Завершение транзакций: Когда транзакции завершаются, они освобождают свои XID, и другие транзакции могут их использовать. Однако, если процесс не может быстро получить новый XID, он может попасть в состояние ожидания XidGen.

Для минимизации ожидания XidGen можно рассмотреть следующие меры:

- Оптимизация транзакций: Уменьшите длительность транзакций и убедитесь, что они завершаются вовремя.

- Настройка autovacuum: Увеличьте количество автовакуумных процессов с помощью параметра autovacuum_max_workers, чтобы управлять транзакциями более эффективно.

- Настройка checkpoint_completion_target: Этот параметр управляет тем, как много времени должно быть потрачено на завершение точки контрольного снимка перед передачей управления другим процессам. Оптимизация этого параметра может помочь снизить конкуренцию за ресурсы.

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

В данной конфигурации , при заданном характере нагрузки :

1. Увеличение значения параметра slru_buffers_scale_size на 60% не привело к увеличению операционной скорости.
2. Увеличение значения параметра slru_buffers_scale_size на 60% не привело к уменьшению медианного времени.
3. Увеличение значения параметра slru_buffers_scale_size на 60% привело к исчезновению событий ожидания SLRUWrite и DataFileRead.
4. Увеличение события ожидания XidGen связано с увеличением операционной скорости СУБД и возникшей конкуренцией за XID.