how to calculate the total number of disk writes done by MySQL

I am trying to write a program to calculate the total disk writes done by MySQL ..i know that you can get the variables with the "show global status" mysql command..i need help in figuring out from the list of variables that actually give a count of any sort of disk write..so that i can sum the counts and my problem is solved

mysql> show global status;

gives values of the following variables

show variables;
etc ...

Innodb_data_writes        
Innodb_data_written       
Innodb_dblwr_pages_written        
Innodb_dblwr_writes       
Key_write_requests        
Key_writes        

I dont know the meaning of some of the variables and what they mean but i guessd and kind开发者_运维百科a narrowed it down to

Innodb_data_writes - The total number of data writes

Innodb_dblwr_writes - The number of doublewrite operations that have been performed.

Innodb_log_writes - The number of physical writes to the log file

Key_writes - The number of physical writes of a key block to disk

are these correct ?or are there any other disk write by mysql that i havnt considered?or is there a better way to calculate disk writes done by my sql

Look at the Handler_write variable (and other Handle_* variables).

The writes variables you have mentioned are logical writes, they are not necessarily writes to disk. Also you have to take into account writes to the binary log and relay logs when enabled.

Another way to measure this is from Performance_Schema. The sys schema provides summaries for different kinds of writes. Here is an example:

https://github.com/mysql/mysql-sys#io_global_by_file_by_bytes--xio_global_by_file_by_bytes

I think this would solve your problem.

Going back to global status, you can divide the total amount of writes against the total fsync counts to get some rough estimate of write size per N time, again this would be approximation since especially with different sync settings for log file and binlog/relay logs when enabled.

If you need to measure actual disk writes for everything that comes out of MySQL, including temporary tables, you have to go to the OS layer. Ideally, you should separate your MySQL data into its dedicated volume or partition to get the basic instrumentation from /proc/diskstats and not mangled with other processes. Look at how pt-diskstats calculate the relative values from its output and you can solve your problem similarly.