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

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

В качестве начального примера, для отработки методики, выбраны настройки для процессов контрольной точки (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 минут.

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

  1. Запуск теста

  2. Отметить среднее статистическое (медиана) значение производительности СУБД в за тестовый период.

  3. Изменить значение параметра

  4. Запуск теста

  5. Отметить среднее статистическое (медиана) значение производительности СУБД в за тестовый период.

  6. Если значение производительности уменьшилось — вернуться к предыдущему значению параметра. Выбрать следующий параметр для оптимизации и перейти к шагу 3. В случае если перебраны все параметры для оптимизации — завершение.

  7. Если значение производительности увеличилось — перейти к шагу 3.

Реализация

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

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

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

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

Рис.1. Результаты тестов

Рис. 1. Результаты тестов

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

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

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

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

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

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

Итоговый тест pgbench

Параметры инициализации 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: 
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: 
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;

Итоги

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

  2. Развитием идеи возможно будет разработка методики адаптивной оптимизации параметров СУБД в зависимости от меняющейся нагрузки на СУБД.

© Habrahabr.ru