Show status in MySQL view health

Login directly under the command line MySQL run show STATUS;

1, view MySQL server configuration information

The code is as follows	Copy Code
Mysql> Show variables;

2, view the MySQL server running the various status values

The code is as follows	Copy Code
Mysql> show global status;

3, slow query

code is as follows

copy code

mysql> show variables like '%slow% '; +------------------+--- ----+ | variable_name    | Value | +------------------+-------+ | log_slow_queries | off   | | slow_launch_time | 2     | +------------------+-------+ mysql> show global status like '%slow% '; +---------------------+-------+ | variable_name       | Value | +---------------------+-------+ | Slow_launch_threads | 0     | | slow_queries        | 279   | +---------------------+-------+

Slow query is turned off in configuration (preferably open, easy to optimize), more than 2 seconds is slow query, a total of 279 slow query

The code is as follows	Copy Code
Show VARIABLES

Show VARIABLES is looking at MySQL's configuration parameters, and you can also use a VARIABLES like ' key% '

The code is as follows	Copy Code
Show Processlist

Show Processlist is a review of ongoing processes and is useful for troubleshooting situations such as locking tables. Under normal circumstances, open the slow query of MySQL

Records are also useful for troubleshooting.

The code is as follows	Copy Code
Show OPEN TABLES

Show open tables displays a list of the tables that are currently open.

Mysqladmin status

Use the Mysqladmin tool with MySQL to view status, using the following command

The code is as follows	Copy Code
mysqladmin-uroot–password= ' Password ' status

The results shown are as follows:

uptime:87117 threads:1 questions:5481626 Slow queries:16 opens:2211 Flush tables:1 Open tables:

Queries per second avg:62.923

You can also add the-I 5 parameter to automatically refresh every five seconds.

The code is as follows	Copy Code
mysqladmin-uroot–password= ' Password ' status-i 5 Mysqladmin Extended-status

The same can be used mysqladmin-uroot–password= ' password ' extended-status to see more MySQL run information, this

The same way as the first view of the information.

share some noun notes

Query_cache_limit: Queries exceeding this size will not cache
Query_cache_min_res_unit: Minimum size of cache block
Query_cache_size: Query Cache Size
Query_cache_type: Cache type, determining what kind of query to cache, the example indicates that the Select Sql_no_cache query is not cached
Query_cache_wlock_invalidate: If any other clients are writing to the MyISAM table, if the query is in query cache, return

Back to the cache results or wait for the write operation to complete the reread table to obtain results.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query are

is small data query, it is easy to cause memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce the query_cache_min_res_unit,

If your query is a small amount of data.

Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%

The query cache utilization rate below 25% indicates that the query_cache_size setting is too large to be reduced properly; query cache utilization is over 80% and

Qcache_lowmem_prunes > 50 says query_cache_size may be a little small, or too much debris.

Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit ratio = 1.94%, bad hit,

It may be written more frequently, and it may be fragmented.

Some explanations

Aborted_clients the number of connections that have been dropped because the customer has not properly shut down the connection already dead.
Aborted_connects the number of attempts to connect to a MySQL server that has failed.
The number of times connections attempted to connect to the MySQL server.
Created_tmp_tables the number of hidden temporary tables that have been created when the statement is executed.
Delayed_insert_threads the number of deferred insert processor threads in use.
Delayed_writes the number of rows written with insert delayed.
Delayed_errors the number of rows in which some errors, possibly heavy-key values, are written with insert delayed.
Flush_commands the number of times the Flush command was executed.
The number of times the Handler_delete request deletes rows from a table.
Handler_read_first the number of times the first row in the table is requested to be read.
The Handler_read_key request number is based on the key read line.
The number of times a handler_read_next request reads in a row based on a key.
The number of times a HANDLER_READ_RND request reads a row based on a fixed position.
Handler_update the number of times a row in the table was requested to be updated.
The number of times the Handler_write request inserts a row into the table.
key_blocks_used the number of blocks used for the keyword cache.
Key_read_requests the number of times a key value is requested to be read from the cache.
Key_reads the number of times a key value is physically read from disk.
Key_write_requests request to write a key block to the cache count.
Key_writes the number of times a key-value block is physically written to disk.
The maximum number of connections that the max_used_connections uses at the same time.
Not_flushed_key_blocks the key that has been changed in the key cache but has not yet been emptied onto the disk.
Not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue.
Open_tables the number of tables opened.
Open_files the number of open files.
Number of Open_streams open streams (mainly for log records)
Opened_tables the number of tables already open.
Questions the number of queries sent to the server.
Slow_queries the number of queries to spend more than long_query_time time.
threads_connected the number of connections currently open.
Threads_running number of threads not in sleep.
How many seconds does the Uptime server work?