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