PostgreSQL. Часть 7. Производительность и конкурентный доступ PostgreSQL

В моем блоге уже был цикл статей с конспектом по MySQL. Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто мои заметки – конспектировал что-то новое и то, что я посчитал интересным и важным. Эта публикация некий конспект про производительность и конкурентный доступ PostgreSQL.

Список литературы

При подготовке этого конспекта я использовал следующие материалы:

1. Книга PostgreSQL 14 Administration Cookbook.

2. Курс DBA1: Администрирование PostgreSQL 13. Базовый курс.

Производительность и конкурентный доступ

Поиск медленных запросов

1. Проверить, что расширение pg_stat_statements установлено:

\x
\dx pg_stat_statements

2. Если расширение установлено, то должна отобразиться соответствующая запись:

-[ RECORD 1 ]--------------------------------------------
-----------
Name
| pg_stat_statements
Version
| 1.9
Schema
| public
Description | track execution statistics of all SQL
statements executed

3. 10 самых нагруженных запросов. Пример запроса:

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

Параметр для записи медленных запросов в журнал. Все запросы, выполняющие более 10 секунд будут логироваться:

ALTER SYSTEM SET log_min_duration_statement = 10000;

Выяснение того, что замедляет запросы

Потенциальные проблемы с теми запросами, которые выполняются медленно:

• Запрос возвращает слишком много данных.
• Запросу приходится обрабатывать слишком много данных.
• Отсутствие нужных индексов.
• Неправильная оценка плана выполнения сервером PostgreSQL. Например, в тех ситуациях, когда статистика для базы данных не обновлялась (отключена автоматическая очистка).
• Проблемы взаимоблокировок.
• Недостаточная производительность дисковой подсистемы, CPU, малое количество оперативной памяти на сервере или высокая нагрузка на сетевую подсистему.

Пример запроса плана выполнения запроса:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM t;

Другие примеры использования запроса для отображения плана обслуживания.

Уменьшение выводимого количества строк

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

SELECT * FROM t TABLESAMPLE SYSTEM (0.1);

Критерии сложности запросов

Считать запрос сложны только из-за того, что он возвращает много результирующих строк не совсем правильно. В определении критериев сложности запроса, пожалуй, может помочь инструкция EXPLAIN. Если в результате анализа плана выполнения запроса через EXPLAIN получается очень много строк, соединений и других вызовов, то запрос, действительно, можно считать сложным.

Если запрос, на ваш взгляд, выглядит сложным из-за количества текста, который формирует его, но при анализе плана выполнения через инструкцию EXPLAIN получается не много шагов, то запрос стоит отнести к несложным запросам.

Оптимизация запросов без необходимости их переписывать

Один из вариантов проверки причин, по которым запрос работает медленно – это выявление недостатка ресурсов или установленных значений конфигурационных параметров. Например, увеличение объема используемой памяти.

Определить текущее значение для параметра work_mem:

SHOW work_mem;

Затем выполнить анализ времени выполнения запроса:

explain select * from t;

Установить более подходящий параметр:

SET work_mem = '128MB';

Затем повторно выполнить анализ времени выполнения запроса:

explain select * from t;

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

Если увеличение значения какого-то параметра не помогло, то можно вернуть его значение к значение по умолчанию:

RESET work_mem;

Почему запрос не использует индекс

Сначала стоит проанализировать таблицу:

ANALYZE;

После анализа таблицы проверить запрос еще раз.

Сравнить время выполнения и план выполнения без индекса:

EXPLAIN ANALYZE SELECT count(*) FROM itable WHERE id > 500;

Сравнить время выполнения и план выполнения при использовании индекса:

SET enable_seqscan TO false;
EXPLAIN ANALYZE SELECT count(*) FROM itable WHERE id > 500;

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

Принудительное использование индекса

Время выполнения запроса можно потенциально уменьшить при использовании параллельной его обработки. Запрос будет “разбит” сервером на несколько частей и каждая из этих частей будет выполняться в своем отдельном потоке. Однако, это увеличивает количество используемых ресурсов.

Сначала выполнил запрос в один поток

\timing
SET max_parallel_workers_per_gather = 0;

Выполним запрос и зафиксируем время его выполнения:

SELECT count(*) FROM big;
count
---------
1000000
(1 row)
Time: 46.399 ms

Теперь увеличим количество потоков:

SET max_parallel_workers_per_gather = 2;

Повторно выполним запрос:

SELECT count(*) FROM big;
count
---------
1000000
(1 row)
Time: 25.009 ms

При использовании нескольких потоков запрос завершился быстрее. Но важно вести наблюдение за общей нагрузкой на сервер PostgreSQL при использовании многопотоковой обработки запросов.

Другие связанные статьи

PostgreSQL. Часть 1. Информационные запросы PostgreSQL

PostgreSQL. Часть 2. Нюансы настройки сервера PostgreSQL

PostgreSQL. Часть 3. Управление сервером PostgreSQL

PostgreSQL. Часть 4. Безопасность в PostgreSQL

PostgreSQL. Часть 5. Мониторинг и диагностика PostgreSQL

PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL

PostgreSQL. Часть 8. Резервное копирование и восстановление PostgreSQL