Those queries are useful to admin MySQL instances using mysql client.
1) Check active processes/connections running
mysql> show processlist;
the previous statement doesn’t show all queries text running. To see the complete statements running use:
mysql> show processlist; --or-- mysql> show full processlist G;
the running queries statements could bee quite long. Use the “G” modifier to see processes in different output paragraphs
2) Check UPTIME
MySql store main infos under metadata views querable using “SHOW STATUS” command. Those are some of the most quick and useful.
mysql> show status like '%uptime%'; +---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | Uptime | 880329 | | Uptime_since_flush_status | 8347 | +---------------------------+--------+ 2 rows in set (0.01 sec)
3) Check Connections
mysql> show status like '%conn%'; +-----------------------------------------------+---------+ | Variable_name | Value | +-----------------------------------------------+---------+ | Aborted_connects | 0 | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 4094917 | | Max_used_connections | 222 | | Performance_schema_session_connect_attrs_lost | 0 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 120 | +-----------------------------------------------+---------+ 14 rows in set (0.00 sec)
4) Check Caching Status
Caching mechanism are managed using startup parameters. First you have to check if caching is enabled:
mysql> show variables like '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 33554432 | | query_cache_type | ON | +------------------------------+----------+ 5 rows in set (0.00 sec)
If query caching is enabled caching metadata infos are visible simply in this way.
mysql> show status like 'QCache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 957 | | Qcache_free_memory | 15523704 | | Qcache_hits | 1978478 | | Qcache_inserts | 132298 | | Qcache_lowmem_prunes | 6037 | | Qcache_not_cached | 5969 | | Qcache_queries_in_cache | 3389 | | Qcache_total_blocks | 8396 | +-------------------------+----------+ 8 rows in set (0.00 sec)
5) Check Instance Wait Time using PERFORMANCE_SCHEMA
Starting with release 5.5 MySql introduce the new performance_schema. Now performance critical conditions can be analized quering a group of system tables realtime dinamically updated.
First we’ll check if performance_schema is turned on. This option can be turned on using startup parameter file.
mysql> SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+
If performance_schema is started there are many tables available to check your instance status (use SHOW TABLES under performance schema to see the complete list). Here we’ll start with one of the most simple, used to analize instance wait conditions in realtime:
mysql> SELECT * FROM events_waits_currentG
*************************** 1. row ***************************
THREAD_ID: 0
EVENT_ID: 5523
EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
SOURCE: thr_lock.c:525
TIMER_START: 201663444489586
TIMER_END: 201660494576112
TIMER_WAIT: 86526
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 142270668
NESTING_EVENT_ID: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: 0
...
zabolab 