A-Tune — «магия», которая ускорит ваш сервер

На нашем сайте уже была обзорная статья по этой технологии, где довольно подробно рассмотрены основные виды способов увеличения производительности серверных приложений, поэтому, чтобы не перегружать нашу новую статью общими описаниями все желающие узнать базовую информацию об A-Tune могут ознакомится с ней пройдя по ссылке.

Поставленная задача

Сегодня мы постараемся показать, как можно поднять производительность СУБД PostgreSQL методом тюнинга параметров конфигурации. В начале тестирования мы снимем показания на конфигурации PGSQL с которой данная СУБД поставляется в дистрибутиве, а затем, применим методы A-Tune и посмотрим насколько улучшатся измеряемые при тестировании показатели. При этом мы сразу оговоримся, что тюнинг параметров мы будем вести в двух направлениях: увеличение производительности PGSQL для выполнения mixed запросов и отдельно увеличение производительности PGSQL для выполнения SELECT запросов (если СУБД используется в основном для аналитики).

Подготовка стенда

Для начала опишем наш стенд, на котором мы будем производить тестирование.

Сервер Huawei 1288H V5:

• 2 CPU (Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz)
• 128 Гб ОП
• 2 SSD диска (INTEL SSDSC2KG480G8)
• Версия ОС: OpenScaler 24.03 LTS
• Ядро: 6.6.0-28.0.0.34.os2403.x86_64
• Версия A-Tune: 1.2.0-3
• Версия СУБД: postgresql-server-15.6-1

Давайте установим СУБД PQSQL из стандартного репозитория:

dnf install postgresql-server

После установки, нам надо создать базовые каталоги для размещения данных СУБД и, собственно, запустить pgsql:

/usr/bin/postgresql-setup --initdb
systemctl enable --now postgresql

Теперь давайте создадим тестовую БД (размером приблизительно 90 Гб):

su - postgres -c "pgbench -i -U postgres -s 4000"

Это займёт какое-то время. После окончания создания, давайте увеличим до 1024 количество возможных подключений к нашей БД:

postgres=# ALTER SYSTEM SET max_connections TO '1024';

И перезапускаем postgresql

Сразу приведём перечень команд, которые мы будем давать после каждого перезапуска сервиса postgres (этими командами мы делаем так называемый «прогрев данных») – разместим их в файле pre_warm.sql в домашнем каталоге пользователя postgres:

psql -U postgres
CREATE EXTENSION pg_prewarm;
select pg_prewarm('pgbench_accounts'::regclass);
select pg_prewarm('pgbench_accounts_pkey'::regclass);
select pg_prewarm('pgbench_tellers'::regclass);
select pg_prewarm('pgbench_history'::regclass);
select pg_prewarm('pgbench_branches'::regclass);

Для удобства проведения наших тестов напишем маленький скрипт на bash:

#!/bin/bash
NAME_TEST="default"
su - postgres -c "psql -U postgres < ~/pre_warm.sql"
for i in 50 100 200 300 400 500 600 700 800 900 1000
do
su - postgres -c "pgbench -j 20 -c $i -T 60 > /test/$NAME_TEST/pgbench-"${NAME_TEST}"-$i-mixed.log"
su - postgres -c "pgbench -j 20 -c $i -T 60 -S > /test/$NAME_TEST/pgbench-"${NAME_TEST}"-$i-select.log"
done

И по мере проведения тестов будем просто менять переменную NAME_TEST.

То есть дефолтные значения (до проведения тюнинга) мы получим запустив этот скрипт с NAME_TEST установленным в значение “default_settings”.

Тестирование — применение профиля

Давайте снимем показатели производительности, полученные утилитой pgbench при дефолтной конфигурации postgresql и запишем их в таблицу:

Теперь давайте применим профиль A-Tune, который предназначен для изменения параметров ОС, и проведём тестирование заново.

Список всех доступных профилей можно увидеть в выводе команды:

atune-adm list

Давайте применим профиль для postgresql:

atune-adm profile database-postgresql-2p-sysbench-ssd

Результаты замеров после установки профиля:

Давайте посмотрим как это выглядит на графиках:

