Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД / forpes.ru

Главная
Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД

Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД +4

16.10.2024 11:05

rinace 13 426 Источник

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

В качестве начального примера, для отработки методики, выбраны настройки для процессов контрольной точки(checkpoint) и фоновой записи (bgwriter)

В качестве метода оптимизации использован метод покоординатного спуска.

Для получения значения производительности СУБД используется метрика Производительность СУБД — расчет метрики, временной анализ, параметрическая оптимизация / Хабр (habr.com)

Стартовые значения параметров

            name             | setting | unit
-----------------------------+---------+------
bgwriter_delay               | 10      | ms
bgwriter_flush_after         | 64      | 8kB
bgwriter_lru_maxpages        | 400     |
bgwriter_lru_multiplier      | 4       |
checkpoint_completion_target | 0.9     |
checkpoint_flush_after       | 32      | 8kB
checkpoint_timeout           | 900     | s
checkpoint_warning           | 60      | s
max_wal_size                 | 8192    | MB

Для упрощения, выбраны следующие параметры для оптимизации:

max_wal_size : Максимальный размер, до которого может вырастать WAL во время автоматических контрольных точек.
bgwriter_lru_maxpages: Задаёт максимальное число буферов, которое сможет записать процесс фоновой записи за раунд активности.
bgwriter_flush_after: Когда процессом фоновой записи записывается больше заданного объёма данных, сервер даёт указание ОС произвести запись этих данных в нижележащее хранилище. Это ограничивает объём «грязных» данных в страничном кеше ядра и уменьшает вероятность затормаживания при выполнении fsync в конце контрольной точки или когда ОС сбрасывает данные на диск большими порциями в фоне.

Тестовая нагрузка на СУБД

Стандартный инструмент создания нагрузки - pgbench

Параметры pgbench для одной итерации теста

--protocol=extended --report-per-command --jobs=24 --client=100 --transactions=10000 test_pgbench

Длительность теста: 30 минут.

Алгоритм оптимизации

Запуск теста
Отметить среднее статистическое(медиана) значение производительности СУБД за тестовый период.
Изменить значение параметра
Запуск теста
Отметить среднее статистическое(медиана) значение производительности СУБД за тестовый период.
Если значение производительности уменьшилось - вернуться к предыдущему значению параметра . Выбрать следующий параметр для оптимизации и перейти к шагу 3. В случае если перебраны все параметры для оптимизации - завершение.
Если значение производительности увеличилось - перейти к шагу 3.

Реализация

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

CPU

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

…

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

RAM

Mem:            188      
Swap:             4

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

Создание и инициализация тестовой БД

start_pg_bench8.4.create_db.sh

#!/bin/sh
# start_pg_bench8.4.create_db.sh
# version 8.4
# Создать тестовую БД
# Инициировать тестовую БД


#Обработать код возврата 
function exit_code {
ecode=$1
if [[ $ecode != 0 ]];
then
	ecode=$1
	LOG_FILE=$2
	ERR_FILE=$3
	
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE >> $LOG_FILE
	
    exit $ecode
fi
}

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

LOG_FILE=$current_path'/start_pg_bench8.2.create_db.log'
ERR_FILE=$current_path'/start_pg_bench8.2.create_db.err'
PROGRESS_FILE=$current_path'/start_pg_bench.progress'

timestamp_label=$(date "+%Y%m%d")'T'$(date "+%H%M%S")

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

   
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench CREATION IS STARTED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench CREATION IS STARTED ' >> $LOG_FILE
		
	  
psql -c "DROP DATABASE IF EXISTS test_pgbench" 2>>$ERR_FILE
exit_code $? $LOG_FILE $ERR_FILE

psql -c "CREATE DATABASE test_pgbench WITH OWNER = pgpropwr" 2>>$ERR_FILE
exit_code $? $LOG_FILE $ERR_FILE
			
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench HAS BEEN CREATED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench HAS BEEN CREATED ' >> $LOG_FILE

