Tales From A Lazy Fat DBA

Hi All,

I was recently asked by one of my team mate to recommend any performance monitoring tool which is free, text based, light weight and is quick to run to check what all is running on the PostgreSQL database. He wanted to use it for one of the performance drive he was leading for one production database setup. The guy was coming from Oracle background, and was looking something like Linux TOP utility or like oratop (like top for oracle database) …

After carefully understanding his requirements, I recommended him to use pgCenter. This is an open source project by Lesovsky Alexey (the man behind pgstats.dev and Weaponry) and is a command-line admin tool for observing and troubleshooting Postgres database. I would have recommended pg_top to him, but pgCenter is one step ahead and better as it provide more details as compared to the later.

pgCenter’s main goal is to help Postgres DBA working with statistics and provide a convenient way to observe Postgres in runtime. Tool is capable to provide replication, table level, Index, activity, table size, database (overall), and function statistics. It also shows details on VACCUM, NIC & Disk stats along with OS level stats of CPU, Memory, load averages, connection and server details.

It’s pretty easy to call or install, please refer below project GitHub link to download and follow install actions. https://github.com/lesovsky/pgcenter

So, let’s see what all it monitors and catches.

[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$ pgcenter top -d perf -h 10.0.0.130 -U enterprisedb  -p 5444

A GIF (from project page on github) to show it in real-time, what all you can do with the tool.

Stats it supports …

PostgreSQL Stats it supports:

• summary activity – a compilation/selection of metrics from different sources – postgres uptime, version, recovery status, number of clients grouped by their states, number of (auto)vacuums, statements per second, age of the longest transaction and the longest vacuum;
• pg_stat_activity – activity of connected clients and background processes.
• pg_stat_database – database-wide and sessions statistics, such as number of commits/rollbacks, processed tuples, deadlocks, temporary files, etc.
• pg_stat_replication – replication statistics, like connected standbys, their activity and replication lag.
• pg_stat_user_tables, pg_statio_user_tables – statistics on accesses (including IO) to tables.
• pg_stat_user_indexes, pg_statio_user_indexes – statistics on accesses (including IO) to indexes.
• pg_stat_user_functions – statistics on execution of functions.
• pg_stat_wal – WAL usage statistics.
• pg_stat_statements – statistics on SQL statements executed including time and resources usage.
• statistics on tables sizes based on pg_relation_size() and pg_total_relation_size() functions;
• pg_stat_progress_vacuum – progress of (auto)vacuums operations.
• pg_stat_progress_cluster – progress of CLUSTER and VACUUM FULL operations.
• pg_stat_progress_create_index – progress of CREATE INDEX and REINDEX operations.
• pg_stat_progress_analyze – progress of ANALYZE operations.
• pg_stat_progress_basebackup – progress of basebackup operations.
• pg_stat_progress_copy – progress of COPY operations.

System Statistics it supports :

pgCenter top also provides system usage information based on statistics from procfs filesystem:

• load average and CPU usage time (user, system, nice, idle, iowait, software, and hardware interrupts, steal);
• memory and swap usage, amount of cached and dirty memory, writeback activity;
• storage devices statistics: IOPS, throughput, latencies, average queue and requests size, devices utilization;
• network interfaces statistics: throughput in bytes and packets, different kind of errors, saturation and utilization.
• mounted filesystems’ usage statistics: total size, amount of free/used/reserved space and inodes.

Hope It Helps!
Prashant Dixit