Use Show status to view MySQL server status information

In the lamp architecture of the Web site development process, sometimes we need to understand the MySQL server status information, such as the current MySQL startup run time , the current MySQL client session connections , the current MySQL server execution number of slow queries , how many SELECT statements The current MySQL executes, how many UPDATE / DELETE / INSERT statements are executed, etc. This makes it easier for us to adjust or optimize the current MySQL server's operating state.

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

Perform some of the results shown by the show status directive

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

--Query the current MySQL Run statistics time after this startup
' 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 statements executed after MySQL starts SELECT , we can execute the following statement:

' Com_select ';

The corresponding output results are as follows:

+---------------+-------+| variable_name | Value |+---------------+-------+| Com_select    | 1     |+---------------+-------+1 row in Set (0.00 sec)

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

' 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 show status like ‘com_select‘ execution example of the above instruction, the SELECT statement statistics displayed only represent the number of statements executed by the current session connection SELECT . Because the show status complete syntax of the instruction is as follows:

SHOW[Statistical Range] STATUS     [    like     ' status Item name '    ]    
 --statistic range keywords are divided into   global    and   session      (or     local     )   two kinds. 

In show status the complete syntax, the section in "[]" is optional, and if our show status statement does not contain a statistical range keyword, the default statistic range is SESSIO N , that is, only the status information of the current connection is counted. if we need to query the total number of statements executed by all connections since the current MySQL startup SELECT , we can execute the following statement:

Global' Com_select '; 

The above is show status the detailed usage. Because show status of the number of State statistics, we no longer one by one explain the specific meaning of each statistic item, here we only list some commonly used status information view statements:

//--View MySQL run time after this boot (in seconds)Show status like ' uptime '; //--View the number of executions of the SELECT statementShow [Global] Status like ' Com_select '; //--View the number of executions of INSERT statementsShow [Global] Status like ' Com_insert '; //--View the number of executions of the UPDATE statementShow [Global] Status like ' Com_update '; //--View the number of executions of the DELETE statementShow [Global] Status like ' Com_delete '; //--View the number of connections attempting to connect to MySQL (regardless of whether the connection is successful)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 the threads_created is larger, you may want to increase the thread_cache_size value. Show status like ' threads_created '; //--View the number of active (non-sleep) threads. Show status like ' Threads_running '; //--View the number of locks for the table immediately obtained. Show status like ' Table_locks_immediate '; //--View the number of locks on a table that cannot be obtained immediately. If the value is high and there is a performance problem, you should first refine the query and then split the table or use replication. Show status like ' table_locks_waited '; //--View the number of threads that have been created longer than slow_launch_time seconds. Show status like ' Slow_launch_threads '; //--View the number of queries that have more than long_query_time seconds of query time. Show status like ' Slow_queries ';

Use Show status to view MySQL server status information