Upgrade: new pgCenter features are now available
The last time I’ve made changes in pgCenter was back in January, so I thought it was about time to make a few revisions and upgrade to a new version. As a result I’ve added following statistics:
- from pg_stat_progress_analyze system view
- from pg_stat_progress_basebackup system view
- pg_stat_statements WAL usage
- pg_stat_statements WAL usage statistics to query reports generated by
“top”tool. - accounting for parallel workers when profiling queries
Below I will zoom in on each specific addition and explain how it works, but if you can’t wait to try it out the new version can be found here: https://github.com/lesovsky/pgcenter .
1 ) Support of pg_stat_progress_analyze statistics.
Postgres 13 has a new progress view, which contains statistics about performed ANALYZE commands. PgCenter now provides extended statistics based on pg_stat_progress_analyze and pg_stat_activity views in top, record and report tools.
In the picture below you see an example with a bunch of ANALYZE executed and sorted by the scanned amount of data.
2 ) Support of pg_stat_progress_basebackup statistics.
PgCenter now provides extended statistics based on pg_stat_progress_analyze and pg_stat_activity views in top, record and report tools.
Below you can see an example with backup which started 12 seconds ago and already streamed 12% of 15GB.
3 ) Add pg_stat_statements WAL usage statistics.
Postgres 13 extends the functionality of pg_stat_statements with new WAL usage statistics. There are three additional new columns:
- wal_records,
- wal_fpi
- wal_bytes.
PgCenter now provides extended statistics based on pg_stat_statements WAL usage in top, record and report tools.
WAL usage statistics are organized in the additional view:
4 ) pg_stat_statements WAL usage statistics was added to query reports generated in top utility.
PgCenter top tool provides query reports based on pg_stat_statements. Now reports extended with the new WAL usage statistics.
5 ) I’ve added the option of seeing tracking statistics of parallel workers during profiling.
Starting from Postgres 13, pg_stat_activity has an additional column — leader_pid, which reports the PID of parallel worker’s leader process. Using this column profile tool can collect statistics about its workers. This function is enabled by default, also a new -W, --no-workers command-line argument has been added to keep the “older”version behavior. Please note that this feature will only work on Postgres 13 and newer versions.
In the example below you can see the query profiled in both modes. In the second one the collected wait_events include parallel workers.
As with any pgCenter release I’ve made a few fixes:
- fixed panic of top when executing it using Docker containers.
- fixed connection problems related to using simple protocol.
- fixed detecting when working with local or remote Postgres.
As always, if you have any issues or suggestions write in comments below or open discussions.
