From Proven Scaling Wiki

This page documents the different interesting variables which you may wish to monitor on a production MySQL server. In a given system you may not want to monitor all of them, but rather pick and choose the most interesting ones, and evolve your selection over time to monitor new and interesting things. Most metrics you will want to monitor will come from SHOW GLOBAL STATUS. Some care should be taken to only poll SHOW GLOBAL STATUS once per monitoring interval, rather than once per statistic, as the command can be somewhat expensive. Running it once per second is not a problem, but running it once per statistic per second could be very expensive.

Contents 1 Types of statistics 1.1 Incrementing 1.2 Absolute 1.3 Formula 2 General 2.1 Overall activity 2.1.1 Questions 2.1.2 Uptime 2.1.3 Uptime_since_flush_status 2.2 Connections 2.2.1 Aborted_clients 2.2.2 Aborted_connects 2.2.3 Connections 2.2.4 Max_used_connections 2.3 Network traffic 2.3.1 Bytes_* 2.4 DML commands 2.4.1 Com_* 2.5 Transactions 2.5.1 Com_* 2.6 Prepared Statements 2.6.1 Com_* 2.6.2 Prepared_stmt_count 2.7 Temporary tables 2.7.1 Created_tmp_tables 2.7.2 Created_tmp_disk_tables 2.8 Table cache usage 2.8.1 Opened_tables 2.8.2 Open_tables 2.9 Threads 2.9.1 Threads_created 2.9.2 Threads_cached 2.9.3 Threads_connected 2.9.4 Threads_running 3 InnoDB 3.1 InnoDB buffer pool 3.1.1 Innodb_buffer_pool_pages_* 3.1.2 Innodb_buffer_pool_* 3.1.3 Innodb_buffer_pool_read_ahead_* 3.1.4 Buffer pool miss rate 3.1.5 Percent of dirty pages 3.1.6 Percent buffer pool used for data 3.1.7 Percent buffer pool free 3.2 InnoDB_data_* 3.3 InnoDB_pages_* 3.4 InnoDB_rows_* 4 MyISAM 4.1 Key buffer size 4.2 Key buffer activity 4.2.1 Key_* 4.2.2 Key_*_requests 4.2.3 Key buffer misses 4.2.4 Key buffer write coalesces

Types of statistics

Incrementing

A statistic that is incremented on each occurrence of something (a counter). These statistics are reset to zero on server restart, or FLUSH STATUS. From MySQL 5.0.x on, it is possible to use Uptime_since_flush_status to tell what time period the statistic covers. Before 5.0.x, it's not possible to tell when FLUSH STATUS has been run, and thus the counters are only useful for actively monitoring the statistic.

A typical strategy to monitor an Incrementing statistic is to:

• Check the value and save it
• Sleep for some number of seconds
• Check the value and compare it to the last value

This requires "stateful" monitoring, where the state can be remembered between polling intervals.

Absolute

A statistic that is either checked "live" from the system (e.g. counting the number of items in a list) or kept in a variable which is both incremented and decremented as actions occur.

No special logic is needed to monitor Absolute statistics, as the value returned is always the current value.

Formula

A statistic composed of more than one absolute or relative statistic provided by MySQL. Quite often these are percentages composed of two or more Absolute statistics.

General

Overall activity

Questions

Incrementing — The number of queries and protocol commands that have been run.

Uptime

Absolute — The number of seconds the server has been online.

Uptime_since_flush_status

Absolute — The number of seconds since either the server was started or FLUSH STATUS has been run.

Connections

Aborted_clients

Incrementing — 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

Incrementing — A count of the number of clients that did not successfully connect due to handshake or network errors in the connect process.

Connections

Incrementing — The number of clients currently connected.

Max_used_connections

Absolute — 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_*

Incrementing — The number of bytes sent to (Bytes_sent) clients (primarily result sets) and received from (Bytes_received) clients (primarily query text).

DML commands

Com_*

Incrementing — 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_*

Incrementing — The number of various transactional commands that have been run:

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

These commands do not count implicit transactions that have been started for single statements using a transactional storage engine. They do count executed but useless commands, such as when using MyISAM tables.

Prepared Statements

Com_*

Incrementing — The number of various prepared statement commands, or their equivalent API commands, that have been run:

• Com_stmt_prepare — PREPARE
• Com_stmt_execute — EXECUTE
• Com_stmt_close — CLOSE

Prepared_stmt_count

Absolute — The number of prepared statements that are currently allocated across all connections.

Temporary tables

Created_tmp_tables

Incrementing — The number of temporary tables that have been created.

Created_tmp_disk_tables

Incrementing — 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

Incrementing — 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

Absolute — 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

Incrementing — The number of threads that have been created. This should be stable as long as the thread_cache variable is large enough.

Threads_cached

Absolute — The number of threads currently in the cache and available for use.

Threads_connected

Absolute — The number of threads currently handling a connection.

Threads_running

Absolute — The number of threads currently in a non-idle (Sleep) state.

InnoDB

InnoDB buffer pool

Innodb_buffer_pool_pages_*

Absolute — 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_*

Incrementing —

• 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_*

Incrementing —

• 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_*

Incrementing —

• 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_*

Incrementing —

• 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_*

Incrementing —

• 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)