MySQL monitor templates description-percona MySQL monitoring template for Cacti

Source: Internet
Author: User
Tags mutex php script savepoint semaphore sorts percona 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.
      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/ 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


  1. 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.
  2. State Copying to Tmp Table
    The server is copying-a temporary table in memory
  3. State End
    This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements
  4. 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
  5. 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
  6. 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.
  7. State Login
    The initial state for a connection thread until the client has been authenticated successfully.
  8. State Preparing
    This state occurs during query optimization.
  9. State Reading from Net
    The server is reading a packet from the network.
  10. 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.
  11. State sorting Result
    For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables
  12. 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.
  13. State Updating
    The thread is searching for rows to update and is updating them
  14. State Writing to Net
    The server is writing a packet to the network.
  15. State None
    Nothing, empty, note is not a null state
  16. State and 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.

Take the template provided by Percona as an example. Where the information get command is as follows (excluding Pt-heartbeat).





SHOW processlist;

SHOW/*!50000 engine*/INNODB STATUS;

MySQL monitor templates description-percona MySQL monitoring template for Cacti

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.