MySQL Status variable)

MySQL Status variable)

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 |
+ --------------- + ---------------- +

-- View the global status variable opened_tables
Root @ 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 status variable OPENED_TABLES from the information_schema.global_status table.
Root @ localhost [tempdb]> select * from information_schema.global_status
-> Where variable_name like 'opened _ tables ';
+ --------------- + ---------------- +
| VARIABLE_NAME | VARIABLE_VALUE |
+ --------------- + ---------------- +
| OPENED_TABLES | 55 |
+ --------------- + ---------------- +

-- Cleaning status variable statistics
Root @ localhost [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.
Root @ localhost [tempdb]> show session status like 'opened _ tables ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Opened_tables | 0 |
+ --------------- + ------- +

-- Author: Leshami
-- Blog: http://blog.csdn.net/leshami

-- Opened_tables at the global level 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
| Connections | 18 |
Suse11b :~ # Mysql

Root @ localhost [(none)]> system mysqladmin extended-status | grep Connections
| Connections | 20 |
Root @ localhost [(none)]> exit
Bye
Suse11b :~ # Mysqladmin -- help | more # Use of mysqladmin related to status variables
Extended-status Gives an extended status message from the server
Flush-status Clear status variables

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: