MySQL state variable (Server status Variables)

MySQL state variables are some of the system state information accumulated after the current server has been started, and are mainly used to evaluate the usage of current system resources to further analyze system performance and make appropriate adjustment decisions. These state variables are understood to be equivalent to the dynamic performance view of the Oracle database. MySQL has a number of state variables, such as SQL execution frequency, index usage, lock resource usage, and so on. State variables can be partitioned globally as well as session-level state variables. State variables are not modifiable, are read-only properties, and are updated by the system. This article demonstrates some examples of state variables, just for the purpose of a presentation.

1. State variables
reflects the cumulative state information of the current MySQL database server since its inception, and is divided into session-level and global-level state information.
Like system variables, some state variables have global and session levels, while others have only global levels. such as Binlog_cache_disk_use only have a global state, and Bytes_sent both have.
Can be viewed by show status like '%variable_name% ' or show global status like '%variable_name% '.
Show status displays all state variables without the use of like.
You can get state variable information by querying system tables Information_schema.global_status and Information_schema.session_status.
Information about the state variable can be obtained at the command line through the Mysqladmin Extended-status method.
You can mysqladmin extended-status-r-I 5 or innotop to continuously observe the change of state variables by command line mode.
Some state variables can be reset to a zero value with the Flush status statement.

2. Show Way view state variables

--Current demo environment [email protected][(none)]> show variables like ' version '; +---------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.5.39-log |+---------------+------------+a, view all state variables [email protected][(none)]> show status;+------------- -----------------------------+-------------+| variable_name | Value |+------------------------------------------+-------------+| aborted_clients | 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)--you can see that the current version 5.5.39 has 312 state variable B, view only global state variables (connections)--View the specified state variables, the following two connection related to global state variables [email&nbsp ;p rotected][(none)]> show global status like ' connection% '; +----------------------+-------+| variable_name | Value |+----------------------+-------+| Connections | 11 | --the number of connections to the MySQL server (including success or failure). +----------------------+-------+suse11b:~ # mysql-ufred[email protected][(none)]> show global status like '% connection% '; +----------------------+-------+| variable_name | Value |+----------------------+-------+| Connections | 12 | --After the connection, we see that the value of connections is changed to 12. +----------------------+-------+c, view variables that have both global and session status--view session state variables opened_tables[email protected][ Tempdb]> Show session status like ' Opened_tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Opened_tables | 0 |+---------------+-------+[email protected][tempdb]> Select COUNT (*) from tb_slow;+----------+| Count*) |+----------+| 424448 |+----------+[email protected][tempdb]> Show session status like ' Opened_tables '; +---------------+--- ----+| variable_name | Value |+---------------+-------+| Opened_tables | 1 | --the value becomes 1+---------------+-------+--Query the state variable from the Information_schema.session_status table opened_tables[email protected][ Tempdb]> SELECT * from Information_schema.session_status, where variable_name like ' opened_tables '; +----------- ----+----------------+| variable_name | Variable_value |+---------------+----------------+| Opened_tables | 1 |+---------------+----------------+--View global state variables opened_tables[email protected][tempdb]> show Global St ATUs like ' opened_tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Opened_tables | 54 | +---------------+-------+[email protected][tempdb]> Select COUNT (*) from mysql.db;+----------+|        COUNT (*) |+----------+| 2 |+----------+[email protected][tempdb]> show GlobAl status like ' Opened_tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Opened_tables |  55 | --the value becomes 55+---------------+-------+--Query the state variable from the Information_schema.global_status table opened_tables[email protected][ Tempdb]> SELECT * from Information_schema.global_status, where variable_name like ' opened_tables '; +------------ ---+----------------+| variable_name | Variable_value |+---------------+----------------+| Opened_tables | 55 | +---------------+----------------+--cleaning state variable statistics [email protected][tempdb]> flush status; Query OK, 0 rows Affected (0.00 sec)-The following query results show that the session-level opened_tables is reset to 0[email protected][tempdb]> shows Session status like ' Opened_tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Opened_tables | 0 |+---------------+-------+--author:leshami--blog:http://blog.csdn.net/leshami--and global level opened_tables unaffected [ Email protected][tempdb]> Show Global Status LIke ' Opened_tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Opened_tables | |+---------------+-------+

3. Get state variables using mysqladmin extended-status

suse11b:~ # mysqladmin Extended-status|grep connections| Connections                              |          |suse11b:~ # mysql[email protected][(none)]> system Mysqladmin Extended-status|grep connections| Connections                              |          |[ Email protected][(none)]> exitbyesuse11b:~ # mysqladmin--help |more    #mysqladmin与状态变量有关的使用extended-status       Gives an extended status message from the Serverflush-status          Clear Status variables

MySQL state variable (Server status Variables)