For performance mornitoring, i would like to have a mysql query which gives the calculated output of TPS. I think tps= Com_commit + Com_rollback / second , but I don't know the query to calculate these formula in mysql. Same related question for TPS had been asked but query was not provided there, so why I ask here. Kindly show me how to calculate values from status in MySQL/MariaDB.
It's a bit clunky, but this should give you the tps since restart of MySQL:
SELECT SUM(IF(variable_name IN ('Com_commit', 'Com_rollback'), variable_value, 0)) / SUM(IF(variable_name = 'Uptime', variable_value, 0)) FROM information_schema.GLOBAL_STATUS
Com_rollback are available in
information_schema.global_status. You need to query them regularly and calculate the difference between readings in order to get
quantity per duration.
When I tried to do something similar, a co-worker asked what it would really tell us. His argument was that it told us nothing about transaction size - no way to know how much
work was being done. For example
BEGIN; COMMIT; is a transaction, which will increase your counter, but not tell you anything. Also, if your
TPS drops, is that because of a bottleneck, or a drop in application demand?
Don't let that stop you from implementing this monitoring, but make sure you know what you're getting from the number.