Помните, как в Корпорации Монстров страшилы добывали энергию из криков детей? Они готовились, чтобы быть пострашнее и собирать побольше энергии: репетировали жуткие звуки, подбирали реквизит, узнавали страхи ребёнка. В общем, настраивали параметры запугивания :) В мире баз данных у параметров конфигурации аналогичная роль: чем точнее настройка, тем выше производительность системы. Однако не все параметры везде работают с одинаковым эффектом (как не все монстры одинаково страшны для всех детей): какой-нибудь параметр идеален для одного профиля нагрузки, но не даёт того же результата для другого.

Администраторы БД часто ломают голову, как подобрать оптимальные настройки под задачи СУБД. И чтобы помочь им разобраться, мы обратились к опыту Майка Вазовски — тренера легендарного монстра Салли. Вдохновились секретами его тренировок и разработали утилиту Pangolin Tuner — аналог Вазовски для настройки параметров конфигурации. 

Я Кристина Демидович, DevOps-инженер в СберТехе, занимаюсь автоматизацией в команде СУБД Pangolin — это целевая СУБД в Сбере и не только. В этой статье покажу на примерах, как всё работает.

Профили и их страхи

Немного про наши условия. Каждый тип нагрузки можно рассматривать как отдельный сценарий, где параметры конфигурации нужно адаптировать для достижения наилучших результатов. Вот основные сценарии Pangolin Tuner.

OLTP. Здесь «монстры» должны быть отзывчивыми и расторопными, чтобы обрабатывать множество транзакций в реальном времени.

Пример запуска:

pangolin-tuner -c postgresql.conf

Кстати, это минимальная строка запуска для генерации настроек. Профиль используется в утилите по умолчанию.

OLAP. В этом сценарии нужны выносливые и сообразительные «монстры», готовые к обработке больших объёмов данных и сложным аналитическим запросам.

Пример запуска:

pangolin-tuner -c postgresql.conf -p olap

1C. Здесь наши «монстры» должны быть настроены для работы со специфическими требованиями платформы 1C, чтобы повысить производительность при работе с бизнес-приложениями.

Пример запуска:

pangolin-tuner -c postgresql.conf -p 1c

Утилита ожидает на входе конфигурационный файл, на основе которого формируется новый. Это обусловлено тем, что она не включает полный набор всех возможных параметров — такой подход лишил бы её гибкости. Ведь не все параметры влияют на производительность, и не все они универсально настраиваются для различных стендов. Есть параметры, принимающие значения в зависимости от особенностей конкретного стенда: shared_preload_libraries, search_path, pgport, max_connections и многие другие. Наша цель — получить на выходе готовый конфигурационный файл, который можно сразу применить на стенде.

Так как же Pangolin Tuner превращает теорию в практику?

Инструмент по пуганию в деталях

Всё довольно просто! Начнём с изучения справки.

Пример запуска:

pangolin-tuner --help

Результат выполнения команды покажет нам следующее:

usage:  pangolin-tuner -c CONFIG [-h] [-r OUTPUT_FILE] [-d DIFF] [-p {oltp,1c,olap}] [-o OPTION] [-v] [-l {info,debug}]
             pangolin-tuner -c file                             tune configuration for profile by default
             or: pangolin-tuner -c file -p oltp                 tune configuration for custom profile
             or: pangolin-tuner -c file -l debug                tune configuration for profile by default with custom log level
             or: pangolin-tuner -c file -r /path/to/file.conf	tune configuration for profile by default with custom path output file
             or: pangolin-tuner -c file -o cpu=10               tune configuration for profile by default with custom options
 
Pangolin Tuner is a Pangolin DBMS configuration tool designed to optimize database performance in various usage scenarios.

options:
  -h, --help        	show this help message and exit
  -c CONFIG, --config CONFIG
                        path to the current configuration file
  -r OUTPUT_FILE, --output-file OUTPUT_FILE
                        path to result configuration file
  -d, --diff            format result configuration file
  -p {oltp,1c,olap}, --profile {oltp,1c,olap}
                        profile for parameter tuning
  -o OPTION, --option OPTION
                        optional parameters
  -v, --version     	show version and exit
  -l {info,debug}, --log {info,debug}
                        logging level

