MySQL status variable (ServerStatusVariables)

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.

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.

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 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
Displays the cumulative status information of the current mysql database server since the current start, divided into session-level and global-level status information.
Similar to system variables, some state variables have global and session levels, while others have only global levels. For example, binlog_cache_disk_use only has the global status, while bytes_sent has both.
You can use show status like '% variable_name %' or show global status like '% variable_name %.
If you do not use Like, show status displays all status variables.
You can query the information_schema.global_status and information_schema.session_status tables to obtain status variable information.
You can use mysqladmin extended-status in the command line to obtain information about status variables.
You can run the command line mysqladmin extended-status-r-I 5 or innotop command to continuously observe the status variable changes.
Some STATUS variables can be reset to zero using the flush status statement.

2. View status variables in show Mode

-- Current demo Environment
Root @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.5.39-log |
+ --------------- + ------------ +

A. View All status variables
Root @ localhost [(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) -- we can see that the current version 5.5.39 has 312 status variables

B. Check connections)
-- View the specified status variable. The following two variables related to connection are global status variables.
Root @ localhost [(none)]> show global status like 'Connection % ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Connections | 11 | -- number of Connections to the MySQL server (including successful or failed Connections ).
+ ---------------------- + ------- +

SUSE11b :~ # Mysql-ufred

Fred @ 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. View variables with both global and session statuses
-- View the session status variable opened_tables
Root @ localhost [tempdb]> show session status like 'opened _ tables ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Opened_tables | 0 |
+ --------------- + ------- +

Root @ localhost [tempdb]> select count (*) from tb_slow;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 424448 |
+ ---------- +

Root @ localhost [tempdb]> show session status like 'opened _ tables ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Opened_tables | 1 | -- the value is 1.
+ --------------- + ------- +

-- Query the status variable OPENED_TABLES from the information_schema.session_status table.
Root @ localhost [tempdb]> select * from information_schema.session_status
-> Where variable_name like 'opened _ tables ';
+ --------------- + ---------------- +
| VARIABLE_NAME | VARIABLE_VALUE |
+ --------------- + ---------------- +
| OPENED_TABLES | 1 |
+ --------------- + ---------------- +