PostgreSQL собирает статистику с помощью фонового процесса «stats collector«, эта статистика может понадобится для анализа работы сервера.
Теория
В этой статье разберем системные представления, с помощью которых можно смотреть на эту статистику.
Статистика PostgreSQL включается конфигурационными параметрами в файле postgresql.conf, например:
- track_counts — обращения к таблицам и индексам. Этот параметр включен по умолчанию.
- track_io_timing — статистика операций ввода/вывода. Этот параметр выключен по умолчанию, в результате нужно включать. Но следует учитывать что включение этого параметра может замедлить сервер;
- track_functions — статистика вызовов функций и времени их выполнения. Этот параметр, тоже выключен по умолчанию. Если мы захотим включить его, то можем указать следующие значения:
- pl — включает отслеживание функций только на процедурном языке,
- all — включает отслеживание функций на всех языках, например SQL и C.
Каждый backend процесс собирает статистику в процессе своей работы. Затем эта статистика отправляется процессу stats collector, который собирает статистику со всех backend процессов. Раз в полсекунды, статистика сбрасывается в каталог $PGDATA/pg_stat_tmp, этот период (500мс) можно изменить только при компиляции. В результате эту статистику можно посмотреть с помощью представлений и функций.
При остановке сервера PostgreSQL, статистика сбрасывается в другой каталог — $PGDATA/pg_stat.
Статистика PostgreSQL ведется с момента первого запуска сервера, а с помощью функции pg_stat_reset() её можно сбросить. Другими словами обнулить все счетчики. Но это обнулит не все счетчики а только в текущей базе данных.
На уровне всего кластера обнулить счетчики можно с помощью функции pg_stat_reset_shared (). Аргумент может принимать значения bgwriter и archiver, с которыми обнуляются все счётчики в представлении pg_stat_bgwriter или pg_stat_archiver.
Статистику можем смотреть в следующих представлениях:
- pg_stat_all_tables — в разрезе строк и страниц для определённой базы данных;
- pg_statio_all_tables — в разрезе 8 KB страниц для определённой базы данных;
- pg_stat_all_indexes — по индексам для определённой базы в разрезе строк;
- pg_statio_all_indexes — по индексам для определённой базы в разрезе страниц;
- pg_stat_database — глобальная статистика по определённой базе данных;
- pg_stat_bgwriter — статистика для анализа фоновой записи.
Практика
Утилита pgbench
В PostgreSQL есть специальная утилита pgbench. С помощью которой можно произвести нагрузочное тестирование. Команда pgbench -i <база данных> создаст 4 таблицы pgbench_accounts, pgbench_branches, pgbench_history и pgbench_tellers. Предварительно уничтожит существующие таблицы с этими именами.
Таким образом можем запустить нагрузочное тестирование на 10 секунд pgbench -T 10 <имя базы данных>.
Подготовка базы для нагрузочного тестирования и анализа
Во-первых, включим сбор статистики ввода/вывода и выполнения функций:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# ALTER SYSTEM SET track_io_timing=on; ALTER SYSTEM Time: 0,733 ms postgres@postgres=# ALTER SYSTEM SET track_functions='all'; ALTER SYSTEM Time: 0,731 ms postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) Time: 1,664 ms
Дальше создадим базу данных admin_monitoring и с помощью специальной утилиты pgbench заполним её:
postgres@postgres=# CREATE DATABASE admin_monitoring; CREATE DATABASE Time: 101,196 ms postgres@postgres=# \q postgres@s-pg13:~$ pgbench -i admin_monitoring dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.25 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.16 s, vacuum 0.05 s, primary keys 0.04 s).
Следующим шагом сбросим все накопленные ранее счетчики статистики. Во-первых, статистику для текущей базы сбросим с помощью функции pg_stat_reset(). А во-вторых, с помощью функции pg_stat_reset_shared() очистим глобальную статистику записи на диск:
postgres@s-pg13:~$ psql -d admin_monitoring
Timing is on.
psql (13.3)
Type "help" for help.
postgres@admin_monitoring=# SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)
Time: 1,033 ms
postgres@admin_monitoring=# SELECT pg_stat_reset_shared('bgwriter');
pg_stat_reset_shared
----------------------
(1 row)
Time: 0,296 ms
Затем запустим на 10 секунд pgbench для нагрузочного тестирования базы admin_monitoring:
postgres@admin_monitoring=# \q postgres@s-pg13:~$ pgbench -T 10 admin_monitoring starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 10 s number of transactions actually processed: 18432 latency average = 0.543 ms tps = 1843.192532 (including connections establishing) tps = 1843.508640 (excluding connections establishing)
В результате мы видим, что pgbench работал с базой данных на скорости 1843 транзакций в секунду в течении 10 секунд.
Далее очистим pgbench_accounts, чтобы это действие тоже попало в статистику:
postgres@s-pg13:~$ psql -d admin_monitoring Timing is on. psql (13.3) Type "help" for help. postgres@admin_monitoring=# VACUUM pgbench_accounts; VACUUM Time: 32,538 ms
Просмотр статистики для одной таблицы
Посмотрим статистику по таблице pgbench_accounts в разрезе строк. Другими словами, сколько наша система прочитала, записала или изменила строк. Для этого заглянем в представление pg_stat_all_tables:
postgres@admin_monitoring=# SELECT * FROM pg_stat_all_tables WHERE relid='pgbench_accounts'::regclass \gx -[ RECORD 1 ]-------+------------------------------ relid | 16514 schemaname | public relname | pgbench_accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 36864 idx_tup_fetch | 36864 n_tup_ins | 0 n_tup_upd | 18432 n_tup_del | 0 n_tup_hot_upd | 16766 n_live_tup | 100000 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | 2021-06-28 15:46:12.080963+03 last_autovacuum | last_analyze | last_autoanalyze | 2021-06-28 15:45:43.07853+03 vacuum_count | 1 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 Time: 13,532 ms
В результате увидим следующую информацию:
- relid — идентификатор базы;
- schemaname — имя схемы;
- relname — имя таблицы;
- seq_scan — сколько раз выполнялось последовательное чтение всей таблицы;
- seq_tup_read — количество строк, прочитанных при последовательных чтениях;
- idx_scan — количество сканирований по индексу;
- idx_tup_fetch — количество строк, отобранных при сканированиях по индексу;
- n_tup_ins — количество вставленных строк;
- n_tup_upd — количество обновлённых строк (UPDATE);
- n_tup_del — количество удалённых строк;
- n_tup_hot_upd — количество строк, обновлённых в режиме HOT (без отдельного изменения индекса);
- n_live_tup — оценочное количество строк;
- n_dead_tup — оценочное количество «мёртвых» строк;
- n_mod_since_analyze — оценочное число строк, изменённых в этой таблице, с момента последнего сбора статистики;
- n_ins_since_vacuum — примерное число строк, вставленных в эту таблицу с момента последнего сбора статистики;
- last_vacuum — когда последний раз работал VACUUM;
- last_autovacuum — когда последний раз работал AUTOVACUUM;
- last_analyze — когда последний раз VACUUM собирал статистику;
- last_autoanalyze — когда последний раз AUTOVACUUM собирал статистику;
- vacuum_count — сколько раз VACUUM выполнялся;
- autovacuum_count — сколько раз AUTOVACUUM выполнялся;
- analyze_count — сколько раз вручную собирали статистику;
- autoanalyze_count — сколько раз AUTOVACUUM собирал статистику.
Короче говоря, команда pgbench -T 10 admin_monitoring просто выполняла UPDATE в таблице.
Из представления pg_statio_all_tables посмотрим статистику в разрезе страниц, то есть сколько страниц было прочитано, изменено и так далее:
postgres@admin_monitoring=# SELECT * FROM pg_statio_all_tables WHERE relid='pgbench_accounts'::regclass \gx -[ RECORD 1 ]---+----------------- relid | 16514 schemaname | public relname | pgbench_accounts heap_blks_read | 27 heap_blks_hit | 68962 idx_blks_read | 275 idx_blks_hit | 77326 toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit | Time: 11,974 ms
В результате мы видим:
- heap_blks_read — сколько страничек было прочитано с диска;
- heap_blks_hit — сколько страничек было прочитано из буферного кэша;
- idx_blks_read — сколько индексов было считано с диска;
- idx_blks_hit — сколько индексов было считано из буферного кэша.
Вдобавок можем посмотреть статистику по индексам. Во-первых в разрезе строк (pg_stat_all_indexes), во-вторых в разрезе страниц (pg_statio_all_indexes):
postgres@admin_monitoring=# SELECT * FROM pg_stat_all_indexes WHERE relid='pgbench_accounts'::regclass \gx -[ RECORD 1 ]-+---------------------- relid | 16514 indexrelid | 16528 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_scan | 36864 idx_tup_read | 38794 idx_tup_fetch | 36864 Time: 11,698 ms postgres@admin_monitoring=# SELECT * FROM pg_statio_all_indexes WHERE relid='pgbench_accounts'::regclass \gx -[ RECORD 1 ]-+---------------------- relid | 16514 indexrelid | 16528 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_blks_read | 275 idx_blks_hit | 77326 Time: 11,315 ms
Просмотр статистики базы данных
В конце концов можем посмотреть глобальную статистику по всей базе данных из представления pg_stat_database:
postgres@admin_monitoring=# SELECT * FROM pg_stat_database WHERE datname='admin_monitoring' \gx -[ RECORD 1 ]---------+------------------------------ datid | 16507 datname | admin_monitoring numbackends | 1 xact_commit | 18515 xact_rollback | 0 blks_read | 510 blks_hit | 243592 tup_returned | 277836 tup_fetched | 38458 tup_inserted | 18438 tup_updated | 55308 tup_deleted | 0 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 checksum_failures | 0 checksum_last_failure | blk_read_time | 10.429 blk_write_time | 0 stats_reset | 2021-06-28 15:43:53.618542+03 Time: 11,633 ms
В результате видим:
- datid — идентификатор базы данных;
- datname — имя базы данных;
- numbackends — количество обслуживающих процессов, в настоящее время подключённых к этой базе данных. Это единственный столбец в представлении, значение в котором отражает текущее состояние. Все другие столбцы возвращают суммарные значения со времени последнего сброса статистики;
- xact_commit — количество зафиксированных транзакций в этой базе данных;
- xact_rollback — количество транзакций в этой базе данных, для которых был выполнен откат транзакции;
- blks_read — сколько дисковых блоков было прочитано с диска, или из дискового кеша;
- blks_hit — сколько дисковых блоков было прочитано из буферного кеша;
- tup_returned — количество строк, возвращённое запросами;
- tup_fetched — количество строк, извлечённое запросами;
- tup_inserted — количество строк, вставленное запросами;
- tup_updated — количество строк, изменённое запросами;
- tup_deleted — количество строк, удалённое запросами;
- conflicts — количество запросов, отменённых из-за конфликта с восстановлением. Конфликты происходят только на ведомых серверах;
- temp_files — количество временных файлов, созданных запросами;
- temp_bytes — объём данных, записанных во временные файлы;
- deadlocks — количество взаимных блокировок;
- checksum_failures — количество ошибок контрольных сумм в страницах данных этой базы либо NULL, если контрольные суммы не проверяются;
- checksum_last_failure — время выявления последней ошибки контрольной суммы в страницах данных этой базы либо NULL, если контрольные суммы не проверяются;
- blk_read_time — время, которое затратили обслуживающие процессы на чтение блоков из файлов данных, в миллисекундах (если включён параметр track_io_timing, в противном случае 0);
- blk_write_time — время, которое затратили обслуживающие процессы на запись блоков в файлы данных, в миллисекундах (если включён параметр track_io_timing, в противном случае 0);
- stats_reset — последнее время сброса этих статистических данных.
Просмотр статистики фоновой записи
Помимо всего прочего PostgreSQL ведёт статистику по фоновой записи. Для того чтобы её посмотреть, нужно заглянуть в представление pg_stat_bgwriter:
postgres@admin_monitoring=# CHECKPOINT; CHECKPOINT Time: 1,232 ms postgres@admin_monitoring=# SELECT * FROM pg_stat_bgwriter \gx -[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 5 checkpoints_req | 2 checkpoint_write_time | 149479 checkpoint_sync_time | 3 buffers_checkpoint | 2095 buffers_clean | 0 maxwritten_clean | 0 buffers_backend | 154 buffers_backend_fsync | 0 buffers_alloc | 508 stats_reset | 2021-06-28 15:44:14.892968+03 Time: 10,707 ms
В результате видно:
- buffers_clean — сколько страниц записал процесс фоновой записи;
- buffers_checkpoint — сколько страниц записал процесс checkpoint;
- buffers_backend — сколько страниц записывали фоновые процессы;
- checkpoints_timed — сколько раз процесс checkpoint срабатывал по таймеру;
- checkpoints_req — сколько раз процесс checkpoint срабатывал в связи с активной работой с таблицей.
Подробнее о статистики в PostgreSQL можете почитать тут.