Pangolin Tuner анализирует данные CPU и RAM (в ГБ) со стенда, на котором он запущен. Если же утилита запускается не там, где установлена СУБД Pangolin, то можно указать дополнительные параметры, определяющие количество CPU и RAM на целевом стенде. У параметров, заданных через опцию -o, приоритет перед автоматически определёнными значениями. Помимо этого, утилита позволяет адаптировать результат под конкретную конфигурацию системы. Например, можно указать тип дискового пространства (dtype) или версию СУБД (dbmsver).

Пример запуска:

pangolin-tuner -c postgresql.conf -o cpu=12 -o mem=18 -o dtype=ssd -o dbmsver=6.5.0

Затем Pangolin Tuner использует специальные формулы для расчёта оптимальных значений параметров на основе профиля нагрузки. Результат можно сохранить в файл или вывести в консоль по умолчанию.

Пример запуска:

pangolin-tuner -c postgresql.conf -r postgresql_tuner.conf

Доступны два формата вывода результата. Формат ключ-значение используется по умолчанию, а разница между исходными данными и рекомендациями может быть получена с помощью команды:

pangolin-tuner -c postgresql.conf -d

Теперь узнаем, как формируются команды монстров для каждого профиля нагрузки.

На каждого монстра своя формула

Разберу ключевые настройки, которые затрагивает Pangolin Tuner. Каждая из них важна для оптимизации производительности базы данных, особенно в контексте выбранного профиля нагрузки.

shared_buffers — этот параметр задаёт объём оперативной памяти для кеширования данных. Чем больше памяти доступно, тем эффективнее система кеширует данные, и тем больше сокращается время доступа к диску. Рекомендации зависят от объёма доступной оперативной памяти для всех профилей:

• RAM <= 32GB: 25% RAM

• RAM > 32GB: 40% RAM

У этого параметра есть рекомендованный предел в 50 ГБ.

work_mem — определяет объём памяти, доступный для каждой отдельной операции сортировки или хеширования. Значение зависит от характера рабочей нагрузки:

• OLTP: 16MB 

• 1C: 256MB

• OLAP: минимум из двух величин — 512 MB или (RAM − shared_buffers) / 10 / 5

Этот параметр позволяет обеспечить быстрое выполнение сложных запросов, в том числе операции сортировки и объединения данных.

maintenance_work_mem — задаёт объём памяти для операций технического обслуживания, таких как VACUUM. Оптимизация этого параметра помогает поддерживать базу данных в хорошем состоянии и избежать избыточной фрагментации данных. Для всех профилей:

• RAM <= 8GB: 512MB

• 8GB < RAM <=  32GB: 1GB

• 32GB < RAM <=  64GB: 2GB

• 64GB < RAM: 4GB

effective_cache_size — позволяет оценить общий размер кеша операционной системы, что помогает СУБД Pangolin эффективно управлять памятью. Правильная настройка effective_cache_size улучшает производительность за счёт уменьшения количества обращений к медленному дисковому хранилищу. Для всех профилей:

• RAM - 2GB — shared_buffers

temp_buffers — этот параметр контролирует объём памяти, выделяемый для временных таблиц и временных файлов. Значения зависят от типа нагрузки и объёма доступной оперативной памяти. Для всех профилей:

• OLTP:

  • RAM <= 32GB: 8MB

  • RAM > 32GB: 16MB

• 1С:

  • RAM <= 32GB: 128MB

  • 32GB < RAM <= 128GB:  256МБ

  • RAM > 128GB: 512МБ

• OLAP:

  • RAM < 256GB:  32MB

  • RAM >= 256GB: 64MB

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

• OLTP: 1.1

• 1С: 1.1

• OLAP: 2.0

Правильное значение random_page_cost позволяет оптимизировать выполнение запросов, учитывая различия в скорости чтения страниц с дисков различного типа (SSD vs HDD).

from_collapse_limit — устанавливает предельное количество таблиц, которые можно объединить в одном подзапросе:

• OLTP: 8

• 1С: 11

• OLAP: 8

log_min_duration_statement — задаёт минимальное время выполнения запроса, необходимое для его записи в журнал: 

• OLTP: 3000

• 1С: 3000

• OLAP: 60000

Регулируя этот параметр, можно контролировать уровень детализации логирования, что полезно для анализа производительности и поиска узких мест.

wal_buffers — определяет объём памяти, выделяемый для буферов WAL:

• RAM <= 8GB: 16MB

• 8GB < RAM: 64MB

Правильно подобранное значение wal_buffers улучшает производительность операций записи, обеспечивая оптимальное использование ресурсов памяти.

effective_io_concurrency — задаёт максимальное количество одновременно выполняемых операций ввода-вывода. Для всех профилей:

