31.10.2012 renisans

mysql show status

The above status variables have the following values: Variable Value Aborted_clients The number of connections that were aborted because the client disconnected without properly closing the connection. See section A.2.9 Communication Errors / Aborted Connections. Aborted_connects The number of failed attempts to connect to the MySQL server. See section…

The above state variables have the following values:

Variable	Value
`Aborted_clients`	The number of connections aborted because the client disconnected without properly closing the connection. See section A.2.9 Communication Errors / Aborted Connections.
`Aborted_connects`	The number of failed attempts to connect to the MySQL server. See section A.2.9 Communication Errors / Aborted Connections.
`Bytes_received`	The number of bytes received from all clients.
`Bytes_sent`	The number of bytes sent to all clients.
`Com_xxx`	The number of executions of each xxx command.
`Connections`	The number of attempts to connect to the MySQL server.
`Created_tmp_tables`	The number of implicit temporary tables created on disk during statement execution.
`Created_tmp_tables`	The number of implicit temporary tables created in memory during statement execution.
`Created_tmp_files`	The number of temporary files created `mysqld`.
`Delayed_insert_threads`	The number of threads used for data insertion in `insert delayed` mode.
`Delayed_writes`	The number of rows inserted using the `INSERT DELAYED` command.
`Delayed_errors`	The number of rows written using the `INSERT DELAYED` command that encountered some error (possibly `duplicate key`).
`Flush_commands`	The number of `FLUSH` commands executed.
`Handler_commit`	The number of internal `COMMIT` commands.
`Handler_delete`	The number of row deletions from a table.
`Handler_read_first`	The number of reads for the first index record. If this value is high, the server is likely performing many full index scans, such as `SELECT col1 FROM foo`, assuming that `col1` is indexed.
`Handler_read_key`	The number of read requests for rows based on a key. A high value for this variable indicates that your queries and tables are properly indexed.
`Handler_read_next`	The number of read requests for the next row in key order. This value increases if an index column is queried with a size restriction. The value also increases during index scans.
`Handler_read_prev`	The number of read requests for the previous row in key order. This is mostly used to optimize `ORDER BY ... DESC`.
`Handler_read_rnd`	The number of read requests for rows based on a fixed position. The value will be high if many queries requiring result sorting are executed.
`Handler_read_rnd_next`	The number of read requests for the next row from the data file. This value will be high if many table scans are performed. This usually means that your tables are not properly indexed or your queries do not take advantage of indexes.
`Handler_rollback`	The number of internal `ROLLBACK` commands.
`Handler_update`	The number of requests to update rows in a table.
`Handler_write`	The number of requests to insert rows into a table.
`Key_blocks_used`	The number of blocks used 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 key blocks to disk.
`Max_used_connections`	The maximum number of simultaneous connections used.
`Not_flushed_key_blocks`	Key blocks in the key cache that have been modified but not yet written to disk.
`Not_flushed_delayed_rows`	The number of rows queued for writing in `INSERT DELAY` queries.
`Open_tables`	The number of open tables.
`Open_files`	The number of open files.
`Open_streams`	The number of open streams (mainly used for logging).
`Opened_tables`	The number of tables that have been opened.
`Rpl_status`	Status of crash-safe replication (not yet used).
`Select_full_join`	The number of connections without keys (if this value is 0, you should carefully check the indexes of your tables).
`Select_full_range_join`	The number of connections where a range search was used on a reference table.
`Select_range`	The number of connections that used ranges in the first table. (Usually this value is not critical, even if it is large)
`Select_scan`	The number of connections where the first table was scanned first.
`Select_range_check`	The number of connections without keys where key usage is checked after each row (if this value is 0, you should carefully check the indexes of your tables).
`Questions`	The number of queries sent to the server.
`Slave_open_temp_tables`	The number of temporary tables currently opened by the slave thread.
`Slave_running`	Contains the value of `ON` if this is a slave computer connected to the master computer.
`Slow_launch_threads`	The number of threads whose creation took longer than specified in `slow_launch_time`.
`Slow_queries`	The number of queries whose processing took longer than `long_query_time`. See section 4.9.5 Slow Query Log.
`Sort_merge_passes`	The number of joins performed by the sort algorithm. If this value is large, you should increase `sort_buffer`.
`Sort_range`	The number of sorts that were performed in ranges.
`Sort_rows`	The number of sorted rows.
`Sort_scan`	The number of sorts performed by scanning the table.
`ssl_xxx`	SSL-related variables; not yet implemented.
`Table_locks_immediate`	The number of requests for immediate table locking. Available starting from version 3.23.33.
`Table_locks_waited`	The number of requests where immediate locking could not be performed and time was required for waiting. If this value is large and you have performance issues, you should first optimize your queries, and then either partition the tables or use replication. Available starting from version 3.23.33.
`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.
`Threads_running`	The number of non-idle threads.
`Uptime`	The time in seconds that the server has been running.

Some notes on the above information:

If the value of Opened_tables is large, it may be that the value of the table_cache variable is too small.
If the value of Key_reads is large, it may be that the value of the key_buffer_size variable is too small. The cache hit rate can be calculated using Key_reads/Key_read_requests.
If the value of Handler_read_rnd is large, it may be that too many requests are being made that require MySQL to perform full table scans, or you have connections that are not using keys properly.
If the value of Threads_created is large, it may be necessary to increase the value of the thread_cache_size variable. The cache hit rate can be calculated using Threads_created/Connections.
If the value of Created_tmp_disk_tables is large, it may be necessary to increase the value of the tmp_table_size variable so that temporary tables are kept in memory rather than on disk.

Note: This post may be AI-assisted and then human-edited. Editorial responsibility: Dmytro Lomaka, Kaltenkirchen.

We value your privacy