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