The amount of transactions committed is an important information, but how could you return an accurate value?
This is a question I got from my colleague Ivan, he was challenging with global status values like
HANDLER_COMMIT, then checking in
innodb_metrics… but this was not accurate.
In fact depending which storage engine you are using, if binary logs are enabled, if you rollback transactions, if you are using
auto_commit, etc… all those parameters influence those values.
So the first question was “What metrics or else should we use ?”. In my opinion, the most accurate “counter” for transactions are the GTIDs.
Update: it’s important to notice here that we are looking for the amount of committed transactions only, no read-only transactions, neither rollback transactions… To get all the transactions, you can use
And this is why I created yet another MySQL Shell plugin that does that calculation:
This plugin is available on my GitHub repository dedicated to MySQL Shell Plugins (there are now only compatible with Shell version >= 8.0.22): https://github.com/lefred/mysqlshell-plugins
So I was happy and Ivan seemed happy too ;-)…. but…
Then Kenny, asked me, why don’t you create something on the server too, like a UDF or any other function ?
Usually those challenges are what I like of course… but before I started to write some C++ or SQL, one thing I learned in so many years, is to not reinvent de wheel over and over again…
After some discussions, my colleague from the Replication team, Pedro F, told me to have a look at the functions used in mtr (MySQL’s test framework).
And bingo, I found there all I needed !
So after adding those functions, I could easily do the calculation and display the amount of transactions committed in a given interval:
MTR is full or resources !