一个InnoDB性能超过Oracle的调优Case-mysql教程-PHP中文网

年前抽空到兄弟公司支援了一下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

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

<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>handler<span style="color: #000040;">/</span>ha_innodb.<span style="color: #007788;">cc</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">58.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>handler<span style="color: #000040;">/</span>ha_innodb.<span style="color: #007788;">cc</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">10</span><span style="color: #008080;">:</span><span style="color: #0000dd;">13</span><span style="color: #008080;">:</span><span style="color: #800080;">41.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">146</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">146</span>,<span style="color: #0000dd;">7</span> @@
 <span style="color: #0000ff;">static</span> ulong innobase_commit_concurrency <span style="color: #000080;">=</span> <span style="color: #0000dd;">0</span><span style="color: #008080;">;</span>
 <span style="color: #0000ff;">static</span> ulong innobase_read_io_threads<span style="color: #008080;">;</span>
 <span style="color: #0000ff;">static</span> ulong innobase_write_io_threads<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span><span style="color: #0000ff;">static</span> ulong innobase_aio_pending_ios_per_thread<span style="color: #008080;">;</span> <span style="color: #666666;">// Change AIO io_limit By P.Linux</span>
 <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">long</span> innobase_buffer_pool_instances <span style="color: #000080;">=</span> <span style="color: #0000dd;">1</span><span style="color: #008080;">;</span>
 
 <span style="color: #0000ff;">static</span> ulong innobase_page_size<span style="color: #008080;">;</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">2870</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">2871</span>,<span style="color: #0000dd;">7</span> @@
 	srv_n_file_io_threads <span style="color: #000080;">=</span> <span style="color: #008000;">&#40;</span>ulint<span style="color: #008000;">&#41;</span> innobase_file_io_threads<span style="color: #008080;">;</span>
 	srv_n_read_io_threads <span style="color: #000080;">=</span> <span style="color: #008000;">&#40;</span>ulint<span style="color: #008000;">&#41;</span> innobase_read_io_threads<span style="color: #008080;">;</span>
 	srv_n_write_io_threads <span style="color: #000080;">=</span> <span style="color: #008000;">&#40;</span>ulint<span style="color: #008000;">&#41;</span> innobase_write_io_threads<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>	srv_n_aio_pending_ios_per_thread <span style="color: #000080;">=</span> <span style="color: #008000;">&#40;</span>ulint<span style="color: #008000;">&#41;</span> innobase_aio_pending_ios_per_thread<span style="color: #008080;">;</span>
 
 	srv_read_ahead <span style="color: #000040;">&</span>amp<span style="color: #008080;">;</span><span style="color: #000080;">=</span> <span style="color: #0000dd;">3</span><span style="color: #008080;">;</span>
 	srv_adaptive_flushing_method <span style="color: #000040;">%</span><span style="color: #000080;">=</span> <span style="color: #0000dd;">3</span><span style="color: #008080;">;</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">12282</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">12284</span>,<span style="color: #0000dd;">11</span> @@
   <span style="color: #FF0000;">"Number of background write I/O threads in InnoDB."</span>,
   <span style="color: #0000ff;">NULL</span>, <span style="color: #0000ff;">NULL</span>, <span style="color: #0000dd;">4</span>, <span style="color: #0000dd;">1</span>, <span style="color: #0000dd;">64</span>, <span style="color: #0000dd;">0</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
 
<span style="color: #000040;">+</span><span style="color: #0000ff;">static</span> MYSQL_SYSVAR_ULONG<span style="color: #008000;">&#40;</span>aio_pending_ios_per_thread, innobase_aio_pending_ios_per_thread,
<span style="color: #000040;">+</span>  PLUGIN_VAR_RQCMDARG <span style="color: #000040;">|</span> PLUGIN_VAR_READONLY,
<span style="color: #000040;">+</span>  <span style="color: #FF0000;">"Number of AIO pending IOS per-thread in InnoDB."</span>,
<span style="color: #000040;">+</span>  <span style="color: #0000ff;">NULL</span>, <span style="color: #0000ff;">NULL</span>, <span style="color: #0000dd;">4</span>, <span style="color: #0000dd;">32</span>, <span style="color: #0000dd;">4096</span>, <span style="color: #0000dd;">0</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>
 <span style="color: #0000ff;">static</span> MYSQL_SYSVAR_LONG<span style="color: #008000;">&#40;</span>force_recovery, innobase_force_recovery,
   PLUGIN_VAR_RQCMDARG <span style="color: #000040;">|</span> PLUGIN_VAR_READONLY,
   <span style="color: #FF0000;">"Helps to save your data in case the disk image of the database becomes corrupt."</span>,
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>srv<span style="color: #000040;">/</span>srv0srv.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">57.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>srv<span style="color: #000040;">/</span>srv0srv.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">10</span><span style="color: #008080;">:</span><span style="color: #0000dd;">23</span><span style="color: #008080;">:</span><span style="color: #800080;">35.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">242</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">242</span>,<span style="color: #0000dd;">7</span> @@
 UNIV_INTERN ulint	srv_n_file_io_threads	<span style="color: #000080;">=</span> ULINT_MAX<span style="color: #008080;">;</span>
 UNIV_INTERN ulint	srv_n_read_io_threads	<span style="color: #000080;">=</span> ULINT_MAX<span style="color: #008080;">;</span>
 UNIV_INTERN ulint	srv_n_write_io_threads	<span style="color: #000080;">=</span> ULINT_MAX<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>UNIV_INTERN ulint   srv_n_aio_pending_ios_per_thread <span style="color: #000080;">=</span> ULINT_MAX<span style="color: #008080;">;</span> <span style="color: #666666;">// Change AIO io_limit By P.Linux</span>
 
 <span style="color: #ff0000; font-style: italic;">/* Switch to enable random read ahead. */</span>
 UNIV_INTERN my_bool	srv_random_read_ahead	<span style="color: #000080;">=</span> FALSE<span style="color: #008080;">;</span>
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>srv<span style="color: #000040;">/</span>srv0start.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">57.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>srv<span style="color: #000040;">/</span>srv0start.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">10</span><span style="color: #008080;">:</span><span style="color: #0000dd;">25</span><span style="color: #008080;">:</span><span style="color: #800080;">12.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">1475</span>,<span style="color: #0000dd;">14</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">1475</span>,<span style="color: #0000dd;">16</span> @@
 
 	ut_a<span style="color: #008000;">&#40;</span>srv_n_file_io_threads

登录后复制

#cat 5.5_innodb_extent_tablespace.patch

<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>sql<span style="color: #000040;">/</span>sql_yacc.<span style="color: #007788;">yy</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">58.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>sql<span style="color: #000040;">/</span>sql_yacc.<span style="color: #007788;">yy</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">14</span><span style="color: #008080;">:</span><span style="color: #0000dd;">45</span><span style="color: #008080;">:</span><span style="color: #800080;">47.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">3878</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">3878</span>,<span style="color: #0000dd;">14</span> @@
           <span style="color: #008000;">&#123;</span> 
             Lex<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>alter_tablespace_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>ts_alter_tablespace_type<span style="color: #000080;">=</span> ALTER_TABLESPACE_DROP_FILE<span style="color: #008080;">;</span> 
           <span style="color: #008000;">&#125;</span>
<span style="color: #000040;">+</span>        <span style="color: #ff0000; font-style: italic;">/* innodb_extent_tablespace By P.Linux */</span>
<span style="color: #000040;">+</span>        <span style="color: #000040;">|</span> tablespace_name
<span style="color: #000040;">+</span>          SET
<span style="color: #000040;">+</span>          opt_ts_extent_size
<span style="color: #000040;">+</span>          <span style="color: #008000;">&#123;</span>
<span style="color: #000040;">+</span>            Lex<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>alter_tablespace_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>ts_alter_tablespace_type<span style="color: #000080;">=</span> ALTER_TABLESPACE_ALTER_FILE<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>          <span style="color: #008000;">&#125;</span>
<span style="color: #000040;">+</span>        <span style="color: #ff0000; font-style: italic;">/* End */</span>
         <span style="color: #008080;">;</span>
 
 logfile_group_info<span style="color: #008080;">:</span>
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>sql<span style="color: #000040;">/</span>handler.<span style="color: #007788;">h</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">58.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>sql<span style="color: #000040;">/</span>handler.<span style="color: #007788;">h</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">14</span><span style="color: #008080;">:</span><span style="color: #0000dd;">29</span><span style="color: #008080;">:</span><span style="color: #800080;">17.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">501</span>,<span style="color: #0000dd;">7</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">501</span>,<span style="color: #0000dd;">8</span> @@
 <span style="color: #008000;">&#123;</span>
   TS_ALTER_TABLESPACE_TYPE_NOT_DEFINED <span style="color: #000080;">=</span> <span style="color: #000040;">-</span><span style="color: #0000dd;">1</span>,
   ALTER_TABLESPACE_ADD_FILE <span style="color: #000080;">=</span> <span style="color: #0000dd;">1</span>,
