• notice
  • Congratulations on the launch of the Sought Tech site

MySQL Use ShowStatus to view mysql server status information

In the process of website development of the LAMP architecture, there are times when we need to understand the status of the MySQL server, such as the current running after MySQL is started Time, the number of current MySQL client sessions, the number of slow queries executed by the current MySQL server, how many SELECT statements are currently executed by MySQL, how many UPDATE/DELETE/INSERT statements are executed, and other statistical information, so as to facilitate us based on the current MySQL server operation The corresponding adjustment or optimization work is performed on the status.

In MySQL, we can use the SHOW STATUS command statement to view the status information of the MySQL server.Next, we connect to MySQL in the form of a DOS command window and execute the show status; command, we will see the following display information:

When we execute the show status statement, MySQL will list up to more than 300 status information records, including various information for us to view and understand.However, if we directly use the show status command to get more than 300 records, we will be dazzled, so we hope to be able to "view" part of the status information on demand.At this time, we can add the corresponding like clause after the show status statement.For example, if we want to view the current running time after MySQL is started, we can execute the following statement:

--Query the current running statistical time of MySQL after this startup
show status like'uptime';

At this point, we can see the following results:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 5667 |
+---------------+-------+
1 row in set (0.00 sec)

Similarly, if we want the number of SELECT statements executed after MySQL started this time, we can execute the following statement:

show status like'com_select';

The corresponding output results are as follows:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+
1 row in set (0.00 sec)

In addition, similar to the LIKE keyword in the WHERE clause, the LIKE keyword after show status can also use wildcards such as'_' or'%' for fuzzy matching.For example, we can execute the following statement to view the thread information of the MySQL server:

show status like'Thread_%';

The corresponding output results are as follows:

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

It is worth noting that in the execution example of the show status like'com_select' command above, the displayed SELECT statement statistics only indicate the number of SELECT statements executed by the current session connection.Because, the complete syntax of the show status command is as follows:

SHOW [statistic range] STATUS [LIKE'status item name']
--Statistics scope keywords are divided into GLOBAL and SESSION (or LOCAL) two kinds.

In the complete grammar of show status, the part in "[]" is optional.If our show status statement does not contain the statistical range keyword, the default statistical range is SESSION, that is, only the current connection is counted.Status information.If we need to query the total number of SELECT statements executed by all connections since the current MySQL started, we can execute the following statement:

show global status like'com_select';

The above is the detailed usage of show status.Since show status has many status statistics items, we will not explain the specific meaning of each statistics item one by one.Here, we only list some commonly used status information view statements:

--View the running time of MySQL after this time (unit: second)
show status like'uptime';

-View the number of executions of the select statement
show [global] status like'com_select';

-View the number of executions of the insert statement
show [global] status like'com_insert';

-View the number of executions of the update statement
show [global] status like'com_update';

-View the number of executions of the delete statement
show [global] status like'com_delete';

-View the number of connections trying to connect to MySQL (regardless of whether the connection is successful or not)
show status like'connections';

-View the number of threads in the thread cache.
show status like'threads_cached';

-View the number of currently open connections.
show status like'threads_connected';

-View the number of currently open connections.
show status like'threads_connected';

-View the number of threads created to handle the connection.If Threads_created is larger, you may have to increase the thread_cache_size value.
show status like'threads_created';

-View the number of active (non-sleeping) threads.
show status like'threads_running';


-View the number of locks on the table immediately acquired.
show status like'table_locks_immediate';

-View the number of locks on the table that cannot be obtained immediately.If the value is high and there are performance issues, you should optimize the query first, then split the table or use replication.
show status like'table_locks_waited';

-View the number of threads whose creation time exceeds slow_launch_time seconds.
show status like'slow_launch_threads';

--View the number of queries whose query time exceeds long_query_time seconds.
show status like'slow_queries';

This is the end of the above content.For others, please refer to the previous articles.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+