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

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

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

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

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

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

• max_wal_size=16384

• bgwriter_lru_maxpages = 800

• bgwriter_flush_after = 64

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

Базовое значение: 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: <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;

Итоги

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

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