<span style="color: #000040;">-</span>  ALTER_TABLESPACE_DROP_FILE <span style="color: #000080;">=</span> <span style="color: #0000dd;">2</span>
<span style="color: #000040;">+</span>  ALTER_TABLESPACE_DROP_FILE <span style="color: #000080;">=</span> <span style="color: #0000dd;">2</span>,
<span style="color: #000040;">+</span>  ALTER_TABLESPACE_ALTER_FILE <span style="color: #000080;">=</span> <span style="color: #0000dd;">3</span> <span style="color: #666666;">// innodb_extent_tablespace By P.Linux</span>
 <span style="color: #008000;">&#125;</span><span style="color: #008080;">;</span>
 
 <span style="color: #0000ff;">enum</span> tablespace_access_mode
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>fil<span style="color: #000040;">/</span>fil0fil.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">57.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>fil<span style="color: #000040;">/</span>fil0fil.<span style="color: #007788;">c</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">14</span><span style="color: #008080;">:</span><span style="color: #0000dd;">31</span><span style="color: #008080;">:</span><span style="color: #800080;">40.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">368</span>,<span style="color: #0000dd;">7</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">368</span>,<span style="color: #0000dd;">8</span> @@
 Checks <span style="color: #0000ff;">if</span> a single<span style="color: #000040;">-</span>table tablespace <span style="color: #0000ff;">for</span> a given table name exists in the
 tablespace memory cache.
 @<span style="color: #0000ff;">return</span>	space id, ULINT_UNDEFINED <span style="color: #0000ff;">if</span> not found <span style="color: #000040;">*/</span>
<span style="color: #000040;">-</span><span style="color: #0000ff;">static</span>
<span style="color: #000040;">+</span><span style="color: #666666;">//static</span>
<span style="color: #000040;">+</span>UNIV_INTERN <span style="color: #666666;">// innodb_extent_tablespace By P.Linux</span>
 ulint
 fil_get_space_id_for_table<span style="color: #008000;">&#40;</span>
 <span style="color: #ff0000; font-style: italic;">/*=======================*/</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">4676</span>,<span style="color: #0000dd;">7</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">4677</span>,<span style="color: #0000dd;">8</span> @@
 Checks <span style="color: #0000ff;">if</span> a single<span style="color: #000040;">-</span>table tablespace <span style="color: #0000ff;">for</span> a given table name exists in the
 tablespace memory cache.
 @<span style="color: #0000ff;">return</span>	space id, ULINT_UNDEFINED <span style="color: #0000ff;">if</span> not found <span style="color: #000040;">*/</span>
