Обнаружение медленных запросов В БД
Даже, когда база данных работает в сочетании с самым современным аппаратным обеспечением, некоторые запросы могут значительно снижать производительность системы. Чтобы оптимизировать скорость работы БД можно использовать техники для выявления и оптимизации SQL-запросов. В данном разделе приведены рекомендации по выявлению ожиданий и медленных запросов в БД Microsoft SQL Server Management Studio и PostgreSQL.
Выявление ожиданий
Для выявления проблем с производительностью Microsoft SQL Server Management Studio используется методология выявления ожиданий. SQL Server автоматически распределяет потоки в очередь на выполнение. Microsoft SQL Server Management Studio Server сохраняет информацию о потоках, ожидающих в очереди. Администратор БД может запросить эту информацию при помощи утилиты DVTrace и использовать её для выявления проблем производительности.
SQL Server постоянно отслеживают ожидания, которые создают очереди на получение доступа к ресурсам. Ожидания не всегда являются проблемой, их нужно правильно интерпретировать.
Для лучшего понимания и за списком наиболее часто встречаемых ожиданий рекомендуем обратиться к следующим статьям:
-
Ожидания в Microsoft SQL Server Management Studio — habr.com,
-
Ожидания в PostgreSQL — simply.name.
Выявление медленных запросов в Microsoft SQL Server Management Studio
В статье на docs.microsoft.com предложен метод для определения запросов, с наивысшим числом логических операций чтения.
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESCДанный запрос предоставит синтаксис и статистику 20 самых требовательных запросах из кэшированного плана выполнения. Использовать запрос может быть удобно, когда требуется быстро определить запросы, которые загружают сервер БД больше всего. Недостаток использования этого запроса в том, что он полагается на кэш запросов. Это значит, что любой запрос, который не был кэширован, в выдачу не попадёт.
После выявления медленных запросов можно заняться оптимизацией их работы. В упомянутой выше статье автор предлагает использовать индексы. Индексы — один из основных способов повышения производительности БД. Чтобы больше узнать об индексах, ознакомьтесь с оригинальной англоязычной статьёй на docs.microsoft.com. Чтобы узнать больше о том, как индексы помогают повысить производительность БД, можно также прочитать вот эту статью на habr.com.
Выявление медленных запросов в PostgreSQL
Включение журнала медленных запросов
Самый простой способ выявить медленные запросы в PostgreSQL — посмотреть журнал медленных запросов. По умолчанию журнал медленных запросов не ведётся, но его можно включить. Есть два способа включения: глобально — путём редактирования postgresql.conf и локально — для определённого пользователя или БД.
postgresql.conf измените следующую строчку:log_min_duration_statement = 5000
При такой конфигурации в журнал будут записаны все запросы, на выполнение которых требуется больше 5 секунд. После изменения конфигурационного файла необходимо будет перезапустить сервер БД:
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)Если необходимо сделать изменения только для одной БД или пользователя, используйте следующий запрос:
postgres=# ALTER DATABASE test SET log_min_duration_statement = 5000;
ALTER DATABASEТеперь каждый запрос, требующий на выполнение больше 5 секунд будет записан в журнал. Чтобы проанализировать медленный запрос можно запустить команду, которая выполнит запрос и предоставит план его выполнения:
explain analyzeПреимущество такого метода в его скорости — можно моментально определить наиболее медленные запросы. Однако, когда производительность БД ухудшается не несколькими медленными запросами, а большим количеством быстрых, такой подход окажется бесполезным.
Использование pg_stat_statements
Применение pg_stat_statements позволяет объединить одинаковые запросы с разными параметрами в группу, собрать и представить общую статистику выполнения для группы запросов.
postgresql.conf следующую строку и перезапустите сервер БД:shared_preload_libraries = 'pg_stat_statements'Выполните запрос:
CREATE EXTENSION pg_stat_statementsНа экран будет выведена примерно следующая таблица:
test=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
test=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
queryid | bigint | | |
query | text | | |
calls | bigint | | |
total_time | double precision | | |
min_time | double precision | | |
max_time | double precision | | |
mean_time | double precision | | |
stddev_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |Таблица поможет понять, как часто выполнялся тот или иной запрос, общее время выполнения запросов такого типа и распределение времени выполнения между ними.
Данный метод позволяет выявить большое количество быстрых запросов, которые могут замедлять работу сервера БД. Недостаток метода в том, что он не даёт возможности быстро определить обычно быстрые запросы, которые иногда выполняются медленно.
Подробнее ознакомиться с методами выявления медленных запросов в PostgreSQL можно в статье Hans-Jürgen Schönig (анг.), с переводом статьи можно ознакомится на habr.com. Подробнее о выявлении медленных запросов при помощи pg_stat_statements в другой статье того же автора.
НАШЛИ ОШИБКУ?