Влияние autovacuum, fillfactor и лишних индексов на производительность СУБД и эффективность автовакуума.

Задача

Сводные данные по сравнительному анализу влияния настроек autovacuum и хранения таблиц и наличие лишних индексов на производительность СУБД и эффективность работы autovacuum.

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

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

Тестовая таблица ~60 000 000 строк

Нагрузка при тестировании : 5-48 тестовых соединений

Ось X - итерация теста. Ось Y - количество одновременных сессий pgbench

Эксперимент-1 : базовые настройки autovacuum, fillfactor

fillfactor для тестовых таблицы , по умолчанию = 100

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

Изменение настроек autovacuum для тестовой таблицы pgbench_accounts:

autovacuum_vacuum_scale_factor = 0

autovacuum_vacuum_threshold = 1000

autovacuum_analyze_scale_factor = 0

autovacuum_analyze_threshold = 1000

autovacuum_vacuum_insert_scale_factor = 0

autovacuum_vacuum_insert_threshold = 1000

autovacuum_vacuum_cost_delay = 1

autovacuum_vacuum_cost_limit = 2000

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

Уменьшение значения параметра fillfactor для тестовых таблиц

ALTER TABLE pgbench_accounts SET (fillfactor = 50);

ALTER TABLE pgbench_tellers SET (fillfactor = 50);

ALTER TABLE pgbench_branches SET (fillfactor = 50);

VACUUM FULL pgbench_branches ;

VACUUM FULL pgbench_tellers ;

VACUUM FULL pgbench_accounts ;

Эксперимент-4 : fillfactor = 50 + лишние индексы

CREATE INDEX pgbench_accounts_idx_1 ON pgbench_accounts ( abalance );

CREATE INDEX pgbench_accounts_idx_2 ON pgbench_accounts ( filler);

CREATE INDEX pgbench_branches_idx_1 ON pgbench_branches (bbalance);

CREATE INDEX pgbench_branches_idx_2 ON pgbench_branches (filler);

CREATE INDEX pgbench_tellers_idx_1 ON pgbench_tellers (tbalance);

CREATE INDEX pgbench_tellers_idx_2 ON pgbench_tellers (filler);

Эксперимент-5 : fillfactor = 100 + лишние индексы

ALTER TABLE pgbench_accounts SET (fillfactor = 100);

ALTER TABLE pgbench_tellers SET (fillfactor = 100);

ALTER TABLE pgbench_branches SET (fillfactor = 100);

VACUUM FULL pgbench_branches ;

VACUUM FULL pgbench_tellers ;

VACUUM FULL pgbench_accounts ;

Влияние настройки autovacuum на операционную скорость

Ось X - точка наблюдения. Ось Y - операционная скорость в экспериментах 1/2/3

• SPEED-1: базовые настройки autovacuum+ fillfactor=100
• SPEED-2 : autovacuum tuning + fillfactor=100
• SPEED-3 : autovacuum tuning + fillfactor=50

Результаты:

• До нагрузки 10 соединение максимальная операционная скорость зафиксирована в эксперименте-2
• После нагрузки 10 соединение максимальная операционная скорость зафиксирована в эксперименте-1
• После нагрузки 18 соединений операционная скорость в эксперименте-3 превосходит операционную скорость в эксперименте-2

Влияние лишних индексов на операционную скорость

Ось X - точка наблюдения. Ось Y - операционная скорость в экспериментах 1/4/5

• SPEED-1: базовые настройки autovacuum+ fillfactor=100
• SPEED-4 : autovacuum tuning + fillfactor=50 + лишние индексы
• SPEED-5 : autovacuum tuning + fillfactor=100 + лишние индексы

Результаты:

• Лишние индексы оказывают существенное негативное влияние на производительность СУБД
• После нагрузки 10 соединений разница операционной скорости в экспериментах 4 и 5 существенно снизилась.

Подробнее о сравнительных экспериментах:

1.Влияние настроек autovacuum

Базовые настройки autovacuum+ fillfactor=100 vs autovacuum tuning + fillfactor=100

2. Влияние fillfactor

autovacuum tuning + fillfactor=100 vs autovacuum tuning + fillfactor=50

3. Влияние лишних индексов при fillfactor = 100

autovacuum tuning + fillfactor=100 vs autovacuum tuning + fillfactor=100 + лишние индексы

4. Влияние лишних индексов при fillfactor = 50

autovacuum tuning + fillfactor=50 vs autovacuum tuning + fillfactor=50 + лишние индексы

5. Влияние fillfactor и лишних индексов на производительность autovacuum

autovacuum tuning + fillfactor=50 + лишние индексы vs autovacuum tuning + fillfactor=100 + лишние индексы