1

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

2
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';
  • thanks. In this way, we can find all the specific variables. – ursitesion Dec 13 '13 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 '18 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 '19 at 9:19
  • I get "Error Code: 3167. The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; (...)" :( – Pedro Araujo Jorge Nov 27 '19 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';
0

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, privacy policy and cookie policy

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