MySQL state variable (Server status Variables)

MySQL state variables are some of the system state information that is accumulated after the current server has been started, and 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 understood to be equivalent to the dynamic performance view of the Oracle database. MySQL has a lot of state variables, such as SQL Run frequency. The use of the index, the use of lock resources, and so on. State variables are able to partition global and session-level state variables. The state variable cannot be changed. is a read-only property that is updated by the system. This article demonstrates some sample examples of state variables. Only for the purpose of the offer.

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. And some have only global levels. such as Binlog_cache_disk_use only have a global state, and Bytes_sent both have.

Can be viewed through show status like '%variable_name% ' or show global status like '%variable_name% '.

Show status displays all state variables without using like.

Can get status variable information by querying system tables Information_schema.global_status and Information_schema.session_status.

The ability to obtain information about a state variable at the command line by Mysqladmin extended-status mode.

The ability to mysqladmin extended-status-r-I 5 or innotop continuously observes 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)--can see the current version number 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 the global state variables [Email&nbs p;protected][(none)]> show global status like ' connection% '; +----------------------+-------+| variable_name | Value |+----------------------+-------+| Connections | 11 | -The number of connections to the MySQLServer (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 the value of connections 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--The global level opened_tables is unaffected by any impact [ 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)