- Documentation Library
- Table of Contents
- MySQL 5.5 Manual
- MySQL 5.4 Manual
- MySQL 5.1 Manual
- MySQL 5.0 Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 5.1 The MySQL Server
- 5.1.1 Server Option and Variable Reference
- 5.1.2 Server Command Options
- 5.1.3 Server System Variables
- 5.1.4 Session System Variables
- 5.1.5 Using System Variables
- 5.1.6 Server Status Variables
- 5.1.7 Server SQL Modes
- 5.1.8 Server-Side Help
- 5.1.9 Server Response to Signals
- 5.1.10 The Shutdown Process
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.4.5.22, “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:
Table 5.4. Status Variable Summary
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.3.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.5.2.11, “Communication Errors and Aborted Connections”.
-
The number of failed attempts to connect to the MySQL server. See Section A.5.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.4.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 internal on-disk temporary tables created by the server while executing statements. This variable was added in MySQL 3.23.24.
If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the
tmp_table_sizeandmax_heap_table_sizevalues. IfCreated_tmp_disk_tablesis large, you may want to increase thetmp_table_sizeormax_heap_table_sizevalues. value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables.You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the
Created_tmp_disk_tablesandCreated_tmp_tablesvariables.See also Section 7.5.8, “How MySQL Uses Internal Temporary Tables”.
-
How many temporary files mysqld has created. This variable was added in MySQL 3.23.28.
-
The number of internal temporary tables created by the server while executing statements.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the
Created_tmp_disk_tablesandCreated_tmp_tablesvariables.See also Section 7.5.8, “How MySQL Uses Internal Temporary Tables”.
-
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 in an index was read. 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, “Server 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. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.
-
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 noncached 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.
-
This is
ONif this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it isOFF.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.
-
The number of negotiates needed to establish the connection. This variable was added in MySQL 4.0.0.
-
The number of accepted SSL connections. This variable was added in MySQL 4.0.0.
-
The number of callback cache hits. This variable was added in MySQL 4.0.0.
-
The current SSL cipher (empty for non-SSL connections). This variable was added in MySQL 4.0.0.
-
The list of possible SSL ciphers. This variable was added in MySQL 4.0.0.
-
The number of SSL connection attempts to an SSL-enabled master. This variable was added in MySQL 4.0.0.
-
The number of negotiates needed to establish the connection to an SSL-enabled master. This variable was added in MySQL 4.0.0.
-
The SSL context verification depth (how many certificates in the chain are tested). This variable was added in MySQL 4.0.0.
-
The SSL context verification mode. This variable was added in MySQL 4.0.0.
-
The default SSL timeout. This variable was added in MySQL 4.0.0.
-
The number of successful SSL connections to the server. This variable was added in MySQL 4.0.0.
-
The number of successful slave connections to an SSL-enabled master. This variable was added in MySQL 4.0.0.
-
The number of SSL session cache hits. This variable was added in MySQL 4.0.0.
-
The number of SSL session cache misses. This variable was added in MySQL 4.0.0.
-
The SSL session cache mode. This variable was added in MySQL 4.0.0.
-
The number of SSL session cache overflows. This variable was added in MySQL 4.0.0.
-
The SSL session cache size. This variable was added in MySQL 4.0.0.
-
The number of SSL session cache timeouts. This variable was added in MySQL 4.0.0.
-
How many SSL connections were reused from the cache. This variable was added in MySQL 4.0.0.
-
Ssl_used_session_cache_entriesHow many SSL session cache entries were used. This variable was added in MySQL 4.0.0.
-
The verification depth for replication SSL connections. This variable was added in MySQL 4.0.0.
-
The verification mode for replication SSL connections. This variable was added in MySQL 4.0.0.
-
The SSL version number. This variable was 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
There's a bug in 5.0.36 (and likely surrounding versions) for Handler_write: it increases by one every time a row from SHOW STATUS is displayed:
1 row in set (0.00 sec)------
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL STATUS; SHOW SESSION STATUS;
[...] 224 rows in set (0.01 sec)
[...] 224 rows in set (0.01 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_write';
------
Given the bug for Handler_write it is not possible to find out how many rows were inserted in this version, unless you use a workaround:
Rows_inserted= Handler_write - (number of times SHOW STATUS was run * number of rows SHOW STATUS displayed)
This could be programmed further using the Com_show_status variable:
--------
mysql> SHOW SESSION STATUS LIKE 'Com_show_status';
1 row in set (0.00 sec)
--------
So, on a system/version that had 224 rows in SHOW STATUS (please check), the workaround formula would be:
Rows_inserted=Handler_write-(Com_show_status * 224)
Note though that this workaround does not work when using partial SHOW STATUS statements (using LIKE etc) from time to time.
Add your own comment.