一个InnoDB性能超过Oracle的调优Case

年前抽空到兄弟公司支援了一下Oracle迁移MySQL的测试，本想把MySQL调优到接近Oracle的性能即可，但经过 @何_登成 @淘宝丁奇 @淘宝褚霸 @淘伯松 诸位大牛的指导和帮助（排名不分先后，仅按第一次为此CASE而骚扰的时间排序），不断修正方案，最终获得了比Oracle更好的性能，虽然是个特殊场景，但是我觉得意义是很广泛的，值得参考，遂记录于此。
所有涉及表结构和具体业务模型的部分全部略去，也请勿咨询，不能透露，敬请谅解。

目录 (Contents) [隐藏 (Hide)]

• 1 一、测试模型：
• 2 二、测试环境：
  • 2.1 1. 机型：
  • 2.2 2. Linux配置：
  • 2.3 3. FusionIO配置：
  • 2.4 4. MySQL版本和通用配置：
  • 2.5 5. 修改补丁
• 3 三、测试结果：4 四、测试结论：
  • 3.1 1. R910 Oracle单实例
  • 3.2 2. R910 MySQL单实例 Percona 5.1.59 原版
  • 3.3 3. R910 MySQL多实例 Percona 5.1.60-13.1原版
  • 3.4 4. R910 MySQL多实例 Percona 5.1.60-13.1 修改版
  • 3.5 5.R510 MySQL单实例 Percona 5.5.18-23.0 修改版
  • 3.6 6. R510 MySQL单实例 Percona 5.5.19-24.0 原版
• 5 五、测试缺陷：
• 6 六、后续Action
• 7 七、随意补充

一、测试模型：

包含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

2. Linux配置：

单实例启动数据库：/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

 

3. FusionIO配置：

启动配置：
/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的预扩展

4. MySQL版本和通用配置：

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.", +

转载于:https://www.cnblogs.com/conanwang/p/5942974.html