PostgreSQL monitor: database statistics
[EN]
The window displays various statistics on the operation of PostgreSQL cluster databases
Database stats
Provides system view information pg_stat_database
SELECT db.datid,
db.datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
stats_reset,
slave.confl_tablespace,
slave.confl_lock,
slave.confl_snapshot,
slave.confl_bufferpin,
slave.confl_deadlock,
temp_files,
pg_size_pretty(temp_bytes),
deadlocks,
blk_read_time,
blk_write_time,
pg_size_pretty(pg_database_size(db.datid))
FROM pg_stat_database db
JOIN pg_stat_database_conflicts slave ON db.datid=slave.datid
where db.datname=$$DBNAME
pg_config
Viewing the pg_config system table of a PostgreSQL database
select * from pg_config
pg_file_settings
Viewing the pg_file_settings system table of the PostgreSQL database
select f.sourcefile,
f.setting,
f.applied,
f.error
from pg_file_settings f
Language support
List of programming languages supported by the PostgreSQL cluster
select l.lanname,
a.rolname,
l.lanispl,
l.lanpltrusted,
l.lanplcallfoid,
l.laninline,
l.lanvalidator
from pg_language l
left join pg_authid a on l.lanowner=a.oid
Percentage before freezing
The page displays information about the performance quality of autovacuum processes in the PostgreSQL cluster. A percentage in the "Percentage before freezing" field that is close to zero indicates that the autovacuum does not have time to clear the tables and there is a high probability of a situation in which it will be necessary to transfer the database to single-user mode and perform vacuum full.
If there are a large number of tables with a low percentage, it is necessary to analyze the values of the auto vacuum parameters to see if they change to more aggressive values.
with rel_frozen as (
select c.oid::regclass as table_name,
(select e.nspname
from pg_catalog.pg_namespace e
where oid = c.relnamespace) as schema,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
greatest(age(c.relfrozenxid), age(t.relfrozenxid))*100./
(pow(2,31)-1) as perc_wrpa
from pg_class c
left join pg_class t on c.reltoastrelid = t.oid
where c.relkind in ('r','m')
)
select table_name::text "table",
schema,
100 - round(perc_wrpa::numeric,2) "froz"
from rel_frozen order by 3
[RU]
В окне представлена разнообразная статистика работы баз данных кластера PostgreSQL
Database stats
Представлена информация системного представления pg_stat_database
SELECT db.datid,
db.datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
stats_reset,
slave.confl_tablespace,
slave.confl_lock,
slave.confl_snapshot,
slave.confl_bufferpin,
slave.confl_deadlock,
temp_files,
pg_size_pretty(temp_bytes),
deadlocks,
blk_read_time,
blk_write_time,
pg_size_pretty(pg_database_size(db.datid))
FROM pg_stat_database db
JOIN pg_stat_database_conflicts slave ON db.datid=slave.datid
where db.datname=$$DBNAME
pg_config
Просмотр системной таблицы pg_config базы данных PostgreSQL
select * from pg_config
pg_file_settings
Просмотр системной таблицы pg_file_settings базы данных PostgreSQL
select f.sourcefile,
f.setting,
f.applied,
f.error
from pg_file_settings f
Language support
Список языков программирования поддерживаемых кластером PostgreSQL
select l.lanname,
a.rolname,
l.lanispl,
l.lanpltrusted,
l.lanplcallfoid,
l.laninline,
l.lanvalidator
from pg_language l
left join pg_authid a on l.lanowner=a.oid
Percentage before freezing
На странице отображается информация о качестве работы процессов autovacuum кластера PostgreSQL. Процент в поле "Percentage before freezing" близкий к нулю говорит о том, что автовакуум не успевает очищать таблицы и велика вероятность ситуации при которой потребуется переводить базу в однопользовательский режим и выполнять vacuum full.
При большом количестве таблиц с низким процентом необходимо проанализировать значения параметров авто вакуума на предмет их изменения на более агрессивные значения.
with rel_frozen as (
select c.oid::regclass as table_name,
(select e.nspname
from pg_catalog.pg_namespace e
where oid = c.relnamespace) as schema,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
greatest(age(c.relfrozenxid), age(t.relfrozenxid))*100./
(pow(2,31)-1) as perc_wrpa
from pg_class c
left join pg_class t on c.reltoastrelid = t.oid
where c.relkind in ('r','m')
)
select table_name::text "table",
schema,
100 - round(perc_wrpa::numeric,2) "froz"
from rel_frozen order by 3