PG_HAZEL : Влияние уменьшения параметра fillfactor на производительность СУБД PostgreSQL при сценарии "Update only"

Фрагментация в таблице - нужно ли бояться ?

Начало работ по теме

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

Оценить качественное и количественное влияние на производительность СУБД уменьшение значения параметра хранения таблицы fillfactor c 100(значение по умолчанию) до 50 при сценарии "Update only"(только операции изменения строк)

fillfactor (integer)

Фактор заполнения для таблицы, задаваемый в процентах, от 10 до 100. Значение по умолчанию — 100 (плотное заполнение). При меньшем факторе заполнения операции INSERT упаковывают данные в страницы только до заданного процента; оставшееся место резервируется для изменения строк на этой странице. В результате UPDATE получает шанс поместить изменённую копию строки в ту же страницу, что и исходную, что гораздо эффективнее, чем размещать её на другой странице, и увеличивает возможности для внесения изменений по схеме кортежи только в куче. Для таблиц, записи в которых никогда не меняются, лучшим выбором будет плотное заполнение, но для активно изменяемых таблиц лучше выбрать меньший фактор заполнения. Этот параметр нельзя задать для таблиц TOAST.

Конфигурация виртуальной машины

CPU = 2

RAM = 2GB

Версия СУБД:

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

Размер тестовой БД = 10GB

Тестовый сценарий

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

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

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

FOR counter IN 1..10

LOOP

current_delta = (ROUND( random ())::bigint)*10 + 1 ;

SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;

SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;

current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;

UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE  aid = current_aid ;

END LOOP ;

FOR counter IN 1..10

LOOP

SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;

SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;

current_tid = floor(random() * (max_i - min_i + 1)) + min_i ;

UPDATE pgbench_tellers SET tbalance = tbalance + current_delta WHERE tid = current_tid ;

END LOOP ;

FOR counter IN 1..10

LOOP

SELECT MIN(bid) INTO min_i FROM pgbench_branches ;

SELECT MAX(bid) INTO max_i FROM pgbench_branches ;

current_bid = floor(random() * (max_i - min_i + 1)) + min_i ;

UPDATE pgbench_branches SET bbalance = bbalance + current_delta WHERE bid = current_bid ;

END LOOP;

Эксперимент-1 : fillfactor = 100

Эксперимент-2 : fillfactor = 50

Результат эксперимента

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

Ось X - нагрузка на СУБД . Ось Y - Операционная скорость.

Для данной СУБД , при использовании сценария "Update only", уменьшение значения параметра fillfactor с 100 до 50 дает прирост производительности СУБД:

• При нагрузке от 5 до 115 соединений : от -2 до 17%
• При нормальной нагрузке(5-22 соединения): от 7 до 16%

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

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

Корреляционный анализ по типам ожиданий СУБД при нормальной нагрузке

Ожидания и корреляция при нормальной нагрузке

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

Корреляция и ожидания при fillfactor = 100

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

Корреляция и ожидания при fillfactor = 50

1. Коэффициент корреляции между операционной скоростью и ожиданиями СУБД кардинально уменьшился
2. Количество ожиданий типа LWLock существенно уменьшилось

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

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

Количество ожиданий типа IO при fillfactor = 50 уменьшилось до 8%.

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

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

Количество ожиданий типа IO при fillfactor = 50 уменьшилось до 30%

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

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

1. Количество событий ожидания DataFileWrite кардинально уменьшилось при значении fillfactor = 50.
2. Корреляция по событию ожидания SLRUWrite - отсутствует при значении fillfactor = 50.

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

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

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

1. Корреляция по характерным событиям ожидания изменилась при изменении значения параметра fillfactor c 100 до 50.
2. Наибольшее снижение события ожидания BufferContent, BufferMapping.
3. Количество и корреляция по событию ожидания CheckpointerComm - увеличилось.
4. Общее количество событий ожидания практически не изменилось.

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

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

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

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

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

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

ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса

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

Итог

Для данной СУБД , при нагрузочном сценарии "Update only":

1. Уменьшение значения параметра хранения таблицы fillfactor cо значения по умолчанию 100 на 50% позволяет получить прирост производительности СУБД до 16% при нормальной нагрузке
2. Основное снижение событий ожидания достигнуто по ожиданию DataFileWrite и составило более 80%
3. Снижение ожиданий BufferContent/BufferMapping составило 36% и 19% соответственно.

Значение fillfactor = 100 рекомендуется использовать только для таблиц без операция UPDATE.

Для таблиц с большим количество обновления , рекомендуется уменьшать значение параметра fillfactor.