PostgreSQL monitor: database statistics

Admin · 2023-12-15T11:49:20.491Z

[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.name, 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.name, 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

top of page

HOME
DOCUMENTATION
SQL QUERY EDITOR
DOWNLOAD
ARTICLES
FORUM

TELEGRAM [RUSSIA] TELEGRAM [ENGLISH] VIDEO Privacy Policy

EMAIL: CerebroSQL@gmail.com

bottom of page