Давайте теперь сравним (на 1000 соединений) как изменилась производительность.

Из данных приведённых таблиц, мы видим, что latency и TPS при mixed запросах у нас просела на 14%, а вот производительность SELECT-only увеличилась на 6%. Не сказать, чтобы прямо мы были довольны результатом, но давайте теперь попробуем «пошаманить» именно с параметрами конфигурации postgresql.

Тестирование — перебор значений параметров конфигурации

Для начала кратко опишем суть этого метода. Мы подготавливаем два конфигурационных файла A-Tune (yaml-манифесты), один для сервера, другой для клиента тюнинга. В серверном файле мы описываем какие параметры postgresql мы будем менять и какие значения этих параметров мы хотим попробовать при тюнинге. А в клиентском файле мы описываем утилиты тестирования производительности (наш pgbench) и какие параметры из выхлопа pgbench нас интересуют в качестве ориентиров для анализа производительности.

Приведём файл серверной конфигурации (файл размещаем по пути /etc/atuned/tuning/postgresql_pgbench_server.yaml):

project: "postgresql_pgbench"

maxiterations: 5000

startworkload: "systemctl start postgresql"

stopworkload: "systemctl stop postgresql"

object :

-

name : "kernel.numa_balancing"

info :

desc : "Specifies whether to enable NUMA automatic balancing."

get : "sysctl -n kernel.numa_balancing"

set : "sysctl -w kernel.numa_balancing=$value"

needrestart : "false"

type : "discrete"

options :

- "0"

- "1"

dtype : "string"

-

name : "kernel.sched_autogroup_enabled"

info :

desc : "When enabled, the kernel creates task groups to optimize desktop program scheduling."

get : "sysctl -n kernel.sched_autogroup_enabled"

set : "sysctl -w kernel.sched_autogroup_enabled=$value"

needrestart : "false"

type : "discrete"

options :

- "0"

- "1"

dtype : "string"

-

name : "shared_buffers"

info :

desc : "PostgreSQL 'shared_buffers' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^shared_buffers =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^shared_buffers.*$/shared_buffers = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "128MB"

- "8GB"

- "16GB"

- "32GB"

dtype : "string"

-

name : "effective_cache_size"

info :

desc : "PostgreSQL 'effective_cache_size' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^effective_cache_size =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^effective_cache_size.*$/effective_cache_size = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "4GB"

- "19GB"

- "48GB"

- "96GB"

dtype : "string"

-

name : "work_mem"

info :

desc : "PostgreSQL 'work_mem' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^work_mem =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^work_mem.*$/work_mem = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "3Mb"

- "4Mb"

- "13Mb"

- "32Mb"

- "45Mb"

- "64Mb"

dtype : "string"

-

name : "maintenance_work_mem"

info :

desc : "PostgreSQL 'maintenance work_mem' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^maintenance_work_mem =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^maintenance_work_mem.*$/maintenance_work_mem = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "64MB"

- "1GB"

- "3GB"

- "6GB"

dtype : "string"

-

name : "min_wal_size"

info :

desc : "PostgreSQL 'min_wal_size' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^min_wal_size =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^min_wal_size.*$/min_wal_size = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "80MB"

- "1GB"

- "2GB"

dtype : "string"

-

name : "max_wal_size"

info :

desc : "PostgreSQL 'max_wal_size' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^max_wal_size =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^max_wal_size.*$/max_wal_size = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "1GB"

- "2GB"

- "3GB"

dtype : "string"

-

name : "effective_io_concurrency"

info :

desc : "PostgreSQL 'effective_io_concurrency' param."

get : "cat /var/lib/pgsql/data/postgresql.conf | grep '^effective_io_concurrency =' | awk -F '=' '{print $2}'"

set : "sed -i 's/^effective_io_concurrency.*$/effective_io_concurrency = $value/g' /var/lib/pgsql/data/postgresql.conf"

needrestart : "true"

type : "discrete"

options :

- "100"

- "200"

dtype : "string"

Мы видим, каким образом A-Tune будет получать (get) текущие значения параметров и как будет устанавливать (set) значение параметров, которые участвуют в тюнинге производительности.

