Team LiB
Previous Section Next Section

Status Variables

Status variables provide information about the server's operational state. These variables can be displayed by the SHOW STATUS statement or by executing the mysqladmin extended-status command.

Status variable names are not case sensitive.

The more general variables are described in the following list. Separate sections after that describe sets of variables that are related to each other. These include variables for statement counters, the InnoDB storage engine, the query cache, and SSL.

  • Aborted_clients

    The number of client connections aborted due to clients not closing the connection properly.

  • Aborted_connects

    The number of failed attempts to connect to the server.

  • Binlog_cache_disk_use

    The number of transactions that had to use a temporary disk file because their size exceeded the value of the binlog_cache_size system variable. This variable was introduced in MySQL 4.1.2.

  • Binlog_cache_use

    The number of transactions that could be held in the binary log cache because their size did not exceed the value of the binlog_cache_size system variable. This variable was introduced in MySQL 4.1.2.

  • Bytes_received

    The total number of bytes received from all clients.

  • Bytes_sent

    The total number of bytes sent to all clients.

  • Com_xxx

    See "Statement Counter Status Variables."

  • Connections

    The number of attempts to connect to the server (both successful and unsuccessful). If this number is quite high, you may want to look into using persistent connections in your clients if possible.

  • Created_tmp_disk_tables

    The number of on-disk temporary tables created while processing statements.

  • Created_tmp_files

    The number of temporary files created by the server.

  • Created_tmp_tables

    The number of in-memory temporary tables created while processing statements.

  • Delayed_errors

    The number of errors that have occured while processing INSERT DELAYED rows.

  • Delayed_insert_threads

    The current number of INSERT DELAYED handlers.

  • Delayed_writes

    The number of INSERT DELAYED rows that have been written.

  • Flush_commands

    The number of FLUSH statements that have been executed.

  • Handler_commit

    The number of requests to commit a transaction.

  • Handler_delete

    The number of requests to delete a row from a table.

  • Handler_read_first

    The number of requests to read the first row from an index.

  • Handler_read_key

    The number of requests to read a row based on an index value.

  • Handler_read_next

    The number of requests to read the next row in index order.

  • Handler_read_prev

    The number of requests to read the previous row in descending index order.

  • Handler_read_rnd

    The number of requests to read a row based on its position.

  • Handler_read_rnd_next

    The number of requests to read the next row. If this number is high, you are likely performing many statements that require full table scans or that are not using indexes properly.

  • Handler_rollback

    The number of requests to roll back a transaction.

  • 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_xxx

    See "InnoDB Status Variables."

  • Key_blocks_not_flushed

    The number of blocks in the key cache that have been modified but not yet flushed to disk. This variable was renamed from Not_flushed_key_blocks in MySQL 4.1.1.

  • Key_blocks_unused

    The current number of unused blocks in the key cache. This variable was introduced in MySQL 4.1.2.

  • Key_blocks_used

    The maximum number of blocks in the key cache that have ever simultaneously been in use.

  • Key_read_requests

    The number of requests to read a block from the key cache.

  • Key_reads

    The number of physical reads of index blocks from disk.

  • Key_write_requests

    The number of requests to write a block to the key cache.

  • Key_writes

    The number of physical writes of index blocks to disk.

  • Last_query_cost

    The query optimizer's most recent query cost calculation, or 1 if no cost has yet been calculated. This variable was introduced in MySQL 5.0.1.

  • Max_used_connections

    The maximum number of connections that have been open simultaneously.

  • Not_flushed_delayed_rows

    The number of rows waiting to be written for INSERT DELAYED statements.

  • Not_flushed_key_blocks

    This is the name of the Key_blocks_not_flushed variable prior to MySQL 4.1.1.

  • Open_files

    The number of open files.

  • Open_streams

    The number of open streams. A stream is a file opened with fopen(); this applies only to log files.

  • Open_tables

    The number of open tables. It does not apply to TEMPORARY tables.

  • Opened_tables

    The total number of tables that have been opened. If this number is high, it may be a good idea to increase your table cache size.

  • Qcache_xxx

    See "Query Cache Status Variables."

  • Questions

    The number of statements that have been received by the server (this includes both successful and unsuccessful statements). The ratio of Questions to Update yields the number of statements per second.

  • Rpl_status

    Failsafe replication status. This variable is not yet used.

  • Select_full_join

    The number of "full" joins; that is, joins performed without using indexes.

  • Select_full_range_join

    The number of joins performed using a range search on a reference table.

  • Select_range

    The number of joins performed using a range on the first table.

  • Select_range_check

    The number of joins performed such that a range search must be used to fetch rows on a secondary table.

  • Select_scan

    The number of joins performed that used a full scan of the first table.

  • Slave_open_temp_tables

    The number of temporary tables the slave thread has open.

  • Slave_running

    Whether this server is acting as a slave that is currently connected to a server.

  • Slow_launch_threads

    The number of threads that took longer than slow_launch_time seconds to create.

  • Slow_queries

    The number of queries that look longer than long_query_time seconds to execute.

  • Sort_merge_passes

    The number of merge passes performed by the sort algorithm.

  • Sort_range

    The number of sort operations performed using a range.

  • Sort_rows

    The number of rows sorted.

  • Sort_scan

    The number of sort operations performed using a full table scan.

  • Ssl_xxx

    See "SSL Status Variables."

  • Table_locks_immediate

    The number of requests for a table lock that could be satisfied immediately with no waiting.

  • Table_locks_waited

    The number of requests for a table lock that could be satisfied only after waiting. If this value is high, it indicates that you have a lot of contention for table locks (most likely for MyISAM tables, which are locked at the table level).

  • Threads_cached

    The number of threads currently in the thread cache.

  • Threads_connected

    The number of currently open connections.

  • Threads_created

    The total number of threads that have been created to handle client connections.

  • Threads_running

    The number of threads that are not sleeping.

  • Uptime

    The number of seconds since the server started running.

