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.

2 Answers 2


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_commit and 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.

  • Thats ok ; but I was asking about the mysql Query for getting that value calculated from mysql prompt itself using some SUM() and ROUND functions in mysql. Could u be able to provide that ?
    – SAGAR Nair
    Oct 18, 2018 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.