Давайте коротко рассмотрим основную статистику 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
На этом можно завершить наш краткий обзор статистики.