Mysql – How to calculate MySQL Transactions per second

MySQLtransaction

How can I calculate MySQL Transactions per second.

I tried to use formula (global com_commit+global com_rollback)/uptime since flush.

I had also tried (global insert+ global delete+ global update)/uptime since flush.

The outcome of inserts+deletes+updates is less than commits+rollback. The outcome of commits+rollbacks is 8 times larger than inserts+deletes+updates. So confused on the formulas which one to take.

Best Answer

BEGIN; INSERT...; INSERT...; COMMIT; = 2 inserts, 1 begin, 1 commit.
SET autocommit = ON; INSERT...; = 1 insert, 0 commits.

I don't see any way to get the 'right' answer of '2 transactions'.

However,

  • if your code always uses BEGIN...COMMIT, then you might use Com_begin as the number of transactions.
  • if your code never uses BEGIN...COMMIT, then you might use Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi as the number of transactions.

(I consider autocommit=OFF...COMMIT to be an abomination.)

Related Question