###################
# ПАРАМЕТРЫ ТЕСТОВОГО СЦЕНАРИЯ
let pgbench_clients=`cat $current_path'/pgbench_clients'`
let transactions=`cat $current_path'/transactions'`
###################

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

#########################################################################################################
#Параметры инициализации	
pgbench_init_param='--quiet --foreign-keys --scale='"$pgbench_clients"' -i test_pgbench'

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


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

pgbench --username=pgpropwr $pgbench_init_param >>$LOG_FILE 2>>$PROGRESS_FILE
exit_code $? $LOG_FILE $PROGRESS_FILE

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

exit 0

Нагрузочный тест pgbench

start_pg_bench8.sh

#!/bin/sh
# start_pg_bench8.sh
# version 8.4
# Тестовая БД создается отдельно
# Тестовая БД создается отдельно
# VACUUM ANALYZE после каждой итерации
# Настройки СУБД - отдельно
# Бесконечный цикл. Остановка вручную
# touch /postgres/scripts/pgbench/STOP_PGBENCH

#Обработать код возврата 
function exit_code {
ecode=$1
if [[ $ecode != 0 ]];
then
	ecode=$1
	LOG_FILE=$2
	ERR_FILE=$3
	
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE >> $LOG_FILE
	
    exit $ecode
fi
}

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

LOG_FILE=$current_path'/start_pg_bench.log'

timestamp_label=$(date "+%Y%m%d")'T'$(date "+%H%M%S")

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

###################
# ПАРАМЕТРЫ ТЕСТОВОГО СЦЕНАРИЯ
let pgbench_clients=`cat $current_path'/pgbench_clients'`
let transactions=`cat $current_path'/transactions'`
###################

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

#Удалить старый флаг
if [ -f /postgres/scripts/pgbench/STOP_PGBENCH ]; 
then
  rm /postgres/scripts/pgbench/STOP_PGBENCH
fi

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество клиентов: '$pgbench_clients
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество клиентов: '$pgbench_clients >> $LOG_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество транзакций: '$transactions
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество транзакций: '$transactions >> $LOG_FILE


#--jobs=потоки Число рабочих потоков в pgbench. Использовать нескольких потоков может быть полезно на многопроцессорных компьютерах
jobs=`cat /proc/cpuinfo|grep processor|wc -l`
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : jobs= '$jobs
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : jobs= '$jobs>> $LOG_FILE



ERR_FILE=$current_path'/start_pg_bench.err'
PROGRESS_FILE=$current_path'/start_pg_bench.progress'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") > $ERR_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") > $PROGRESS_FILE

pgbench_param='--protocol=extended --report-per-command --jobs='"$jobs"' --client='"$pgbench_clients"' --transactions='"$transactions"' test_pgbench'			
		
   
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_param= '$pgbench_param
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_param= '$pgbench_param>> $LOG_FILE
 

let counter=1
while [ 1 = 1 ]
do 
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : iteration '$counter' - STARTED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : iteration '$counter' - STARTED' >> $LOG_FILE

######################################################
if [ -f /postgres/scripts/pgbench/STOP_PGBENCH ]; 
then
  echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench has been stopped '
  echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench has been stopped '>> $LOG_FILE
  
  echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '
  echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '>> $LOG_FILE

  rm $ERR_FILE
  
  exit 0
fi

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench STARTED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench STARTED' >> $LOG_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench progress stored in file :'$PROGRESS_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench progress stored in file :'$PROGRESS_FILE  >> $LOG_FILE

pgbench --username=pgpropwr $pgbench_param >>$LOG_FILE 2>>$PROGRESS_FILE
exit_code $? $LOG_FILE $PROGRESS_FILE

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

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : iteration '$counter' - FINISHED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : iteration '$counter' - FINISHED' >> $LOG_FILE
let counter=$counter+1
######################################################

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

