Рекомендации по поиску проблем и тонкой настройке PostgreSQL

pg_stat_database

datname - имя базы данных или NULL для общих объектов,
numbackends - количество подключений к базе данных или NULL для общих объектов (текущее значение),
xact_commit - количество зафиксированных транзакций (нарастающим итогом),
xact_rollback - количество отменённых транзакций (нарастающим итогом),
blks_read - количество блоков, прочитанных с диска (нарастающим итогом),
blks_hit - количество блоков, найденных в кэше (нарастающим итогом),
tup_returned - количество строк, возвращённых запросом (нарастающим итогом),
tup_fetched - количество строк, переданных по сети (нарастающим итогом),
tup_inserted - количество вставленных строк (нарастающим итогом),
tup_updated - количество обновлённых строк (нарастающим итогом),
tup_deleted - количество удалённых строк (нарастающим итогом),
conflicts - количество запросов, отменённых из-за конфликта при восстановлении базы данных (нарастающим итогом, только для запасных серверов),
temp_files - количество созданных временных файлов (нарастающим итогом),
temp_bytes - объём данных, записанных во временные файлы (нарастающим итогом),
deadlocks - количество взаимоблокировок (нарастающим итогом),
blk_read_time - время, потраченное на чтение блоков данных (нарастающим итогом, если включена опция track_io_timing или 0),
blk_write_time - время, потраченное на запись блоков данных (нарастающим итогом, если включена опция track_io_timing или 0),
stats_reset - отметка времени последнего сброса статистики.

blks_hit, blks_read

При соотношении данных, прочитанных из кэша к общему объёму прочитанных данных blks_hit / (blks_hit + blks_read) ниже 0.8 стоит увеличить размер shared_buffers в файле конфигурации postgresql.conf.

xact_commit, xact_rollback

При низком соотношений подтверждённых транзакций к их общему количеству xact_commit / (xact_rollback + xact_commit) стоит проанализировать отменённые транзакции в журнале PostgreSQL.

deadlocks

Если взаимоблокировки происходят часто, то имеются ошибки в проектировании приложения. Для поиска проблем можно включить опцию в log_lock_waits в файле конфигурации postgresql.conf.

temp_files, temp_bytes

При частом создании временных файлов и/или большом их размере стоит включить опцию log_temp_files для обнаружения запросов, приведших к созданию временных файлов. Можно также увеличить размер work_mem в файле конфигурации postgresql.conf.

pg_stat_all_tables, pg_stat_user_tables и g_stat_sys_tables

relname - имя таблицы,
seq_scan - количество последовательных сканирований таблицы,
seq_tup_read - количество строк, прочитанных при последовательном сканировании,
idx_scan - количество сканированией таблицы с использованием индекса,
idx_tup_fetch - количество строк, прочитанных при сканировании с использованием индекса,
n_tup_ins - количество вставленных строк,
n_tup_upd - количество обновлённых строк, включая обновлённые на месте,
n_tup_del - количество удалённых строк,
n_tup_hot_upd - количество строк, обновлённых на месте,
n_live_tup - расчётное количество живых строк,
n_dead_tup - расчётное количество мёртвых строк,
n_mod_since_analyze - расчётное количество изменённых строк после операции анализа таблицы,
n_ins_since_vacuum - расчётное количество вставленных строк после операции сжатия таблицы,
last_vacuum - отметка времени последней операции ручного сжатия, исключая полный,
last_autovacuum - отметка времени последней операции автоматического сжатия,
last_analyze - отметка времени последней операции ручного сжатия,
last_autoanalyze - отметка времени последней операции автоматического сжатия,
vacuum_count - количество ручных операций сжатия, исключая полный,
autovacuum_count - количество автоматических операций сжатия,
analyze_count - количество ручных операций анализа,
autoanalyze_count - количество автоматических операций анализа.

n_tup_upd, n_tup_hot_upd

При обновлении строк PostgreSQL создаёт копию строки с новыми данными, а прежнюю строку помечает как устаревшую. Устаревшие версии строк могут использоваться внутри ещё не зафиксированных транзакций с уровнем REPEATABLE READ. Когда устаревшая строка более не используется ни в одной транзакции, занимаемое ей место можно отметить как свободное и записать на это место новую строку. Для экономии места на диске PostgreSQL периодически выполняет процедуру автоматического сжатия таблицы (так называемый VACUUM), собирая данные из нескольких разрежённых страниц в одну и уменьшая размер файлов на диске за счёт освободившихся страниц.

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

Если соотношение строк, обновлённых в пределах страницы, к обновлённым строкам n_tup_hot_upd / n_tup_upd низкое, то стоит уменьшить процент заполнения страниц таблицы при помощи запросов следующиего вида:

ALTER TABLE mytable SET (fillfactor = 70);

Это запрос повлияет только на заполнение новых страниц таблицы. Чтобы во всех страницах таблицы появилось свободное место, которое позволило бы чаще обновлять данные внутри страницы, можно воспользоваться запросом:

VACUUM FULL mytable;