(mysql.info) server-status-variables
Info Catalog
(mysql.info) using-system-variables
(mysql.info) mysqld
(mysql.info) server-sql-mode
5.2.4 Server Status Variables
-----------------------------
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:
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 | 3 |
| Created_tmp_tables | 2 |
...
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+-----------------------------------+------------+
Many status variables are reset to 0 by the `FLUSH STATUS' statement.
The status variables have the following meanings. Variables with no
version indicated were already present prior to MySQL 5.0. For
information regarding their implementation history, see `MySQL 3.23,
4.0, 4.1 Reference Manual'.
* `Aborted_clients'
The number of connections that were aborted because the client
died without closing the connection properly. See
communication-errors.
* `Aborted_connects'
The number of failed attempts to connect to the MySQL server. See
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.
* `Binlog_cache_use'
The number of transactions that used the temporary binary log
cache.
* `Bytes_received'
The number of bytes received from all clients.
* `Bytes_sent'
The number of bytes sent to all clients.
* `Com_XXX'
The `Com_XXX' statement counter variables indicate 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, respectively.
The `Com_stmt_XXX' status variables were added in 5.0.8:
* `Com_stmt_prepare'
* `Com_stmt_execute'
* `Com_stmt_fetch'
* `Com_stmt_send_long_data'
* `Com_stmt_reset'
* `Com_stmt_close'
Those variables stand for prepared statement commands. Their
names refer to the `COM_XXX' 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,
`Com_stmt_prepare', `Com_stmt_execute' and `Com_stmt_close' also
increase for `PREPARE', `EXECUTE', or `DEALLOCATE PREPARE',
respectively. Additionally, the values of the older (available
since MySQL 4.1.3) statement counter variables `Com_prepare_sql',
`Com_execute_sql', and `Com_dealloc_sql' increase for the
`PREPARE', `EXECUTE', and `DEALLOCATE PREPARE' statements.
`Com_stmt_fetch' stands for the total number of network
round-trips issued when fetching from cursors.
All of the `Com_stmt_XXX' variables are increased even if a
prepared statement argument 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.
* `Compression'
Whether the client connection uses compression in the
client/server protocol. Added in MySQL 5.0.16.
* `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.
* `Created_tmp_files'
How many temporary files `mysqld' has created.
* `Created_tmp_tables'
The number of in-memory temporary tables created automatically by
the server while executing statements. If
`Created_tmp_disk_tables' is large, 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.
* `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 times that tables have
been discovered via this mechanism.
* `Handler_delete'
The number of times that rows have been deleted from tables.
* `Handler_read_first'
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
that `col1' is indexed.
* `Handler_read_key'
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.
* `Handler_read_next'
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.
* `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'.
* `Handler_read_rnd'
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.
* `Handler_read_rnd_next'
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.
* `Handler_rollback'
The number of internal `ROLLBACK' statements.
* `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 page-flush requests. 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 cannot be flushed or
removed for some other reason. Added in MySQL 5.0.2.
* `Innodb_buffer_pool_pages_misc'
The number of pages that are 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'
The 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 initiated by `InnoDB'. This
happens when a query scans 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 initiated by `InnoDB'. 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
the 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 is necessary to read or create a page
and no clean pages are available, it is also necessary to wait for
pages to be flushed first. This counter counts instances of these
waits. If the buffer pool size has been 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 operations that have been performed and
the number of pages that have been written for this purpose. Added
in MySQL 5.0.2. See innodb-disk-io.
* `Innodb_log_waits'
The number of times that the log buffer was too small and a wait
was required 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 `fsync()' writes done to the log file. Added in
MySQL 5.0.2.
* `Innodb_os_log_pending_fsyncs'
The number of pending log file `fsync()' operations. Added in
MySQL 5.0.2.
* `Innodb_os_log_pending_writes'
The number of 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 into `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
have not yet been flushed to disk.
* `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.
* `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 large, 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. This is useful for comparing the cost of different
query plans for the same query. The default value of 0 means that
no query has been compiled yet. This variable was added in MySQL
5.0.1, with a default value of -1. In MySQL 5.0.7, the default was
changed to 0; also in version 5.0.7, the scope of
`Last_query_cost' was changed to session rather than global.
Prior to MySQL 5.0.16, this variable was not updated for queries
served from the query cache.
* `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.
* `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 the query cache.
* `Qcache_free_memory'
The amount of free memory for the query cache.
* `Qcache_hits'
The number of query cache hits.
* `Qcache_inserts'
The number of queries added to the query cache.
* `Qcache_lowmem_prunes'
The number of queries that were deleted from the query cache
because of low memory.
* `Qcache_not_cached'
The number of non-cached queries (not cacheable, or not cached due
to the `query_cache_type' setting).
* `Qcache_queries_in_cache'
The number of queries registered in the query cache.
* `Qcache_total_blocks'
The total number of blocks in the query cache.
* `Questions'
The number of statements that clients have sent to the server.
* `Rpl_status'
The status of failsafe replication (not yet implemented).
* `Select_full_join'
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.
* `Select_full_range_join'
The number of joins that used a range search on a reference table.
* `Select_range'
The number of joins that used ranges on the first table.
This is normally not a critical issue even if the value is quite
large.
* `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.
* `Select_scan'
The number of joins that did a full scan of the first table.
* `Slave_open_temp_tables'
The number of temporary tables that the slave SQL thread currently
has open.
* `Slave_running'
This is `ON' if this server is a slave that is connected to a
master.
* `Slave_retried_transactions'
The total number of times since startup that the replication slave
SQL thread has retried transactions. This variable was added in
version 5.0.4.
* `Slow_launch_threads'
The number of threads that have taken more than `slow_launch_time'
seconds to create.
* `Slow_queries'
The number of queries that have taken more than `long_query_time'
seconds. See slow-query-log.
* `Sort_merge_passes'
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_size' system variable.
* `Sort_range'
The number of sorts that were done using ranges.
* `Sort_rows'
The number of sorted rows.
* `Sort_scan'
The number of sorts that were done by scanning the table.
* `Ssl_XXX'
Variables used for SSL connections.
* `Table_locks_immediate'
The number of times that a table lock was acquired immediately.
* `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.
* `Threads_cached'
The number of threads in the thread cache.
* `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'.
* `Threads_running'
The number of threads that are not sleeping.
* `Uptime'
The number of seconds that the server has been up.
Info Catalog
(mysql.info) using-system-variables
(mysql.info) mysqld
(mysql.info) server-sql-mode
automatically generated byinfo2html