How to monitor InnoDB Disk I/O using Server Status Variables? This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? "

As mention in my previous question i want to count total number of disk i/o per query, but while testing i got confused with some result ,

i am using below example :: 

mysql&gt; explain user;    -------&gt;[user table from Website database]
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| username | varchar(50) | YES  | UNI | NULL    |                |
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| password | varchar(50) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+


I have created a index on username. 

My analysis is 

before running any query system variable status was

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '26'

when i tried 

insert into user (`username`,`password`) values ("xyz1","xyz1");

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27' -------&gt; (shows one write operation in Innodb_log )

when i tried 

Select * from user where username ="xyz1";

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27'

and when i tried 

UPDATE user SET `username`="xyz1", `password`="hello"
WHERE `username`="devlad1";


'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '29'------&gt;[shows two write operation to log]


My first question are


  1). I am calculating disk i/o correctly or i am missing something here?
  
  2). I have create index on 'username' so while doing insert or update index >    count should be modify but varible "Key_writes" keep showing me '0' ,what >    does it means? is index is caching some where ?  


thanks in advance!!!!
Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time.

Additionally, MySQL may write to the mysql binary, general or slow log, or use certain temporary structures for pending transactions, binlog, tables for intermediate results, etc. if they do not fit in memory. Also, bear into mind that if a query involves a temporary table, it can show I/O activity in other engines, as it may create such tables in MyISAM format.

Depending on the configuration and change, a write to InnoDB may cause 0 writes to the transaction log (for example, if it has not yet been synced, or no page was modified) or several (at least one per page modified). Even if you only update 1 record, it may case the modification of several pages (e.g. because a blob is involved, or an index have to be updated). You can check the size of the writes to the transaction log by monitoring the InnoDB Log Sequence Number. Under a strict "flush log at transaction commit", the writes to the log are the only thing that is done to disk, all other changes are tried to be buffered on memory and are written at any future time.

While somehow inaccurate, InnoDB has a section in File I/O in its SHOW ENGINE INNODB STATUS command, for a more global view. As @Ela says, innotop is a good summary tool for monitoring InnoDB operations, including the former command.
Yes, index changes can be buffered, as any writes. Not only the index pages, but index updates are usually heavily buffered through the change buffer and merged later (more than normal updates, as they involve reading data that may not be on cache). Check the INSERT BUFFER AND ADAPTIVE HASH INDEX section on the previous INNODB STATUS command.


In a nutshell, InnoDB is a complex "set of buffers and asynchronous reads and writes", and there is never a guarantee that we can stop all I/O operations (some of them only kick when the server is idle). This is a good introduction material to InnoDB architecture and operations: https://www.percona.com/live/london-2013/sessions/innodb-architecture-and-performance-optimization