MySQL monitor templates description-percona MySQL monitoring template for Cacti

http://blog.chinaunix.net/uid-16844903-id-3535535.htmlhttps://www.percona.com/doc/percona-monitoring-plugins/ 1.1/zabbix/index.html

• InnoDB Adaptive Hash Index
• InnoDB Buffer Pool Activity
• InnoDB Buffer Pool
• InnoDB Checkpoint Age
• InnoDB Current Lock Waits
• InnoDB I/O
• InnoDB I/O Pending
• InnoDB Insert Buffer
• InnoDB Insert Buffer Usage
• InnoDB Internal Hash Memory Usage
• InnoDB Lock Structures
• InnoDB Log
• InnoDB Memory Allocation
• InnoDB Row Lock Time
• InnoDB Row Lock Waits
• InnoDB Row Operations
• InnoDB semaphores Wait Time
• InnoDB semaphores Waits
• InnoDB semaphores
• InnoDB Tables in use
• InnoDB Transactions active/locked
• InnoDB transactions
• MyISAM Indexs
• MyISAM Key Cache
• MySQL Binary/relay Logs
• MySQL Command Counts
• MySQL Connections
• MySQL Files and Tables
• MySQL Handler
• MySQL Network Traffic
• MySQL processlist
• MySQL Query Cache
• MySQL Query Cache Memory
• MySQL Query Response Time (microseconds)
• MySQL Query Time Histogram (Count)
• MySQL Replication
• MySQL Select Types
• MySQL Sorts
• MySQL Table Locks
• MySQL Temporary Objects
• MySQL Threads
• MySQL Transaction Handler

InnoDB Adaptive Hashindex

Shows the use of Adaptive hash Index , which can only be used to search for an equivalent query .
# Hash Table Size 17700827, node heap has 35112 buffer (s)
# 3577.42 Hash searches/s, 507.49 non-hash searches/s

1. Hash Index Cells Total
   The number of slots in the adaptive hash table =innodb_buffer_pool_size/256
2. Hash Index Cells used
   The number of queries used in the Adaptive hash table

InnoDB Buffer poolactivity

Displays the internal activity of the InnoDB buffer pool , the creation , reading, and writing of the page . If there is a sudden increase or decrease , you need to check the application

1. Pages Created
2. Pages Read
3. Pages written

InnoDB Buffer Pool

1. Pool Size
   Number of pages in InnoDB buffer pool, size 16K per page
2. Database Pages
   Data page Size
3. Free Pages
   Free Page Size
4. Modified Pages
   Dirty data page. If there are too many dirty data pages, you need to check the disk IO status.

InnoDB Checkpoint Age

1. Uncheckpointed Bytes
   Shows the amount of data that was not written to the disk. If the value is close to the total size of Innodb_log_file_size * N, you need to increase the value of innodb_log_file_size, but be aware that a longer response time is required if the outage occurs. (Recovered from Redolog)

InnoDB Current Lockwaits

1. InnoDB Lock Wait Secs
   Displays the total number of InnoDB transactions that are in lock waits per second. If you have a very large value, you should check the lock wait transactions, see the following template

InnoDB I/O

1. File Reads
   Displays the number of reads per second of the file
2. File writes
   Displays the number of times a file is written per second
3. Log writes
   Number of Log Writes
4. File Fsyncs
   The number of times the Fsync () function was called. Related to the setting of the Innodb_flush_log_at_trx_commit value.

InnoDB I/O Pending

Shows InnoDB pending synchronous or asynchronous IO operations . If there are too many pending operations , larger RAM, larger buffer pools , and faster disks are required.

1. Pending Aio Log Ios
2. Pending Aio Sync Ios
3. Pending Buf Pool Flushes
4. Pending CHKP writes
5. Pending ibuf Aio Reads
6. Pending Log Flushe
7. Pending Log writes
8. Pending Normal Aio Reads
9. Pending Normal Aio writes

InnoDB Insert Buffer

