(mysql.info.gz) Server status variables
Info Catalog
(mysql.info.gz) Server system variables
(mysql.info.gz) Configuring MySQL
5.2.4 Server Status Variables
-----------------------------
The server maintains many status variables that provide information
about its operations. You can view these variables and their values by
using the `SHOW STATUS' statement:
mysql> SHOW STATUS;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 60 |
| Created_tmp_tables | 8340 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_delete | 462604 |
| Handler_read_first | 105881 |
| Handler_read_key | 27820558 |
| Handler_read_next | 390681754 |
| Handler_read_prev | 6022500 |
| Handler_read_rnd | 30546748 |
| Handler_read_rnd_next | 246216530 |
| Handler_update | 16945404 |
| Handler_write | 60356676 |
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
| Max_used_connections | 0 |
| Not_flushed_delayed_rows | 0 |
| Not_flushed_key_blocks | 0 |
| Open_files | 2 |
| Open_streams | 0 |
| Open_tables | 1 |
| Opened_tables | 44600 |
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
| Questions | 2026873 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 99646 |
| Select_range_check | 0 |
| Select_scan | 30802 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 30 |
| Sort_range | 500 |
| Sort_rows | 30296250 |
| Sort_scan | 4650 |
| Table_locks_immediate | 1920382 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 30022 |
| Threads_running | 1 |
| Uptime | 80380 |
+--------------------------+------------+
Many status variables are reset to 0 by the `FLUSH STATUS' statement.
The status variables have the following meanings. The `Com_XXX'
statement counter variables were added beginning with MySQL 3.23.47. The
`Qcache_XXX' 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.
`Aborted_clients'
The number of connections that were aborted because the client
died without closing the connection properly. Communication
errors.
`Aborted_connects'
The number of tries to connect to the MySQL server that failed.
Communication errors.
`Binlog_cache_disk_use'
The number of transactions that used the temporary binary log
cache but that exceeded the value of `binlog_cache_size' and used
a temporary file to store statements from the transaction. This
variable was added in MySQL 4.1.2.
`Binlog_cache_use'
The number of transactions that used the temporary binary log
cache. This variable was added in MySQL 4.1.2.
`Bytes_received'
The number of bytes received from all clients. This variable was
added in MySQL 3.23.7.
`Bytes_sent'
The number of bytes sent to all clients. This variable was added
in MySQL 3.23.7.
`Com_XXX'
The number of times each XXX statement has been executed. There
is one status variable for each type of statement. For example,
`Com_delete' and `Com_insert' count `DELETE' and `INSERT'
statements.
`Connections'
The number of connection attempts (successful or not) to the MySQL
server.
`Created_tmp_disk_tables'
The number of temporary tables on disk created automatically by
the server while executing statements. This variable was added in
MySQL 3.23.24.
`Created_tmp_files'
How many temporary files `mysqld' has created. This variable was
added in MySQL 3.23.28.
`Created_tmp_tables'
The number of in-memory temporary tables created automatically by
the server while executing statements. If
`Created_tmp_disk_tables' is big, you may want to increase the
`tmp_table_size' value to cause temporary tables to be memory-based
instead of disk-based.
`Delayed_errors'
The number of rows written with `INSERT DELAYED' for which some
error occurred (probably `duplicate key').
`Delayed_insert_threads'
The number of `INSERT DELAYED' handler threads in use.
`Delayed_writes'
The number of `INSERT DELAYED' rows written.
`Flush_commands'
The number of executed `FLUSH' statements.
`Handler_commit'
The number of internal `COMMIT' statements. This variable was
added in MySQL 4.0.2.
`Handler_discover'
The MySQL server can ask the `NDB Cluster' storage engine if it
knows about a table with a given name. This is called discovery.
`Handler_discover' indicates the number of time tables have been
discovered. This variable was added in MySQL 4.1.2.
`Handler_delete'
The number of times a row was deleted from a table.
`Handler_read_first'
The number of times the first entry was read from an index. If
this is high, it suggests that the server is doing a lot of full
index scans; for example, `SELECT col1 FROM foo', assuming that
`col1' is indexed.
`Handler_read_key'
The number of requests to read a row based on a key. If this is
high, it is a good indication that your queries and tables are
properly indexed.
`Handler_read_next'
The number of requests to read the next row in key order. This
will be incremented if you are querying an index column with a
range constraint or if you are doing an index scan.
`Handler_read_prev'
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.
`Handler_read_rnd'
The number of requests to read a row based on a fixed position.
This will be 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 whole tables or you have joins that don't
use keys properly.
`Handler_read_rnd_next'
The number of requests to read the next row in the data file.
This will be 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.
`Handler_rollback'
The number of internal `ROLLBACK' statements. This variable was
added in MySQL 4.0.2.
`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.
`Innodb_buffer_pool_pages_data'
The number of pages containing data (dirty or clean). Added in
MySQL 5.0.2.
`Innodb_buffer_pool_pages_dirty'
The number of pages currently dirty. Added in MySQL 5.0.2.
`Innodb_buffer_pool_pages_flushed'
The number of buffer pool pages that have been requested to be
flushed. Added in MySQL 5.0.2.
`Innodb_buffer_pool_pages_free'
The number of free pages. Added in MySQL 5.0.2.
`Innodb_buffer_pool_pages_latched'
The number of latched pages in `InnoDB' buffer pool. These are
pages currently being read or written or that can't be flushed or
removed for some other reason. Added in MySQL 5.0.2.
`Innodb_buffer_pool_pages_misc'
The number of pages busy because they have been allocated for
administrative overhead such as row locks or the adaptive hash
index. This value can also be calculated as
`Innodb_buffer_pool_pages_total' - `Innodb_buffer_pool_pages_free'
- `Innodb_buffer_pool_pages_data'. Added in MySQL 5.0.2.
`Innodb_buffer_pool_pages_total'
Total size of buffer pool, in pages. Added in MySQL 5.0.2.
`Innodb_buffer_pool_read_ahead_rnd'
The number of "random" read-aheads `InnoDB' initiated. This happens
when a query is to scan a large portion of a table but in random
order. Added in MySQL 5.0.2.
`Innodb_buffer_pool_read_ahead_seq'
The number of sequential read-aheads `InnoDB' initiated. This
happens when `InnoDB' does a sequential full table scan. Added in
MySQL 5.0.2.
`Innodb_buffer_pool_read_requests'
The number of logical read requests `InnoDB' has done. Added in
MySQL 5.0.2.
`Innodb_buffer_pool_reads'
The number of logical reads that `InnoDB' could not satisfy from
buffer pool and had to do a single-page read. Added in MySQL
5.0.2.
`Innodb_buffer_pool_wait_free'
Normally, writes to the `InnoDB' buffer pool happen in the
background. However, if it's necessary to read or create a page
and no clean pages are available, it's necessary to wait for pages
to be flushed first. This counter counts instances of these waits.
If the buffer pool size was set properly, this value should be
small. Added in MySQL 5.0.2.
`Innodb_buffer_pool_write_requests'
The number writes done to the `InnoDB' buffer pool. Added in
MySQL 5.0.2.
`Innodb_data_fsyncs'
The number of `fsync()' operations so far. Added in MySQL 5.0.2.
`Innodb_data_pending_fsyncs'
The current number of pending `fsync()' operations. Added in
MySQL 5.0.2.
`Innodb_data_pending_reads'
The current number of pending reads. Added in MySQL 5.0.2.
`Innodb_data_pending_writes'
The current number of pending writes. Added in MySQL 5.0.2.
`Innodb_data_read'
The amount of data read so far, in bytes. Added in MySQL 5.0.2.
`Innodb_data_reads'
The total number of data reads. Added in MySQL 5.0.2.
`Innodb_data_writes'
The total number of data writes. Added in MySQL 5.0.2.
`Innodb_data_written'
The amount of data written so far, in bytes. Added in MySQL 5.0.2.
`Innodb_dblwr_writes'
`Innodb_dblwr_pages_written'
The number of doublewrite writes that have been performed and the
number of pages that have been written for this purpose. Added in
MySQL 5.0.2.
`Innodb_log_waits'
The number of waits we had because log buffer was too small and we
had to wait for it to be flushed before continuing. Added in
MySQL 5.0.2.
`Innodb_log_write_requests'
The number of log write requests. Added in MySQL 5.0.2.
`Innodb_log_writes'
The number of physical writes to the log file. Added in MySQL
5.0.2.
`Innodb_os_log_fsyncs'
The number of fsyncs writes done to the log file. Added in MySQL
5.0.2.
`Innodb_os_log_pending_fsyncs'
The number of pending log file fsyncs. Added in MySQL 5.0.2.
`Innodb_os_log_pending_writes'
Pending log file writes. Added in MySQL 5.0.2.
`Innodb_os_log_written'
The number of bytes written to the log file. Added in MySQL 5.0.2.
`Innodb_page_size'
The compiled-in `InnoDB' page size (default 16KB). Many values are
counted in pages; the page size allows them to be easily converted
to bytes. Added in MySQL 5.0.2.
`Innodb_pages_created'
The number of pages created. Added in MySQL 5.0.2.
`Innodb_pages_read'
The number of pages read. Added in MySQL 5.0.2.
`Innodb_pages_written'
The number of pages written. Added in MySQL 5.0.2.
`Innodb_row_lock_current_waits'
The number of row locks currently being waited for. Added in
MySQL 5.0.3.
`Innodb_row_lock_time'
The total time spent in acquiring row locks, in milliseconds.
Added in MySQL 5.0.3.
`Innodb_row_lock_time_avg'
The average time to acquire a row lock, in milliseconds. Added in
MySQL 5.0.3.
`Innodb_row_lock_time_max'
The maximum time to acquire a row lock, in milliseconds. Added in
MySQL 5.0.3.
`Innodb_row_lock_waits'
The number of times a row lock had to be waited for. Added in
MySQL 5.0.3.
`Innodb_rows_deleted'
The number of rows deleted from `InnoDB' tables. Added in MySQL
5.0.2.
`Innodb_rows_inserted'
The number of rows inserted in `InnoDB' tables. Added in MySQL
5.0.2.
`Innodb_rows_read'
The number of rows read from `InnoDB' tables. Added in MySQL
5.0.2.
`Innodb_rows_updated'
The number of rows updated in `InnoDB' tables. Added in MySQL
5.0.2.
`Key_blocks_not_flushed'
The number of key blocks in the key cache that have changed but
haven't yet been flushed to disk. This variable was added in
MySQL 4.1.1. It used to be known as `Not_flushed_key_blocks'.
`Key_blocks_unused'
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_size' in Server system variables.
This variable was added in MySQL 4.1.2. Server system
variables.
`Key_blocks_used'
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.
`Key_read_requests'
The number of requests to read a key block from the cache.
`Key_reads'
The number of physical reads of a key block from disk. If
`Key_reads' is big, then your `key_buffer_size' value is probably
too small. The cache miss rate can be calculated as
`Key_reads'/`Key_read_requests'.
`Key_write_requests'
The number of requests to write a key block to the cache.
`Key_writes'
The number of physical writes of a key block to disk.
`Last_query_cost'
The total cost of the last compiled query as computed by the query
optimizer. Useful for comparing the cost of different query plans
for the same query. The default value of -1 means that no query
has been compiled yet. This variable was added in MySQL 5.0.1.
`Max_used_connections'
The maximum number of connections that have been in use
simultaneously since the server started.
`Not_flushed_delayed_rows'
The number of rows waiting to be written in `INSERT DELAY' queues.
`Not_flushed_key_blocks'
The old name for `Key_blocks_not_flushed' before MySQL 4.1.1.
`Open_files'
The number of files that are open.
`Open_streams'
The number of streams that are open (used mainly for logging).
`Open_tables'
The number of tables that are open.
`Opened_tables'
The number of tables that have been opened. If `Opened_tables' is
big, your `table_cache' value is probably too small.
`Qcache_free_blocks'
The number of free memory blocks in query cache.
`Qcache_free_memory'
The amount of free memory for query cache.
`Qcache_hits'
The number of cache hits.
`Qcache_inserts'
The number of queries added to the cache.
`Qcache_lowmem_prunes'
The number of queries that were deleted from the cache because of
low memory.
`Qcache_not_cached'
The number of non-cached queries (not cachable, or due to
`query_cache_type').
`Qcache_queries_in_cache'
The number of queries registered in the cache.
`Qcache_total_blocks'
The total number of blocks in the query cache.
`Questions'
The number of queries that have been sent to the server.
`Rpl_status'
The status of failsafe replication (not yet implemented).
`Select_full_join'
The number of joins that 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.
`Select_full_range_join'
The number of joins that used a range search on a reference table.
This variable was added in MySQL 3.23.25.
`Select_range'
The number of joins that used ranges on the first table. (It's
normally not critical even if this is big.) This variable was
added in MySQL 3.23.25.
`Select_range_check'
The number of joins without keys that check for key usage after
each row. (If this is not 0, you should carefully check the
indexes of your tables.) This variable was added in MySQL 3.23.25.
`Select_scan'
The number of joins that did a full scan of the first table. This
variable was added in MySQL 3.23.25.
`Slave_open_temp_tables'
The number of temporary tables currently open by the slave SQL
thread. This variable was added in MySQL 3.23.29.
`Slave_running'
This is `ON' if this server is a slave that is connected to a
master. This variable was added in MySQL 3.23.16.
`Slow_launch_threads'
The number of threads that have taken more than `slow_launch_time'
seconds to create. This variable was added in MySQL 3.23.15.
`Slow_queries'
The number of queries that have taken more than `long_query_time'
seconds. Slow query log.
`Sort_merge_passes'
The number of merge passes the sort algorithm has had to do. If
this value is large, you should consider increasing the value of
the `sort_buffer_size' system variable. This variable was added
in MySQL 3.23.28.
`Sort_range'
The number of sorts that were done with ranges. This variable was
added in MySQL 3.23.25.
`Sort_rows'
The number of sorted rows. This variable was added in MySQL
3.23.25.
`Sort_scan'
The number of sorts that were done by scanning the table. This
variable was added in MySQL 3.23.25.
`Ssl_XXX'
Variables used for SSL connections. These variables were added in
MySQL 4.0.0.
`Table_locks_immediate'
The number of times that a table lock was acquired immediately.
This variable was added as of MySQL 3.23.33.
`Table_locks_waited'
The number of times that a table lock could not be acquired
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 as of MySQL 3.23.33.
`Threads_cached'
The number of threads in the thread cache. This variable was
added in MySQL 3.23.17.
`Threads_connected'
The number of currently open connections.
`Threads_created'
The number of threads created to handle connections. If
`Threads_created' is big, you may want to increase the
`thread_cache_size' value. The cache hit rate can be calculated
as `Threads_created'/`Connections'. This variable was added in
MySQL 3.23.31.
`Threads_running'
The number of threads that are not sleeping.
`Uptime'
The number of seconds the server has been up.
Info Catalog
(mysql.info.gz) Server system variables
(mysql.info.gz) Configuring MySQL
automatically generated byinfo2html