PG_HAZEL : Влияние настроек автовакуума таблицы на производительность СУБД

Пылесос - вещь в хозяйстве нужная.

Задача

1. Определить качественное и количественное влияние настроек autovacuum отдельной большой таблица на производительность СУБД, при сценарии смешанной нагрузки (SELECT , SELECT + UPDATE для тестовой таблица).
2. Практически проверить рекомендации нейросети

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

• CPU = 2
• RAM = 2GB
• Postgres Pro (enterprise certified) 15
• Размер тестовой БД = 10GB
• Размер тестовой таблицы (pgbench_accounts) = 68 500 000 строк
• Минимальная нагрузка = 5
• Максимальная = 32

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

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

Настройки autovacuum для таблицы - отсутствуют.

Настройки по умолчанию:

autovacuum_vacuum_scale_factor= 0.2

autovacuum_vacuum_threshold = 50

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

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_cost_limit = 2000);

Эксперимент-3: 100K

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 100000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_cost_limit = 2000);

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

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_cost_limit = 2000);

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

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

Количество выполнения autovacuum:

• Эксперимент-1(DEF): 0 (после завершения теста остались мертвые строки)
• Эксперимент-2(1K): 238
• Эксперимент-3(100K): 23
• Эксперимент-4(1M): 7 (после завершения теста остались мертвые строки)

Разница в скорости, по сравнению с настройками по умолчанию:

Эксперимент-1(DEF) / Эксперимент-2(1K):

• max: -3,1621%
• average: -5,9828%

Эксперимент-1(DEF) / Эксперимент-3(100K):

• max: 5,3145%
• average: -2,7143%

Эксперимент-1(DEF) / Эксперимент-7(1M):

• max: 3,5635%
• average: -0,8469%

Разница в скорости при минимальной нагрузки (9 соединений):

Эксперимент-1(DEF) / Эксперимент-2(1K):

• max: -3,1621%
• average: -4,0757%

Эксперимент-1(DEF) / Эксперимент-3(100K):

• max: 5,3145%
• average: 2,2866%

Эксперимент-1(DEF) / Эксперимент-7(1M):

• max: 3,5594%
• average: 1,7877%

Итог

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

autovacuum_vacuum_scale_factor = 0

autovacuum_vacuum_threshold = 100000;

autovacuum_vacuum_cost_limit = 2000;

Эксперименты по тонкой настройке autovacuum будут продолжены