+65% к PostgreSQL: Практический кейс точечной настройки ОС и анализа всей стека.
+65% к PostgreSQL: Практический кейс точечной настройки ОС и анализа всей стека.

Часто при замедлении работы базы данных первым решением кажется увеличение вычислительных ресурсов: больше ядер, памяти, быстрые диски. Однако существует и другой, более экономичный путь — заглянуть глубже, на уровень операционной системы, управляющей этими ресурсами.

Данная статья — это практический разбор реального кейса, где скрупулёзная настройка параметров подсистемы ввода-вывода, кэширования и планировщика задач Linux позволила поднять производительность PostgreSQL на впечатляющие 65%. Без замены железа, без увеличения лицензий, только за счёт грамотной оптимизации «фундамента», на котором работает СУБД. 

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Задача

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

Тестовая среда, инструменты и конфигурация СУБД:

Тестовый сценарий-1 (SELECT): вес = 0.7
CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$
DECLARE
 test_rec record ;
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_tid bigint ;
 current_bid bigint ;
 current_delta bigint ;
 counter bigint;
BEGIN
---------------------------------------------------
--СЦЕНАРИЙ 1 - SELECT ONLY
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
select br.bbalance
into test_rec
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = current_aid ;
--СЦЕНАРИЙ 1 - SELECT ONLY
---------------------------------------------------
return 0 ;
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-2 (UPDATE): вес = 0.2
CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$
DECLARE
 test_rec record ;
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_tid bigint ;
 current_bid bigint ;
 current_delta bigint ;
 counter bigint;
BEGIN
---------------------------------------------------
--СЦЕНАРИЙ 2 - SELECT + UPDATE
--1)UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
--2)SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
--3)UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
--4) UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
current_delta = (ROUND( random ())::bigint)*10 + 1 ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
--1)
UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid ;
--1)
--2)
SELECT abalance INTO test_rec FROM pgbench_accounts WHERE aid = current_aid ;
--2)
SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;
SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;
current_tid = floor(random() * (max_i - min_i + 1)) + min_i ;
--3)
UPDATE pgbench_tellers SET tbalance = tbalance + current_delta WHERE tid = current_tid ;
--3)
SELECT MIN(bid) INTO min_i FROM pgbench_branches ;
SELECT MAX(bid) INTO max_i FROM pgbench_branches ;
current_bid = floor(random() * (max_i - min_i + 1)) + min_i ;
--4)
UPDATE pgbench_branches SET bbalance = bbalance + current_delta WHERE bid = current_bid ;
--4)
-- СЦЕНАРИЙ 2 - OLTP
 return 0 ;
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-3 (INSERT): вес = 0.1
CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$
DECLARE
 test_rec record ;
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_tid bigint ;
 current_bid bigint ;
 current_delta bigint ;
 counter bigint;
BEGIN
---------------------------------------------------
--СЦЕНАРИЙ 3 - INSERT ONLY
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;
SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;
current_tid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT MIN(bid) INTO min_i FROM pgbench_branches ;
SELECT MAX(bid) INTO max_i FROM pgbench_branches ;
current_bid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT random() * 1000.0
INTO current_delta;
FOR counter IN 1..1000
LOOP
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES ( current_tid , current_bid , current_aid , current_delta , CURRENT_TIMESTAMP );
END LOOP;
--ССЦЕНАРИЙ 3 - INSERT ONLY
---------------------------------------------------
 return 0 ;
END
$$ LANGUAGE plpgsql;

Базовые значения параметров IO

Общие параметры производительности:

  • vm.dirty_ratio = 30

  • vm.dirty_background_ratio = 10

Параметры IO-планировщика:

  • [none] mq-deadline kyber bfq

Настройки кэширования и буферизации:

  • vm.vfs_cache_pressure = 100

Параметры файловой системы:

  • /dev/mapper/vg_data-LG_data on /data type ext4 (rw,relatime)

Размер буферов для операций с блочными устройствами

  • read_ahead_kb=4096

Рекомендации по изменению параметров ОС.

Эксперимент-2: Общие параметры производительности
vm.dirty_ratio=10
vm.dirty_background_ratio=5

Эксперимент-3: Параметры IO-планировщика
[mq-deadline] kyber bfq none

Эксперимент-5: Настройки кэширования и буферизации
vm.vfs_cache_pressure=50

Эксперимент-7: Оптимизация параметров файловой системы
/dev/mapper/vg_data-LG_data on /data type ext4 (rw,noatime,nodiratime)

Эксперимент-8: Изменение размера буферов для операций с блочными устройствами
echo 256 > /sys/block/vdd/queue/read_ahead_kb

Итоговый результат

Сравнительный график изменения операционной скорости в ходе нагрузочного тестирования для Эксперимента-8(SPEED-8) и базовыми значениями параметров IO для Эксперимента-1(SPEED-1)
Сравнительный график изменения операционной скорости в ходе нагрузочного тестирования для Эксперимента-8(SPEED-8) и базовыми значениями параметров IO для Эксперимента-1(SPEED-1)

В результате оптимизации параметров подсистемы ввода-вывода операционной системы производительность СУБД PostgreSQL повысилась в среднем на 65% по сравнению с базовой конфигурацией.

Показатели производительности , ожиданий СУБД и метрик производительности IO в ходе экспериментов

Операционная скорость

График изменения операционной скорости в ходе экспериментов
График изменения операционной скорости в ходе экспериментов

Ожидания СУБД

График изменения ожиданий СУБД в ходе экспериментов
График изменения ожиданий СУБД в ходе экспериментов

IOPS

График изменения IOPS в ходе экспериментов
График изменения IOPS в ходе экспериментов

Пропускная способность (MB/s)

График изменения MB/s в ходе экспериментов
График изменения MB/s в ходе экспериментов

Длина очереди (aqu_sz)

График изменения aqu_sz в ходе экспериментов
График изменения aqu_sz в ходе экспериментов

Ожидание по чтению

График изменения r_await(ms) в ходе экспериментов
График изменения r_await(ms) в ходе экспериментов

Ожидание по записи

График изменения w_await(ms) в ходе экспериментов
График изменения w_await(ms) в ходе экспериментов

Итоговый вывод

Систематическая оптимизация параметров подсистемы IO — таких как настройки кэширования, планировщика операций ввода-вывода и параметров файловой системы — позволила достичь значительного повышения производительности PostgreSQL.

Суммарный эффект от внесённых изменений выразился в среднем увеличении операционной скорости на 65,09% по сравнению с базовой конфигурацией. Наиболее существенный вклад внесли корректировки размера буферов предварительного чтения (read_ahead_kb) и отключение избыточного обновления временных меток файлов (noatime, nodiratime).

Результаты подтверждают - целенаправленная настройка окружения ОС является критически важным этапом развёртывания высоконагруженных СУБД.

P.S. Завершение серии экспериментов по анализу и оптимизации инфраструктуры

PG_EXPECTO: Чек-лист проверки инфраструктуры Linux по результатам нагрузочного тестирования PostgreSQL