Как провести бенчмаркинг PostgreSQL для достижения оптимальной производительности

С увеличением принятия PostgreSQL администраторы баз данных (DBA) и разработчики часто нуждаются в оценке его производительности для обеспечения эффективной работы своих приложений под различными нагрузками. Бенчмаркинг производительности — это критически важный процесс, который измеряет, насколько хорошо PostgreSQL справляется с изменяющимися нагрузками, помогая выявлять узкие места и области для оптимизации. Эта статья рассматривает инструменты, метрики и тестовые сценарии, чтобы помочь вам провести бенчмаркинг PostgreSQL на профессиональном уровне.

Зачем проводить бенчмаркинг PostgreSQL?

Бенчмаркинг позволяет вам:

1. Измерить производительность и задержку вашей базы данных под конкретными нагрузками.
2. Выявить узкие места в аппаратной части или конфигурации.
3. Сравнить влияние оптимизаций, таких как изменения индексов или перезапись запросов.
4. Симулировать реальные сценарии, такие как высокая.concurrentная активность пользователей или массовые записи данных.

Ключевые метрики для отслеживания

При проведении бенчмаркинга PostgreSQL сосредоточьтесь на этих метриках:

• TPS (Транзакции в секунду): Измеряет, сколько транзакций база данных выполняет за секунду.
• IOPS (Операции ввода-вывода в секунду): Отслеживает активность диска.
• Задержка: Измеряет время выполнения запросов, что влияет на опыт пользователя.
• Использование ресурсов: Отслеживает использование ЦП, памяти и диска во время бенчмаркинга.

Инструменты для бенчмаркинга PostgreSQL

1. pgbench

Что такое pgbench? 

pgbench — это встроенный инструмент для тестирования производительности PostgreSQL. Он имитирует одновременных клиентов, выполняющих транзакции, и измеряет производительность базы данных.

Установка

Он поставляется вместе с установкой PostgreSQL. Для проверки запустите:

bash
pgbench --version

Начало работы

1. Инициализируйте базу данных для тестирования:

bash
pgbench -i -s 50 mydb

Здесь -s задает коэффициент масштабирования, который определяет размер набора данных.

2. Запустите простой тест:

bash
pgbench -c 10 -j 2 -T 60 mydb

• -c 10: Количество клиентских соединений.
• -j 2: Количество потоков.
• -T 60: Продолжительность теста в секундах.

Пример вывода:

transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 10
number of threads: 2
duration: 60 s
tps = 1420.123 (excluding connections establishing)

2. Sysbench

Почему использовать Sysbench? 

Sysbench — это универсальный инструмент для тестирования производительности баз данных и систем. Он предлагает больше гибкости, чем pgbench, для пользовательских рабочих нагрузок.

Установка

Установите Sysbench с помощью следующей команды:

bash
sudo apt-get install sysbench

Начало работы

1. Подготовьте тест:

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --tables=10 --table-size=1000000 \
  oltp_read_write prepare

2. Запустите тест:

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --threads=4 \
  --time=60 oltp_read_write run

3. pg_stat_statements

Что такое pg_stat_statements?

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

Настройка

1. Включите расширение в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

2. Перезагрузите конфигурацию и создайте расширение:

CREATE EXTENSION pg_stat_statements;

Использование

Запустите следующий запрос для выявления долго выполняемых операторов:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

Сценарии тестирования

Ниже представлено визуальное отображение результатов тестирования для трех сценариев: с высокой нагрузкой на чтение, с высокой нагрузкой на запись и смешанные рабочие нагрузки. Диаграмма транзакций в секунду (TPS) демонстрирует способность PostgreSQL эффективно обрабатывать параллельные транзакции, а диаграмма латентности иллюстрирует время выполнения запросов в миллисекундах.

Транзакции PostgreSQL в секунду (TPS)

Латентность запросов PostgreSQL

Типы рабочих нагрузок

1. Рабочие нагрузки с высокой нагрузкой на чтение

Цель: Тестирование производительности базы данных при высокой активности чтения.

Настройка: Используйте pgbench с транзакциями только для чтения по умолчанию:

bash
pgbench -c 50 -T 120 -S mydb

• -S: Выполнять только запросы SELECT.
• -c 50: Симулировать 50 параллельных клиентов.

2. Рабочие нагрузки с высокой нагрузкой на запись

Цель: Измерение производительности базы данных при частых вставках или обновлениях.

Настройка: Измените тест для включения операций записи:

bash
pgbench -c 20 -j 4 -T 120 -N mydb

• -N: Выполнять запросы, отличные от SELECT.

3. Смешанные рабочие нагрузки на чтение и запись

Цель: Симулировать реальную рабочую нагрузку, сочетающую чтение и запись.

Настройка: Используйте сбалансированную конфигурацию:

bash
pgbench -c 30 -j 4 -T 180 mydb

Оптимизация PostgreSQL для лучших результатов тестирования

Настройка параметров памяти

Отрегулируйте эти параметры в postgresql.conf:

shared_buffers = 25% of system memory
work_mem = 4MB
maintenance_work_mem = 64MB

Включить параллельное выполнение запросов

Отрегулируйте эти параметры в postgresql.conf:

max_parallel_workers_per_gather = 4

Оптимизация дискового ввода-вывода

Используйте SSD для файлов WAL и настройте эти параметры:

wal_buffers = 16MB
synchronous_commit = off

Примеры результатов и их интерпретация

Сценарий: 50 одновременных клиентов выполняют рабочую нагрузку с преобладанием чтения в течение 60 секунд.

Вывод:

tps = 2500.456 (excluding connections establishing)

Интерпретация: База данных способна обрабатывать 2500 транзакций в секунду при такой рабочей нагрузке.

Если TPS ниже ожидаемого, проанализируйте планы запросов с помощью EXPLAIN ANALYZE, чтобы выявить узкие места в производительности.

Заключение

Бенчмаркинг PostgreSQL — это мощный способ выявления ограничений по производительности и оптимизации базы данных для различных рабочих нагрузок. Инструменты, такие как pgbench и sysbench, в сочетании с данными из pg_stat_statements, позволяют вам моделировать реальные сценарии и точно настраивать конфигурации PostgreSQL.

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