Mysql show status Details

You can use the following command to view the current status of a server:

1. Go to the mysql/bin directory and enter mysqladmin extended-status

2. ConnectMysql, Enter show status;

3. If you want to view data, you can

Mysql> show status like 'table % ';

+ ----------------------- + ------- +

| Variable_name | Value |

+ ----------------------- + ------- +

| Table_locks_immediate | 12 |

| Table_locks_waited | 0 |

+ ----------------------- + ------- +

 

You need to pay attention to the following parts:

Qcache %, open % tables, threads %, % key_read %, created_tmp %, sort %, com_select

The tuning of these variables refer to the mysql server tuning http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html

For more information about status variables, some new variables may not be listed,

See the mysql manual http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html for full State explanation (Red is the variable that focuses on tuning)

 

Aborted_clients	Indicates the number of connections that fail due to the failure of the client program to close the connection. If the customer does not adjust the mysql_close () function before exiting, The wait_timeout or interactive_timeout restrictions have been exceeded, or the client program is disabled during transmission, this will happen.
Aborted_connects	Indicates the number of failures attempted to connect to MYSQL. In this case, when the customer tries to connect with the wrong password and does not have the permission to connect, it takes more time to get the connected data packet than the connect_timeout limit in seconds, or the packet does not contain the correct information.
Bytes_received	The number of bytes received from the customer.
Bytes_sent	The number of bytes that have been sent to all customers.
Com _ [statement]	One of these variables used in each statement. The variable value indicates the number of times this statement is executed. For example, com_select indicates the number of times the query statement is executed.
Connections	The number of attempts to connect to the MYSQL server.
Created_tmp_disk_tables	Number of implicit temporary tables generated on the disk during statement execution
Created_tmp_tables	Number of implicit temporary tables generated in memory during statement execution
Created_tmp_files	Number of temporary files generated by mysqld
Delayed_insert_threads	Number of threads currently in use for delayed insert handle
Delayed_writes	Number of records written by the insert delayed statement
Delayed_errors	The records written by the insert delayed statement when an error occurs. Most common errors are replication keys.
Flush_commands	Number of FLUSH statements executed
Handler_commit	Number of internal COMMIT commands
Handler_delete	Number of rows deleted from a table
Handler_read_first	The number of times the first entry in an index is read, usually full index scan (for example, if indexed_col is indexed, SELECT indexed_col from tablename leads to a full index scan)
Handler_read_key	The number of requests that use indexes when reading a row of data. If an index is used during the query, you want this value to increase rapidly.
Handler_read_next	The number of requests for reading data from the next row in the order of indexing. If a full index is used for scanning, or an index is queried within a constant range, the value will increase.
Handler_read_prev	The number of requests for reading data from the previous row in the order of indexes. This variable value is used by select fieldlist order by fields DESC statements.
Handler_read_rnd	The number of requests for reading a row of data at a fixed position. The query operation that requires the result to be saved will increase the value of this counter.
Handler_read_rnd_next	The number of requests for reading data from the next row in the data file. Generally, this value cannot be too high, because it means that the query operation does not use indexes and must read data files.
Handler_rollback	Number of internal ROLLBACK commands
Handler_update	Number of requests for updating a record in the table
Handler_write	Number of requests for inserting a record in the table
Key_blocks_used	Number of data blocks used in the key Cache
Key_read_requests	The number of data block requests that cause the read key from the cache of the key. The ratio of Key_reads to Key_read_requests should not be higher than (that is, is terrible)
Key_reads	The number of physical reads performed on data blocks that cause the read key from the disk.
Key_write_requests	Number of requests that cause key data blocks to be written to the cache
Key_writes	Number of physical writes to data blocks that write keys to Disks
Max_used_connections	Maximum number of connections in use at any time
Not_flushed_key_blocks	The number of data blocks in the key cache that have changed but have not been refreshed to the disk
Not_flushed_delayed_rows	Number of records waiting to be written in the insert delay queue
Open_tables	Number of opened tables
Open_files	Number of files currently opened
Open_streams	The number of currently opened streaming data. These stream data is mainly usedLogsRecord
Opened_tables	Number of opened tables
Questions	Total number of initial query operations
Qcache_queries_in_cache	Number of queries in the cache
Qcache_inserts	The number of hits of the queries added to the cache divided by the number of inserts indicates the ratio of the number of hits, and the hit rate is calculated by subtracting 1 from the value.
Qcache_hits	Query the number of cache accesses
Qcache_lowmem_prunes	The cache is out of memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The abovefree_blocksAndfree_memoryCan tell you which situation ).
Qcache_not_cached	Number of queries not cached (because it is too large or because of QUERY_CACHE_TYPE)
Qcache_free_memory	The number of memories that can still be used to query the cache
Qcache_free_blocks	The number of idle memory blocks in the query cache. If the number is large, fragments may exist.
Qcache_total_blocks	Total number of data blocks in the query Cache
Rpl_status	Full replication status (this variable is only used in Versions later than MYSQL 4)
Select_full_join	Number of connections that have been executed without indexes. The variable value cannot be set too high.
Handler_rollback	Number of internal ROLLBACK statements
Handler_update	Number of requests for updating a row in the table
Handler_write	Number of requests for inserting a row in the table
Innodb_buffer_pool_pages_data	Number of pages containing data (dirty or clean)
Innodb_buffer_pool_pages_dirty	Current dirty page number
Innodb_buffer_pool_pages_flushed	Number of buffer pool pages to be cleared
Innodb_buffer_pool_pages_free	Number of blank pages
Innodb_buffer_pool_pages_latched	Number of pages locked in the InnoDB buffer pool. This is the number of pages currently being read or written, or cannot be cleared or deleted for other reasons
Innodb_buffer_pool_pages_misc	Busy pages because they have been assigned priority for management, such as row locking or applicable hash indexes. The value can also be calculated as bytes.
Innodb_buffer_pool_pages_total	Total buffer pool size (page number)
Innodb_buffer_pool_read_ahead_rnd	The number of random read-aheads initialized by InnoDB. This occurs when a query scans a large portion of a table in a random order.
Innodb_buffer_pool_read_ahead_seq	The number of InnoDB-initiated sequential read-aheads. Occurs when InnoDB performs a full table scan in sequence.
Innodb_buffer_pool_read_requests	Number of logical read requests completed by InnoDB
Innodb_buffer_pool_reads	The number of logical reads in the buffer pool that InnoDB must read on a single page cannot meet
Innodb_buffer_pool_wait_free	Generally, data is written to the InnoDB buffer pool through the background. However, if you want to read or create a page without a clean page available, you still need to wait for the page to be cleared. This counter counts the waiting instance. If yes> when the buffer pool size is set, this value should be small
Innodb_buffer_pool_write_requests	Number of writes to the InnoDB Buffer Pool
Innodb_data_fsyncs	Fsync () Operations
Innodb_data_pending_fsyncs	Currently suspended fsync () Operations
Innodb_data_pending_reads	Currently suspended reading
Innodb_data_pending_writes	Number of currently suspended writes
Innodb_data_read	The amount of data that has been read (in bytes)
Innodb_data_reads	Total number of data reads
Innodb_data_writes	Total number of data writes
Innodb_data_written	The data size (in bytes) that has been written so far)
Innodb_dblwr_writes,	Innodb_dblwr_pages_written the number of dual-write operations that have been performed and the number of pages that have been written for this purpose.
Innodb_log_waits	We have to wait for the time. Because the log buffer is too small, we must wait for it to be cleared before continuing.
Innodb_buffer_pool_bytes_data	Size of the data cached by the current bufferpool, including dirty data
Key_blocks_unused	Number of unused cache clusters (blocks)
Key_blocks_used	The maximum number of blocks Used in the past
Key_blocks_unused	If it is too small, you can either increase the key_buffer_size, or transition the index to fill up the cache.