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