MySQL 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 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 InnoDb Throughput test helps administrators in this regard!

This test tracks the load on the InnoDB tables of the MySQL 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 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
Key reads	Indicates the number of physical reads of a key block from disk into the MyISAM key cache.	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 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 to disk.	Number
Key write rate	Indicates the number of physical writes of a key block from the MyISAM key cache 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 during the last measurement period.	Number
Sequential read ahead rate	Indicates the number of “sequential” read-aheads initiated by InnoDB during the last measurement period.	Number
Wait for free buffer	Indicates the number of buffer pool waits 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 during the last measurement period.	Number
Row update	Indicates the number of rows that were updated in the InnoDB tables 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 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 during the last measurement period.	Updates/sec
Row delete rate	Indicates the rate at which rows were deleted from the InnoDB tables during the last measurement period.	Deletes/sec
Row read rate	Indicates the rate at which rows were read from the InnoDB tables during the last measurement period.	Reads/sec