psql -d test_pgbench -c 'VACUUM ANALYZE' >>$LOG_FILE 2>>$PROGRESS_FILE
exit_code $? $LOG_FILE $PROGRESS_FILE

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


done

exit 0

Финальный тест pgbench

start_pg_bench10.sh

#!/bin/sh
# start_pg_bench10.sh
# version 10.0
# Входные параметры : время clients 

#Обработать код возврата 
function exit_code {
ecode=$1
if [[ $ecode != 0 ]];
then
	ecode=$1
	LOG_FILE=$2
	ERR_FILE=$3
	
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : ERROR : Details in '$ERR_FILE >> $LOG_FILE
	
    exit $ecode
fi
}

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

LOG_FILE=$current_path'/start_pg_bench10.log'

timestamp_label=$(date "+%Y%m%d")'T'$(date "+%H%M%S")

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

echo 'Время теста в секундах:'
read test_time

echo 'Количество клиентов:'
read clients

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Время теста в секундах: '$test_time
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Время теста в секундах: '$test_time >> $LOG_FILE

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество клиентов: '$clients
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Количество клиентов: '$clients >> $LOG_FILE


echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Проход: '$connect_count
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : Проход: '$connect_count >> $LOG_FILE

##################################################################################


#--jobs=потоки Число рабочих потоков в pgbench. Использовать нескольких потоков может быть полезно на многопроцессорных компьютерах
jobs=`cat /proc/cpuinfo|grep processor|wc -l`
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : jobs= '$jobs
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : jobs= '$jobs>> $LOG_FILE

let pgbench_clients=$clients
pg_bench_time=$test_time

##################

   #Параметры инициализации	
   pgbench_init_param='--no-vacuum --quiet --foreign-keys --scale='"$pgbench_clients"' -i test_pgbench10'
   ######################################################
   
		
		
	    ERR_FILE=$current_path'/start_pg_bench10.err'
        PROGRESS_FILE=$current_path'/start_pg_bench10.progress'
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") > $ERR_FILE
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") > $PROGRESS_FILE


		#первый проход без connect
		pgbench_param='--progress=60 --protocol=extended --report-per-command --jobs='"$jobs"' --client='"$pgbench_clients"' --time='"$pg_bench_time"' test_pgbench10'			
		
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_init_param= '$pgbench_init_param
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_init_param= '$pgbench_init_param>> $LOG_FILE
   
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_param= '$pgbench_param
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pgbench_param= '$pgbench_param>> $LOG_FILE
   
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 CREATION IS STARTED '
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 CREATION IS STARTED ' >> $LOG_FILE
		
	  
		psql -c 'select pg_reload_conf()'
		exit_code $? $LOG_FILE $ERR_FILE
	
		psql -c "DROP DATABASE IF EXISTS test_pgbench10" 2>>$ERR_FILE
		exit_code $? $LOG_FILE $ERR_FILE

		psql -c "CREATE DATABASE test_pgbench10 WITH OWNER = pgpropwr" 2>>$ERR_FILE
		exit_code $? $LOG_FILE $ERR_FILE
			
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 HAS BEEN CREATED '
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 HAS BEEN CREATED ' >> $LOG_FILE
   
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : INITIALIZATION OF pg_bench STARTED'
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : INITIALIZATION OF pg_bench STARTED' >> $LOG_FILE
		pgbench --username=pgpropwr $pgbench_init_param >>$LOG_FILE 2>>$PROGRESS_FILE
		exit_code $? $LOG_FILE $PROGRESS_FILE
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : INITIALIZATION OF pg_bench FINISHED'
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : INITIALIZATION OF pg_bench FINISHED' >> $LOG_FILE

		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench STARTED'
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench STARTED' >> $LOG_FILE
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench progress stored in file :'$PROGRESS_FILE
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench progress stored in file :'$PROGRESS_FILE  >> $LOG_FILE
		pgbench --username=pgpropwr $pgbench_param >>$LOG_FILE 2>>$PROGRESS_FILE
		exit_code $? $LOG_FILE $PROGRESS_FILE
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench FINISHED'
		echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : pg_bench FINISHED' >> $LOG_FILE
 
  
