MySQL: How many transactions were committed during an interval of time ?

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 COM_COMMIT or 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 performance_schema.events_transactions_summary_global_by_event_name

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:

If you are interested by those functions, you can find them in MySQL source code or on this gist.

MTR is full or resources !