Insert buffering , not part of the cache, but physical pages , inserts or updates for nonclustered indexes , not every time the index page is inserted directly . Instead, you first determine whether the inserted nonclustered index page is in the buffer pool . If so, insert directly and, if no longer , put in an insert buffer . The merge operation of the insert buffer and nonclustered index page child nodes is then performed at a certain frequency .
Usage criteria : Nonclustered index , non-unique

1. IBUF Inserts
   Number of records inserted
2. IBUF merged
   The number of merged pages
3. Ibuf Merges
   Number of merges

If the value of merges/merged equals 3/1, the IO request for the non-clustered index page is approximately 3 times times Lower for the insert buffer

InnoDB Insert Bufferusage

1. Ibuf Cell Count
   Segment Size
2. Ibuf used Cells
   Size of the Insert buffer
3. Ibuf Free Cells
   The length of the "Free list"

InnoDB Internal hashmemory Usage

Shows the amount of memory that is occupied by various hash structures within the INNODB (not manual intervention).

1. Adaptive Hash Memory
   Memory size consumed by adaptive hash Index
2. Page Hash Memory
3. Dictionary Cache Memory
4. File System Memory
5. Lock System Memory
6. Recovery System Memory
7. Thread Hash Memory

InnoDB Lock Structures

1. InnoDB Lock Structs
   The graph shows how many lock structures (not deadlocks) are inside the InnoDB. This is roughly related to the number of rows locked by the current transaction. Can be used to determine if there is a lock contention.
   For the quantity is good or bad, there is no hard rules. In practice, a large number of transactions are waiting for the lock, it is clear that the smaller the value the better.
   This data source is show ENGINE INNODB STATUS;

# lock struct (s), Heap size 3024, undo log Entries 27

# lock WAIT # lock struct (s), Heap size 3024, undo log Entries 5

# lock WAIT 2 lock struct (s), Heap size 368

InnoDB Log

Related variables : innodb_log_buffer_size

1. InnoDB Log Buffer Size
2. Log Bytes Written
   Log Sequence Number
   Location of the current log
3. Log Bytes Flushed
   Log flushed up to
   Where the log has been refreshed
4. Unflushed Log
   Is the difference between Log_bytes_written and log_bytes_flushed, which indicates how much data in the log buffer has not been flushed to the log file.
   If the difference exceeds 30% of the innodb_log_buffer_size setting, you need to consider whether to increase the parameter value.

InnoDB memoryallocation

# Total Memory allocated8824815616; In additional pool allocated 0

1. Total Mem Alloc
   InnoDB total amount of memory requested, in bytes
2. Additional Pool Alloc
   Total amount allocated to additional memory, per byte

InnoDB Row Lock Time

1. InnoDB Row Lock Time
   The Innodb_row_lock_time state variable read by the template that represents the total time, in milliseconds, that the InnoDB engine waits for each request for data row locks.

InnoDB Row Lock Waits

1. InnoDB Row Lock Waits
   Read the Innodb_row_lock_waits state variable, which means that InnoDB has been getting a row lock for such a long time. Milliseconds

InnoDB Row Operations

# Number of rows inserted50678311, updated 66425915, deleted 20605903, read 454561562
Broadly capable of performing innodb internal operations

1. Row Read
2. Row Deleted
3. Row Updated
4. Row Inserted

InnoDB semaphores WaitTime

1. InnoDB Sem Wait Time Ms
   Displays the total elapsed time (in milliseconds) of the wait times for InnoDB threads that are currently waiting for mutexes.
   Analysis:
   Under normal circumstances, InnoDB semaphores wait time and InnoDB semaphores waits should be empty. Unless the server is running high-concurrency workloads, it prompts InnoDB to take the action that the operating system waits for. Information is located on show ENGINE INNODB Status of the semaphores fragment.
   The relevant PHP script code sections are as follows:

ElseIf (Strpos ($line, ' seconds the semaphore: ') > 0) {

#--thread 907205 have waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore:

Increment ($results, ' innodb_sem_waits ', 1);

Increment ($results, ' Innodb_sem_wait_time_ms ', To_int ($row [9]) * 1000);

}