######################################################

psql -c "DROP DATABASE IF EXISTS test_pgbench10" 2>>$ERR_FILE
exit_code $? $LOG_FILE $ERR_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 HAS BEEN DROPPED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench10 HAS BEEN DROPPED ' >> $LOG_FILE
   

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

rm $ERR_FILE
exit 0

Шаги реализации

1.max_wal_size=8192

Среднее статистическое значение производительности = 235631,814286247

2.max_wal_size=16384

Среднее статистическое значение производительности = 307091,478012516

Значение производительности увеличилось.

3.max_wal_size=32768

Среднее статистическое значение производительности = 296544,027015618

Значение производительности уменьшилось, возвращаемся к старому значению параметра max_wal_size=16384 .

Считаем данное значение параметра max_wal_size=16384 - оптимальным для данного характера нагрузки.

Переходим к следующему параметру, для оптимизации.

5.bgwriter_lru_maxpages = 800

Среднее статистическое значение производительности = 332130,209038783

Значение производительности увеличилось.

6.bgwriter_lru_maxpages=1600

Среднее статистическое значение производительности =310819,820905112

Значение производительности уменьшилось, возвращаемся к старому значению параметра bgwriter_lru_maxpages = 800

Считаем данное значение параметра bgwriter_lru_maxpages = 800 - оптимальным для данного характера нагрузки.

Переходим к следующему параметру, для оптимизации.

7.bgwriter_flush_after = 32

Среднее статистическое значение производительности = 331818,714844122

Значение производительности уменьшилось , по сравнению с 5.bgwriter_lru_maxpages = 800 , возвращаемся к старому значению параметра bgwriter_flush_after = 64

Протестированы, все выбранные параметры для оптимизации. Тест завершен.

Оптимальные значения параметров

Таким образом, для данного характера нагрузки, оптимальными значениями являются:

max_wal_size=16384
bgwriter_lru_maxpages = 800
bgwriter_flush_after = 64

Рис.2. Изменение производительности в ходе тестов

Изменение производительности СУБД в результате оптимизации

Базовое значение: 235631,814286247

Значение производительности после оптимизации: 310819,820905112

Прирост производительности: ~41%

Финальный тест

Параметры инициализации pgbench

--no-vacuum --quiet --foreign-keys --scale=100 -i test_pgbench10

Параметры теста pgbench

--progress=60 --protocol=extended --report-per-command --jobs=24 --client=100 --time=1800 test_pgbench10

Результаты при базовых значениях параметров

latency average = 10.616 ms
latency stddev = 8.605 ms
tps = 9333.052818 (without initial connection time)

pgbench (14.11)
transaction type: <builtin: TPC-B (sort of)>
default transaction isolation level: read committed
transaction maximum tries number: 1
scaling factor: 100
query mode: extended
number of clients: 100
number of threads: 24
duration: 1800 s
number of transactions actually processed: 16799220
latency average = 10.616 ms
latency stddev = 8.605 ms
initial connection time = 181.648 ms
tps = 9333.052818 (without initial connection time)
statement latencies in milliseconds:
         0.012  \set aid random(1, 100000 * :scale)
         0.002  \set bid random(1, 1 * :scale)
         0.002  \set tid random(1, 10 * :scale)
         0.002  \set delta random(-5000, 5000)
         0.394  BEGIN;
         0.648  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.388  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         1.020  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         3.170  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.678  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         4.331  END;

Результаты по оптимизированным параметрам

latency average = 9.759 ms
latency stddev = 7.164 ms
tps = 10147.221902 (without initial connection time)

