`SHOW STATUS'
.............
`SHOW STATUS' provides server status information (like `mysqladmin
extended-status'). The output resembles that shown below, though the
format and numbers probably differ:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 8340 |
| Created_tmp_files | 60 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 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_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 44600 |
| Questions | 2026873 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 99646 |
| Select_range_check | 0 |
| Select_scan | 30802 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| 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_created | 30022 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 80380 |
+--------------------------+------------+
The status variables listed above have the following meaning:
*Variable* *Meaning*
`Aborted_clients' Number of connections aborted because the
client died without closing the connection
properly. Note:Communication errors.
`Aborted_connects' Number of tries to connect to the MySQL server
that failed. Note:Communication errors.
`Bytes_received' Number of bytes received from all clients.
`Bytes_sent' Number of bytes sent to all clients.
`Com_xxxx' Number of times the xxx commands has been
executed.
`Connections' Number of connection attempts to the MySQL
server.
`Created_tmp_disk_tables' Number of implicit temporary tables on disk
created while executing statements.
`Created_tmp_tables' Number of implicit temporary tables in memory
created while executing statements.
`Created_tmp_files' How many temporary files `mysqld' have created.
`Delayed_insert_threads' Number of delayed insert handler threads in
use.
`Delayed_writes' Number of rows written with `INSERT DELAYED'.
`Delayed_errors' Number of rows written with `INSERT DELAYED'
for which some error occurred (probably
`duplicate key').
`Flush_commands' Number of executed `FLUSH' commands.
`Handler_delete' Number of times a row was deleted from a table.
`Handler_read_first' 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' 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' Number of requests to read next row in key
order. This will be incremented if you are
querying an index column with a range
constraint. This also will be incremented if
you are doing an index scan.
`Handler_read_rnd' 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.
`Handler_read_rnd_next' Number of requests to read the next row in the
datafile. 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_update' Number of requests to update a row in a table.
`Handler_write' Number of requests to insert a row in a table.
`Key_blocks_used' The number of used blocks in the key cache.
`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.
`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.
`Max_used_connections' The maximum number of connections in use
simultaneously.
`Not_flushed_key_blocks' Keys blocks in the key cache that has changed
but hasn't yet been flushed to disk.
`Not_flushed_delayed_rows'Number of rows waiting to be written in
`INSERT DELAY' queues.
`Open_tables' Number of tables that are open.
`Open_files' Number of files that are open.
`Open_streams' Number of streams that are open (used mainly
for logging).
`Opened_tables' Number of tables that have been opened.
`Select_full_join' Number of joins without keys (If this is 0,
you should carefully check the index of your
tables).
`Select_full_range_join' Number of joins where we used a range search
on reference table.
`Select_range' Number of joins where we used ranges on the
first table. (It's normally not critical even
if this is big.)
`Select_scan' Number of joins where we did a full scann of
the first table.
`Select_range_check' Number of joins without keys where we check
for key usage after each row (If this is 0,
you should carefully check the index of your
tables).
`Questions' Number of queries sent to the server.
`Slave_open_temp_tables' Number of temporary tables currently open by
the slave thread
`Slow_launch_threads' Number of threads that have taken more than
`slow_launch_time' to create.
`Slow_queries' Number of queries that have taken more than
`long_query_time'. Note:Slow query log.
`Sort_merge_passes' Number of merges passes the sort algoritm have
had to do. If this value is large you should
consider increasing `sort_buffer'.
`Sort_range' Number of sorts that where done with ranges.
`Sort_rows' Number of sorted rows.
`Sort_scan' Number of sorts that where done by scanning
the table.
`Table_locks_immediate' Number of times a table lock was acquired
immediately. Available after 3.23.33.
`Table_locks_waited' Number of times 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(s)
or use replication. Available after 3.23.33.
`Threads_cached' Number of threads in the thread cache.
`Threads_connected' Number of currently open connections.
`Threads_created' Number of threads created to handle
connections.
`Threads_running' Number of threads that are not sleeping.
`Uptime' How many seconds the server has been up.
Some comments about the above:
* If `Opened_tables' is big, then your `table_cache' variable is
probably too small.
* If `key_reads' is big, then your `key_buffer_size' variable is
probably too small. The cache hit rate can be calculated with
`key_reads'/`key_read_requests'.
* If `Handler_read_rnd' is big, then you probably have a lot of
queries that require MySQL to scan whole tables or you have joins
that don't use keys properly.
* If `Threads_created' is big, you may want to increase the
`thread_cache_size' variable.