MySQL DELAY_KEY_WRITE Option

delay_key_write

 

This option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.

 

If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover-options option (for example, --myisam-recover-options=BACKUP,FORCE). See Section 5.1.3, “Server Command Options”, and Section 14.4.1, “MyISAM Startup Options”.

---

Another performance option in MySQL is the DELAY_KEY_WRITE option. According to the MySQL documentation the option makes index updates faster because they are not flushed to disk until the table is closed.

Note that this option applies only to MyISAM tables,

You can enable it on a table by table basis using the following SQL statement:

ALTER TABLE sometable DELAY_KEY_WRITE = 1;

This can also be set in the advanced table options in the MySQL Query Browser.

This performance option could be handy if you have to do a lot of update, because you can delay writing the indexes until tables are closed. So frequent updates to large tables, may want to check out this option.

Ok, so when does MySQL close tables?

That should have been your next question. It looks as though tables are opened when they are needed, but then added to the table cache. This cache can be flushed manually with FLUSH TABLES; but here's how they are closed automatically according to the docs:

• When the cache is full and a thread tries to open a table that is not in the cache.
• When the cache contains more than table_cache entries and a thread is no longer using a table.
• FLUSH TABLES; is called.

If DELAY_KEY_WRITE is enabled, this means that the key buffer for tables with this option are not flushed on every index update, but only when a table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover option (for example, --myisam-recover=BACKUP,FORCE).

So if you do use this option you may want to flush your table cache periodically, and make sure you startup using the myisam-recover option.

---

 

插入大数据时，有索引会很慢，可以DISABLE KEYS，或者直接在table中加入DELAY_KEY_WRITE

注：

delay_key_write这个参数只对myisam类型表有效
如果你某个表需要经常update操作，这个参数就很管用！
但等delay_key_write使用时，出现断电或重启时，会导致在cache的索引update没来得及更新，所以必须在启动参数加上 –myisam-recover，或者在conf设置myisam-recover=BACKUP,FORCE。这样在你启动mysql的时候会检查你的 表并同步表和索引.

另外如果修复myisam类表可以在my.cnf中mysqld段设置myisam-recover恢复功能，参数有：default,force,backup,QUICK

1.
LOCK TABLES `test` WRITE;
ALTER TABLE `test` DISABLE KEYS ;

INSERT INTO `test` VALUES (1,'???',80,1),(2,'???',90,2),(1,'李四',80,3),(2,'王五',90,4),(1,'aa',12,5), (3,'aa',123,6),(4,'aadwa',123,7);

ALTER TABLE `test` ENABLE KEYS;
UNLOCK TABLES;

2.

最近天天MySQL负载经常一会高，一会低的不稳定。

整天还被perl 折腾着，晕死了。

 

首先, iostat -x 1看看是不是 io 瓶颈较大。

iowait 才 0.45 见鬼，多又是程序问题。

top 一下看看了 mysqld 消耗CPU非常厉害

估计又是程序问题。

用我的per程序取了下 MySQL的数据

发现 key_writes / key_write_request 几乎接近 1了。晕

说明，update，delete, insert  语句非常平凡。

最后看了 binlog 仅1小时 有个表 有1w多次 UPDATE操作，

 

看来需要 打开 delay_key_write 了。

这个参数只对 MyISAM有效，可以再create table 时指定 delay_key_write ，如果表已经存在可以使用 alter table sometable delay_key_write =1;

 

如果你的某个表有很多update操作，这个参数的优势会很好的体现出来。因为这个参数能延迟更新索引到表关闭。

当我们需要经常跟新一个大表的时候，可以考虑使用这个参数。

 

那么，表关闭会在什么时候发生？你可以理解成当flash table的时候，表将关闭。那么有2种情况将会发生 flush table：
当cache 满了一个新的thread试图打开一个表的时候，那个表没有在cache；
当cache里的表数比table_cache多时thread不在使用表；
这个2种情况将会flush table。

 

当delay_key_write 使用的时候，如果出现重启或者掉电等情况，会导致在cache的索引update没来得及更新，所以必须在启动参数加上--myisam-recover，或者在conf设置myisam-recover=BACKUP,FORCE。这样在你启动mysql的时候会检查你的表并同步表和索引.

常用MySQL的童鞋都知道这个myisam类型的表极容易损坏，多数人可能都是用myisamchk命令来人工修复，下面介绍一种自动修复myisam的方法，也是我上午刚学的，共同进步，呵呵~
在MySQL的配置文件my.cnf中，启动项部分加入myisam-recover设置数据恢复功能，具体参数如下：
DEFAULT
与没有使用--myisam-recover选项相同。
BACKUP
如果在恢复过程中，数据文件被更改了，将tbl_name.MYD文件备份为tbl_name-datetime.BAK。
FORCE
即使.MYD文件将丢掉多个行也进行恢复。
QUICK
如果没有删除块，不要检查表中的行。
我设置了BACKUP和FORCE参数，如下：
[mysqld]
myisam-recover=BACKUP,FORCE

参考：

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_delay_key_write

http://www.petefreitag.com/item/441.cfm

http://www.cnblogs.com/zhizhesky/archive/2011/08/22/2149357.html