pgbench (14.11)
transaction type: <builtin: TPC-B (sort of)>
default transaction isolation level: read committed
transaction maximum tries number: 1
scaling factor: 100
query mode: extended
number of clients: 100
number of threads: 24
duration: 1800 s
number of transactions actually processed: 18264638
latency average = 9.759 ms
latency stddev = 7.164 ms
initial connection time = 210.679 ms
tps = 10147.221902 (without initial connection time)
statement latencies in milliseconds:
         0.011  \set aid random(1, 100000 * :scale)
         0.002  \set bid random(1, 1 * :scale)
         0.002  \set tid random(1, 10 * :scale)
         0.002  \set delta random(-5000, 5000)
         0.389  BEGIN;
         0.519  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.380  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.918  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         2.912  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.562  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         4.088  END;

Финальный тест, также показал прирост значения TPS:

Базовое значение TPS: 9333.052818
Значение после оптимизации параметров: 10147.221902
Прирост: +8%

Итоги и планы

Следующий шаг - автоматизация процесса по заданным начальным условиям и условию остановки .
Более аккуратный/гибкий подбор шага изменения (золотое сечение, Фибоначчи etc.)
Более глубокий анализ количественного и качественного изменения ожиданий СУБД в процессе изменения параметров(это самая интересная тема).
Протестировать применимость метода покоординатного спуска для подбора оптимального значения других групп конфигурационных параметров СУБД. Первый потенциальный кандидат - буферный кэш.
Развитием идеи, возможно будет разработка инструмента адаптивной оптимизации параметров СУБД в зависимости от меняющейся нагрузки на СУБД. Но, это в относительно далекой перспективе, конечно. И самое главное : пока окончательно непонятно - имеет ли смысл тратить ресурсы на данную тему . Есть реальные шансы - сильно закопаться с минимальным результатом в итоге. Поживём , увидим.

Комментарии (13)

lazy_val
16.10.2024 19:42
#27425586
В качестве метода оптимизации использован метод покоординатного спуска

По ссылке открывается п.19.4.5 Фоновая запись страницы Документация к Postgres Pro Enterprise 16.4.1

Не нашел там ничего про покоординатный спуск

Не туда смотрю?
1. rinace Автор
  16.10.2024 19:42
  #27426216
  Спасибо , за замечание .
  
  Исправлено .

akhkmed
16.10.2024 19:42
#27425752
Спасибо за статью.

Различие есть и 8% - уже немало, поэтому идея требует дальшейшего развития.

Подскажите, на каком железе проводите тесты? Если hdd, а не ssd, то на скорость от запуска к запуску будет влиять расположение данных на диске.
Все ли данные тестовой БД влазят в shared buffers?
Принимаются ли перед началом тестов меры, чтобы состояние памяти (shared buffers, кеш ОС, грязные страницы) были полностью равны для каждого запуска?

К отключению vaccuum есть вопрос, так как только что записанные при инициализации данные не годятся для чтения - любое чтение будет создавать грязные страницы и увеличивать нагрузку на запись. Тут на мой взгляд правильнее прогнать принудительный vacuum, но выключить autovacuum, чтобы не влиял непредсказуемым образом на результат.

Сможете опубликовать полный набор скриптов для инициализации БД и запуска теста, чтобы воспроизвести ваши результаты?
1. rinace Автор
  16.10.2024 19:42
  #27426220
