MySQL InnoDb IO Test

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change table data that result from SQL statements or low-level API calls. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization, and before connections are accepted. By default, the redo log is physically represented on disk by two files named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion. Data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo.

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it writes a log checkpoint, closes and removes the old log files, creates new log files at the requested size, and opens the new log files. When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small redo log files cause many unnecessary disk writes. Although historically big redo log files caused lengthy recovery times, recovery is now much faster and you can confidently use large redo log files.

If too much of data is pending to be written/read from the InnoDB tables, then, data will be catered directly from the disks which is an expensive operation. To reduce such expensive disk reads and to improve the redo logs and buffer pool usage, the InnoDB tables should be populated with the data that is frequently accessed. This is why, it is important that the InnoDB tables are updated frequently with the data. Any delay in such reads/writes to the redo log files or the buffer pool of the InnoDB tables will have a significant impact in the disk I/O of the database server. It is therefore imperative that administrators are promptly notified if any slowness is observed in reads from disk to redo log files/buffer pool or writes from redo log files/buffer pool to disk. The MySQL InnoDB IO test provides these useful alerts to administrators!

This test monitors reads from the redo log files and writes to disk. Using this test, administrators can figure out which type of operations is pending frequently on the redo logs - is it reads? or writes? or fsync() operations? In the process, the test proactively alerts administrators to potential latencies in reading from and/or writing to disk.

Target of the test : A MySQL server

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the target database server instance being monitored

Configurable parameters for the test

Parameter	Description
Test Period	How often should the test be executed.
Host	The IP address of the MySQL server.
Port	The port at which the specified host listens.
Database	Specify the name of a database on the target MySQL database server being monitored
Username and Password	The eG agent has to be configured with the credentials of a user who has server-wide Process and Select privileges on the monitored MySQL server. To know how to create such a user, refer to Pre-requisites for Monitoring the MySQL Server topic.
Confirm Password	Confirm the password by retyping it here.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Pending log write rate	Indicates the rate at which writes to the Innodb redo logs were pending during the last measurement period.	Writes/sec
Pending checkpoint write rate	Indicates the rate at which checkpoint writes to the Innodb redo logs were pending during the last measurement period.	Writes/sec
Log IO completed	Indicates the total number of I/O operations completed for the Innodb redo logs during the last measurement period.	Number
Log IO completed rate	Indicates the rate at which I/O operations were completed for the Innodb redo logs during the last measurement period.	IOPS
InnoDB data read rate	Indicates the rate at which Innodb data was read during the last measurement period.	Reads/sec
InnoDB data write rate	Indicates the rate of Innodb data writes during the last measurement period.	Writes/sec
InnoDB data fsync rate	Indicates the rate at which Innodb fsync( ) calls(operations) were completed during the last measurement period.	Sync/sec
InnoDB pending read rate	Indicates the rate at which Innodb data was pending reads during the last measurement period.	Reads/sec
InnoDB pending write rate	Indicates the rate at which Innodb data was pending to be written during the last measurement period.	Writes/sec
InnoDB pending fsync rate	Indicates the number of pending fsync() operations per second during the last measurement period.	Sync/sec
InnoDB create page rate	Indicates the number of pages created by operations on the Innodb tables per second during the last measurement period.	Creates/sec
InnoDB read page rate	Indicates the number of pages that were read from the InnoDB buffer pool by operations on InnoDB tables per second during the last measurement period.	Reads/sec
InnoDB written page rate	Indicates the number of pages written by operations on InnoDB tables per second during the last measurement period.	Writes/sec