Where is the value of Innodb_sem_waits , which indicates how many threads are waiting , while Innodb_sem_wait_time_ms represents the time that all threads wait, the default is seconds , multiplied in the script 1000, so the units in the monitor chart are milliseconds .

• deal with this problem
  InnoDB takes a multi-stage wait strategy. First, try to cycle the lock. If a preset cycle wait period (Innodb_sync_spin_loops = 30, the current profile defaults to 30) has not succeeded, it will fall back into the more expensive and more complex waiting array , if the concurrency is too large, it causes a sudden increase in system load.
  The cost of the loop wait is relatively low, but it is necessary to constantly check whether a resource is locked and consume CPU cycles, that is, when another thread can handle things, the loop waits for the processor.
  The alternative to a recurring wait is to have the operating system do context switching (waiting for the array), and switching thousands of times per second can cause a lot of overhead.
• Solutions
  Adjust the parameters according to the specific application, or optimize the application to reduce concurrency.

InnoDB semaphoreswaits

1. InnoDB Sem Waits
   Displays the number of InnoDB threads that are currently waiting for mutexes.

InnoDB semaphores

Displays the signal activity status inside the InnoDB .
Includes the sum of the quantities of various semaphores, such as the Mutex spin waits,rw-shared spins,rw-excl spins .

1. Spin Rounds
   InnoDB internally preset number of mutex signals
2. Spin Waits
   Number of InnoDB internal lock cycles (related to innodb_sync_spin_loops parameters)
3. Os Wait System waits
   Number of wait arrays that the transaction has retreated to the operating system
   In the case of high concurrency, it can be found that the value is spike-like and unstable. The image mainly shows that, in the case of unreasonable design, the contention between the different connection types of the row lock or mutex.

InnoDB Tables in use

# MySQL tables in use 2,locked 2

1. InnoDB Tables in use
   The number of tables to use for all transactions
2. InnoDB Locked Tables
   The number of tables locked by all transactions

InnoDB transactionsactive/locked

The figure shows the number of states of the InnoDB transaction .

1. Active transactions
   Number of transactions being executed
2. Locked transactions
   Number of locked transactions
3. Current transactions
   Current number of transactions (including not started,active,... various states)
   Not started # Transaction has been committed to disk
   ACTIVE # transactions that are executing
4. Read Views (pending Update)
   Read views open inside InnoDB

InnoDB transactions

Displays information related to InnoDB transactions

1. InnoDB transactions
   The total number of transactions inside the InnoDB. Calculated from the following values:
   Trx ID Counter 89f56195 # The current transaction ID, which accumulates every new transaction created
   Purge done for Trx ' s N:o < 89f5609c undo N:o < 0 # InnoDB the transaction ID used to clear the old version of MVCC. The old version data before this ID has been cleared.
   This value is the value of the transaction ID minus the old data cleared by the current transaction ID, and then the hexadecimal to decimal. (Refer to Ss_get_mysql_stats.php script 902 lines)
2. History List
   The length of the history record. The number of non-purged transactions in the undo space of the InnoDB data file. When a transaction executes an update and commits, the number is incremented, and it decrements when the purge process removes an older version of the data.

MyISAM Indexs

Shows read and write on the MyISAM index

1. Key Reads Requests
   Reads the number of read operations from the key cache for the index block
2. Key Reads
   Reads the number of read operations for the index block from disk
3. Key Write Requests
   Number of requests to write an index block to the key cache
4. Key writes
   Number of write operations to write index blocks to disk

MyISAM Key Cache

1. Key Buffer Size
   Key Cache Size
2. Key Buf Bytes used
   Same key_blocks_used variable
   The number of cache blocks that have been used in the key cache
3. Key Buf Bytes Unused
   With key_blocks_unused
   The number of cache blocks that have not been used in the key cache

MySQL Binary/relaylogs

1. Binlog Cache Use
   The number of transactions saved in the binary log cache