1. rinace Автор
  16.10.2024 19:42
  #27426242
  Спасибо , за комментарий
  
  Подскажите, на каком железе проводите тесты?
  
  Все работы проводятся в облачной среде.
  
  Все ли данные тестовой БД влазят в shared buffer
  
  Скорее всего да. Судя по hit ratio.
  
  Принимаются ли перед началом тестов меры, чтобы состояние памяти (shared buffers, кеш ОС, грязные страницы) были полностью равны для каждого запуска?
  
  По завершении итерации теста выполняется vacuum analyze
  
  выключить autovacuum, чтобы не влиял непредсказуемым образом на результат
  
  Вряд ли этот будет сделано . Причина - это никогда не будет сделано в продуктивен. Да есть рекомендация - отключать автовакуум при проведении бенчмарков . Но , я отношусь к такой рекомендации пока с большим подозрением и сомнением .
  
  Сможете опубликовать полный набор скриптов для инициализации БД и запуска теста, чтобы воспроизвести ваши результаты?
  
  Ок. Принято. Сделаем .
  
  Тут категорически соглашусь - воспроизводимость результатов эксперимента это осень важно.
  
  Конечно , прошу прощения опубликовать полный список скриптов для расчёта метрики производительности , пока не получится . Во-первых объем довольно большой , во-вторых решение еще в процессе исследования , постоянных изменений и не готово в качестве продукта . Но для данного эксперимента результатов pgbench вполне достаточно .
1. rinace Автор
  16.10.2024 19:42
  #27426266
  8% - уже немало, поэтому идея требует дальшейшего развития
  
  Самый ближайший результат будет - автоматизация процесса .
  
  После инсталляции новой СУБД , запускаю скрипт , например в ночь, и получаю готовый набор измененных параметров СУБД по сравнению с дефолтными, для достижения оптимальной производительности при тестовой нагрузке при данной конфигурации инфраструктуры. Т.е. последовательно увеличиваю нагрузку и меняю параметры .
  
  Тема в работе.
1. rinace Автор
  16.10.2024 19:42
  #27426648
  Сможете опубликовать полный набор скриптов для инициализации БД и запуска теста, чтобы воспроизвести ваши результаты?
  
  Добавлены тексты скриптов для запуска нагрузочного и финального теста pgbench
  
  С расчетом метрики производительности, сорри, тем еще в исследовании , как готовый продукт - не готово. Объем большой, документации нет, методология использования - в разработке и тестировании .

