MySQL Cluster InnoDb Throughput Test

InnoDB uses asynchronous disk I/O where possible, by creating a number of threads to handle I/O operations, while permitting other database operations to proceed while the I/O is still in progress. If InnoDB determines that there is a high probability of data being needed soon, it performs read-ahead operations to bring that data into the buffer pool so that it is available in memory. Making a few large read requests for contiguous data can be more efficient than making several small, spread-out requests. There are two read-ahead heuristics in InnoDB:

  • In sequential read-ahead, if InnoDB notices that the access pattern to a segment in the tablespace is sequential, it posts in advance a batch of reads of database pages to the I/O system.

  • In random read-ahead, if InnoDB notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool, it posts the remaining reads to the I/O system.

If the rows in the InnoDB of each node in the MySQL Cluster database server cannot be read through, then, the data should be catered to the users from the disks which is an expensive operation. To avoid such unwanted disk reads/writes, administrators should keep track on the efficiency of the InnoDB tables, figure out how well the rows in the tables are inserted, deleted etc. The MySQL Cluster InnoDb Throughput test helps administrators in this regard!

This test tracks the load on the InnoDB tables of each node on the target MySQL Cluster database server and reports which type of operations (reads or writes or inserts or updates or deletes) are contributing to the workload of the InnoDB tables. The test additionally reports the random/sequential read-aheads initiated by the InnoDB tables. Capacity planning and clustering decisions can be taken based on insights provided by this test.

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

Key reads

Indicates the number of physical reads of a key block from disk into the MyISAM key cache on this node.

Number

If the value of this measure is increasing at a steady pace, then administrators should consider reconfiguring the key_buffer_size value.

Key read rate

Indicates the number of physical reads of a key block from disk into the MyISAM key cache on this node per second during the last measurement period.

Reads/sec

A consistent increase in the value of this measure could indicate a potential overload.

Key writes

Indicates the number of physical writes of a key block from the MyISAM key cache on this node to disk.

Number

 

Key write rate

Indicates the number of physical writes of a key block from the MyISAM key cache on this node to disk per second during the last measurement period.

Writes/sec

A consistent increase in the value of this measure could indicate a potential overload.

Random read ahead rate

Indicates the number of “random” read-aheads initiated by InnoDB on this node during the last measurement period.

Number

 

Sequential read ahead rate

Indicates the number of “sequential” read-aheads initiated by InnoDB on this node during the last measurement period.

Number

 

Wait for free buffer

Indicates the number of buffer pool waits captured on this node during the last measurement period.

Number

Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a page and no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This measure counts the instances of such waits.

A consistently high value for this measure indicates that the innodb_buffer_pool_size value should be reconfigured with appropriate value.

Row insert

Indicates the number of rows inserted into the Innodb tables on this node during the last measurement period.

Number

 

Row update

Indicates the number of rows that were updated in the InnoDB tables on this node during the last measurement period.

Number

 

Row delete

Indicates the number of rows that were deleted from the InnoDB tables during the last measurement period.

Number

 

Row Read

Indicates the number of rows that were read from the InnoDB tables on this node during the last measurement period.

Number

 

Row insert rate

Indicates the rate at which rows were inserted into InnoDB tables during the last measurement period.

Inserts/sec

 

Row update rate

Indicates the rate at which rows were updated in the InnoDB tables on this node during the last measurement period.

Updates/sec

 

Row delete rate

Indicates the rate at which rows were deleted from the InnoDB tables on this node during the last measurement period.

Deletes/sec

 

Row read rate

Indicates the rate at which rows were read from the InnoDB tables on this node during the last measurement period.

Reads/sec