Задача

Определить максимальное количество предельное количество работающих соединений к СУБД , при заданном сценарии нагрузки .

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

Создание нагрузки на СУБД - используется инструмент pgbench

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

Аналогично описанным в Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД / Хабр (habr.com)

Сценарий теста

1.Базовая нагрузка на СУБД

Запуск pgbench c неизменным значением параметра client:

--client=клиенты
Число имитируемых клиентов, то есть число одновременных сеансов базы данных.

2.Период сбора статистических данных для долгой и короткой скользящей

3.Дополнительная нагрузка

Запуск дополнительного сценария pgbench с постоянным периодом выполнения и увеличением параметра client = Итерация * 40

4.Условие остановки теста

Значение коэффициента корреляции между количеством активных сессий и значением долгой скользящей R_xy< -0.7

5.Результат

Максимальное значение параметра client при выполнении дополнительного сценария pgbench.( Пункт 3.).

Реализация теста

Создание и инициализация БД для базовой нагрузки

stress_base.create_db.sh

#!/bin/sh
# stress_base.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'/stress_base.create_db.log'
ERR_FILE=$current_path'/stress_base.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

Сценарий базовой нагрузки

stress_base.sh

#!/bin/sh
# stress_base.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'/stress_base.log'

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

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

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

#отключить наблюдение
touch /postgres/scripts/stress/STRESS_NOT_STARTED ;

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

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


RESULT_LOG_FILE=$current_path'/result.log'
echo $(date "+%d-%m-%Y %H:%M:%S")' | stress_base | ' > $RESULT_LOG_FILE
echo ' timestamp | response time | response time sort | response time long | CPI | Active | CPI MM short  | Active short  | CPI MM short/Active short correlation | CPI MM long  | Active long  | CPI MM short/Active long correlation |' >> $RESULT_LOG_FILE


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


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



ERR_FILE=$current_path'/stress_base.err'
PROGRESS_FILE=$current_path'/stress_base.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") ' : stress_base : OK : pgbench_param= '$pgbench_param
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : pgbench_param= '$pgbench_param>> $LOG_FILE
 

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

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

  rm $ERR_FILE
  
  exit 0
fi

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : pg_bench STARTED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : pg_bench STARTED' >> $LOG_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : pg_bench progress stored in file :'$PROGRESS_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : 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") ' : stress_base : OK : pg_bench FINISHED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : pg_bench FINISHED' >> $LOG_FILE

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

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : VACUUM ANALYZE : STARTED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : 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") ' : stress_base : OK : VACUUM ANALYZE : FINISHED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_base : OK : VACUUM ANALYZE : FINISHED' >> $LOG_FILE



done

exit 0

Используемые значения для создания нагрузки:

pgbench_clients 100

transactions 10000

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

stress_additional.create_db.sh

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


#Обработать код возврата 
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'/stress_additional.create_db.log'
ERR_FILE=$current_path'/stress_additional.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


###################
# ПАРАМЕТРЫ ТЕСТОВОГО СЦЕНАРИЯ
let pgbench_clients=`cat $current_path'/pgbench_clients'`
###################
   
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench11 CREATION IS STARTED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : DATABASE test_pgbench11 CREATION IS STARTED ' >> $LOG_FILE
		
	  
psql -c "DROP DATABASE IF EXISTS test_pgbench11" 2>>$ERR_FILE
exit_code $? $LOG_FILE $ERR_FILE

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


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

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

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

Сценарий дополнительной нагрузки

stress_additional.sh

#!/bin/sh
# stress_additional.sh
# version 11.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'/stress_additional.log'

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

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

test_time=$1
clients=$2

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

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


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

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


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

pg_bench_time=$1
let pgbench_clients=$2


##################
ERR_FILE=$current_path'/stress_additional.err'
PROGRESS_FILE=$current_path'/stress_additional.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"' --time='"$pg_bench_time"' test_pgbench11'			
		
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pgbench_param= '$pgbench_param
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pgbench_param= '$pgbench_param>> $LOG_FILE

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pg_bench STARTED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pg_bench STARTED' >> $LOG_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pg_bench progress stored in file :'$PROGRESS_FILE
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : 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") ' : stress_additional : OK : pg_bench FINISHED'
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : stress_additional : OK : pg_bench FINISHED' >> $LOG_FILE

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

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

  rm $ERR_FILE
  
  exit 0
fi


rm $ERR_FILE
exit 0

Запуск дополнительной нагрузки

stress_start.sh

