Сайт
/
Блог
/
Производительность PostgreSQL

Производительность PostgreSQL: мониторинг и улучшение

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

А если вам необходимо узнать, что вся система в порядке, запишитесь на аудит серверной инфраструктуры

Исследование проблем с производительностью

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

Ключевые показатели эффективности. Регулярный мониторинг использования ЦП, памяти, операций ввода-вывода и дискового пространства особенно важен, и инструменты htop, iostat, и vmstat позволяют получать информацию в режиме реального времени.

На скрине ниже, htop отображает полный список всех запущенных процессов и деталей: PID, пользователь, приоритет, потребление памяти и загрузка ЦП.

htop

Специфический мониторинг PostgreSQL. Вы можете полагаться на внутренний сбор статистики PostgreSQL, в этом вопросе освещаются два важных аспекта: pg_stat_activity (или pg_activity) и pg_stat_statements.

Вы можете использовать pg_activity или запрос ниже — это поможет определить активные запросы к базе и кто их выполняет, выявить долгие или застрявшие запросы.

SELECT pid, datname, usename, query, state
FROM pg_stat_activity
WHERE state = 'active';

А pg_stat_statements отобразит 5 самых популярных запросов с наибольшим временем выполнения — так вы найдете потенциально неэффективные запросы, которые нужно оптимизировать.

SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Анализ производительности запросов

Методы, которые можно использовать для анализа проблем с производительностью PostgreSQL.

Анализ журналов. Настройка PostgreSQL для регистрации медленных запросов критически важна. Для этого вам просто нужно найти файл postgresql.conf и отредактировать строку log_min_duration_statement как показано ниже:

log_min_duration_statement = 1000  # MS

Важно! Не забудьте сохранить и перезапустить Postgres сервер.

EXPLAIN ANALYZE. Эта команда спасает жизнь, когда нужно разобраться в производительности запросов.

EXPLAIN ANALYZE SELECT * FROM readers WHERE last_login > CURRENT_DATE - INTERVAL '1 year';

Использования лишь команды EXPLAIN может быть достаточно, чтобы получить пояснение по запросу, но рекомендуется использовать ANALYZE для получения фактической статистики выполнения.

Пожалуйста, имейте в виду, что выполнение ANALYZE запроса на производственной базе данных с DELETE запросом — все равно что играть со взрывчаткой.

Seq Scan on readers  (cost=0.00..1234.56 rows=4321 width=104)
  Filter: (last_login > (CURRENT_DATE - '1 year'::interval))

План указывает на неэффективность для больших наборов данных и подчеркивает важность индексации, поскольку эффективность фильтра зависит от того, сколько строк приходится на последний год.

Профилирование загрузки базы данных

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

Вы можете установить его с официального сайта или через менеджер пакетов, например brew.

brew install pgbadger

Следующим шагом будет изменение PostgreSQL конфигурации в файле postgresql.conf:

log_destination = 'stderr'
logging_collector = on
log_statement = 'all'
log_duration = on
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_min_duration_statement = 0

Важно! Не забудьте сохранить и перезагрузить конфигурацию с помощью pg_reload_conf()

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

pgbadger /path/postgresql.log -o /path/output.html

pgbadger

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

Методы оптимизации производительности

Индексирование. Для большинства людей, которые работают над повышением производительности баз данных, этот шаг идет сразу после использования EXPLAIN ANALYZE, до такой степени, что это становится мемом.

База данных в данном случае — это книга, а индекс служит руководством для эффективного доступа к конкретным записям без необходимости сканирования всего набора данных.

Индекс значительно сокращает время, необходимое для поиска, сортировки и фильтрации.

Правильный тип индекса.

B-tree: обычно используется при упорядочивании данных на основе определенного столбца.

CREATE INDEX readers_name_index ON readers(name);

Добавление индекса B-tree в name столбец таблицы readers позволит оптимизировать запросы, которые сортируют или выполняют поиск по именам читателей.

Hash: для ускорения обработки данных на основе проверок на равенство.

CREATE INDEX books_id_index ON books USING HASH (books_id);

Добавление хэш-индекса в столбец books_id таблицы books оптимизировало бы запросы, которые включают условие равенства, например SELECT * FROM books, WHERE books_id = 28

GIN: обычно используется для полнотекстового поиска или операций со сложными данными, такими как массивы, JSON и т.д.

CREATE INDEX attachment_content_index ON attachments USING GIN (to_tsvector('english', content));

Добавление индекса GIN в столбец content таблицы attachments оптимизировало бы полнотекстовый поиск с использованием функции to_tsvector для текста на английском языке.

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

Чтобы определить раздувание индекса, мы можем просто положиться на pg_stat_user_indexes, например, мы можем получить индексы > 1 МБ, отсортированные по размеру.

SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(i.indexrelid)) AS size, 
       idx_scan as number_of_scans 
FROM pg_stat_user_indexes JOIN pg_index i ON i.indexrelid = indexrelid
WHERE pg_relation_size(i.indexrelid) > '1MB'::bigint 
ORDER BY pg_relation_size(i.indexrelid) DESC;

Далее — перестроение индексов с помощью команды REINDEX:

REINDEX INDEX CONCURRENTLY index_name;

Важно! Переиндексация может заблокировать таблицы, поэтому крайне важно запланировать время простоя для обслуживания. Кстати, команда CONCURRENTLY сокращает время простоя.

Оптимизация запросов

Написание эффективного SQL. Выберите определенные столбцы вместо SELECT *, указав только те столбцы, которые необходимы.

SELECT name, address, phone FROM readers;

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

SELECT client.name, reservations.create_date FROM client INNER JOIN reservations ON client.id = reservations.client_id;

Расширенные возможности SQL.

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

CREATE TABLE reservations (reservation_id int NOT NULL,
                           reservation_date date NOT NULL,
                           amount decimal)
PARTITION BY RANGE (reservation_date);

Параллельное выполнение запроса: увеличение параметра max_parallel_workers_per_gather предоставляет запросу big_big_table право использовать до 7 ядер процессора, что приводит к увеличению времени выполнения крупномасштабных данных.

SET max_parallel_workers_per_gather TO 7;
SELECT blah, blah, blah FROM big_big_table WHERE whatever_conditions;

Настройка конфигурации базы данных

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

Рекомендуемые значения:

shared_buffers: около 25% доступной оперативной памяти.

work_mem: 4 МБ ~ 16 МБ на активный запрос.

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

Уборка и автоуборка. Давайте рассмотрим базу данных бронирований, в которой клиенты обрабатывают и отменяют бронирования ежедневно. Без очистки база со временем накапливала бы отмененные бронирования, что приведет к неэффективности и замедлению запросов.

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

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

VACUUM FULL a_specific_table;

Ручной анализ: для обновления статистики вы можете добавить ANALYZE к предыдущей команде или использовать ее отдельно, как показано ниже:

ANALYZE your_table;

Конфигурация автоочистки: настройка параметров в файле postgresql.conf включает автоочистку, настраивает пороговые значения для очистки и анализа, а также ограничивает затраты на очистку.

autovacuum = on 
autovacuum_vacuum_scale_factor = 0,2 
autovacuum_analyze_scale_factor = 0,1 
autovacuum_vacuum_cost_limit = 800

Предлагаем курс от Слёрм — по работе с СУБД PostgreSQL и оптимизации SQL-запросов. Научитесь построению репликаций, работе с резервным копированием и организации мониторинга всей системы.

PostgreSQL База

Узнать больше