Статистика работы PostgreSQL

PostgreSQL собирает статистику с помощью фонового процесса «stats collector«, эта статистика может понадобится для анализа работы сервера.
Tags:

Теория

В этой статье разберем системные представления, с помощью которых можно смотреть на эту статистику.

Статистика 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 можете почитать  тут .

Если понравилась статья, подпишись на мой канал в  VK  или  Telegram .