5

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

2 Answers 2

2

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!

1
  • thanks @Vérace for your answer but can you suggest me any tool for Linux(ubuntu 14.04) by which i can calculate total number of disk i/o per (Insert,update,delete) query?I am asking this specific question because i am in need of calculate total number of disk i/o so that we can figureout can we host our mysql server on AWS or google cloudSQL. Jul 10, 2014 at 5:29
3

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

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

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.

4
  • What would you say about this post by Baron Schwartz?
    – Vérace
    Jul 9, 2014 at 16:10
  • @Vérace Most of the article is about MyISAM. As for the InnoDB part, the ratios mean nothing in terms of queries. However, you should be looking to tune the engine itself. The ratios can be altered by adjusting linear things (increasing or decreasing frequency of flushes, lowering pending flushes, increasing/decreasing buffer pool in the presence of many secondary indexes, etc). The ratio just becomes more of a fudge factor. Notwithstanding, it can be a place to start for the novice. For the more advanced, indexing and key distribution would be the next thing to focus after tuning the engine. Jul 9, 2014 at 16:47
  • Thanks @Rolando for you answer,I have some doubt that I want to ask you (1).Mysql 5.6 innodb storage engine is provided by default,does it means that innodb storage engine perform all disk(HHD)related operation(read/write) means While executing a Query disk read/write operation performed by mysql is same as innodb? Jul 10, 2014 at 5:16
  • (2)while searching on INTERNET and SO i found that for monitoring(calculating for each query)disk i/o i have to manually monitor status of server variable using SHOW STATUS for each query, Is there any tool or any other method you can suggest me by winch i can track disk i/o ? i am in need of calculate total number of disk i/o so that we can figureout can we host our mysql server on AWS or google cloudSQL. Jul 10, 2014 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.