#!/bin/sh
# stress_start.sh
# version 12
# Стресс - тестирование
# Бесконечный цикл. Остановка вручную
# 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'/stress_start.log'
RESULT_LOG_FILE=$current_path'/result.log'

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

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

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


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

cd $current_path

RESULT_LOG_FILE=$current_path'/result.log'
echo $(date "+%d-%m-%Y %H:%M:%S")' | stress_start | ' >> $RESULT_LOG_FILE

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

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

  rm $ERR_FILE
  
  exit 0
fi

##################################
# Старт стрессовой нагрузки 
# 10 минут 
# количество клиентов = counter*40
let clients=counter*40
./stress_additional.sh 600 $clients

let counter=$counter+1

done

exit 0

Протоколирование результатов теста

stress.log.sh

#!/bin/sh
# stress.log.sh
# version 12
# Логирование
# Бесконечный цикл.
# Остановка теста - 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`

if [ -f /postgres/scripts/pgbench/STOP_PGBENCH ]; 
then
  exit 0
fi

RESULT_LOG_FILE=$current_path'/result.log'
ERR_FILE=$current_path'/result.err'
PROGRESS_FILE=$current_path'/result.progress'


result_str=`psql -Aqtc 'select unnest(backrest.cpi_mm_active_report())' 2>$ERR_FILE`
exit_code $? $LOG_FILE $PROGRESS_FILE

echo $result_str >> $RESULT_LOG_FILE


signal=`echo $result_str | awk -F '|' '{print $13}' ` 
echo 'signal='$signal

#Если наблюдение , еще не включено
if [ -f /postgres/scripts/stress/STRESS_NOT_STARTED ]; 
then
  exit 0
fi


if [ "$signal" ==  'SIGNAL' ]
then
	echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' | ATTENTION : STRONG NEGATIVE CORRELATION  !!! | ' >> $RESULT_LOG_FILE
    touch /postgres/scripts/pgbench/STOP_PGBENCH	
fi

rm $ERR_FILE

exit 0

Реализация теста

Для простоты и сокращения объема материала, приведены графики после запуска дополнительной нагрузки и до окончания теста.

Частота замеров показаний = 1 минута.

Период медианного сглаживания долгой скользящей = 1 час.

Рис.3. Долгая скользящая производительности СУБД

Рис.4. Долгая скользящая по активным сессиям

Рис.5. Коэффициент корреляции между долгой скользящей производительности СУБД и долгой скользящей активных сессий

Результат теста

Завершение теста: 17.10.2024 22:22:00

Работающий сценарий добавочной нагрузки:

TIMESTAMP : 17-10-2024 22:20:03  : stress_additional : OK : STARTED
TIMESTAMP : 17-10-2024 22:20:03  : stress_additional : OK : Количество клиентов: 120

Результат: предельная нагрузка по сценарию pgbench составляет 220 клиентов.

Итог и планы

Разработанная методика будет использована для настройки параметров СУБД при новых инсталляциях
После определения максимального значения нагрузки pgbench значение будет использовано для оптимизации конфигурационных параметров по методике описанной в Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД / Хабр (habr.com) (Примечание: после разработки автоматизированного скрипта)
В дальнейшем данная методика будет использована при нагрузочном тестировании с использованием pgbench и пользовательских скриптов (Программа pgbench поддерживает запуск пользовательских сценариев оценки производительности, позволяя заменять стандартный скрипт транзакции (описанный выше) скриптом, считываемым из файла (с параметром -f). В этом случае «транзакцией» считается одно выполнение данного скрипта. )
После определения максимальной нагрузки с использованием pgbench + пользовательские скрипты , значение максимальной нагрузки будет проведена оптимизация конфигурационных параметров аналогично Пункту 2.
По завершении оптимизации конфигурационных параметров, СУБД передается в опытную эксплуатацию и сопровождение.

Таким образом - выстраивается простой , однозначный и математически обоснованный способ настройки конфигурационных параметров СУБД для новой инсталляции и при передаче СУБД в опытную эксплуатацию.

Update

Результаты стресс-тестирования СУБД с базовыми настройками(default) checkpoint/bgwriter и после оптимизации(tuning) настроек, с использованием метода Этюд: использование метода покоординатного спуска для оптимизации параметров СУБД / Хабр (habr.com) :

1.Статистические показатели производительности и нагрузки на СУБД

Прирост производительности и активных сессий

2. Ожидания СУБД

Кардинальное снижение ожиданий по пользовательским запросам

Вывод

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

После автоматизации оптимизации - эксперименты будут продолжены.

Статистический анализ стресс-теста СУБД