Use of MySQL SHOW STATUS statement

To do MySQL performance adjustment and service status monitoring, we must know the current running status of MySQL. Many developers have read some articles and analyses on database and table sharding, read-write separation, SQL performance analysis, etc. However, blindly making some MySQL configuration adjustments without considering the actual running status of MySQL is a rough and estimated approach. It may just meet the current actual problem and improve performance, or it may be useless. Therefore, technology should be combined with actual conditions and theories, and cannot be done on paper.

This article refers to the official MySQL documentation: server-status-variables

Practice

View the variables that can be monitored

We can use the statement SHOW [GLOBAL | SESSION] STATUS to view the visible status indicators of the global/current session

For example, we want to see what MySQL global status indicators are available.

mysql> SHOW GLOBAL STATUS;

+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
...
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |
...
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+-----------------------------------+------------+

If you are only interested in your current connection, you can use SHOW SESSION STATUS

If you want to refresh the statistics of status variables, you can use the command FLUSH STATUS

Many status variables are reset to 0 by the FLUSH STATUS statement.

Some key indicator queries

Based on the state variables that can be queried above, I selected several variables for some demonstrations.

Query MySQL running time:

mysql> show status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 398545 |
+---------------+--------+
1 row in set (0.01 sec)

Query the number of select executions in MySQL

mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 19 |
+---------------+-------+
1 row in set (0.01 sec)

Query the number of insert executions in MySQL

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert | 0 |
+---------------+-------+
1 row in set (0.00 sec)

Query the number of update executions in MySQL

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update | 0 |
+---------------+-------+
1 row in set (0.00 sec)

Query the number of delete executions in MySQL

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete | 0 |
+---------------+-------+
1 row in set (0.00 sec)

The above CRUD count can be directly used as the basis for actual MySQL performance optimization. For example, the memory allocation strategy can be adjusted according to the ratio of reads and writes.

Query connection times

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 286 |
+---------------+-------+
1 row in set (0.00 sec)

Query the number of slow queries

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set (0.00 sec)

Query slow query time (default 10 seconds)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

In fact, there are many indicators. When you encounter actual problems, you can query them in a targeted manner and adjust MySQL parameters based on these data.

The above is the detailed content of the use of MySQL SHOW STATUS statement. For more information about MySQL SHOW STATUS, please pay attention to other related articles on 123WORDPRESS.COM!