13th. mysql Server Status-high-performance MySQL learning notes

13.1 System Variables-server configuration variables

MySQL displays many system variables through the show VARIABLES SQL command.

13.2 State Variable--show status

The Show status command displays the server state variables in a table of two columns (name/value). These variables are read-only.

Show status shows the session variables by default, and show global status displays the Globals.

can also be from INFORMATION_SCHEMA. Global_status and INFORMATION_SCHEMA. Session_status table out.

32-bit systems many counters are zeroed. 64 bits rarely appear.

The best way to look at these variables is to see how much they have changed in a time period of several minutes.

The following is a brief introduction to the MySQL manual for all variables.

13.2.1 Thread and link statistics

Connections, Aborted_connects ...

Eg: number of threads created per second (threads_created/uptime). If this value is no worse than 0, the thread buffer is too small.

13.2.2 Status of binary logs

The Binlog_cache_use and Binlog_cache_disk_use state variables show how many transactions are saved by the binary log.

13.2.3 Command counter

The com_* variable records the number of each type of SQL or C API command that has been issued.

Eg:com_select records the number of SELECT statements,

COM_CHANGE_DB records the number of times a default connection database is changed using use or through the C API.

The Com_admin_commands state variable can be large, which not only records the number of administrative commands, but also records the number of ping requests sent to the MySQL instance.

Questions records the total number of queries and commands received by the server. Because of factors such as caching, the total number of com_* variables is not exactly equal.

13.2.4 Temporary files and tables

View the variables that record the number of times MySQL creates temporary tables and files:

Mysql> SHOW GLOBAL STATUS like ' created_tmp% ';

13.2.5 handler operation

The Handler API is the interface between MySQL and the storage engine. The handler_* variable records the number of Handler operations.

Reading the handler_* variable allows you to see what kinds of jobs the server is doing most.

13.2.6 MyISAM index Key buffer

The key_* variable contains the measures and counters for the MyISAM index key buffer.

13.2.2 File Descriptor

The open_* variable.

If you use the MyISAM engine primarily, it is important to view the statistics for the file descriptor.

13.2.8 Query Cache

Check the query cache through the qcache_* state variable.

13.2.9 various types of select

The select_* variable records the number of select queries of various types.

Select_range: The number of joins that are scanned within a specified range of the index of the first table.

Select_scan: The number of joins that make a full table scan of the first table.

Select_full_range_join: ....

Select_range_check: The cost of such a query plan is high.

Select_full_join: Very bad.