2. Binlog Cache Disk Use
   The number of transactions that use disk temp files exceeds the cache size set by binlog_cache_size
3. Binlog Log Space
   The size of the binary log
4. Relay Log Space
   Size of the trunk log

If the Binlog cache Disk Use/binlog Cache use has a large value , you should try increasing the size of the Binlog_cache_size . But don't expect to improve too much , If the number of write temporary files is reduced from 1 per second to one per minute , this has proven to be good enough for optimization. There is no need to consume a lot of memory to handle transactions in Binlog_cache_size .

MySQL Command Counts

Command counter , showing the number of times MySQL (in the last 1 seconds ) has executed various commands

1. Questions
   Records the total number of queries and commands received by the server. (The total number of com_* variables is not necessarily equal.)
2. Com Select
3. Com Delete
4. Com Insert
5. Com Update
6. Com Replace
7. Com Load
8. Com Delete Multi
9. Com Insert Select
10. Com Update Multi
11. Com Replace Select

MySQL Connections

1. Max Connections
   Maximum number of client connections allowed to remain open at the same time
2. Max used Connections
   The maximum number of open connections that have been opened before
3. Aborted clients
   Number of connections discarded due to client not shutting down properly
4. Aborted connects
   The number of unsuccessful attempts to connect to the MySQL server
5. Threads CONNECTD
   Number of connections that are now in the open state
6. Connections
   The number of attempts to connect to the MySQL server

MySQL Files and Tables

1. Table Cache
2. Open Tables
   The number of data tables that are currently open. Excluding temporary
3. Open Files
   The number of files that are currently open, and if they are close to open_files_limit, you should increase the value of open_files_limit.
4. Opened Tables
   The total number of data tables that have been opened by the MySQL server (including explicitly defined temporary tables). If this value is high, you should consider carefully whether to increase the data table cache (Table_open_cache).

MySQL Handler

1. Handler_writer the number of requests to insert a row of data into the data table
2. Handler_update the number of requests to modify a data row in the data table
3. Handler_delete the number of requests to delete a data row from a data table
4. Handler_read_first number of requests to read the first index entry in an index
5. Handler_read_key the number of requests to read a data row based on an index value
6. Handler_read_next the number of requests to read the next data row in index order
7. Handler_read_prev the number of requests to read the previous data row in reverse order by index
8. Handler_read_rnd the number of requests to read a data row based on the location of a data row
9. Handler_read_rnd_next the number of requests to read the next data row. If this number is high, it means that there are many statements that need to be scanned by a full table to complete or that many queries are not using the appropriate index

MySQL Network Traffic

1. Bytes Send
   Number of bytes Sent
2. Bytes Received
   Number of bytes Received

MySQL processlist

Http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

2. State Closing Tables
   The thread is flushing the changed table data to disk and closing the used tables. This should is a fast operation. If not, verify-do not has a full disk and that the disk was not in very heavy use.
4. State Copying to Tmp Table
   The server is copying-a temporary table in memory
6. State End
   This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements
8. State freeing Items
   The thread has executed a command. Some freeing of items do during this state involves the query cache. This was usually followed by cleaning up
10. State Init
    This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query Cache cleanup op Erations.
    For the end state, the following operations could is happening:
    Removing query cache entries after data in a table is changed
    Writing an event to the binary log
    Freeing memory buffers, including for BLOBs
12. State Locked
    The query is locked by another query.
    As of MySQL 5.5.3, this state was removed because it is equivalent to the Table lock state and no longer appears in SHOW Processlist output.
14. State Login
    Login
    The initial state for a connection thread until the client has been authenticated successfully.
16. State Preparing
    This state occurs during query optimization.
18. State Reading from Net
    The server is reading a packet from the network.
20. State sengding Data
    The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the Longest-running state over the lifetime of a given query.
22. State sorting Result
    For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables
24. State Statistics
    The server is calculating statistics to develop a query execution plan. If a thread is a and a long time, the server is probably Disk-bound performing.
