Влияние vacuum/analyze/bloat на производительность СУБД / Habr

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

Оценить степень влияния регулярного выполнения vacuum/analyze на производительность СУБД.
Оценить степень влияния распухания таблицы на производительность СУБД.

Реализация эксперимента

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

CPU = 1

vendor_id       : GenuineIntel
cpu family      : 6
model           : 85
model name      : Intel Xeon Processor (Skylake, IBRS, no TSX)

RAM

Mem:          718Mi

Редакция/версия СУБД

Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

Конфигурационные параметры СУБД

shared_preload_libraries = 'pg_stat_statements'
Остальные параметры: по умолчанию

Инициализация тестовых таблиц

Создать и заполнить таблицы данными

recreate.sql

DROP TABLE IF EXISTS  table_parent ;
CREATE TABLE table_parent
(
id SERIAL ,
name text ,
curr_value numeric
);

DROP TABLE IF EXISTS  table_child1  ;
CREATE TABLE table_child1
(
id SERIAL ,
parent_key integer ,
name text ,
curr_value numeric
);

DROP TABLE IF EXISTS table_child2 ;
CREATE TABLE table_child2
(
id integer ,
parent_key integer ,
name text ,
curr_value numeric
);


INSERT INTO table_parent ( name , curr_value )
SELECT i::text , i::numeric
FROM generate_series(1, 1000000) AS t(i) ;

INSERT INTO table_child1 ( parent_key , name , curr_value )
SELECT i::numeric , i::text , i::numeric
FROM generate_series(300, 900000) AS t(i) ;

INSERT INTO table_child2 ( id , parent_key , name , curr_value )
SELECT i::numeric , i::numeric , i::text , i::numeric
FROM generate_series(20000, 800000) AS t(i) ;

Индексировать и собрать статистику по таблицам

index_analyze.sql

CREATE INDEX table_parent_idx ON table_parent ( id );
CREATE INDEX table_child1_idx ON table_child1 ( parent_key );
CREATE INDEX table_child2_idx ON table_child2 ( parent_key );

ANALYZE table_parent ;
ANALYZE table_child1 ;
ANALYZE table_child2 ;

Итерация теста

generator3.sql

CREATE OR REPLACE FUNCTION generator3() RETURNS integer AS $$
DECLARE
result_rec record ;
BEGIN
SELECT child1.name , child2.name
INTO      result_rec
FROM   table_parent parent
      join table_child1 child1 ON ( parent.id = child1.parent_key )
      join table_child2 child2 ON ( parent.id = child2.parent_key )                     
WHERE
      ( parent.id > 500000 AND parent.id < 500100 )   and ( parent.id % 2 = 0 );

INSERT INTO table_child2 ( id , parent_key , name , curr_value )
SELECT i::numeric , i::numeric , i::text , i::numeric
FROM generate_series(900000, 910000) AS t(i) ;

UPDATE table_child2
SET name = name || 'test'
WHERE id between 900000 AND 910000 ;
               
DELETE FROM table_child2
WHERE id between 900000 AND 910000 ;

return 0 ;
END
$$ LANGUAGE plpgsql;

Тест

generator3.4.sh

#!/bin/sh
#generator3.4.sh
#version.3.4

script=$(readlink -f $0)
current_path=`dirname $script`


LOG_FILE=$current_path'/generator.log'

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : START '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : START '> $LOG_FILE

/opt/pgpro/ent-15/bin/psql -d test_db -c "select pg_stat_reset()" >> $LOG_FILE
if [ $? -ne 0 ]
then
  ##################################################################### 
  #ОШИБКА СУБД  
  echo 'ERROR : PostgreSQL ERROR : pg_stat_reset TERMINATED WITH ERROR '
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR ' >> $LOG_FILE
  exit 1000
  #####################################################################
fi


/opt/pgpro/ent-15/bin/psql -d test_db -c "select pg_stat_statements_reset()" >> $LOG_FILE
if [ $? -ne 0 ]
then
  ##################################################################### 
  #ОШИБКА СУБД  
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR '
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR ' >> $LOG_FILE
  exit 1000
  #####################################################################
fi


for ((i=1; i <= 1000; i++))
do
	echo 'PASS = '$i
	echo 'PASS = '$i>> $LOG_FILE
	
	/opt/pgpro/ent-15/bin/psql -d test_db -c "select generator3()" 
	if [ $? -ne 0 ]
	then
		##################################################################### 
		#ОШИБКА СУБД  
		echo 'ERROR : PostgreSQL ERROR : generator TERMINATED WITH ERROR '
		echo 'ERROR : PostgreSQL ERROR : generator TERMINATED WITH ERROR ' >> $LOG_FILE
		exit 1000
		#####################################################################
	fi
	

done

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '>> $LOG_FILE

exit 0

Оцениваемые результаты

Общее время выполнения (total_exec_time представления pg_stat_statements)
Количество запросов (calls представления pg_stat_statements)
Количество результирующих строк (rows представления pg_stat_statements)
Общее число прочитанных разделяемых блоков (shared_blks_read представления pg_stat_statements)
Общее число «загрязнённых» разделяемых блоков (shared_blk_dirtied представления pg_stat_statements)
Общее число записанных разделяемых блоков (shared_blk_written представления pg_stat_statements)
Количество транзакций (xact_commit представления pg_stat_database)
Метрика производительности СУБД (CPI)

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

Эталонный тест: vacuum + analyze после каждой итерации

Тест без выполнения vacuum + analyze после каждой итерации

Сравнение результатов с «Эталонный тест: vacuum + analyze после каждой итерации»

Время выполнения теста — незначительно уменьшилось
Объем обработанных разделяемых блоков — значительно увеличился
Производительность — существенно уменьшилась

Фрагментация 11%

Сравнение результатов с «Тест без выполнения vacuum + analyze после каждой итерации»

Время выполнения теста — существенно увеличилось
Объем обработанных разделяемых блоков — существенно увеличился
Производительность — существенно уменьшилась

Фрагментация 100%

Сравнение результатов с «Тест без выполнения vacuum + analyze после каждой итерации»

Время выполнения теста — существенно увеличилось
Объем обработанных разделяемых блоков — существенно увеличился
Производительность — существенно уменьшилась

Итоги

Выполнение vacuum+analyze после массовых изменений данных существенно увеличивает производительность СУБД, хотя общее время выполнения и несколько возрастает.

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

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

Мониторить и оптимизировать надо не фрагментацию БД в целом, а фрагментацию наиболее часто используемых таблиц.