Statement Counter Status Variables

The server maintains a set of status variables that serve as counters to indicate the number of times particular types of statements (commands) have been executed. There are dozens of such variables, and they all have similar names, so they are not listed individually here. Each statement counter variable name begins with Com_, and has a suffix that indicates the type of statement to which the counter corresponds. For example, Com_select and Com_drop_table indicate, respectively, how many SELECT and DROP TABLE statements the server has executed.

InnoDB Status Variables

The following variables display information about the operation of the InnoDB storage engine. Many of them are available in the output of SHOW ENGINE INNODB STATUS, but are more easily parsed in the output from SHOW STATUS. Most of these variables were introduced in MySQL 5.0.2; exceptions are so noted.

  • Innodb_buffer_pool_pages_data

    The number of pages in the InnoDB buffer pool that contain data. This counts both clean pages that have not been modified and dirty pages that contain modified data.

  • Innodb_buffer_pool_pages_dirty

    The number of pages in the InnoDB buffer pool that contain modified data.

  • Innodb_buffer_pool_pages_flushed

    The number of InnoDB buffer pool pages for which flush requests have been issued.

  • Innodb_buffer_pool_pages_free

    The number of free pages in the InnoDB buffer pool.

  • Innodb_buffer_pool_pages_latched

    The number of pages in the InnoDB buffer pool that are in the process of being written or that for some other reason cannot be flushed and freed for reuse.

  • Innodb_buffer_pool_pages_misc

    The number of pages in the InnoDB buffer pool that are allocated for internal operations.

  • Innodb_buffer_pool_pages_total

    The total number of pages in the InnoDB buffer pool.

  • Innodb_buffer_pool_read_ahead_rnd

    The number of random read-aheads initiated by InnoDB. These occur when InnoDB must read a large part of a table but in non-sequential order.

  • Innodb_buffer_pool_read_ahead_seq

    The number of sequential read-aheads initiated by InnoDB. These occur when InnoDB performs sequential full-table scans.

  • Innodb_buffer_pool_read_requests

    The number of logical read requests issued by InnoDB.

  • Innodb_buffer_pool_reads

    The number of single-pages reads done due to not being able to perform a logical read from the InnoDB buffer pool.

  • Innodb_buffer_pool_wait_free

    The number of times InnoDB had to wait for writes to the buffer pool to be flushed. Writes usually are done in the background, but InnoDB must perform a wait if no pages are available when it needs to read a page or create a new one.

  • Innodb_buffer_pool_write_requests

    The number writes to the InnoDB buffer pool.

  • Innodb_data_fsyncs

    The number sync-to-disk operations performed by InnoDB.

  • Innodb_data_pending_fsyncs

    The number of pending InnoDB data sync-to-disk operations.

  • Innodb_data_pending_reads

    The number of pending InnoDB data read operations.

  • Innodb_data_pending_writes

    The number of pending InnoDB data write operations.

  • Innodb_data_read

    The number of bytes read by InnoDB.

  • Innodb_data_reads

    The number of InnoDB data read operations.

  • Innodb_data_writes

    The number of InnoDB data write operations.

  • Innodb_data_written

    The number of bytes written by InnoDB.

  • Innodb_dblwr_pages_written

    The number of pages written to the InnoDB doublewrite buffer.

  • Innodb_dblwr_writes

    The number of writes to the InnoDB doublewrite buffer.

  • Innodb_log_waits

    The number of times InnoDB had to wait for writes to the log buffer pool to be flushed.

  • Innodb_log_write_requests

    The number of requests to write to the InnoDB log file.

  • Innodb_log_writes

    The number of physical writes to the InnoDB log file.

  • Innodb_os_log_fsyncs

    The number of sync-to-disk operations for the InnoDB log file.

  • Innodb_os_log_pending_fsyncs

    The number of pending sync-to-disk operations for the InnoDB log file.

  • Innodb_os_log_pending_writes

    The number of pending write operations for the InnoDB log file.

  • Innodb_os_log_written

    The number of bytes written to the InnoDB log file.

  • Innodb_page_size

    The compiled-in page size used by InnoDB. This can be used to convert measurements that are counted in page units to byte units. The default value is 16KB.

  • Innodb_pages_created

    The number of pages created by InnoDB.

  • Innodb_pages_read

    The number of pages read by InnoDB.

  • Innodb_pages_written

    The number of pages written by InnoDB.

  • Innodb_row_lock_current_waits

    The number of row locks that InnoDB currently is waiting to acquire. This variable was introduced in MySQL 5.0.3.

  • Innodb_row_lock_time

    The total amount of time in milliseconds spent acquiring InnoDB row locks. This variable was introduced in MySQL 5.0.3.

  • Innodb_row_lock_time_avg

    The average amount of time in milliseconds required to acquire an InnoDB row lock. This variable was introduced in MySQL 5.0.3.

  • Innodb_row_lock_time_max

    The maximum amount of time in milliseconds required to acquire an InnoDB row lock. This variable was introduced in MySQL 5.0.3.

  • Innodb_row_lock_waits

    The number of times that InnoDB had to wait to acquire a row lock. This variable was introduced in MySQL 5.0.3.

  • Innodb_rows_deleted

    The number of rows deleted from InnoDB tables.

  • Innodb_rows_inserted

    The number of rows inserted in InnoDB tables.

  • Innodb_rows_read

    The number of rows read from InnoDB tables.

  • Innodb_rows_updated

    The number of rows updated in InnoDB tables.

