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