Monitoring MySQL

Overall activity

Questions

累计值：The number of queries and protocol commands that have been run.

Uptime

当前值：The number of seconds the server has been online.

Uptime_since_flush_status

当前值：The number of seconds since either the server was started or FLUSH STATUS has been run.

 

Connections

Aborted_clients

累计值：A count of the number of clients that did not call mysql_close() or equivalent in their API, and thus the MySQL server forcibly closed their connection.

Aborted_connects

累计值：A count of the number of clients that did not successfully connect due to handshake or network errors in the connect process.

Connections

累计值：The number of clients currently connected.

Max_used_connections

当前值：The maximum number of clients that have been connected since the server started. If this is equal to max_connections configuration variable, you have reached maxed out the connections to the server.

 

Network traffic

Bytes_*

累计值：The number of bytes sent to (Bytes_sent) clients (primarily result sets) and received from (Bytes_received) clients (primarily query text).

 

DML commands

Com_*

累计值：The number of commands of various types that have been run:

Com_select — SELECT

Com_insert — INSERT

Com_insert_select — INSERT ... SELECT ...

Com_update — UPDATE

Com_delete — DELETE

 

Transactions

Com_*

累计值：The number of various transactional commands that have been run:

• Com_begin — BEGIN or START TRANSACTION
• Com_commit — COMMIT
• Com_rollback — ROLLBACK

 

Temporary tables

Created_tmp_tables

累计值：The number of temporary tables that have been created.

Created_tmp_disk_tables

累计值：

The number of temporary tables that have been converted from in-memory (HEAP) to on-disk (MyISAM) temporary tables. This can occur for one of a few reasons:

• The table reached tmp_table_size or max_heap_table_size and could no longer be stored in-memory.
• The table contains BLOB or TEXT column types and couldn’t be created as an in-memory table at all.

 

Table cache usage

Opened_tables

累计值：

The number of tables that have been opened because there wasn’t an available cached open table in the table cache. This normally implies that either:

• The FLUSH TABLES command is being run often.
• The size of table_cache is too small, and tables must be closed in order to open others.

 

Open_tables

当前值：The number of tables currently open. If this is equal to the table_cache variable, it would imply that the table_cache setting is too low.

 

Threads

Threads_created

累计值：The number of threads that have been created. This should be stable as long as the thread_cache variable is large enough.

Threads_cached

当前值：The number of threads currently in the cache and available for use.

Threads_connected

当前值： The number of threads currently handling a connection.

Threads_running

当前值： The number of threads currently in a non-idle (Sleep) state.

 

InnoDB

InnoDB buffer pool

Innodb_buffer_pool_pages_*

当前值： The number of pages in various states in the buffer pool:

• Innodb_buffer_pool_pages_data — Data pages; containing data or indexes from tables.
• Innodb_buffer_pool_pages_dirty — Pages marked as "dirty"; having changes that have yet to be written to disk.
• Innodb_buffer_pool_pages_free — Free pages; available for data to be stored.
• Innodb_buffer_pool_pages_total — All pages; the total number of pages which can be allocated for InnoDB’s buffer pool. This number will be equal to innodb_buffer_pool_size / Innodb_page_size

 

Innodb_buffer_pool_*

累计值：

• Innodb_buffer_pool_read_requests — The number of requests for a page from the buffer pool. This counter is incremented whether the page exists in the buffer pool, or requires a disk read to fetch the page into the buffer pool.
• Innodb_buffer_pool_reads — The number of pages that had to actually be read from disk into the buffer pool.
• Innodb_buffer_pool_write_requests — The number of pages that have been requested to be written from the buffer pool to disk.

 

Innodb_buffer_pool_read_ahead_*

累计值：

Innodb_buffer_pool_read_ahead_rnd

Innodb_buffer_pool_read_ahead_seq

 

Buffer pool miss rate

100 * (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

 

The buffer pool miss rate is the percentage of the time that a page was requested but was not present in the buffer pool and had to be read from the disk. Increasing the size of innodb_buffer_pool_size may decrease the number of buffer pool misses.

The inverse of this (100 – miss rate) is the buffer pool hit rate.

 

Percent of dirty pages

100 * (Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_data)

 

The percent of dirty pages is a ratio of many pages are in the cache in a modified state (dirty) and shoudl be written to disk before shutdown.

 

Percent buffer pool used for data

100 * (Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total)

 

The percent of the buffer pool used for data is a ratio of how many pages are currently allocated for data and index pages in the buffer pool.

 

Percent buffer pool free

100 * (Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)

 

The percent of the buffer pool free is the ratio of how many pages are not currently allocated in the buffer pool. These pages may or may not have been allocated from the operating system by MySQL/InnoDB. That is they may really be "free" as in entirely unallocated, or they may be "free in the buffer pool", allocated but currently unused.

 

InnoDB_data_*

累计值：

• Innodb_data_reads — The number of times data has been read.
• Innodb_data_read — The amount of data read (in bytes) because of Innodb_data_reads.
• Innodb_data_writes — The number of times data has been written.
• Innodb_data_written — The amount of data written (in bytes) because of Innodb_data_writes.

 

InnoDB_pages_*

累计值：

• Innodb_pages_created — The number of pages that were newly created because of new data being inserted or updated and pages being appended or split.
• Innodb_pages_read — The number of pages that have been read because they were not present in the buffer pool.
• Innodb_pages_written — The number of pages that have been written or rewritten.

 

InnoDB_rows_*

累计值：

• Innodb_rows_deleted — The number of rows that have been deleted.
• Innodb_rows_inserted — The number of rows that have been inserted.
• Innodb_rows_read — The number of rows that have been read.
• Innodb_rows_updated — The number of rows that have been updated.

 

MyISAM

Key buffer size

• Key_blocks_used —
• Key_blocks_unused —
• Key_blocks_not_flushed —

Key buffer activity

Key_*

• Key_reads
• Key_writes

Key_*_requests

• Key_read_requests
• Key_write_requests

Key buffer misses

100 * (Key_reads / Key_read_requests)

 

Key buffer write coalesces

(Key_write_requests / Key_writes)