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

Question

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!!!

Answer 1

This is a complex topic which is very interesting - I/O, caching - i.e. what's really happening?

Percona always have good stuff about every aspect of MySQL performance - check here. Mark Leith (a senior MySQL devlelopment manager) is worth a look here. Be sure to follow the links to Baron Schwartz's and Brendan Gregg's stuff, both big hitters in the MySQL world. Morgan Tocker is a MySQL community manager and has this to say.

[EDIT - in response to comment by OP]

As I said - it's complex. What is i/o as far as Google/Amazon are concerned? See my own answer (interpretation) to this question below? How do they factor in caching? I'm sure they do it in the background. I do notice that

Amazon appear to charge by GB/month in/out, so you don't care about disk or queries with them.

Google likewise appear to charge by data in/out. So, many small queries will be equal to a few large ones - seems fair.

Both also charge for RAM, so a complex app will also increase price - again, not unreasonably.

My reading of the data/in out charge is that this only counts for bandwidth between your end users and Amazon or Google - I/O processing done within Amazon/Google is covered by your arrangement with them.

Get your calculator out! :-) IANAL!

Answer 2

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.