from: http://bbs.chinaunix.net/viewthread.php?tid=2331463
You are here: Home / Performance / InnoDB转MyISAM场景漏调整某重要参数值造成的悲剧
InnoDB转MyISAM场景漏调整某重要参数值造成的悲剧
六月 10, 2011 by Eugene · Leave a Comment (Edit)
【导读】
为帮助朋友解决问题，曾写过一篇文章介绍，如何快速把数量众多的MyISAM引擎的表，转换成存储
引擎为InnoDB。现在，将根据实际应用
场景，写篇关于表引擎全是InnoDB的业务场景，因资源的问题，而不得不将部分数据存储表引擎转为MyISAM，而碰到的麻烦，分享给大家，以示借鉴意义。
n 测试环境
操作系统
：CentOS release 5.5 (Final)
MySQL版本：5.0.82-percona-highperf-b16-log
存储引擎：build-in InnoDB
转储表的数据量：540W~800W条数据，容量大小：260M~420M
服务器
配置：150G*15K*1，8G*1 MEMORY，4*core Intel E5506 2.13GHZ
跑的业务信息简述：C++程序接受前端抛过来的日志信息，共三类日志信息：少量且核
心的报警性日志信息、量大且主要用于用户
行为分析的日志信息、
接近实时的用户部分属性。第三类日志性数据缓存１个小时，再刷
入数据库
，可转存储的日志信息不缓存，第一、二类直接写入数据
库中，且每天一张存储引擎为：InnoDB的表存储数据。
n 业务场景
业务简述中描述了有三种类型的日志信息，第一类日志只有INSERT操作，我们需要进行转储的日志属于第二类是INSERT + UPDATE的操作，第三类日志是 INSERT +UPDATE操作，必须保留不删档的。
第二类可以定期清理的日志信息，是每天生成一张新表用于存储记录相关日志信息，游戏玩家触发了，打有标签类型的统计项的话，若此统计部存在的话，先对表进行INSERT操作，增加一条新记录，若再次触发相同统计项的话，就进行UPDATE操作更新计数字段的值。而且每天的数据量会因统计项和玩家数有关系，一般的平台都会在：540W~800W条数据，而且UPDATE操作量非常大，为使更新动作更快，统计程序有一个缓存和和并更新操作的功效，且确保数据更新的线程不堵塞， 就必须选用支持事务的InnoDB引擎，提高并发写的能力。
n 数据库中数据转储需求
每天自动生成用于存储第二类型数据的表，其名称格式：user_statistic_＋日期（格式：YYYYMMDD），例如：user_statistic_20110608，此类表的数据保留期：2个月+当前月的数据，而且不能删除，以备后续继续使用。
数据转储的方式，先把数据表的存储引擎由InnoDB 转换为 MyISAM，然后再执行FLUSH TABLE tablename;命令确保正确关闭需要移出的表，然后直接把MyISAM引擎的表mv到/home/mysql
_bak/目录中，接着进行scp到其他服务器，最后执行rm –f /home/mysql_bak/user_statistic_*，以释放出更多的磁盘空间。
n 参数调整的影响
首先阐述下，我们整个系统
中，除了系统数据库中有MyISAM引擎表外，再无其他存储数据表的存储引擎为MyISAM，为此设置如下：
net_buffer_length = 1M
sort_buffer_size = 16M
key_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
max_heap_table_size = 1G
tmp_table_size = 128M
请注意红色字体，其它相关参数都调整了，唯独没有调整key_buffer_size的值，在此相关配置参数的状态下，执行SQL语句：
ALTER TABLE user_statistic_20110608 ENGINE=MyISAM;
会出现什么样的状况呢？5.0.82和5.1.40版本会有点不太一样，列出的状态中有区别的话，注明是哪个版本特有的：
u 服务器LOAD飙升很厉害，IO WAIT能立即达到30%左右；
u SHOW PROCESSLIST明显能看到其他程序的写线程速度很慢，慢查询日志也多起来；
u 磁盘上可以看到三个临时文件
：#sql-3e0b_593.frm、#sql-3e0b_593.MYD、#sql-3e0b_593.MYI，其中mysqld服务是先写磁盘上的 *.MYI文件的数据，再写*.MYD的数据，也即先把表的索引数据写到磁盘上，后把元数据写到磁盘上；
u 存储引擎转储的速度很慢，与服务器物理IO能力和有大量的写请求也有关系；
u 10%左右概率，会因表存储引擎转换而导致mysqld服务出现hang（备注：5.1.*不会）；
备注：MySQL 5.0.82在对表进行ALTER 或UPDATE大量数据时候，物理IO能力又有限或写请求过大，容易造成mysqld服务出现hang，这个在官方网站的BUG列表中可查证。
在上述公布的参数基础之上，特意对key_buffer_size的值进行了调整，之前没注意到，以为此值调整为128M了，那么是如何发现的呢？因为my.cnf中是写有key_buffer_size = 128M ，但是忘记进行SET GLOBAL 操作导致的，也即正在跑的mysqld服务的 key_buffer_size参数的值依然是：16M，但是通过观察到进行ALTER TABLE 时候，是先写索引数据到磁盘上，而不是元数据：
[root@**-**-*****-db1a *****************]# ls -lh
………………………………………………………………………………………
-rw-rw—- 1 mysql mysql 8.5K Jun 10 14:34 #sql-3e0b_5d4.frm
-rw-rw—- 1 mysql mysql 1.0K Jun 10 14:34 #sql-3e0b_5d4.MYD
-rw-rw—- 1 mysql mysql 1.0K Jun 10 14:34 #sql-3e0b_5d4.MYI
………………………………………………………………………………………
[root@**-**-*****-db1a *****************]# ls -lh
………………………………………………………………………………………
-rw-rw—- 1 mysql mysql 8.5K Jun 10 14:34 #sql-3e0b_5d4.frm
-rw-rw—- 1 mysql mysql 1.0K Jun 10 14:34 #sql-3e0b_5d4.MYD
-rw-rw—- 1 mysql mysql 16M Jun 10 14:35 #sql-3e0b_5d4.MYI
………………………………………………………………………………………
[root@**-**-*****-db1a *****************]# ls -lh
………………………………………………………………………………………
-rw-rw—- 1 mysql mysql 8.5K Jun 10 14:34 #sql-3e0b_5d4.frm
-rw-rw—- 1 mysql mysql 1.0K Jun 10 14:34 #sql-3e0b_5d4.MYD
-rw-rw—- 1 mysql mysql 24M Jun 10 14:35 #sql-3e0b_5d4.MYI
………………………………………………………………………………………
按所配置参数值和MyISAM引擎实现机制，肯定是先写元数据，后写索引数据到磁盘上，为此特意执行：SHOW VARIABLES LIKE ‘key_buffer_size’;发现不对，为此修改之后，我们可以发现写的情况：
[root@**-**-*****-db1a *****************]# ls -lh
………………………………………………………………………………………
-rw-rw—- 1 mysql mysql 8.5K Jun 7 16:19 #sql-3e0b_593.frm
-rw-rw—- 1 mysql mysql 8.0M Jun 7 16:19 #sql-3e0b_593.MYD
-rw-rw—- 1 mysql mysql 1.0K Jun 7 16:19 #sql-3e0b_593.MYI
………………………………………………………………………………………..
[root@**-**-*****-db1a *****************]# ls -lh
………………………………………………………………………………….
-rw-rw—- 1 mysql mysql 8.5K Jun 7 16:19 #sql-3e0b_593.frm
-rw-rw—- 1 mysql mysql 16M Jun 7 16:20 #sql-3e0b_593.MYD
-rw-rw—- 1 mysql mysql 1.0K Jun 7 16:19 #sql-3e0b_593.MYI
………………………………………………………………………………………..
[root@**-**-*****-db1a *****************]# ls -lh
……………………………………………………………………………..
-rw-rw—- 1 mysql mysql 8.5K Jun 7 16:19 #sql-3e0b_593.frm
-rw-rw—- 1 mysql mysql 72M Jun 7 16:21 #sql-3e0b_593.MYD
-rw-rw—- 1 mysql mysql 1.0K Jun 7 16:19 #sql-3e0b_593.MYI
………………………………………………………………………………………..
修改之后改善：
u 执行ALTER TABLE tablename ENGINE=MyISAM;没有再出现过mysqld服务hang；
u 内存
表参数值和key_buffer_size大小相同情况下，不同大小的转储表，不会出现先向磁盘写索引文件数据，而是先写元数据，或者元数据和索引文件数据，同时写入磁盘；
u 表存储引擎转换的变更SQL语句执行速度更快了；
u 服务器的LOAD，IO WAIT 明显更小；
n 总结
服务器端内存配置参数：key_buffer_size=128M、tmp_table_size=128M、max_heap_table_size=1G，这三个参数的作用，以及为何这么设置？
文章开篇的环境信息介绍中，说明了我们表数据容量大小，为此我们设置参数：key_buffer_size和tmp_table_size足够分别缓存索引数据和元数据之用，待全部转换完，再顺序向磁盘写回数据，从而节约物力IO，提高存储引擎转换的速度，至于max_heap_table_size设置为1G是其他用处，跟此事情无关。
“常在河边走，哪有不湿靴的？”。此话道出我们这些技术
人员，尤其运维
人员，总是会犯错的，关键是如何去排查问题产生的根源，这就需要借助经验分析，并且要再换一个角度，抛弃一些经验再思考、分析，并且用自己所掌握的知识，加外部的知识（注：咨询朋友、网络
等），佐证自己的分析和解决办法，相信总是会找到答案的。
原文链接地址：http://www.mysqlops.com/2011/06/ ... yisam-optimize.html