MySQL Cluster 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 Cluster InnoDB IO test provides these useful alerts to administrators!

This test monitors reads from the redo log files and writes to disk on each node of the target MySQL Cluster database server. 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 Cluster

Agent deploying the test : A remote agent

Outputs of the test : One set of results for each node in the target cluster being monitored

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port at which the specified host listens.

Database

Specify the name of a database on the target MySQL Cluster 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 Cluster database server. To know how to create such a user, refer to Pre-requisites for Monitoring the MySQL Cluster Database 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 on this node 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 on this node during the last measurement period.

Writes/sec

 

Log IO completed

Indicates the total number of I/O operations completed for the Innodb redo logs on this node 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 on this node during the last measurement period.

IOPS

 

InnoDB data read rate

Indicates the rate at which Innodb data was read on this node during the last measurement period.

Reads/sec

 

InnoDB data write rate

Indicates the rate of Innodb data writes on this node during the last measurement period.

Writes/sec

 

InnoDB data fsync rate

Indicates the rate at which Innodb fsync( ) calls(operations) were completed on this node during the last measurement period.

Sync/sec

 

InnoDB pending read rate

Indicates the rate at which Innodb data was pending reads from this node during the last measurement period.

Reads/sec

 

InnoDB pending write rate

Indicates the rate at which Innodb data was pending to be written to this node during the last measurement period.

Writes/sec

 

InnoDB pending fsync rate

Indicates the number of pending fsync() operations per second on this node 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