2

When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these variables many times during performance hit. For example, can we write below multiple statement into one?

show global status like 'Threads_cached';
show global status like 'Threads_connected';
show global status like 'Threads_created';
show global status like 'Threads_running';
show global status like 'Select_scan';

If we fetch these variables in a single statement, it will be really a great help...

3 Answers 3

3
select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Threads_cached' or VARIABLE_NAME like 'Threads_connected' or VARIABLE_NAME like 'Threads_created' or VARIABLE_NAME like 'Threads_running' or VARIABLE_NAME like 'Select_scan';
4
  • thanks. In this way, we can find all the specific variables.
    – ursitesion
    Dec 13, 2013 at 12:50
  • @Kazimieras Aliulis, How to add Com_commit + Com_rollback to find out TPS for performance ? I believe TPS => (Com_Commit + Com_rollback ) / second gives out the TPS. Could you give a query to calculate out TPS , Please guide me.
    – SAGAR Nair
    Oct 15, 2018 at 5:40
  • This may need an update. The global_status in recent MySQL versions (eg. 8.0, 5.7) is in the performance_schema schema, and it also doesn't include Com_ values.
    – Marcus
    Oct 17, 2019 at 9:19
  • I get "Error Code: 3167. The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; (...)" :( Nov 27, 2019 at 10:04
2

Remember that in MySQL 8 the global_status table has been moved to the performance_schema, therefore you should query:

SELECT * FROM performance_schema.global_status
 WHERE VARIABLE_NAME like 'Open_files'
    OR VARIABLE_NAME like 'Uptime';
1

If we assume the usage of the SHOW GLOBAL STATUS (therefore, exclude the performance schema), don't forget that the statement supports WHERE conditions, which fit nicely with the regexp match operator (RLIKE):

SHOW GLOBAL STATUS WHERE Variable_name RLIKE '^(Threads_cached|Threads_connected|Threads_created|Threads_running|Select_scan)$'

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.