• для машин с SSD: 200

• для машин с HDD: 10

max_worker_processes — определяет максимальное количество фоновых процессов, которые могут выполняться параллельно. Для всех профилей:

• CPU <= 12: 12

• CPU > 12: CPU

max_parallel_workers — контролирует максимальное количество параллельных рабочих процессов, задействованных в выполнении запросов:

• OLTP: max_worker_processes / 2

• 1C: max_worker_processes / 2

• OLAP: max_worker_processes * 4 / 5

Параллельная обработка запросов может существенно ускорить выполнение сложных аналитических задач, особенно в OLAP-сценариях.

max_parallel_workers_per_gather — указывает максимальное количество параллельных рабочих процессов для операции GATHER:

• OLTP: 2

• 1С: 4

• OLAP: 4 + max_parallel_workers / 8

Эта настройка особенно полезна для OLAP-нагрузки, когда запросы требуют интенсивной параллельной обработки данных.

max_files_per_process — устанавливает максимальное количество открытых файлов для одного процесса:

• OLTP: 1000

• 1С: 10000

• OLAP: 1000

max_locks_per_transaction — определяет максимальное количество блокировок, которое может удерживать одна транзакция:

• OLTP: 64

• 1С: 256

• OLAP: 64

Дополнительно

Ещё Pangolin Tuner даёт рекомендации по настройке autovacuum и других важных параметров. Например, подсказывает, как управлять планировщиком запросов для 1С, чтобы повысить производительность при работе с бизнес-приложениями.

Монстры в действии

Мы провели серию нагрузочных тестов, чтобы посмотреть, как Pangolin Tuner может сработать на благо производительности БД. Вот результаты.

OLTP (сценарий TPC-C)

Характеристики стенда:

• CPU: 4 ядра

• RAM: 64 ГБ

Скорость обработки транзакций (TPS) на стенде с использованием конфигурационного файла, оптимизированного Pangolin Tuner, — 1794. Без применения утилиты — 1355. Значительный прирост производительности. Система, настроенная с помощью Pangolin Tuner, способна обрабатывать больше транзакций, что делает её устойчивее к пиковым нагрузкам и ускоряет обработку запросов.

OLAP (сценарий TPC-H)

Характеристики стенда:

• CPU: 4 ядра

• RAM: 64 ГБ

При обработке сложных аналитических запросов (TPS) с использованием конфигурационного файла, настроенного через Pangolin Tuner, скорость выросла до 4,12. Этот же показатель без использования утилиты — 3,07. Система существенно быстрее реагирует на запросы, а значит, у нас улучшается эффективность анализа данных и быстрее формируются результаты.

1C (сценарий APDEX)

Характеристики стенда:

• CPU: 176 ядер

• RAM: 2 ТБ

Тестирование в подсистеме «Тест-центр» с использованием трёхчасового сценария нагрузочного тестирования «Документооборот» для 1000 рабочих мест показало значительное улучшение производительности. С использованием конфигурационного файла, обработанного Pangolin Tuner, показатель APDEX достиг отметки 0,916, что подтверждает повышение стабильности и быстродействия системы управления предприятием. Это говорит о снижении задержек, что положительно сказывается на общей эффективности работы системы.

И на этом всё?

Монстры будущего. Дальше... Страшнее!

Самое интересное впереди! Что планируем ещё сделать?

• Добавить новые профили нагрузки — те, что подходят для веб-приложений и смешанных нагрузок.

• Улучшить алгоритмы расчёта параметров.

• Сделать интеграцию с системами мониторинга, чтобы Pangolin Tuner мог учитывать нагрузку системы в расчётах.

• Создать веб-сайт для расчётов с учётом различных схем развёртывания.

• Интегрировать ИИ в Pangolin Tuner. Планируем обучить модель ИИ на образцовых конфигурационных файлах с ограниченным набором параметров. В результате ИИ, взаимодействуя с существующей функциональностью, должен предлагать оптимальные настройки, проанализировав данные о производительности, схему развёртывания и профиль нагрузки.

Благодарю за интерес к статье. О том, как мы в СУБД Pangolin развиваем разные инструменты, мы пишем в сообществе команды, там же появляются наши вакансии и другие полезные активности, присоединяйтесь!

Если у вас есть интересные истории или советы по настройке, которыми хочется поделиться, — пишите их в комментариях! Кто знает, вдруг именно ваш кейс войдёт в историю по укрощению монстров баз данных!