年前抽空到兄弟公司支援了一下oracle迁移mysql的测试，本想把mysql调优到接近oracle的性能即可，但经过 @何_登成 @淘宝丁奇 @淘宝褚霸 @淘伯松 诸位大牛的指导和帮助（排名不分先后，仅按第一次为此case而骚扰的时间排序），不断修正方案，最终获得了比oracl
年前抽空到兄弟公司支援了一下oracle迁移mysql的测试，本想把mysql调优到接近oracle的性能即可，但经过 @何_登成 @淘宝丁奇 @淘宝褚霸 @淘伯松 诸位大牛的指导和帮助（排名不分先后，仅按第一次为此case而骚扰的时间排序），不断修正方案，最终获得了比oracle更好的性能，虽然是个特殊场景，但是我觉得意义是很广泛的，值得参考，遂记录于此。
所有涉及表结构和具体业务模型的部分全部略去，也请勿咨询，不能透露，敬请谅解。
一、测试模型：
包含12张业务表，每个事务包含12个sql，每个sql向一张表做insert，做完12个sql即完成一个事务。
用一个c api编写的程序连接mysql，不断执行如下操作
开始事务：start transaction;
每张表插入一行：insert into xxx values (val1,val2,…); #一共12次
提交事务：commit;
通过一个shell脚本来启动32个测试程序并发测试
二、测试环境：
1. 机型：r510
cpu：intel(r) xeon(r) cpu e5645 @ 2.40ghz 双路24线程
内存：6 * 8g 48g
存储：fusionio 320g mlc
r910
cpu：intel(r) xeon(r) cpu e7530 @ 1.87ghz 四路48线程
内存：32* 4g 128g
存储：fusionio 640g mlc
单实例启动数据库：/boot/grub/menu.lst修改kernel启动参数增加numa=off
多实例启动数据库：numactl –cpunodebind=$bind_no –localalloc $mysqld
rhel 5.4 with 2.6.18内置内核
rhel 6.1 with 2.6.32淘宝版内核
fs.aio-max-nr = 1048576 #调整系统允许的最大异步io队列长度
vm.nr_hugepages = 18000 #大页页数
vm.hugetlb_shm_group = 601 #允许使用大页的用户id，即mysql用户
vm.swappiness = 0 #不倾向使用swap
启动配置：
/etc/modprobe.d/iomemory-vsl.conf
options iomemory-vsl use_workqueue=0 # 忽略linux io调度
options iomemory-vsl disable-msi=0 # 开启msi中断
options iomemory-vsl use_large_pcie_rx_buffer=1 # 打开pcie缓冲
options iomemory-vsl preallocate_memory=sn号 # 预分配管理内存
格式化配置：
fio-format -b 4k /dev/fct0 # 格式化设备为4k匹配nand芯片页大小
mkfs.xfs -f -i attr=2 -l lazy-count=1,sectsize=4096 -b size=4096 -d sectsize=4096 -l data /dev/fioa # 调整xfs与fusionio 4k页匹配，比较激进，需要更多稳定性测试认为这组参数充分安全
mount配置：
/dev/fioa on /data type xfs (rw,noatime,nodiratime,noikeep,nobarrier,allocsize=100m,attr2,largeio,inode64,swalloc) # fusionio的逻辑block是100m，所以设为100m的预扩展
percona 5.1.60-13.1 原版
percona 5.1.60-13.1 修改版
* 允许自定义innodb aio队列申请长度 (5.5_change_aio_io_limit.patch)
percona 5.5.19-24.0 原版
* 允许innodb_flush_neighbor_pages=2来合并真正相邻的脏页合并
* group commit
percona 5.5.18-23.0 修改版
* 允许自定义innodb aio队列申请长度 (5.5_change_aio_io_limit.patch)
* 允许预先扩展数据文件 (5.5_innodb_extent_tablespace.patch，@淘宝丁奇 贡献)
* group cimmit
innodb_buffer_pool_size=20g
sync_binlog=1
innodb_flush_log_at_trx_commit=1
测试并发：32
5. 修改补丁#cat 5.5_change_aio_io_limit.patch
--- percona-server-5.5.18-23.0/storage/innobase/handler/ha_innodb.cc 2011-12-20 06:38:58.000000000 +0800 +++ percona-server-5.5.18-23.0-debug/storage/innobase/handler/ha_innodb.cc 2012-01-17 10:13:41.000000000 +0800 @@ -146,6 +146,7 @@ static ulong innobase_commit_concurrency = 0; static ulong innobase_read_io_threads; static ulong innobase_write_io_threads; +static ulong innobase_aio_pending_ios_per_thread; // change aio io_limit by p.linux static long innobase_buffer_pool_instances = 1; static ulong innobase_page_size; @@ -2870,6 +2871,7 @@ srv_n_file_io_threads = (ulint) innobase_file_io_threads; srv_n_read_io_threads = (ulint) innobase_read_io_threads; srv_n_write_io_threads = (ulint) innobase_write_io_threads; + srv_n_aio_pending_ios_per_thread = (ulint) innobase_aio_pending_ios_per_thread; srv_read_ahead &= 3; srv_adaptive_flushing_method %= 3; @@ -12282,6 +12284,11 @@ "number of background write i/o threads in innodb.", null, null, 4, 1, 64, 0); +static mysql_sysvar_ulong(aio_pending_ios_per_thread, innobase_aio_pending_ios_per_thread, + plugin_var_rqcmdarg | plugin_var_readonly, + "number of aio pending ios per-thread in innodb.", + null, null, 4, 32, 4096, 0); + static mysql_sysvar_long(force_recovery, innobase_force_recovery, plugin_var_rqcmdarg | plugin_var_readonly, "helps to save your data in case the disk image of the database becomes corrupt.", --- percona-server-5.5.18-23.0/storage/innobase/srv/srv0srv.c 2011-12-20 06:38:57.000000000 +0800 +++ percona-server-5.5.18-23.0-debug/storage/innobase/srv/srv0srv.c 2012-01-17 10:23:35.000000000 +0800 @@ -242,6 +242,7 @@ univ_intern ulint srv_n_file_io_threads = ulint_max; univ_intern ulint srv_n_read_io_threads = ulint_max; univ_intern ulint srv_n_write_io_threads = ulint_max; +univ_intern ulint srv_n_aio_pending_ios_per_thread = ulint_max; // change aio io_limit by p.linux /* switch to enable random read ahead. */ univ_intern my_bool srv_random_read_ahead = false; --- percona-server-5.5.18-23.0/storage/innobase/srv/srv0start.c 2011-12-20 06:38:57.000000000 +0800 +++ percona-server-5.5.18-23.0-debug/storage/innobase/srv/srv0start.c 2012-01-17 10:25:12.000000000 +0800 @@ -1475,14 +1475,16 @@ ut_a(srv_n_file_io_threads
#cat 5.5_innodb_extent_tablespace.patch
--- percona-server-5.5.18-23.0/sql/sql_yacc.yy 2011-12-20 06:38:58.000000000 +0800
+++ percona-server-5.5.18-23.0-debug/sql/sql_yacc.yy 2012-01-17 14:45:47.000000000 +0800
@@ -3878,6 +3878,14 @@
{
lex->alter_tablespace_info->ts_alter_tablespace_type= alter_tablespace_drop_file;
}
+ /* innodb_extent_tablespace by p.linux */
+ | tablespace_name
+ set
+ opt_ts_extent_size
+ {
+ lex->alter_tablespace_info->ts_alter_tablespace_type= alter_tablespace_alter_file;
+ }
+ /* end */
;
logfile_group_info:
--- percona-server-5.5.18-23.0/sql/handler.h 2011-12-20 06:38:58.000000000 +0800
+++ percona-server-5.5.18-23.0-debug/sql/handler.h 2012-01-17 14:29:17.000000000 +0800
@@ -501,7 +501,8 @@
{
ts_alter_tablespace_type_not_defined = -1,
alter_tablespace_add_file = 1,
- alter_tablespace_drop_file = 2
+ alter_tablespace_drop_file = 2,
+ alter_tablespace_alter_file = 3 // innodb_extent_tablespace by p.linux
};
enum tablespace_access_mode
--- percona-server-5.5.18-23.0/storage/innobase/fil/fil0fil.c 2011-12-20 06:38:57.000000000 +0800
+++ percona-server-5.5.18-23.0-debug/storage/innobase/fil/fil0fil.c 2012-01-17 14:31:40.000000000 +0800
@@ -368,7 +368,8 @@
checks if a single-table tablespace for a given table name exists in the
tablespace memory cache.
@return space id, ulint_undefined if not found */
-static
+//static
+univ_intern // innodb_extent_tablespace by p.linux
ulint
fil_get_space_id_for_table(
/*=======================*/
@@ -4676,7 +4677,8 @@
checks if a single-table tablespace for a given table name exists in the
tablespace memory cache.
@return space id, ulint_undefined if not found */
-static
+//static
+univ_intern // innodb_extent_tablespace by p.linux
ulint
fil_get_space_id_for_table(
/*=======================*/
--- percona-server-5.5.18-23.0/storage/innobase/handler/ha_innodb.cc 2011-12-20 06:38:58.000000000 +0800
+++ percona-server-5.5.18-23.0-debug/storage/innobase/handler/ha_innodb.cc 2012-01-17 14:37:49.000000000 +0800
@@ -433,6 +434,12 @@
/*=======================*/
uint flags);
+/****************************************************************//**
+alter tablespace supported in an innodb table. allow setting extent space. */
+int innobase_alter_tablespace(handlerton *hton,
+ thd* thd, st_alter_tablespace *alter_info);
+/* innodb_extent_tablespace by p.linux */
+
static const char innobase_hton_name[]= "innodb";
/*************************************************************//**
@@ -2489,6 +2496,7 @@
innobase_hton->flags=hton_no_flags;
innobase_hton->release_temporary_latches=innobase_release_temporary_latches;
innobase_hton->alter_table_flags = innobase_alter_table_flags;
+ innobase_hton->alter_tablespace= innobase_alter_tablespace; // innodb_extent_tablespace by p.linux
ut_a(data_mysql_true_varchar == (ulint)mysql_type_varchar);
@@ -3146,6 +3155,33 @@
| ha_inplace_add_pk_index_no_read_write);
}
+/****************************************************************//**
+alter tablespace supported in an innodb table. allow setting extent space. */
+int innobase_alter_tablespace(handlerton *hton,
+ thd* thd, st_alter_tablespace *alter_info)
+{
+ if (alter_info->ts_alter_tablespace_type != alter_tablespace_alter_file)
+ {
+ return ha_admin_not_implemented;
+ }
+
+ ulint table_space= fil_get_space_id_for_table(alter_info->tablespace_name);
+
+ if (table_space == ulint_undefined)
+ {
+ my_error(er_wrong_table_name, myf(0), alter_info->tablespace_name);
+ return ee_filenotfound;
+ }
+
+ ulint extent_size= alter_info->extent_size;
+
+ ulint actual_size=0;
+ fil_extend_space_to_desired_size(&actual_size, table_space, extent_size);
+
+ return 0;
+}
+/* innodb_extent_tablespace by p.linux */
+
/*****************************************************************//**
commits a transaction in an innodb database. */
static
--- percona-server-5.5.18-23.0/storage/innobase/include/fil0fil.h 2011-12-20 06:38:57.000000000 +0800
+++ percona-server-5.5.18-23.0-debug/storage/innobase/include/fil0fil.h 2012-01-17 14:39:20.000000000 +0800
@@ -744,6 +744,18 @@
/*============================*/
ulint id); /*!< in: space id */
+/*******************************************************************//**
+checks if a single-table tablespace for a given table name exists in the
+tablespace memory cache.
+@return space id, ulint_undefined if not found */
+univ_intern
+ulint
+fil_get_space_id_for_table(
+/*=======================*/
+ const char* name); /*!< in: table name in the standard
+ 'databasename/tablename' format */
+/* innodb_extent_tablespace by p.linux */
+
/*************************************************************************
return local hash table informations. */
三、测试结果：
1. r910 oracle单实例测试人：童家旺，支付宝
tps：稳定值2000,峰值2600 （我没参与测试，也没有报告，无法确定详情）
我的补充：oracle已经是调优的过的，请相信我们的oracle dba不是吃素的。我把听oracle dba描述的只言碎语随便写下，oracle跑到后面tps也是有所下降，不是能一直100%稳定，最后cpu已经吃尽了，所以基本上再怎么优化提升的幅度会比较小。
测试人：帝俊，支付宝
tps：峰值1500，无法稳定（具体不祥）
测试人描述：
目前的测试数据显示，由于mysql在checkpoint上处理跟不上，不足以持续支持1.5k/s的事务数，10mb/s的redo量下的交易创建。该负载下，fio的写出速度为160～190mb/s，写iops为2～2.3k，测试fio的写吞吐量可以到600mb/s，写iops有8k+，需要进一步研究如何进一步提升系统的吞吐量。
测试人：彭立勋，b2b
tps：峰值500*4（无法稳定），谷值100，均值450＊4
重要配置：
innodb_page_size=4k # 修改数据页大小与fusionio匹配
innodb_log_block_size=4k # 修改日志页大小于fusionio匹配
innodb_log_file_size=1g
innodb_log_files_in_group=3
innodb_buffer_pool_size=20g
innodb_max_dirty_pages_pct=75
innodb_flush_method=all_o_direct # 修改文件写入方式全部为o_direct
innodb_read_io_threads=2
innodb_write_io_threads=10
innodb_io_capacity=20000
innodb_extra_rsegments=16
innodb_use_purge_thread=4
innodb_adaptive_flushing_method=3 # 采用keep_average刷新方式
innodb_flush_neighbor_pages=0 # 不为了凑顺序io刷相邻未修改的页
测试人描述：
每颗物理cpu绑定一个mysql实例，四个实例同时接受测试。可以看到在测试过程中，iops抖动很大，在4k～17k之间抖动，可以判定，是checkpoint机制不完善导致刷新间歇性繁忙，在io闲置的时候不能充分发挥性能。但多实例可以提升整体tps接近oracle的均值，说明mysql内部可能某些常量设置不合理，或者锁定力度太粗导致单实例不能充分发挥单机性能。
测试人：彭立勋，b2b
tps：峰值1200*4，谷值0，均值950*4
重要配置：（在测试3的基础上）
innodb_aio_pending_ios_per_thread=1024
测试人描述：
经过对测试3的分析，可以发现，innodb已经标记了很多page到flush_list，但是并没有被即时的回写，可以在innodb_buffer_pool_pages系统表中发现很页flush_type=2，即在flush_list中。
经过review代码，发现innodb申请的aio队列的长度只有256，由常量os_aio_n_pending_ios_per_thread（os0file.h）定义。将此常量修改为innodb的参数后，重新测试，可以使fusionio的iops达到7k～18k，io利用率得以提升，整体性能已经超越oracle，但存在严重的低谷，大约每10s一次。
测试人：彭立勋,b2b
tps：峰值2800，谷值2300，均值2500
重要配置：（在测试3的基础上）
innodb_aio_pending_ios_per_thread=512
alter?tablespace?`trade/xxx`?set?extent_size=5000000; # 预先扩展数据文件
测试人描述：
根据测试4的结果进行分析，需要解决的主要问题就是抖动，抖动可能是两个原因导致的，一个是checkpoint机制不完善，一个是数据文件扩展。checkpoint机制不完善这个暂时无法改进，只能先解决数据文件扩展上的问题，采用淘宝丁奇的方法，对mysql增加预先扩展文件的功能，在测试前先将文件扩展至测试写满需要的大小，使测试过程中无需扩展文件。
实例测试中发现非常有效，抖动范围在2300～2800之间，可以接受。但是buffer pool一旦脏页写满，为了控制脏页量innodb就会加大刷新量，影响到tps。实际上在脏页未满的时候，iops就没有用完，但是innodb计算刷新量并没有考虑操作系统反馈的影响信息，只是根据自己的redo产生量计算。
同时观察cpu还发现，2.6.18内核会将所有软中断发送到core0上处理，这可能也是瓶颈之一。（当时忘记拷贝状态，这是后来确认内核问题看得，可以看这篇文章，一样的，cpu软中断实践）
03:05:17 pm cpu %user %nice %sys %iowait %irq %soft %steal %idle intr/s
03:05:18 pm all 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1014.00
03:05:18 pm 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1000.00
测试人：彭立勋，b2b
tps：峰值3100，谷值2400，均值2700
重要配置：（在测试3的基础上）
替换内核版本为2.6.32淘宝版，使用io中断负载均衡。
innodb_adaptive_flushing_method = 2
innodb_flush_neighbor_pages = cont
测试人描述：
采用淘宝版内核后，可以发现每个cpu都被用的比较满：(部分)
06:27:26?pm??cpu????%usr???%nice????%sys?%iowait????%irq???%soft??%steal??%guest???%idle
06:27:27?pm??all???69.80????0.00???18.68????0.51????0.00????0.17????0.00????0.00???10.84
06:27:27?pm????0???74.75????0.00???17.17????0.00????0.00????0.00????0.00????0.00????8.08
06:27:27?pm????1???73.96????0.00???16.67????1.04????0.00????0.00????0.00????0.00????8.33
06:27:27?pm????2???73.20????0.00???17.53????1.03????0.00????0.00????0.00????0.00????8.25
06:27:27?pm????3???71.72????0.00???19.19????1.01????0.00????0.00????0.00????0.00????8.08
06:27:27?pm????4???71.43????0.00???18.37????1.02????0.00????0.00????0.00????0.00????9.18
06:27:27?pm????5???70.71????0.00???19.19????1.01????0.00????0.00????0.00????0.00????9.09
这是个好现象，说明cpu被充分用起来了，在脏页未满之前，tps可以比较稳定的维持在3000以上。但还是老问题，脏页一满，速度就下降，到测试结束时下降为2400。
四、测试结论：
mysql的调优与操作系统结合非常紧密，需要整体联动才能获得好的效果，innodb琐粒度较粗的缺陷，在代码实现简单的情况下，实际上对并发的影响不是很明显。
目前mysql对高速硬件的利用主要缺陷是，不少常量写死，checkpoint机制不完善，checkpoint刷新脏页–>innodb aio队列–>操作系统io队列–>存储设备，中间任何一环存在问题，都可能导致性能下降。
innodb aio队列可以通过补丁开放参数设置，这个瓶颈已经消除。
操作系统io队列可以通过淘宝的内核补丁将中断分散到每个核上处理来解决。
目前存在最大的问题就是checkpoint刷新脏页的机制，仅仅依赖redo产生的速度，其实硬件io还有很多余量，但innodb并不知道。
如果能限定一种机型，限定一种操作系统，在mysql内获取操作系统报告的硬件状态，自适应的决策自己的行为，这样可以充分利用系统资源，例如io util%并不高的时候，即使脏页还没到阈值，也可以加大刷新量，充分利用io，这样可能系统根本就达不到脏页阈值，可以一直保持搞tps，至少可以延缓tps下降的趋势。
抖动问题则是oracle和mysql都存在的问题，扩展数据文件的瞬间必然导致tps下降，淘宝丁奇的方法可以完美解决，oracle也是类似的方法通过预先分配表空间文件解决。
五、测试缺陷：
测试case不全，没有在r910上测试5.5（虽然已经超了oracle，但r910上应该还能猛一点），没有测试5.5多实例下可以获得何种性能，没有测试5.1在2.6.32内核下的表现，没有测试不同的页大小对innodb的影响。
没有稳定性测试，原版+多实例 属于稳定方案，其他改动是否100%不影响稳定，尚需测试。
在r910上的测试没有监控系统，也就没有图，坑爹了。
六、后续action
在innodb控制刷赃页量的地方加入对系统diskstat的监控，当系统io util%
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！