Эта одна из публикаций цикла конспектов по 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_time, query
FROM pg_stat_statements
ORDER BY total_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 при использовании многопоточной обработки запросов.