MySQL status variable (ServerStatusVariables) _ MySQL

The MySQL status variable is the system status information accumulated after the current server is started. it is mainly used to evaluate the usage of the current system resources to further analyze the system performance and make corresponding adjustment decisions. These status variables are equivalent to the dynamic performance of the Oracle Database. the MySQL status variables indicate the system status information accumulated after the current server is started, it is mainly used to evaluate the usage of current system resources to further analyze system performance and make corresponding adjustment decisions. These state variables are equivalent to the dynamic performance View of Oracle databases. MySQL has many status variables, such as SQL execution frequency, index usage, and resource lock usage. State variables can partition global and session-level state variables. Status variables cannot be modified. they are read-only and updated by the system. This article demonstrates some examples of state variables for your reference.

1. status variables

2. View status variables in show mode

-- Root @ localhost [(none)]> show variables like 'version' in the current demo environment '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.5.39-log | + ------------- + ------------ + a. View all status variables root @ localhost [(none) ]> show status; + ------------------------------------------ + ------------- + | Variable_name | Value | + -------------------------------------------- + --------------- + | aborte Ents | 0 | Binlog_stmt_cache_use | 1 | Bytes_received | 135 | Bytes_sent | 266 | ................ | Threads_running | 1 | Uptime | 76242 | Uptime_since_flush_status | 76242 | + ---------------------------------------- + ------------- + 312 rows in set (0.00 sec) -- we can see that the current version 5.5.39 has 312 state variables. B. Check connections only. -- view the specified state variables, the following two variables related to connection are global state variables root @ localhost [(none)]> show global s Tatus like 'connection % '; + ---------------------- + ------- + | Variable_name | Value | + ---------------------- + ------- + | Connections | 11 | -- number of Connections to the MySQL server (including successful or failed ). + ---------------------- + ------- + Suse11b :~ # Mysql-ufredfred @ localhost [(none)]> show global status like '% connection % '; + ---------------------- + ------- + | Variable_name | Value | + ---------------------- + ------- + | Connections | 12 | after the connection, we can see that the Connections Value has changed to 12. + ---------------------- + ------- + C. check the global and session status variables. view the session status variable opened_tablesroot @ localhost [tempdb]> show session status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 0 | + --------------- + ------- + root @ localhost [tempdb]> select count (*) from tb_slow; + ---------- + | count (*) | + ---------- + | 424448 | + ---------- + root @ localhost [tempdb]> show session status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 1 | -- the Value is changed to 1 + ------------- + ------- + -- query the OPENED_TABLESroot @ localhost [tempdb]> select * from information_schema.session_status-> where variable_name like 'opened _ tables '; + Region + | VARIABLE_NAME | VARIABLE_VALUE | + Region + ---------------- + | OPENED_TABLES | 1 | + Region + ---------------- + -- view the global status variable opened_tablesroot @ localhost [tempdb]> show global status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 54 | + --------------- + ------- + root @ localhost [tempdb]> select count (*) from mysql. db; + ---------- + | count (*) | + ---------- + | 2 | + ---------- + root @ localhost [tempdb]> show global status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 55 | -- the Value is 55 + --------------- + ------- + -- query the OPENED_TABLESroot @ localhost [tempdb]> select * from information_schema.global_status-> where variable_name like 'opened _ tables '; + summary + -------------- + | VARIABLE_NAME | VARIABLE_VALUE | + summary + ---------------- + | OPENED_TABLES | 55 | + summary + ---------------- + -- cleaning status variable statistics root @ localhost [tempdb]> flush status; query OK, 0 rows affected (0.00 sec) -- The following Query results can be seen, the session-level opened_tables is reset to 0root @ localhost [tempdb]> show session status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 0 | + --------------- + ------- + -- Author: Leshami -- Blog: http://blog.csdn.net/leshami-- The global opened_tables is not affected. root @ localhost [tempdb]> show global status like 'opened _ tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Opened_tables | 55 | + --------------- + ------- +

3. use mysqladmin extended-status to obtain the status variable

Suse11b :~ # Mysqladmin extended-status | grep Connections | 18 | suse11b :~ # Mysqlroot @ localhost [(none)]> system mysqladmin extended-status | grep Connections | 20 | root @ localhost [(none)]> exitByesuse11b :~ # Mysqladmin -- help | more # use extended-status Gives an extended status message from the serverflush-status Clear status variables for mysqladmin related to status variables