<span style="color: #000040;">-</span><span style="color: #0000ff;">static</span>
<span style="color: #000040;">+</span><span style="color: #666666;">//static</span>
<span style="color: #000040;">+</span>UNIV_INTERN <span style="color: #666666;">// innodb_extent_tablespace By P.Linux</span>
 ulint
 fil_get_space_id_for_table<span style="color: #008000;">&#40;</span>
 <span style="color: #ff0000; font-style: italic;">/*=======================*/</span>
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>handler<span style="color: #000040;">/</span>ha_innodb.<span style="color: #007788;">cc</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">58.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>handler<span style="color: #000040;">/</span>ha_innodb.<span style="color: #007788;">cc</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">14</span><span style="color: #008080;">:</span><span style="color: #0000dd;">37</span><span style="color: #008080;">:</span><span style="color: #800080;">49.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">433</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">434</span>,<span style="color: #0000dd;">12</span> @@
 <span style="color: #ff0000; font-style: italic;">/*=======================*/</span>
 	uint	flags<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
 
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/****************************************************************/</span><span style="color: #ff0000; font-style: italic;">/**
+Alter tablespace supported in an InnoDB table. Allow setting extent space. */</span>
<span style="color: #000040;">+</span><span style="color: #0000ff;">int</span> innobase_alter_tablespace<span style="color: #008000;">&#40;</span>handlerton <span style="color: #000040;">*</span>hton,
<span style="color: #000040;">+</span>                                THD<span style="color: #000040;">*</span> thd, st_alter_tablespace <span style="color: #000040;">*</span>alter_info<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/* innodb_extent_tablespace By P.Linux */</span>
<span style="color: #000040;">+</span>
 <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">const</span> <span style="color: #0000ff;">char</span> innobase_hton_name<span style="color: #008000;">&#91;</span><span style="color: #008000;">&#93;</span><span style="color: #000080;">=</span> <span style="color: #FF0000;">"InnoDB"</span><span style="color: #008080;">;</span>
 
 <span style="color: #ff0000; font-style: italic;">/*************************************************************/</span><span style="color: #ff0000; font-style: italic;">/**
@@ -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);
 }
 
+/****************************************************************/</span><span style="color: #ff0000; font-style: italic;">/**
+Alter tablespace supported in an InnoDB table. Allow setting extent space. */</span>
<span style="color: #000040;">+</span><span style="color: #0000ff;">int</span> innobase_alter_tablespace<span style="color: #008000;">&#40;</span>handlerton <span style="color: #000040;">*</span>hton,
<span style="color: #000040;">+</span>                                THD<span style="color: #000040;">*</span> thd, st_alter_tablespace <span style="color: #000040;">*</span>alter_info<span style="color: #008000;">&#41;</span>
<span style="color: #000040;">+</span><span style="color: #008000;">&#123;</span>
<span style="color: #000040;">+</span>       <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>alter_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>ts_alter_tablespace_type <span style="color: #000040;">!</span><span style="color: #000080;">=</span> ALTER_TABLESPACE_ALTER_FILE<span style="color: #008000;">&#41;</span>
<span style="color: #000040;">+</span>       <span style="color: #008000;">&#123;</span>
<span style="color: #000040;">+</span>               <span style="color: #0000ff;">return</span> HA_ADMIN_NOT_IMPLEMENTED<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>       <span style="color: #008000;">&#125;</span>
<span style="color: #000040;">+</span>
<span style="color: #000040;">+</span>       ulint table_space<span style="color: #000080;">=</span> fil_get_space_id_for_table<span style="color: #008000;">&#40;</span>alter_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>tablespace_name<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>
<span style="color: #000040;">+</span>       <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>table_space <span style="color: #000080;">==</span> ULINT_UNDEFINED<span style="color: #008000;">&#41;</span>
<span style="color: #000040;">+</span>       <span style="color: #008000;">&#123;</span>
<span style="color: #000040;">+</span>               my_error<span style="color: #008000;">&#40;</span>ER_WRONG_TABLE_NAME, MYF<span style="color: #008000;">&#40;</span><span style="color: #0000dd;">0</span><span style="color: #008000;">&#41;</span>, alter_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>tablespace_name<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>               <span style="color: #0000ff;">return</span> EE_FILENOTFOUND<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>       <span style="color: #008000;">&#125;</span>
<span style="color: #000040;">+</span>
<span style="color: #000040;">+</span>       ulint extent_size<span style="color: #000080;">=</span> alter_info<span style="color: #000040;">-</span><span style="color: #000040;">&</span>gt<span style="color: #008080;">;</span>extent_size<span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>       
<span style="color: #000040;">+</span>       ulint actual_size<span style="color: #000080;">=</span><span style="color: #0000dd;">0</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>       fil_extend_space_to_desired_size<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>amp<span style="color: #008080;">;</span>actual_size, table_space, extent_size<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span>
<span style="color: #000040;">+</span>       <span style="color: #0000ff;">return</span> <span style="color: #0000dd;">0</span><span style="color: #008080;">;</span>
<span style="color: #000040;">+</span><span style="color: #008000;">&#125;</span>
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/* innodb_extent_tablespace By P.Linux */</span>
<span style="color: #000040;">+</span>
 <span style="color: #ff0000; font-style: italic;">/*****************************************************************/</span><span style="color: #ff0000; font-style: italic;">/**
 Commits a transaction in an InnoDB database. */</span>
 <span style="color: #0000ff;">static</span>
