How to monitor InnoDB Disk I/O using Server Status Variables?

Question

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> explain user;    ------->[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' -------> (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'------>[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!!!!

Answer 1

1. 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.

2. 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

Answer 2

You should look at this. Mark Leith is a senior MySQL dev. manager. It's a way (MySQL are trying to get better...) that you can track some metrics - not per query like you want, but data access by table. Install the sys schema in your 5.6 instance and see what happens.

Oh - yes - you were asking about InnodDB in 5.6 - you will have to enable innodb_file_per_table (on by default by 5.7).

On a test system, at least that's a start.

You can start by looking at

select file, total_read, total_written from x$io_global_by_file_by_bytes limit 10;

which doesn't give you data by query, but at least it will start to give you an idea of where to look.

This should start to give you and idea of your i/o per table, if not by query - which (and I'm open to correction here) is not possible with MySQl at the moment.

[EDIT in repsonse to OP's comments]

I'm afraid that I'm having a bit of difficulty in following your exact question. From the docco here and here, you have the defintions of the variables you're asking about. The first is the number of write operations and the second is the number of bytes. How you're going to relate the two, I just don't know. I imagine that it's not simple - what with caching, checkpointing and buffering. I'm guessing that the relationship between the two is some sort of complex function of the application performing the database access - no. of commits &c.

On a brighter note, I did come across something which may help you and make your life a bit easier. If you look here, Mark Leith (mentioned above) talks about how the ps_helper tool is now being implemented in MySQL-Workbench as the sys schema. He also has dba_helper (more experimental AIUI) which is also useful - I think you need dba_helper alongside the sys schema (installed it recently but have forgotten the exact details).

The key point is that all of this (or most of it anyway) is now available within MySQL Workbench in the Peformance->Performance reports section. You could take a look at that and see if that goes any further to answering your issues.

You have said that you are looking for data i/o per query - why are you trying to do this? In any case, if you have your InnoDB tables as separate files (innodb_file_per_table=ON), then you can have a very good idea of what your system is doing - even if you can't get an exact data per query report.

If I were you, I would install Mark Leith's sys schema and dba_helper scripts and then investigate the performance reports and if you have any questions, come back here. I hasten to add that I'm not an expert in this area - I just happen to have looked at it a little.