How to monitor InnoDB Disk I/O Required for MySQL?

I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and googled also and encounter a post give on this link Which is quite helpful.

It says I have to use SHOW STATUS

mysql>SHOW STATUS;

And monitor Key_read and Key_write for physical index read write , Created_tmp_disk_tables and Created_tmp_files for creating temp file for creating temp table and file on disk.

As i am using innodb , buffer pool size also affected on total number of disk read operation [which is totally depend on availability of hot pages ] so here i am confused about


1>>.What are the Innodb "Server Status Variables” i should consider while
counting disk read write?


2>>.Are there any other "Server Status Variables” i should consider while
counting disk read write?


3>>. Are there other factor i am missing here?


4>>. Dose hard disk type and speed will affect on total i/o disk read write?

thanks in advance!!!

Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are the values you need to monitor for buffer reads and disk read for the Buffer Pool.

What you are looking for is a cache miss rate of 1% or less based on this

Innodb_buffer_pool_reads X 100 / Innodb_buffer_pool_read_requests

I have written posts like this before

• Feb 04, 2014 : Tuning key_reads in MySQL

• Jun 08, 2012 : How to improve MySQL Server Performance..??

You should also be tracking how much data is being poured into the InnoDB transaction logs

• Innodb_os_log_written : The number of bytes written to the InnoDB redo log files.

• Innodb_log_write_requests : The number of write requests for the InnoDB redo log.

• Innodb_log_writes : The number of physical writes to the InnoDB redo log file.

EXAMPLE

you could take the delta of Innodb_os_log_written (bytes written in a one second window) and divide it by the delta of Innodb_log_writes and track how many bytes are being flushed to the logs per second. If the result seems too high for you, you could tweek innodb_flush_method, innodb_flush_log_at_trx_commit, innodb_log_file_size and things of this nature.

StackExchange Database Administrators Q#70060, answer score: 3