News & Blog back

Subscribe

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: 

As always, if you have any issues or suggestions write in comments below or open discussions.

You may also like:

Знакомимся с новым DBA – Александром Никитиным.

Каждый из наших DBA является безусловным профессионалом своего дела, в то же время каждый привносит в команду собственную уникальную экспертизу и интересы.  Пополнение в команде всегда означает…

pgstats.dev теперь еще лучше!

Прошло 5 месяцев с момента выхода разработанной нашим DBA, Алексеем Лесовским, динамической реализации диаграммы Postgres Observability – pgstats.dev. За это время мы получили огромное количество комментариев, предложений,…