VVitaly
16.10.2024 19:42
#27426298
:-) Статья вызывает "сложные" чувства...
С одной стороны вполне годная, как вводная/начальная, для настройки параметров PG, да и в принципе любых параметров ("железа" или OS) влияющих на производительность приложения.
С другой стороны необходимо четко понимать, оптимальная настройка PG базы для конкретного приложения примерно на порядок сложнее. Влияющих факторов очень много, один параметр настройки влияет на оптимальность настройки другого. И если вы настроили "последовательно" даже 5 параметров PG (из пары десятков) на максимальную/оптимальную производительность - это совершенно не означает что "другое" вполне определенное сочетание данных параметров не даст вам более оптимальную по производительности систему, т.е. большую производительность (или меньшее потребление ресурсов) при той же нагрузке на БД...
И при этом я уж не говорю о параметрах OS сервера БД, зачастую заметно влияющих на производительность PG базы и "приводящих" к другому "сочетанию" настроек оптимальных параметров производительности PG + настроек параметров "железа" и/или виртуальной среды для OS БД.
Вот как то так.... :-)
1. rinace Автор
  16.10.2024 19:42
  #27426326
  необходимо четко понимать, оптимальная настройка PG базы для конкретного приложения примерно на порядок сложнее.
  
  В конце статьи специально уточнено :
  
  Развитием идеи, возможно будет разработка инструмента адаптивной оптимизации параметров СУБД в зависимости от меняющейся нагрузки на СУБД. Но, это в относительно далекой перспективе, конечно
  
  Дело в том, что меня всегда удивляло - откуда берутся эти цифры из так называемых best practics ? Пришла задачу на инсталляцию новой СУБД , какие значения менять по сравнению с дефолтными ? И самое главное почему ?
  
  До текущего момента все эти цифры, скажем честно брались с потолка , просто потому, что кто то , где то прочитал и передал друзьям и коллегам и в результате например никто и не задумывается - а почему именно такое значение для например autova_max_workers ? А кто тотпроверял другие ?
  
  Теперь будет по другому - запускается автоматический скрипт и подбирает оптимальные значения параметров для данной инфраструктуры .
  
  Затем будет развитие - для данной архитектуры (нужно будет использовать кастомные скрипты в pgbench).
  
  А задача адаптивной оптимизации параметров по текущей продуктивной нагрузке на СУБД зто перспективная тема .
  
  Наверное уже на следующий год займусь плотнее.
  1. VVitaly
    16.10.2024 19:42
    #27427768
    :-) Подбирать параметры PG под нагрузку от pgbench для клиента особого смысла нет... Это только на "очень простых" прикладных приложениях можно смоделировать (с помощью pgbench) реальную нагрузку на БД клиентского профиля нагрузки приложения (что по факту так же потребует еще кучу тестов и времени), а именно работа БД под реальной нагрузкой цель оптимизации параметров БД на определенных вычислительных ресурсах...
    БД инсталлируется не "просто так", а под конкретное прикладное приложение, с определенной планируемой нагрузкой (как на приложение, так и на БД). Нормальный и вменяемый разработчик прикладного ПО дает обычно первоначальные "усредненные" рекомендации по "железу", параметрам OS и БД под конкретную планируемую прикладную нагрузку для решения определенной прикладной задачи. Конечно к agile подходу решения прикладной задачи это применить практически невозможно, но тут уж "сам себе злобный буратино"...
    А вот дальше... Дальше всегда наступает момент когда "меняется профиль нагрузки на БД" и/или меняются "выделенные для БД вычислительные ресурсы" и только знание "принципов работы БД" и знание "какой параметр БД и как влияет на работу БД" помогут оптимизировать ее работу.
  1. VVitaly
    16.10.2024 19:42
    #27427970
    Ознакомился с другой вашей статьей. Впечатления примерно такие же как и от этой...
    "Отношение операционной скорости к объемной скорости и будет принято как производительность СУБД." - это обобщенное заключение верное.
    Вот только "по факту" для процесса "оптимизации параметров конкретной БД" нужно учитывать и изменения этих "двух скоростей" по времени + поставленные (или фактические) ограничения других метрик - приложения/БД/OS/"железа"/"цены решения".
    Именно по этой причине ваш вывод что контролировать/измерять/оценивать необходимо только "сглаженные данные" в общем неверен (начиная со скважности измерения и заканчивая длительностью). Все зависит от поставленных/определенных условий.
    
    rinace Автор
    16.10.2024 19:42
    #27427990
    У вас есть другая методика расчёта метрики производительности СУБД ?
    
    VVitaly
    16.10.2024 19:42
    #27428092
    :-) Метрики производительности - это просто метрики... Проблема с непониманием принципов работы БД - это просто проблема.
    Настройка параметров БД под определенные требования (в соответствии в контролируемыми метриками) - отдельный процесс. Требующий как получения "правильных" метрик, так и их "правильный" анализ, так и "правильное" изменение параметров влияющих на метрики так как требуется "заказчику".
    Для вас оставлю ссылку которая возможно даст вам дополнительную информацию которой вам возможно не хватало по PG - https://tembo.io/blog/optimizing-memory-usage

Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД +4

Стартовые значения параметров

Тестовая нагрузка на СУБД

Алгоритм оптимизации

Реализация

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

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

Шаги реализации

1.max_wal_size=8192

2.max_wal_size=16384

3.max_wal_size=32768

5.bgwriter_lru_maxpages = 800

6.bgwriter_lru_maxpages=1600

7.bgwriter_flush_after = 32

Оптимальные значения параметров

Изменение производительности СУБД в результате оптимизации

Финальный тест

Параметры инициализации pgbench

Параметры теста pgbench

Результаты при базовых значениях параметров

Результаты по оптимизированным параметрам

Итоги и планы

Комментарии (13)

lazy_val

rinace Автор

akhkmed

rinace Автор

rinace Автор

rinace Автор

rinace Автор

VVitaly

rinace Автор

VVitaly

VVitaly

rinace Автор

VVitaly