Немного статистики PostgreSQL

Давайте коротко рассмотрим основную статистику Postgres, которая может помочь нам улучшить производительность нашей базы данных.

Первой интересующей нас вьюхой является pg_stat_database. Данное представление позволяет нам получить общую статистику, касающуюся всей базы данных в целом.

 

rucert=# select * from pg_stat_database;

-[ RECORD 1 ]--+------------------------------
datid          | 16985
datname        | db
numbackends    | 15
xact_commit    | 1668787
xact_rollback  | 2373
blks_read      | 8280857
blks_hit       | 87326677
tup_returned   | 1382978742
tup_fetched    | 77637970
tup_inserted   | 1939051
tup_updated    | 28284
tup_deleted    | 1142063
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2017-12-11 21:28:13.655069+03

Здесь интересующие нас строки:

datname - имя базы данных.

numbackends - количество серверных процессов, подлюченных к базе данных на текущий момент.

blks_read - количество считанных дисковых блоков.

blks_hit - количество дисковых блоков, обнаруженных в буферном кеше.

stats_reset - дата псоледнего сброса статистики.

Соотношение blks_hit/blks_read позволяет оценить, насколько часто требуемые блоки обнаруживаются в кеше Postgres.

SELECT
      datname,
      CASE
        WHEN blks_read = 0 THEN 0
        ELSE blks_hit / blks_read
      END AS blks_ratio
    FROM
      pg_stat_database;

Кроме того, можно оценить сколько строк было считано, извлечено, вставлено, изменено, удалено в данной БД.

SELECT datname, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted
FROM pg_stat_database;

 

Следующей вьюшкой является pg_stat_user_tables. Это представление содержит информацию о каждой пользовательской таблице, например:

rucert=# select * from pg_stat_user_tables;

-[ RECORD 19 ]------+--------------------------------------------------
relid               | 17029
schemaname          | public
relname             | onlinecourse
seq_scan            | 5117
seq_tup_read        | 1619778
idx_scan            | 337994
idx_tup_fetch       | 315391
n_tup_ins           | 770
n_tup_upd           | 2757
n_tup_del           | 14
n_tup_hot_upd       | 651
n_live_tup          | 762
n_dead_tup          | 11
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 2018-09-03 19:00:04.803183+03
last_analyze        | 2018-09-10 12:02:14.266699+03
last_autoanalyze    | 2018-09-03 19:00:04.87984+03
vacuum_count        | 0
autovacuum_count    | 9
analyze_count       | 1
autoanalyze_count   | 16

Здесь интересующие нас строки:

seq_scan - количество последовательных чтений, запущенных по этой таблице.

seq_tup_read  - количество "живых" строк, прочитанных при последовательных чтениях.

idx_scan - количество сканирований по индексу, запущенных по этой таблице.

idx_tup_fetch - количество "живых" строк, отобранных при сканированиях по индексу.

Количество вставленных, изменённых, удалённых строк:

n_tup_ins
n_tup_upd 
n_tup_del

 

Наиболее интересными для нас являются показатели последовательного и индексного сканирования. Они позволят выявить потребность в создании дополнительного индекса. 

Для получения более детальной информации по индексам конкретной таблицы можно воспользоваться представлением pg_stat_user_indexes

rucert=# select * from pg_stat_user_indexes where relid=17029;
-[ RECORD 1 ]-+------------------------------
relid         | 17029
indexrelid    | 17266
schemaname    | public
relname       | onlinecourse
indexrelname  | onlinecourse_pkey
idx_scan      | 216118
idx_tup_read  | 215428
idx_tup_fetch | 193827
-[ RECORD 2 ]-+------------------------------
relid         | 17029
indexrelid    | 17268
schemaname    | public
relname       | onlinecourse
indexrelname  | onlinecourse_unique_global_id
idx_scan      | 113122
idx_tup_read  | 113046
idx_tup_fetch | 112443
-[ RECORD 3 ]-+------------------------------
relid         | 17029
indexrelid    | 23514
schemaname    | public
relname       | onlinecourse
indexrelname  | onlinecourse_global_id_index
idx_scan      | 8827
idx_tup_read  | 8781
idx_tup_fetch | 8772

 

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

rucert=# select * from pg_stat_activity;


-[ RECORD 15 ]---+---------------------------------------------------------------------
datid            | 16985
datname          | db
pid              | 31029
usesysid         | 16982
usename          | user
application_name | psql
client_addr      | xxx.xx.xx.xxx
client_hostname  | 
client_port      | 52678
backend_start    | 2018-09-10 12:42:18.60994+03
xact_start       | 2018-09-10 16:19:27.881744+03
query_start      | 2018-09-10 16:19:27.881744+03
state_change     | 2018-09-10 16:19:27.881747+03
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 19031
query            | select * from pg_stat_activity;

query - последний выполненный запрос.

state - состояние процесса. Не нужно пугаться, если там написано idle - это значит, что процесс ожитает следующих команд. Проблемы могут возникнуть, если процесс долгое время находится в состоянии idle in transaction.

Напоследок стоит упомянуть про pg_locks - представление, хранящее информацию о текущих блокировках таблиц.

rucert=# select * from pg_locks;
-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 16985
relation           | 11695
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 16/2519
pid                | 31029
mode               | AccessShareLock
granted            | t
fastpath           | t

Где

relation = pg_class.oid

database = pg_database.oid

На этом можно завершить наш краткий обзор статистики.