Postgresovaya stat without nerves and straining
A couple of years ago, when dealing with another problem in postgres performance, I had to delve into postgresovoy statistics. Delving into the views and functions, I caught myself thinking that working with what is by default is not quite convenient.
And in fact, there are quite a lot of metrics in postgres, all of them are represented as representations and functions. On the one hand it is very convenient, wrote SELECT and received data. On the other hand, such periodical writing is a little tiring.
At the same time, I didn’t need to look at some statues for nothing, I had to track its change. I don’t remember that it was accurate, maybe I had to look at the change in the replication lag, or with what frequency a particular request was called ...
So, having caught myself thinking that I had enough of a tool, I began searching for utilities for my task, but alas, I couldn’t find anything that came to me. So pgCenter appeared .
Initially it was a console utility and it was conceived as an easy-to-use thing, which in a top-like interface would display changes in post-performance statistics. However, taking into account that during the troubleshooting of the postgres, you still need to have system metrics in front of your eyes, the system statistics were also added there ... cpu, memory, swap, disk utilization and network interfaces. Later, other admin functions were added, such as viewing the configuration and editing configs, working with logs, the ability to shoot requests and clients. In general, gradually the number of functions grew ... and users suggested different ideas on how to make the tool even better. Today, after some development, I’m finally ready to submit a new release, in which pgCenter has changed a lot.
The main and perhaps the most important change is that pgCenter is now written in Go. Initially, it was in C, but to be honest, on Go it is easier for me to implement ideas and add new functions. Another positive feature is the distribution - the executable file is a single binary that you just need to download, unpack and use.
Another big innovation is that pgСenter is now not only a top-like viewer for statistics. Now statistics can be dumped into local files and then viewed if necessary. It is used when there is no imputed monitoring, or it is necessary to assemble the stack during benchmarking on a temporary virtual machine or container. In the end, it turned out a thing that resembles an oraklovy statspack or for example sar from the packagethe sysstat . There is no need to fence anything at the base level, no additional functions, etc.
The result was 3 utilities in one binary and all of them are called as separate subcommands like the way it looks in git or perf .
This is where major innovations end and small improvements remain. They are mainly associated with the top viewer:
It is likely that I forgot to mention some other features, but I listed the most interesting ones in my opinion.
It seems that everything, I urge all those who already use the utility to upgrade, and I urge everyone who hears for the first time, to try or watch a small demo. There is also a presentation from the report, although the old version is described there, but it makes sense to look, too. And of course, I am waiting for feedback and bug reports. Have a nice day, everyone!
And in fact, there are quite a lot of metrics in postgres, all of them are represented as representations and functions. On the one hand it is very convenient, wrote SELECT and received data. On the other hand, such periodical writing is a little tiring.
At the same time, I didn’t need to look at some statues for nothing, I had to track its change. I don’t remember that it was accurate, maybe I had to look at the change in the replication lag, or with what frequency a particular request was called ...
So, having caught myself thinking that I had enough of a tool, I began searching for utilities for my task, but alas, I couldn’t find anything that came to me. So pgCenter appeared .
Initially it was a console utility and it was conceived as an easy-to-use thing, which in a top-like interface would display changes in post-performance statistics. However, taking into account that during the troubleshooting of the postgres, you still need to have system metrics in front of your eyes, the system statistics were also added there ... cpu, memory, swap, disk utilization and network interfaces. Later, other admin functions were added, such as viewing the configuration and editing configs, working with logs, the ability to shoot requests and clients. In general, gradually the number of functions grew ... and users suggested different ideas on how to make the tool even better. Today, after some development, I’m finally ready to submit a new release, in which pgCenter has changed a lot.
The main and perhaps the most important change is that pgCenter is now written in Go. Initially, it was in C, but to be honest, on Go it is easier for me to implement ideas and add new functions. Another positive feature is the distribution - the executable file is a single binary that you just need to download, unpack and use.
Another big innovation is that pgСenter is now not only a top-like viewer for statistics. Now statistics can be dumped into local files and then viewed if necessary. It is used when there is no imputed monitoring, or it is necessary to assemble the stack during benchmarking on a temporary virtual machine or container. In the end, it turned out a thing that resembles an oraklovy statspack or for example sar from the packagethe sysstat . There is no need to fence anything at the base level, no additional functions, etc.
The result was 3 utilities in one binary and all of them are called as separate subcommands like the way it looks in git or perf .
This is where major innovations end and small improvements remain. They are mainly associated with the top viewer:
- Now, by default, instead of database statistics, it shows activity statistics - who is connected, what requests are executed, for how long, and so on. Experience shows that most often you need to evaluate exactly the current activity, so perhaps it makes sense to immediately show exactly this stat.
- In activity statistics, you can now see background processes (added in postgres 10), and idle clients that do nothing (enabled by hotkey). Previously, idle was not shown at all, because of their uselessness, but few people suddenly need it.
- Statistics associated with tables used to be shown in two separate views, now all statistics are combined and shown in one view.
- The filtering function now supports regular expressions, and you can more flexibly set parameters for exactly what you want to see ... the names of tables, indexes, functions, queries, etc.
It is likely that I forgot to mention some other features, but I listed the most interesting ones in my opinion.
It seems that everything, I urge all those who already use the utility to upgrade, and I urge everyone who hears for the first time, to try or watch a small demo. There is also a presentation from the report, although the old version is described there, but it makes sense to look, too. And of course, I am waiting for feedback and bug reports. Have a nice day, everyone!