MySQL – Difference Between Handler_write and Innodb_data_writes

innodbMySQLmysql-5.5

Handler write is the number of requests to insert a row in a table.
InnoDB data writes is the total number of data writes.

Questions are

  1. Does inserting a row count as a disk write?
  2. If I do an insert operation in MYISaM engine which will get incremented?
  3. If I do an insert operation in InnoDB engine which will get incremented?

  4. If Handler_write is the number request to insert a row then why does it gets incremented every-time I run select queries.

    mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195556 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195557 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195558 |
+--------+
1 row in set (0.00 sec)

Best Answer

You need the answers based on the storage engine in use

Does inserting a row count as a disk write?

For MyISAM, I would say yes because a MyISAM table keeps its total row count in its header. Inserting a row into a MyISAM would consequently impose an additional write to the table header to update the row count.

For InnoDB, the disk writing activity to a table is delayed until row changes have to traveled throughout the InnoDB Plumbing as shown in this Pictorial Representation from Vadim Tkachenko (Percona CTO)

dskxn

A newly inserted row would go into the InnoDB Buffer Pool, double write buffer (inside ibdata1), and the redo log (ib_logfile0,ib_logfile1).

For a table using the MEMORY storage engine, it would have to be no because the table's data and indexes reside in memory.

If I do an insert operation in MyISAM engine which will get incremented?

Yes

If I do an insert operation in InnoDB engine which will get incremented?

Yes

If Handler_write is the number request to insert a row then why does it gets incremented every-time I run select queries.

Look at the design of information_schema.global_status

mysql> show create table information_schema.global_status\G
*************************** 1. row ***************************
       Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Every time, run a SELECT against this table, mysqld has to update the table by writing new row information. This is by not adding a new but updating VARIABLE_VALUE for VARIABLE_NAME = 'Handler_write'. An UPDATE is just a viable a write source as an INSERT. Keep in mind that Handler_write is storage engine agnostic. In that case, it's just a metric for write requests without knowledge of the storage engine layer.

Related Question