Query Cache Status Variables

The following variables display information about the operation of the query cache.

  • 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 hits in the query cache; that is, the number of query requests satisfied by queries held in the cache.

  • Qcache_inserts

    The number of queries that have ever been registered in the query cache.

  • Qcache_lowmem_prunes

    The number of cached query results that had to be kicked out of the query cache to make room for newer results.

  • Qcache_not_cached

    The number of queries that were uncacheable or for which caching was suppressed with the SQL_NO_CACHE keyword.

  • Qcache_queries_in_cache

    The number of queries currently registered in the cache.

  • Qcache_total_blocks

    The total number of memory blocks in the query cache.

Note that SHOW VARIABLES also lists a few query cache-related variables; they all have names that begin with query_cache.

SSL Status Variables

The following variables provide information about the SSL management code. Many of them reflect the state of the current connection, and will be blank unless the connection actually is secure. These variables are unavailable unless SSL support actually has been built into the server.

  • Ssl_accept_renegotiates

    The number of start renegotiations in server mode.

  • Ssl_accepts

    The number of started SSL/TLS handshakes in server mode.

  • Ssl_callback_cache_hits

    The number of sessions successfully retrieved from the external session cache in server mode.

  • Ssl_cipher

    The SSL cipher (protocol) for the current connection (blank if no cipher is in effect). You can use this variable to determine whether the current connection is encrypted.

  • Ssl_cipher_list

    The list of available SSL ciphers.

  • Ssl_client_connects

    The number of started SSL/TLS handshakes in client mode.

  • Ssl_connect_renegotiates

    The number of start renegotiations in client mode.

  • Ssl_ctx_verify_depth

    The SSL context verification depth.

  • Ssl_ctx_verify_mode

    The SSL context verification mode.

  • Ssl_default_timeout

    The default SSL session timeout.

  • Ssl_finished_accepts

    The number of successfully established SSL/TLS sessions in server mode.

  • Ssl_finished_connects

    The number of successfully established SSL/TLS sessions in client mode.

  • Ssl_session_cache_hits

    The number of SSL sessions found in the session cache.

  • Ssl_session_cache_misses

    The number of SSL sessions not found in the session cache.

  • Ssl_session_cache_mode

    The type of SSL caching used by the server.

  • Ssl_session_cache_overflows

    The number of sessions removed from the cache because it was full.

  • Ssl_session_cache_size

    The number of sessions that can be stored in the SSL session cache.

  • Ssl_session_cache_timeouts

    The number of sessions that have timed out.

  • Ssl_sessions_reused

    Whether the session was reused from an earlier session.

  • Ssl_used_session_cache_entries

    The number of sessions currently in the session cache.

  • Ssl_verify_depth

    The SSL verification depth.

  • Ssl_verify_mode

    The SSL verification mode.

  • Ssl_version

    The protocol version of the connection.

    Team LiB
    Previous Section Next Section