26. State Updating
    The thread is searching for rows to update and is updating them
28. State Writing to Net
    The server is writing a packet to the network.
30. State None
    Nothing, empty, note is not a null state
32. State and other
    Other

MySQL query Cache mysql query cachememory mysql query responsetime (microseconds)

Percona Documentation

MySQL Query Timehistogram (Count)

Percona Documentation

MySQL Replication

The default is to get each status value with the SHOW SLAVE status command

1. Slave Running
   Whether the I/O thread and the SQL thread are running from the server
2. Slave Stopped
3. Slave Lag
   Replication Latency
4. Slave Open Tmp Tables
   The number of temporary files that have been opened from the SQL thread in the server
5. Slave Retried Transactions
   Number of times a transaction has been re-attempted from a SQL thread in the server

MySQL Select Types

1. Select Full Join
   The number of times a multi-table join operation was not completed using an index. This is a performance killer and it's best to optimize SQL.
2. Select Full Range Join
   The number of times a majority of table join operations completed using the interval search operation on an accessible reference table (reference table).
   This value indicates the number of times the table was joined using a range query.
3. Select Range
   The number of times a most-used-table join operation was performed with a range on the first data table.
4. Select Range Check
   This variable records the number of query plans that re-examines the index for each row of data at the time of the join, and it has a high overhead.
   If the value is high or increasing, some queries do not find a good index.
5. Select Scan
   The number of times a majority of the table join operations completed by a full table scan of the first data table.

MySQL Sorts

1. Sort Rows
   Sort on how many rows
2. Sort Range
   The number of times the sort operation takes place with one interval
3. Sort Merge Passes
   The number of times that the query caused the file to be sorted. You can optimize SQL or add sort_buffer_size variables appropriately
4. Sort Scan
   The number of sorting operations completed with one full table sweep

MySQL Table Locks

1. Table Locks Immediate
   The number of data table lock requests that can be satisfied immediately without waiting
2. Table Locks waited
   Shows how many tables are locked and causes server-level lock waits (storage engine-level locks, such as InnoDB row-level locks, do not increase the variable).
   If this value is higher or increasing, then there is a serious concurrency bottleneck.
3. Slow Queries
   Number of slow queries (execution time exceeds long_query_time value)

MySQL temporaryobjects

1. Created_tmp_tables
   The number of temporary data tables created in memory by the MySQL server when processing SQL query statements.
   If the value is too high, the only workaround is to refine the query statement.
2. Created_tmp_disk_tables
   The number of temporary data tables created on disk by the MySQL server when processing SQL query statements, if this value is higher, the probable cause:
   A. Query creates a temporary table when a BLOB or text column is selected
   The values of b.tmp_table_size and max_heap_table_size may be too small.
3. Created_tmp_files
   The number of temporary files created by the MySQL server

MySQL Threads

1. Thread Cache Size
   The maximum number of threads that the thread cache can hold. Disconnected MySQL connections are placed in this cache, and new connections are reused instead of creating new threads.
   If there are free threads in the cache, MySQL can quickly respond to connection requests without creating a new thread for each connection. Each thread in the cache typically consumes 256KB of memory.
2. Thread Created
   Total number of threads created for processing connections

Mysqltransaction Handler

1. Handler Commit
   The number of requests to commit a transaction
2. Handler Rollback
   The number of requests to roll back a transaction
3. Handler savepoint
   The number of requests to create a transaction save point
4. Handler savepoint Rollback
   The number of requests that are rolled back to a transaction save point.

Reference:
Http://www.percona.com/doc/percona-monitoring-plugins/cacti/mysql-templates.html
http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
Take the template provided by Percona as an example. Where the information get command is as follows (excluding Pt-heartbeat).

SHOW/*!50002 GLOBAL * * STATUS;

SHOW VARIABLES;

SHOW SLAVE STATUS;

SHOW MASTER LOGS;

SHOW processlist;

SHOW/*!50000 engine*/INNODB STATUS;

MySQL monitor templates description-percona MySQL monitoring template for Cacti