- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
The server maintains many status variables that provide
information about its operation. You can view these variables
and their values by using the SHOW STATUS
statement (see Section 12.5.4.17, “SHOW STATUS Syntax”).
mysql> SHOW STATUS;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
...
The following table lists all available server status variables:
Many status variables are reset to 0 by the FLUSH
STATUS statement.
MySQL Enterprise. For expert advice on using status variables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
The status variables have the following meanings. The
Com_ statement
counter variables were added beginning with MySQL 3.23.47. The
xxxQcache_ query
cache variables were added beginning with MySQL 4.0.1.
Otherwise, variables with no version indicated have been present
since at least MySQL 3.22.
xxx
For meanings of status variables specific to MySQL Cluster, see Section 15.4.4, “MySQL Cluster Status Variables”.
-
The number of connections that were aborted because the client died without closing the connection properly. See Section A.1.2.11, “Communication Errors and Aborted Connections”.
-
The number of failed attempts to connect to the MySQL server. See Section A.1.2.11, “Communication Errors and Aborted Connections”.
-
The number of transactions that used the temporary binary log cache but that exceeded the value of
binlog_cache_sizeand used a temporary file to store statements from the transaction. This variable was added in MySQL 4.1.2. -
The number of transactions that used the temporary binary log cache. This variable was added in MySQL 4.1.2.
-
The number of bytes received from all clients. This variable was added in MySQL 3.23.7.
-
The number of bytes sent to all clients. This variable was added in MySQL 3.23.7.
-
The
Com_statement counter variables were added beginning with MySQL 3.23.47. They indicate the number of times eachxxxxxxstatement has been executed. There is one status variable for each type of statement. For example,Com_deleteandCom_insertcountDELETEandINSERTstatements, respectively. However, if a query result is returned from query cache, the server increments theQcache_hitsstatus variable, notCom_select. See Section 7.5.3.4, “Query Cache Status and Maintenance”.New
Com_stmt_status variables have been added in MySQL 4.1.13:xxxCom_stmt_prepareCom_stmt_executeCom_stmt_send_long_dataCom_stmt_resetCom_stmt_close
Those variables stand for prepared statement commands. Their names refer to the
COM_command set used in the network layer. In other words, their values increase whenever prepared statement API calls such as mysql_stmt_prepare(), mysql_stmt_execute(), and so forth are executed. However,xxxCom_stmt_prepare,Com_stmt_executeandCom_stmt_closealso increase forPREPARE,EXECUTE, orDEALLOCATE PREPARE, respectively. Additionally, the values of the older (available since MySQL 4.1.3) statement counter variablesCom_prepare_sql,Com_execute_sql, andCom_dealloc_sqlincrease for thePREPARE,EXECUTE, andDEALLOCATE PREPAREstatements.All of the
Com_stmt_variables are increased even if their argument (a prepared statement) is unknown or an error occurred during execution; in other words, their values correspond to the number of requests issued, not to the number of requests successfully completed.xxx -
The number of connection attempts (successful or not) to the MySQL server.
-
The number of temporary tables on disk created automatically by the server while executing statements. This variable was added in MySQL 3.23.24.
-
How many temporary files mysqld has created. This variable was added in MySQL 3.23.28.
-
The number of in-memory temporary tables created automatically by the server while executing statements. If
Created_tmp_disk_tablesis large, you may want to increase thetmp_table_sizevalue to cause temporary tables to be memory-based instead of disk-based. -
The number of rows written with
INSERT DELAYEDfor which some error occurred (probablyduplicate key). -
The number of
INSERT DELAYEDhandler threads in use. -
The number of
INSERT DELAYEDrows written. -
The number of executed
FLUSHstatements. -
The number of internal
COMMITstatements. This variable was added in MySQL 4.0.2. -
The number of times a row was deleted from a table.
-
The number of times the first entry was read from an index. If this value is high, it suggests that the server is doing a lot of full index scans; for example,
SELECT col1 FROM foo, assuming thatcol1is indexed. -
The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
-
The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
-
The number of requests to read the previous row in key order. This read method is mainly used to optimize
ORDER BY ... DESC. This variable was added in MySQL 3.23.6. -
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.
-
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
-
The number of internal
ROLLBACKstatements. This variable was added in MySQL 4.0.2. -
The number of requests to update a row in a table.
-
The number of requests to insert a row in a table.
-
The number of key blocks in the key cache that have changed but have not yet been flushed to disk. This variable was added in MySQL 4.1.1. It used to be known as
Not_flushed_key_blocks. -
The number of unused blocks in the key cache. You can use this value to determine how much of the key cache is in use; see the discussion of
key_buffer_sizein Section 5.1.3, “System Variables”. This variable was added in MySQL 4.1.2. -
The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
-
The number of requests to read a key block from the cache.
-
The number of physical reads of a key block from disk. If
Key_readsis large, then yourkey_buffer_sizevalue is probably too small. The cache miss rate can be calculated asKey_reads/Key_read_requests. -
The number of requests to write a key block to the cache.
-
The number of physical writes of a key block to disk.
-
The maximum number of connections that have been in use simultaneously since the server started.
-
The number of rows waiting to be written in
INSERT DELAYqueues. -
The old name for
Key_blocks_not_flushedbefore MySQL 4.1.1. -
The number of files that are open.
-
The number of streams that are open (used mainly for logging).
-
The number of tables that are open.
-
The number of tables that have been opened. If
Opened_tablesis big, yourtable_cachevalue is probably too small. -
The current number of prepared statements. (The maximum number of statements is given by the
max_prepared_stmt_countsystem variable.) This variable was added in MySQL 4.1.23. -
The number of free memory blocks in the query cache.
-
The amount of free memory for the query cache.
-
The number of query cache hits.
-
The number of queries added to the query cache.
-
The number of queries that were deleted from the query cache because of low memory.
-
The number of non-cached queries (not cacheable, or not cached due to the
query_cache_typesetting). -
The number of queries registered in the query cache.
-
The total number of blocks in the query cache.
-
The number of statements that clients have sent to the server.
-
The status of fail-safe replication (not yet implemented).
-
The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. This variable was added in MySQL 3.23.25.
-
The number of joins that used a range search on a reference table. This variable was added in MySQL 3.23.25.
-
The number of joins that used ranges on the first table. This is normally not critical issue even if the value is quite large. This variable was added in MySQL 3.23.25.
-
The number of joins without keys that check for key usage after each row. (If this is not equal to
0, you should very carefully check the indexes of your tables.) This variable was added in MySQL 3.23.25. -
The number of joins that did a full scan of the first table. This variable was added in MySQL 3.23.25.
-
The number of temporary tables that the slave SQL thread currently has open. This variable was added in MySQL 3.23.29.
-
Total (since startup) number of times the replication slave SQL thread has retried transactions. This variable was added in MySQL 4.1.11.
-
Slave_runningThis is
ONif this server is a slave that is connected to a master, and both the I/O SQL and threads are running. This variable was added in MySQL 3.23.16. -
The number of threads that have taken more than
slow_launch_timeseconds to create. This variable was added in MySQL 3.23.15. -
The number of queries that have taken more than
long_query_timeseconds. See Section 5.3.5, “The Slow Query Log”. -
The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the
sort_buffer_sizesystem variable. This variable was added in MySQL 3.23.28. -
The number of sorts that were done with ranges. This variable was added in MySQL 3.23.25.
-
The number of sorted rows. This variable was added in MySQL 3.23.25.
-
The number of sorts that were done by scanning the table. This variable was added in MySQL 3.23.25.
-
Ssl_xxxVariables used for SSL connections. These variables were added in MySQL 4.0.0.
-
The number of times that a request for a table lock could be granted immediately. This variable was added in MySQL 3.23.33.
-
The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. This variable was added in MySQL 3.23.33.
-
The number of threads in the thread cache. This variable was added in MySQL 3.23.17.
-
The number of currently open connections.
-
The number of threads created to handle connections. If
Threads_createdis big, you may want to increase thethread_cache_sizevalue. The cache miss rate can be calculated asThreads_createddivided byConnections. This variable was added in MySQL 3.23.31. -
The number of threads that are not sleeping.
-
The number of seconds that the server has been up.
User Comments
Reading the explanation for Handler read rnd next , I question it! I list some number from a test db that does almost all accesses by locating a record with a key (GE or xxx%) and then using next to access related record; yet the Handler read rnd next is relatively large.
Handler read key 42053
Handler read next 453703
Handler read rnd 696
Handler read rnd next 104378
On MySQL 5.0 the com_* variables of 'show status' are counted for the current connection only. The new undocumented command 'show global status' shows server-wide counters. (http://bugs.mysql.com/bug.php?id=19422)
In version 4.0.17, the explanation to Handler_ is different:
Example:
Handler_delete:
The number of times a row was deleted from a table.
Handler_update
The number of requests to update a row in a table.
Handler_write
The number of requests to insert a row in a table.
Could this be a typo for Handler_delete? Because requests and rows updated/deleted/inserted are different concept.
Sheila
Add your own comment.