DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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