<span style="color: #000040;">---</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>include<span style="color: #000040;">/</span>fil0fil.<span style="color: #007788;">h</span>	<span style="color: #0000dd;">2011</span><span style="color: #000040;">-</span><span style="color: #0000dd;">12</span><span style="color: #000040;">-</span><span style="color: #0000dd;">20</span> <span style="color: #208080;">06</span><span style="color: #008080;">:</span><span style="color: #0000dd;">38</span><span style="color: #008080;">:</span><span style="color: #800080;">57.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
<span style="color: #000040;">+++</span> Percona<span style="color: #000040;">-</span>Server<span style="color: #000040;">-</span>5.5.18<span style="color: #000040;">-</span><span style="color: #800080;">23.0</span><span style="color: #000040;">-</span>debug<span style="color: #000040;">/</span>storage<span style="color: #000040;">/</span>innobase<span style="color: #000040;">/</span>include<span style="color: #000040;">/</span>fil0fil.<span style="color: #007788;">h</span>	<span style="color: #0000dd;">2012</span><span style="color: #000040;">-</span><span style="color: #208080;">01</span><span style="color: #000040;">-</span><span style="color: #0000dd;">17</span> <span style="color: #0000dd;">14</span><span style="color: #008080;">:</span><span style="color: #0000dd;">39</span><span style="color: #008080;">:</span><span style="color: #800080;">20.000000000</span> <span style="color: #000040;">+</span><span style="color: #800080;">0800</span>
@@ <span style="color: #000040;">-</span><span style="color: #0000dd;">744</span>,<span style="color: #0000dd;">6</span> <span style="color: #000040;">+</span><span style="color: #0000dd;">744</span>,<span style="color: #0000dd;">18</span> @@
 <span style="color: #ff0000; font-style: italic;">/*============================*/</span>
 	ulint		id<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>	<span style="color: #ff0000; font-style: italic;">/*!< in: space id */</span>
 
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/*******************************************************************/</span><span style="color: #ff0000; font-style: italic;">/**
+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 */</span>
<span style="color: #000040;">+</span>UNIV_INTERN
<span style="color: #000040;">+</span>ulint
<span style="color: #000040;">+</span>fil_get_space_id_for_table<span style="color: #008000;">&#40;</span>
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/*=======================*/</span>
<span style="color: #000040;">+</span>       <span style="color: #0000ff;">const</span> <span style="color: #0000ff;">char</span><span style="color: #000040;">*</span>     name<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>  <span style="color: #ff0000; font-style: italic;">/*!< in: table name in the standard
+                               'databasename/tablename' format */</span>
<span style="color: #000040;">+</span><span style="color: #ff0000; font-style: italic;">/* innodb_extent_tablespace By P.Linux */</span>
<span style="color: #000040;">+</span>
 <span style="color: #ff0000; font-style: italic;">/*************************************************************************
 Return local hash table informations. */</span>

登录后复制

三、测试结果：

1. R910 Oracle单实例

测试人：童家旺，支付宝
TPS：稳定值2000,峰值2600 （我没参与测试，也没有报告，无法确定详情）
我的补充：Oracle已经是调优的过的，请相信我们的Oracle DBA不是吃素的。我把听Oracle DBA描述的只言碎语随便写下，Oracle跑到后面TPS也是有所下降，不是能一直100%稳定，最后CPU已经吃尽了，所以基本上再怎么优化提升的幅度会比较小。

2. R910 MySQL单实例 Percona 5.1.59 原版

测试人：帝俊，支付宝
TPS：峰值1500，无法稳定（具体不祥）
测试人描述：
目前的测试数据显示，由于MySQL在checkpoint上处理跟不上，不足以持续支持1.5K/s的事务数，10MB/s的redo量下的交易创建。该负载下，FIO的写出速度为160～190MB/s，写IOPS为2～2.3k，测试FIO的写吞吐量可以到600MB/s，写IOPS有8K ，需要进一步研究如何进一步提升系统的吞吐量。

3. R910 MySQL多实例 Percona 5.1.60-13.1原版

测试人：彭立勋，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内部可能某些常量设置不合理，或者锁定力度太粗导致单实例不能充分发挥单机性能。

4. R910 MySQL多实例 Percona 5.1.60-13.1 修改版

测试人：彭立勋，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一次。

5.R510 MySQL单实例 Percona 5.5.18-23.0 修改版

测试人：彭立勋,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产生量计算。
一个InnoDB性能超过Oracle的调优Case
同时观察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

6. R510 MySQL单实例 Percona 5.5.19-24.0 原版

测试人：彭立勋，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。
一个InnoDB性能超过Oracle的调优Case

四、测试结论：

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%

七、随意补充

为什么读为主的应用不用担心IO用不完？因为读操作是同步IO，一旦请求就被发送到磁盘，所以只要并发够多，总能把IO压爆。但是写为了加速，几乎所有数据库都是先写到内存，再异步写到磁盘，当然你要是搞最大保护模式，应该也是有数据库可以直接同步写磁盘的，但是对大部分数据库都是先写内存，再异步到磁盘，所以如果异步IO这里存在设计上的瓶颈，不管加多少并发，都是徒劳，内存一旦写满，链接线程就都堵住了，要等异步IO消化完才能继续，所以对于写为主的应用，这个CASE都是很有参考价值的。

原文地址：一个InnoDB性能超过Oracle的调优Case, 感谢原作者分享。

本站声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn