PostgreSQL monitor: dashboard
[EN]
The main tool for managing and monitoring the operation of a PostgreSQL cluster
To start, double click on "MONITOR" or "PARAMETER"
PostgreSQL monitor is a comprehensive screen with information about the operation of the PostgreSQL database
Allows you not only to monitor the performance of the cluster, but also to make changes to the database configuration
Basic information on the load on the database. Data is updated once per second
Server session - information on sessions (total, active, idle)
Transaction per second - information on transactions (active, recording data, rolling back)
Tuples In - data on operations that change data (insert, update, delete)
Tuples Out - data on operations to retrieve data from the database (Number of rows returned by queries in this database; Number of rows returned by queries in this database)
Block I/O - number of blocks read from disk or found in memory
To select the base on which to display statistics, use the "Base statistics" drop-down list.
Available: database name or ALL - for all in total
Data collection is controlled via the "Running" switch
SELECT 'session_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Total'::text "Name1", count(*)::int "COUNT1" FROM pg_stat_activity
) AS "1",
(SELECT 'Active'::text "Name2", count(*)::int "COUNT2" FROM pg_stat_activity WHERE state = 'active'
) "2",
(SELECT 'Idle'::text "Name3", count(*)::int "COUNT3" FROM pg_stat_activity WHERE state = 'idle'
) "3"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Transactions'::text "Name1", (sum(xact_commit) + sum(xact_rollback))::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Commits'::text "Name2", sum(xact_commit)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT 'Rollbacks'::text "Name3", sum(xact_rollback)::int "COUNT3" FROM pg_stat_database
) "3"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Inserts'::text "Name1", sum(tup_inserted)::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Updates'::text "Name2", sum(tup_updated)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT 'Deletes'::text "Name3", sum(tup_deleted)::int "COUNT3" FROM pg_stat_database
) "3"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Fetched'::text "Name1", sum(tup_fetched)::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Returned'::text "Name2", sum(tup_returned)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT ''::text "Name3", 0::int "COUNT3") "3"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Reads'::text "Name1", sum(blks_read)::int "COUNT1" FROM pg_stat_database
)"1",
(SELECT 'Hits'::text "Name2", sum(blks_hit)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT ''::text "Name3", 0::int "COUNT3") "3"
) t
[RU]
Основное средство управления и мониторинга работы кластера PostgreSQL
Для запуска дважды кликнуть по "MONITOR" или "PARAMETER"
PostgreSQL monitor - это комплексный экран с информацией о работе базы данных PostgreSQL
Позволяет не только отслеживать производительность кластера, а так же вносить изменение в конфигурацию БД
Основная информация по нагрузке на БД. Данные обновляются 1 раз в сек
Server session - информация по сессиям (всего, активных, простаивающих)
Transaction per second - информация по транзакциям (активных, фиксирующих данные, выполняющих откат)
Tuples In - данные по операциям изменяющим данные (вставка, обновление, удаление)
Tuples Out - данные по операциям извлечения данных из базы (Количество строк, возвращённое запросами в этой базе данных; Количество строк, возвращённое запросами в этой базе данных)
Block I/O - количество блоков прочитанных с диска или найденных в памяти
Для выбора базы по которой выводить статистику использовать выпадающий список "Base statistics".
Доступно: имя базы или ALL - по всем суммарно
Управление сбором данных осуществляется через переключатель "Running"
SELECT 'session_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Total'::text "Name1", count(*)::int "COUNT1" FROM pg_stat_activity
) AS "1",
(SELECT 'Active'::text "Name2", count(*)::int "COUNT2" FROM pg_stat_activity WHERE state = 'active'
) "2",
(SELECT 'Idle'::text "Name3", count(*)::int "COUNT3" FROM pg_stat_activity WHERE state = 'idle'
) "3"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Transactions'::text "Name1", (sum(xact_commit) + sum(xact_rollback))::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Commits'::text "Name2", sum(xact_commit)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT 'Rollbacks'::text "Name3", sum(xact_rollback)::int "COUNT3" FROM pg_stat_database
) "3"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Inserts'::text "Name1", sum(tup_inserted)::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Updates'::text "Name2", sum(tup_updated)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT 'Deletes'::text "Name3", sum(tup_deleted)::int "COUNT3" FROM pg_stat_database
) "3"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Fetched'::text "Name1", sum(tup_fetched)::int "COUNT1" FROM pg_stat_database
) "1",
(SELECT 'Returned'::text "Name2", sum(tup_returned)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT ''::text "Name3", 0::int "COUNT3") "3"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, t.*
FROM (SELECT * from
(SELECT 'Reads'::text "Name1", sum(blks_read)::int "COUNT1" FROM pg_stat_database
)"1",
(SELECT 'Hits'::text "Name2", sum(blks_hit)::int "COUNT2" FROM pg_stat_database
) "2",
(SELECT ''::text "Name3", 0::int "COUNT3") "3"
) t