Но так как у нас заточка параметров идёт под разные типы работы СУБД, то файлов клиента мы создадим 2: один будет замерять производительность mixed запросов, другой будет замерять производительность select запросов.

Для mixed запросов postgresql_pgbench_client-mixed.yaml:

project: "postgresql_pgbench"

engine : "gbrt"

iterations : 200

random_starts : 10

benchmark : "sh /root/pgsql-tuning/postgresql_pgbench_benchmark-mixed.sh"

evaluations :

-

name: "TPS"

info:

get: "grep 'tps = ' /a/pgbench-tuning-mixed.log | awk -F ' ' 'NR==1{print $3}'"

type: "negative"

weight: 100

Содержимое скрипта проверки: postgresql_pgbench_benchmark-mixed.sh:

#!/bin/bash

su - postgres -c "pgbench -U postgres -c 20 -j 100 -T 60 > /test/pgbench-tuning-mixed.log"

Для SELECT-only запросов postgresql_pgbench_client-select.yaml:

project: "postgresql_pgbench"

engine : "gbrt"

iterations : 200

random_starts : 10

benchmark : "sh /root/pgsql-tuning/postgresql_pgbench_benchmark-select.sh"

evaluations :

-

name: "TPS"

info:

get: "grep 'tps = ' /test/pgbench-tuning-select.log | awk -F ' ' 'NR==1{print $3}'"

type: "negative"

weight: 100

Содержимое скрипта проверки:  postgresql_pgbench_benchmark-select.sh

#!/bin/bash

su - postgres -c "pgbench -U postgres -c 20 -j 100 -T 60 -S > /test/pgbench-tuning-select.log"

Теперь давайте запустим тюнинг параметров (сначала под mixed запросы)

atune-adm tuning --project postgresql_pgbench --detail postgresql_pgbench_client-mixed.yaml

Дожидаемся окончания (это может занять несколько часов) и получаем в выводе на консоль рекомендацию по настройке:

The final optimization result is: kernel.numa_balancing=0,kernel.sched_autogroup_enabled=1,shared_buffers=8GB,effective_cache_size=48GB,work_mem=3Mb,maintenance_work_mem=6GB,min_wal_size=80MB,max_wal_size=3GB,effective_io_concurrency=100

The final evaluation value is: TPS=16061.66

Baseline Performance is: (TPS=8929.71)

Tuning Finished

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

Посмотрим, как результаты выглядят на графиках:

Останавливаем postgresql, откатываем конфигурационный файл на дефолтный, запускаем заново и проводим тюнинг параметров под select-only:

atune-adm tuning --project postgresql_pgbench --detail postgresql_pgbench_client-select.yaml

Дожидаемся окончания (это может занять несколько часов) и получаем в выводе на консоль рекомендацию по настройке:

The final optimization result is: kernel.numa_balancing=0,kernel.sched_autogroup_enabled=0,shared_buffers=128MB,effective_cache_size=4GB,work_mem=32Mb,maintenance_work_mem=6GB,min_wal_size=80MB,max_wal_size=2GB,effective_io_concurrency=200

The final evaluation value is: TPS=257468.18

Baseline Performance is: (TPS=98132.72)

Tuning Finished

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

Посмотрим, как это выглядит на графиках:

Теперь, сравнив полученные в двух таблицах значения с теми, которые мы получили в самом начале нашего тестирования (на дефолтной конфигурации) мы видим следующее:

• Прирост производительности в mixed запросах составил довольно ощутимые 39%
• тюнинг производительности в select-only запросах добавил производительности без малого 9%

Таким образом мы продемонстрировали подход (по сути перебор значений конфигурационного файла для поиска оптимального сочетания) при котором при минимальном усилии со стороны администратора мы можем получить конфигурацию postgresql, которая будет точно работать быстрее дефолтной.

Итоги тестирования

Сегодня мы рассмотрели один из способов, при помощи которого A-Tune может существенно поднять производительность нашего программного обеспечения.

В следующей нашей статье мы рассмотрим как A-Tune может помочь увеличить производительность выбранного нами приложения при помощи модного ныне подхода с использованием искусственного интеллекта.