MySQL uses show status to view the status of the MySQL server, statusmysql

MySQL uses show status to view the status of the MySQL server, statusmysql

During website development in the LAMP architecture, we sometimes need to know the MySQL server status information, such as the running time after MySQL is started and the number of client session connections of MySQL, the number of slow queries executed by the current MySQL server, the number of SELECT statements executed by the current MySQL, the number of UPDATE/DELETE/INSERT statements executed, and other statistical information, so that we can adjust or optimize the current MySQL Server Based on its running 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 doscommand window, and execute the show status; command, we will see the following information:

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

-- Query the running statistical time show status like 'uptime' after the current MySQL instance is started ';

Now, we can see the following results:

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

Similarly, if we want to execute the SELECT statement this time after MySQL is started, we can execute the following statement:

show status like 'com_select';

The output result is 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, you can run the following statement to view the thread information of the MySQL server:

show status like 'Thread_%';

The output result is 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 select statement statistics displayed only indicate the number of SELECT statements executed by the current session connection. The complete syntax of the show status command is as follows:

SHOW [statistical scope] STATUS [LIKE 'status itemname'] -- Statistical range keywords are classified into GLOBAL and SESSION (or LOCAL.

In the complete show status syntax, the part in "[]" is optional. If our show status statement does not contain the keyword of the statistical range, the default statistical range is SESSION, that is, only the status information of the current connection is counted. To query the total number of SELECT statements executed by all connections after MySQL is started, run the following statement:

show global status like 'com_select';

The above is the detailed usage of show status. Because show status has many statistical items, we will not explain the specific meaning of each statistical item one by one. Here, we only list some common status information viewing statements:

-- View the MySQL running time (in seconds) after the current start show status like 'uptime'; -- view the number of executions of the select statement show [global] status like 'com _ select '; -- view the execution count of the insert statement show [global] status like 'com _ insert'; -- view the execution count of the update statement show [global] status like 'com _ Update '; -- view the execution count of the delete statement show [global] status like 'com _ delete'; -- view the number of connections that attempt to connect to MySQL (whether or not the connection is successful '; -- view the number of threads in the thread cache. Show status like 'threads _ cached '; -- view the number of currently opened connections. Show status like 'threads _ connected'; -- view the number of currently opened connections. Show status like 'threads _ connected'; -- view the number of threads created to process connections. If Threads_created is large, you may need to increase the value of thread_cache_size. Show status like 'threads _ created '; -- view the number of activated (non-sleep) threads. Show status like 'threads _ running '; -- view the number of times the table lock is obtained immediately. Show status like 'table _ locks_immediate '; -- view the number of times the table lock cannot be obtained immediately. If the value is high and there are performance problems, you should first optimize the query and then split the table or use replication. Show status like 'table _ locks_waited '; -- view the number of threads created when slow_launch_time is exceeded. Show status like 'slow _ launch_threads; -- view the number of queries whose query time exceeds long_query_time. Show status like 'slow _ queries ';

The above content is described here. For other articles, refer to the previous articles of the customer's house.