PostgreSQL: аналитика для DBA
Автор: Иван Золотухин
Многие пользователи СУБД PostgreSQL знают, что сервер во время своей работы собирает разнообразную статистику, но не все знают, что ее полезно анализировать и как ее извлекать для этого. В этом небольшом тулките собраны несколько полезных запросов, дающих некоторое представление о том, как использовать это "скрытое знание", которое постоянно копится. Эти запросы можно использовать для мониторинга состояния PostgreSQL (ручного или с помощью плагинов для систем мониторинга вроде Nagios, Cacti или Zabbix), для поиска узких мест в работе сервера и многих других подобных задач. Помните, что это лишь верхушка айсберга; в документации можно найти описания нескольких десятков системных представлений, которые также могут быть полезны администратору PostgreSQL.
Для корректной работы тулкита необходимо включить опции stats_block_level и stats_row_level в postgresql.conf, а также настроить параметр stats_reset_on_server_start по своему усмотрению. Если при каждом перезапуске сервера PostgreSQL вы меняете какие-то существенные параметры его конфигурации, имеет смысл обнулять статистику, чтобы отслеживать эффект внесенных изменений. Если же вас интересует долгосрочная перспектива и рестарт производится не вследствие изменения конфигурации PostgreSQL, ставьте параметрstats_reset_on_server_start в значение off.
Отношение hit / read
При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет -- делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.
SELECT
datname,
CASE
WHEN blks_read = 0 THEN 0
ELSE blks_hit / blks_read
END AS ratio
FROM
pg_stat_database;
Количество модификаций, произошедших в таблице
Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;
Статистика seq scan / index scan
Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных.
Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.
SELECT
relname,
seq_scan,
idx_scan,
CASE
WHEN idx_scan = 0 THEN 100
ELSE seq_scan / idx_scan
END AS ratio
FROM
pg_stat_user_tables
ORDER BY
ratio DESC;
Статистика по индексам
Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.
SELECT indexrelname, idx_tup_read, idx_tup_fetch, (idx_tup_read - idx_tup_fetch), CASE WHEN idx_tup_read = 0 THEN 0 ELSE (idx_tup_read::float4 - idx_tup_fetch) / idx_tup_read END as r FROM pg_stat_user_indexes ORDER BY r desc;
Выполняющиеся запросы с их продолжительностью
Простой список выполняющихся сервером в настоящий момент запросов. Бывает полезен, когда вы недостаточно хорошо знаете систему или просто не успели ее настроить -- с его помощью можно найти и прервать "плохой" запрос, который мешает работе сервера (колонка procpid содержит PID процесса, которому можно сделать kill при необходимости). Помните, однако, что простой периодический просмотр выполняющхся запросов ни в коем случае не заменит замечательный анализатор логов pgFouine. Также не стоит забывать, что процесс, в котором вы выполняете данный запрос, тоже попадает в результирующий список.
SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity ORDER BY duration DESC;
Список текущих блокировок
Список текущих блокировок с указанием типа блокировки, таблицы и базы данных, на которой она выставлена и номера транзакции, которая выставила блокировку. Не стоит пугаться, если запрос выдает длинный список lock-ов -- не все они являются критическими и блокирующими таблицу от всех возможных изменений и даже чтения. Для анализа списка блокировок обязательно стоит ознакомиться с документацией об их типах в PostgreSQL и о том, когда и какими запросами они выставляются. Один из распространенных случаев, когда список блокировок может пригодиться: выполняя команду ps aux | grep ^postgres вы видите, что в статусе одного из процессов postgres-а написано waiting -- это как раз и означает, что данный процесс ждет, когда будет снята нужная ему блокировка. Какая именно -- выясняйте, запустив данный запрос.
SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid FROM pg_locks AS l LEFT JOIN pg_database AS d ON l.database= d.oid LEFT JOIN pg_class AS c ON l.relation = c.oid;
Автор выражает благодарность Максиму Богуку за ряд ценных замечаний.
Any feedback is welcome at iz at sai dot msu dot ru
Sep 2007