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.

up vote 1 down vote accepted

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 at 0:40

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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