MySQL 5.5 新特性介绍和性能评估

找到好物，google了一下发现中文网上少有，共享之。

CSDN下载 ：http://download.csdn.net/detail/coollen_mmx/3990139

原始地址：http://www.slideshare.net/pwesh/mysql-55eval （注册可下载）

MySQL 5.5 新特性介绍和性能评估

顾春江
2010年，12月
目 录
概要..........................................................................................................................4
性能上的改进..............................................................................................................5
InnoDB成为默认的数据库引擎...................................................................................5
改进事务中对元数据的加锁机制.................................................................................5
提升MySQL在win32/64系统上的性能........................................................................5
InnoDB数据恢复效率大大提升...................................................................................5
InnoDB同时支持多个BufferPool实例..........................................................................6
InnoDB同时支持多个回滚段......................................................................................6
InnoDB提升默认的并发线程策略...............................................................................6
InnoDB细粒度控制后台I/O线程数量..........................................................................6
InnoDB可配置的主线程I/O速率................................................................................6
InnoDB可控制的自适应hash索引操作........................................................................6
InnoDB重新支持批量日志写入...................................................................................6
Linux平台原生异步I/O支持......................................................................................6
Linux平台原生原子操作支持.....................................................................................7
支持扩展的数据变化缓冲..........................................................................................7
扩展性方面的提升.......................................................................................................8
Log Sys Mutex和Flush List Mutex粒度更细..................................................................8
数据页GC逻辑从主线程中剥离.................................................................................8
可用性方方面的提升....................................................................................................9
半同步复制(Google Patch)..................................................................................................................9
复制心跳................................................................................................................9
自动Relay Log恢复..................................................................................................9
管理和效率方面的提升...............................................................................................10
快速索引创建........................................................................................................10
高效数据压缩........................................................................................................10
新的Performance Schema..................................................................................................................10
新参数解释...............................................................................................................11
Innodb_use_native_aio........................................................................................................................11
Innodb_buffer_pool_instance..............................................................................................................12
Innodb_change_buffering....................................................................................................................12
Innodb_purge_threads........................................................................................................................13
Innodb_purge_batch_size....................................................................................................................14
性能测试..................................................................................................................15
测试目的..............................................................................................................15
机器配置..............................................................................................................15
Linux配置.........................................................................................................15
服务器存储性能测试...............................................................................................15
只读IOPS 测试..................................................................................................16
配置.............................................................................................................16
测试结果.......................................................................................................16
只读带宽测试....................................................................................................17
配置.............................................................................................................17
测试结果.......................................................................................................17
只写IOPS测试...................................................................................................17
配置.............................................................................................................18
测试结果.......................................................................................................18
只写带宽测试....................................................................................................19
配置.............................................................................................................19
测试结果.......................................................................................................19
读写IOPS测试...................................................................................................19
配置.............................................................................................................20
测试结果.......................................................................................................20
MySQL性能图表...................................................................................................21
MySQL配置......................................................................................................21
MySQL 5.5.8配置...........................................................................................21
MySQL 5.1.54配置..........................................................................................22
MyISAM引擎只读测试.......................................................................................22
MyISAM引擎读写测试.......................................................................................23
InnoDB引擎只读测试..........................................................................................23
InnoDB引擎读写测试..........................................................................................24
MySQL InnoDB引擎TPC-C测试...........................................................................25
MySQL 5.5.8 tpcc-mysql输出............................................................................25
MySQL 5.1.54 tpcc-mysql输出...........................................................................26
测试结果..............................................................................................................27
概要
MySQL 5.5 GA作为Oracle接管MySQL Ab之后的第一个里程碑式的GA，不仅在Oracle自身的
LAMP软件堆叠中有非常重要的含义，而且在功能和性能/扩展性上有较为显著的提升，从总的特性上来看，
MySQL5.5GA将非常适合基于web的应用程序，很多的优化的目的都是改进并发，如对多核CPU的高效
利用，对大量并发连接请求的处理等。
总的来说，有如下改进：
• MySQL全面改用CMake作为主编译系统
• InnoDB成为默认引擎
• InnoDB在存储，管理和性能方面都有很大改进
• MySQL在Win平台上的性能开始追至和其他平台相差无几的状况
• 全面支持多核对称CPU
• 复制能够被更好地监控和管理
• 执行SQL语句时支持自定义异常处理并可将异常抛会调用程序
• 新增性能模式库(Performance Schema)
性能上的改进
InnoDB成为默认的数据库引擎
MySQL 5.5 GA的一个非常重要的改变是把InnoDB作为了MySQL的默认引擎，应该说在5.5以前，
MyISAM作为MySQL的默认引擎的表现还是非常好的，包括很高的读取速度和简洁的存储文件结构以及
基于MyISAM原子操作的快速堆添加写入。不过作为数据库的引擎，MyISAM还缺乏兼容ACID的事务，
而且现在InnoDB已经开发到版本1.1，代码已经在架构级做了重构，并且在并发和失败恢复上有了很大改
进，而且新版本可以把InnoDB静态编译进MySQL的主代码。同时也是因为版权不再成为问题，MySQL
5.5 Ga将采用InnoDB作为默认的数据库引擎。
InnoDB 1.1的文件格式代号为Barracuda，支持DYNAMIC和COMPRESSED行格式。DYNAMIC
支持将表的PrimaryKey全部缓存在内存（但如果其中包含TEXT和BLOB，那这些仍将保存在磁盘），
避免主键查询引起的I/O； COMPRESSED支持数据压缩和索引压缩。MPB1上有个例子，DYNAMIC行
格式能够在60GB左右的原始表数据的基础上降低30%左右的I/O； COMPRESSED能够把60GB左右
的表压缩到5GB左右，同时I/O降到5%左右，CPU降到5%（原来大量的await），100倍左右的主键查
询速度提升。
改进事务中对元数据的加锁机制
MySQL 5.5 GA也有在SQL解析器上的改进，新版本改进了事务引擎的表级锁完整性。 原来的版本是
这样的： 在某个事务执行过程中，如果某个表被事务里的语句引用，那么在这个语句的执行过程中，这个表
上会有DDL排它锁，当这个语句执行完后（可能事务还没有结束），这个表上的DDL排它锁将会被释放，
可能会造成非法事务。新版本把这个锁的时效一直延续到事务结束，避免DBA的一些正常操作以至于破坏
事务完整性。
提升MySQL在win32/64系统上的性能
这次的改进是把原来的在win平台上相对比较慢的MySQL拉回到和Linux平台差不多的状况，有下面几
个改变比较重要：
• 新版本的mutex和锁实现开始采用原生Windows同步原语（CRITICAL_SECTIONs,Semaphores对
象），大量减少Windows Events的产生（原来用的是event对象），同时也大量减少了MySQL
处理事件回调的次数。
• 新版本的读写相关的锁实现开始采用原生Windows原子操作和Pthreads
• 新版本默认采用Windows本地系统内存分配器
• 其他版本的一些优化补丁也在Windows版本上应用
• 解决了很多Windows版本特有的bug
这样，MySQL会在Windows平台上成为SQL Server很好的补充。
InnoDB数据恢复效率大大提升
MySQL实例失败后，InnoDB的表可能会损坏，下次启动MySQL实例的时候，系统会自动做失败恢复。
原来的恢复策略需要不断检查InnoDB的buffer pool的大小，以避免被存储恢复用的redo log的hash
table充满，导致有O(n * m)的时间复杂度(n是线性的，会很大)，新版本1引入红-黑树来做插入排序的中间
1 Real-Life Use Case for “Barracuda” InnoDB File Format
1 InnoDB recovery is now faster…much faster!
数据结构，同时不再像以前那样从flish_list顶部插入，时间复杂度为O(m)，效果非常明显。
InnoDB同时支持多个BufferPool实例
原来InnoDB的BufferPool都是InnoDB很多线程的必争之地，像free list，flush list，LRU，cache
page都会被很多线程同时访问，同样肯定会造成mutex过热的情况。新版本2支持将BufferPool均分为多
个子BufferPool（前提是BufferPoolSize需要大于1GB），然后有个hash函数来路由访问请求。好处是
细分后将大量降低mutex争抢的情况。
InnoDB同时支持多个回滚段
一般数据库在高并发情况下，不可避免地会有很多的事务回滚操作，这个时候回滚段(undo segments)可
能会有热点发生，Oracle支持并发写入undo，新版本InnoDB也支持并发写入undo，从原来的同时事务
回滚操作量1023个提升到123 x1023(128k)个。
InnoDB提升默认的并发线程策略
现在innodb_thread_concurrency的值默认为0，即没有限制，可以充分利用多CPU资源。这
个和以前版本的语义不同，在InnoDB 1.0.3之前，如果这个值为0，那么将关闭线程并发。
InnoDB细粒度控制后台I/O线程数量
新版本可以具体控制后台I/O读写线程的具体数量，由参数innodb_read_io_threads和
innodb_write_io_threads控制。老版本默认的读写线程数量都是1（linux平台，windows平
台总数可配）。这个选项可以针对多CPU，高性能存储设备进行定量配置。
InnoDB可配置的主线程I/O速率
新版本可以细化配置存储的IOPS，使用参数innodb_io_capacity。
InnoDB可控制的自适应hash索引操作
新版本终于有选项可以关掉这个了，原来一个InnoDB的表如果大小可以，足够放入BufferPool，
那么MySQL会监控对这个表上的索引的访问频度，如果MySQL发现某个索引被大量访问，那么将自动构
建一个和这个索引相同字段的前缀hash索引，以加速读取。不过在高并发的情况下，这个自动构建过程可
能会对其他线程造成大量的读写闩(RW-latch)，所以在某些情况下，关闭这个功能会好些。
InnoDB重新支持批量日志写入
新版本可支持binlog的组写入(group write)，配合日志写入策略可显著提升I/O流量。
Linux平台原生异步I/O支持
早版本在Windows平台已经支持异步I/O，在Linux平台原来也支持模拟的异步I/O(由MySQL代理
I/O)，现在已经支持动态编译到libaio。
2 Improvements to Performance from Multiple Buffer Pools
Linux平台原生原子操作支持
新版本在Linux平台也支持内核原子操作和较好地支持Pthreads。
支持扩展的数据变化缓冲
原来MySQL已经支持二级索引上的插入缓冲（新的索引修改不立刻写到磁盘，而是先放在BufferPool
中，等到这个索引被用户请求并读入BufferPool之后，在内存中快速修改索引数据； 作用就是就省去了索引
修改刷到磁盘的那些I/O），现在扩展支持把一般数据删除也放在BufferPool中，包括delete操作和perge
操作（可调，由innodb_change_buffering控制）。
扩展性方面的提升
扩展性的变化主要体现在新版本的一些mutex的粒度更细了，一些琐碎的逻辑从主线程代码中剥离出来，形
成新的轻量线程。
Log Sys Mutex和Flush List Mutex粒度更细
原来的Log Sys Mutex会在很多DDL级操作和bin log操作的时候锁死BufferPool，新版本把log_sys
互斥量细拆出一个log_flush_order互斥量，线程根据主题去访问不同的互斥量，提升BufferPool的可用性。
Flush List同理。
数据页GC逻辑从主线程中剥离
原来BufferPool的GC操作由一个主线程来调度，在GC的时候也会全部锁死BufferPool，新版本把逻
辑独立之后由新线程来操作，并可设置GC的线程数量。这个不会有太多性能提升，不过在调试其他性能时
会起到辅助作用。
可用性方方面的提升
半同步复制(Google Patch)
原来MySQL的复制都是异步的，新版本支持半同步复制，即至少有一个slave写完relay log并返回
ack，那么master才会提交事务。如果master和slave之间有超时，那么master将自动切回异步复制，直
到至少一个被配置成半同步复制的连上master并追上进度。
复制心跳
可以让slave知道master的可用状态，避免不必要的relay log rotate。
自动Relay Log恢复
新版本slave可以在自身失败后，知道哪些relay log还没有被处理，然后扔掉这些log重新开始，保证了
relaylog的安全性。
管理和效率方面的提升
快速索引创建
新版本在添加删除表索引的时候将不再将原表复制一边，大大提升DDL的效率。
高效数据压缩
支持DYNAMIC和COMPRESSED的行数据格式，并支持自定义KEY_BLOCK_SIZE，可根据表的
特征赋予不同的数据页大小。
新的Performance Schema
Performance Schema为新的性能视图，可以看到细粒度的性能信息。
新参数解释
Innodb_use_native_aio
Innodb-Plugin version 1.0
Version Introduced 5.5.4
Command-Line Format --innodb_use_native_aio=#
Config-File Format innodb_use_native_aio
Option Sets Variable Yes， innodb_use_native_aio
Variable Name innodb_use_native_aio
Variable Scope Global
Dynamic Variable No
Permitted Values
Type boolean
Default on
异步IO功能在MySQL-5.5.4中才提供，异步IO这个功能其实来源于Google的MySQL 补丁
InnodbAsyncIo3。在做以下操作的时候，可以利用到异步IO，insert buffer merging、log IO、read
prefetch requests、writing dirty buffer cache pages，因此如果开启异步IO的话，
innodb_write_io_threads和innodb_read_io_threads 这个配置参数是非常有用的。
从Innodb-Plugin-1.0中开始支持了异步IO，要开启异步IO的功能，操作系统必须支持异步IO。异
步 I/O 是 Linux 内核中提供的一个相当新的增强，它是2.6 版本内核的一个标准特性，但是我们在 2.4 版
本内核的补丁中也可以找到它。一般说来，异步I/O是和同步I/O相比较来说的，如果是同步I/O，当一个
I/O操作执行时，应用程序必须等待，直到此I/O执行完。 相反，异步I/O操作在后台运行，I/O操作和应
用程序可以同时运行，应用程序毋须等待I/O操作结果，提高了系统性能; 使用异步I/O会提高I/O流量，
如果应用是对裸设备进行操作，这种优势更加明显， 因此像数据库，文件服务器等应用往往会利用异步
I/O，使得多个I/O操作同时执行。这样，可以大大地提供IO处理的速度。默认情况是MySQL支持异步
IO，如果要关闭异步IO的话，可以设置innodb_use_native_aio=0.
在Linux中，支持异步IO必须得安装libaio包，可以通过 "whereis libaio " 来判断该包是否已经安装。
同时我们可以通过以下方式来检查异步IO是否使用，kiocb的值不为0即是正在使用异步IO。
$ cat /proc/slabinfo | grep kio
kioctx 64 110 384 10 1 : tunables 54 27 8 : slabdata 11 11 0
kiocb 13 315 256 15 1 : tunables 120 60 8 : slabdata 21 21 44
分两种情况进行测试，开启异步IO 和不开启异步IO。
同时感觉在开启异步IO的情况下，Innodb的读写多线程对性能的影响应该是比较大的，以前在利用
Blogbench调优测试Innodb-Plugin的时候，发现增加读写线程的个数并没有带来性能上的提升，那个时候
还不支持异步IO，增加线程的个数其实意义也不大。因此本次测试也增加了读写进程个数的测试。测试结
果如下：
innodb_use_native_a
io
innodb_write_io_thr
eads
innodb_read_io_thre
ads
tps
Off 1 1 789
On 1 1 810
On 2 2 859
On 4 4 823
On 8 8 785
3 InnodbAsyncIo
从测试的结果来看，开启异步IO对性能的提升还是有帮助的，有一定的提升。同时在开启异步IO的情
况下，innodb_write_io_threads和innodb_read_io_threads对性能有一定的提升，但是线程不是越多越好，
数量的增加也增大了开销。
Innodb_buffer_pool_instance
Innodb-Plugin version 1.1
Version Introduced 5.5.5
Command-Line Format --Innodb_buffer_pool_instance=#
Config-File Format Innodb_buffer_pool_instance
Option Sets Variable Yes， Innodb_buffer_pool_instance
Variable Name Innodb_buffer_pool_instance
Variable Scope Global
Dynamic Variable No
Permitted Values
Type int(1-64)
Default 1
Innodb缓冲池的个数。Innodb缓冲池主要是用来缓存数据和索引，一般来讲，在生产环境中，缓冲池
的大小以G为单位。MySQL在检索数据的时候，都是先从缓冲池中查找，看数据是否存在，此时如果有多
个线程同时从缓冲池中拿数据的话，有可能遇到瓶颈。于是在MySQL-5.5+Innodb-Plugin_1.1中引入了一
个新的配置参数Innodb_buffer_pool_instance来控制缓冲池的个数，来降低这种竞争。每个页面都随机在
存在某个缓冲池中，这里会利用到hash函数。每个缓冲池都管理各自的空闲列表，脏数据列表和LRU队列。
当然这个参数只有innodb_buffer_pool_size比较大的时候才有用，至少在1G以上。目前这个参数的值需
要和innodb_buffer_pool_size综合起来考虑，官方推荐是最好是使每个缓冲池的大小为1G或者更大一些。
4
对于该参数，分别以2，4进行测试。
Innodb_buffer_pool_instance tps
1 859
2 765
4 786
从测试的结果来看，多缓冲池并没有带来性能的提升，反而还有所下降。
Innodb_change_buffering
Command-Line Format --innodb_change_buffering=#
Config-File Format innodb_change_buffering
Option Sets Variable Yes, innodb_change_buffering
Variable Name innodb_change_buffering
Variable Scope Global
Dynamic Variable Yes
Permitted Values (<= 5.5.3)
Type enumeration
Default inserts
Valid
Values
inserts, none
Permitted Values (>= 5.5.4)
Type enumeration
Default all
Valid Inserts, deletes, purges, changes,
4 – We recommend specifying a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each
buffer pool instance is at least 1 gigabyte.
Values all, none
该参数在MySQL-5.5.3以前，默认值为Insert，在MySQL-5.5.4以后，默认值就变成all（包含的操
作不仅仅是Insert，还包括deleted、purge等操作）。这里有必要介绍下几个选项值。inserts是指Insert
操作，deletes是指索引记录被标记为删除，purges是指索引记录在后台被物理删除，changes包括inserts
和deletes两类操作。
在数据库应用中，主键是一个唯一的识别符，并且新行被以主键的升序来插入，这是个常见的情况。
因此，对集束索引的插入不需要从一个磁盘随机读。另一方面，第二索引通常是非唯一的，到第二索引的插
入以相对随机次序发生。这可能会导致大量的随机磁盘I/O操作，而没有一个被用在InnoDB中的专用机制。
如果一个索引记录应该被插入到一个非唯一第二索引，InnoDB检查第二索引页是否在缓冲池中。如果在，
InnoDB直接把该索引记录插入到索引页。如果索引页没有在缓冲池中被发现，InnoDB插入索引记录到一
个专门的插入缓冲。插入缓冲被保持得如此小以至于它完全适合在缓冲池，并且可以非常快地做插入。插入
缓冲周期地被合并到数据库中第二索引树里。把数个插入合并到索引树的同一页，节省磁盘I/O操作。据测
量，插入缓冲可以提高到表的插入速度达15倍。
从上面的描述我们可以知道，MySQL在做Insert操作的时候，并不是每次操作都直接写入到磁盘，如
果在buffer pool中没找到数据，那么直接buffer起来，把多个insert操作合并，节省磁盘I/O，避免额外
的IO；MySQL-5.5.4及以后，Delete & Purge跟插入一样，如果buffer pool中没有命中，先buffer起来，
避免额外的IO。
下面就对比测试下all和Inserts两类操作。
Innodb_change_buffer tps
all 859
inserts 840
从测试的结果来看，all会比inserts效果更好点，毕竟IO操作的次数会更少一些。
Innodb_purge_threads
Version Introduced 5.5.4
Command-Line Format --innodb_purge_threads=#
Config-File Format innodb_purge_threads
Variable Name innodb_purge_threads
Variable Scope Global
Dynamic Variable No
Permitted Values
Type numeric
Default 0
Range 0-1
Innodb中用于Purge操作的后台线程数，目前这个变量的允许值只能为0和1，这个变量是在MySQL-
5.5.4才引入的，以前的话，Innodb中用于Purge操作的线程都是Master主线程。把Purge操作用专门的
线程独立出来，有利于减少Innodb的内部竞争。
下面分别以0（不使用单独线程）和1（使用1个单独的线程）进行测试。
Innodb_purge_threads tps
0 859
1 721
从测试的结果来看，使用单独的线程性能带来的较大的下降。这个也和官方文档相似。5不过这个参数会在
5 -- Currently, the performance gain might be minimal because the background thread might encounter different kinds of
接下来的版本中进行优化。
Innodb_purge_batch_size
Version Introduced 5.5.4
Command-Line Format --innodb_purge_batch_size=#
Config-File Format innodb_purge_batch_size
Variable Name innodb_purge_batch_size
Variable Scope Global
Dynamic Variable No
Permitted Values
Type numeric
Default 20
Range 1-5000
该变量MySQL-5.5.4才引入，表示缓冲池中改变的粒度（redo log中inserts和deletes两类操作的个
数），即触发一个Purge操作的条件，Purge操作将会把缓冲池中的脏数据块刷新到磁盘，默认条件是redo
log中inserts和deletes两类操作的个数为20。该参数经常和Innodb_purge_threads来共同调整性能，下
面就联合参数Innodb_purge_threads来做以下测试。
利用Sysbench load（8个并发session），插入记录数1000万(数据文件大小为39 G左右)，Innodb的
Buffer大小设置为8G。
测试结果如下：
Innodb_purge_threads Innodb_purge_batch_size 所需时间（单位秒）
0 20 3052
0 50 2975
0 100 3002
0 150 3038
1 20 3058
1 50 3057
1 100 2964
1 150 3013
从测试结果来看，Innodb_purge_threads和Innodb_purge_batch_size 的不同搭配对性能的影响不
大。
contention than before. This feature primarily lays the groundwork for future performance work.
性能测试
在北美服务器MySQL23上做GA版MySQL 5.5.8和MySQL 5.1.53的性能测试对比。
测试目的
MySQL单机性能测试的目的，主要是测试MySQL对于服务器资源的利用程度和自身处理请求的效率，
具体体现在请求处理量和CPU/IO利用量之间的比例。根据这个测试服务器的配置情况，并且经过简单测试，
CPU基本不会成为性能瓶颈，I/O子系统是主要的瓶颈来源（后面有对此此服务器的I/O性能测试数据）。
按照业界的通常做法，测试MySQL数据库的性能可以分为单项功能性测试和综合性的TPC-C测试，单
项的含义是只读，只写，读写，数据分布比较简单，一般基于一张表来做，主要执行的语句包含常用的过滤
谓词，并且在取值上也有做一定模式的数据分布。TPC-C6则是一种兼容ACID的行业测试标准（需要数据
库支持事务），包含多种执行语句，并在数据选择上作了正态分布，是数据库的综合事务处理能力评分指数。
下面的测试，会分别对MySQL的两个版本做这两类测试，我们使用Sysbench7做单项功能性测试，使用
Percona公司的tpcc-mysql8做TPC-C测试。另外，使用fio9做服务器磁盘性能测试。
机器配置
Linux配置
• Linux Gentoo mysql23 2.6.34-hardened-r6 x86_64 GenuineIntel GNU/Linux
• 4*6(core) Intel(R) Xeon(R) CPU X5670 @ 2.93GHz, 12M Cache, 6.40 GT/s Intel® QPI
• 32GB 内存
• 存储: 394G /data, 82G /logs
◦ 设备类型: RAID10
◦ 文件系统: ext3
◦ 块大小: 4096
◦ 队列调度算法: deadline
◦ 是否使用AIO: 是
• 库
◦ gcc: version 4.4.4-r2
◦ libaio: 0.3.107
服务器存储性能测试
存储性能使用fio(Flexible IO Tester)进行测试。
经过测试对比, 如果在单线程状态下对这个存储设备进行操作, 并且使用O_DIRECT模式打开文件, 这时
6 TPC Benchmark C
7 Sysbench Home Page
8 tpcc-mysql Launchpad Source Code
9 fio(Flexible IO Tester) Git Repository
IO请求队列的值在32左右达到存储设备的性能顶点. 因此后续都将把IO请求队列(iodepth)的值设为32.
只读IOPS 测试
这个测试是面向测试磁盘的最大IO个数处理能力, 并非带宽, 因此测试的时候,IO操作的block size设定
为4 k(文件系统的block size). 同时, 并发线程数量需要比较大, 设置为64个.
测试磁盘IO读IOPS性能时, 为了让结果代表整个磁盘, 需要让测试数据均匀落在磁盘上, 本次测试的磁盘
的大小为394GB, 按照85%的利用率算(394 GB * 85% =320GB), 测试前将产生320GB的数据供读取(一
共64个文件, 每个文件5GB).
配置
[global]
directory=/data
ioengine=libaio
direct=1
buffered=0
rw=randread
iodepth=32
bs=4k
size=5G
numjobs=64
runtime=30
group_reporting
write_bw_log
# 测试使用AIO库，O_DIRECT模式，跳过磁盘缓冲，IO队列长度32个，数据块大小4 k，文件段长度5 GB，并发线程64
# 个，运行时间30秒
测试结果
Jobs: 64 (f=64): [r..r] [100.0% done] [17M/0K /s] [4K/0 iops] [eta 00m:00s]
file1: (groupid=0, jobs=64): err= 0: pid=6886
read : io=489MB, bw=16,636KB/s, iops=4,159, runt= 30126msec
slat (usec): min=1, max=930K, avg=15089.52, stdev=10526.88
clat (msec): min=1, max=1,486, avg=467.76, stdev=16.14
bw (KB/s) : min= 4, max= 622, per=1.62%, avg=269.54, stdev=12.96
cpu : usr=0.01%, sys=0.04%, ctx=9263, majf=0, minf=3664
IO depths : 1=0.1%, 2=0.1%, 4=0.2%, 8=0.4%, 16=0.8%, 32=98.4%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=99.9%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
issued r/w: total=125295/0, short=0/0
lat (msec): 2=0.02%, 4=0.64%, 10=2.49%, 20=0.78%, 50=0.09%
lat (msec): 100=0.14%, 250=0.65%, 500=88.23%, 750=5.01%, 1000=1.82%
lat (msec): 2000=0.14%
Run status group 0 (all jobs):
READ: io=489MB, aggrb=16,636KB/s, minb=17,035KB/s, maxb=17,035KB/s,
mint=30126msec, maxt=30126msec
Disk stats (read/write):
sda: ios=126971/1853864, merge=4/131741236, ticks=4544219/187357972,
in_queue=191910625, util=83.47%
测试结果的IOPS为: 在4 k的块大小上每秒有4159次IO读操作
IOPS=4159(4k block)
只读带宽测试
这个测试是面向测试磁盘的最大带宽, 并非最大IO处理能力, 因此测试的时候,
IO操作的block size设定为1M(需要体现出连续读的特性). 同时, 并发线程数量
需要比较小, 设置为4个.
测试磁盘IO读带宽不一定要使用全盘, 因此测试时产生20GB数据供读取(4个文件,
每个文件5GB).
配置
[global]
directory=/data
ioengine=libaio
direct=1
buffered=0
rw=randread
iodepth=32
bs=1M
size=5G
numjobs=4
runtime=30
group_reporting
write_bw_log
# 测试使用AIO库，O_DIRECT模式，跳过磁盘缓冲，IO队列长度32个，数据块大小4 k，文件段长度5 GB，并发线程4
# 个，运行时间30秒
测试结果
Jobs: 4 (f=4): [rrrr] [100.0% done] [312M/0K /s] [305/0 iops] [eta 00m:00s]
job1: (groupid=0, jobs=4): err= 0: pid=14949
read : io=8,967MB, bw=297MB/s, iops=297, runt= 30149msec
slat (usec): min=42, max=203K, avg=13381.75, stdev=17294.42
clat (msec): min=104, max=1,893, avg=415.01, stdev=40.64
bw (KB/s) : min=13972, max=88275, per=24.75%, avg=75389.11, stdev=4187.38
cpu : usr=0.00%, sys=0.46%, ctx=1270, majf=0, minf=65624
IO depths : 1=0.1%, 2=0.1%, 4=0.2%, 8=0.4%, 16=0.7%, 32=98.6%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
issued r/w: total=8967/0, short=0/0
lat (msec): 250=0.56%, 500=87.76%, 750=11.45%, 1000=0.06%, 2000=0.18%
Run status group 0 (all jobs):
READ: io=8,967MB, aggrb=297MB/s, minb=305MB/s, maxb=305MB/s,
mint=30149msec, maxt=30149msec
Disk stats (read/write):
sda: ios=37978/86032, merge=22445/6088541, ticks=4343623/9276272,
in_queue=13630869, util=88.65%
测试结果IO读流量为: 297MB/s
只写IOPS测试
这个测试是面向测试磁盘的最大IO个数处理能力, 并非带宽, 因此测试的时候,
IO操作的block size设定为4 k(文件系统的block size). 同时, 并发线程数量需
要比较大, 设置为64个.
测试磁盘IO写性能也需要磁盘上有均匀的数据分布, 本次测试的磁盘的大小为
394GB, 按照85%的利用率算(394 GB * 85% = 320GB), 测试时将先产生320GB的数
据填充磁盘(一共64个文件, 每个文件5 GB).
配置
[global]
directory=/data
ioengine=libaio
direct=1
buffered=0
rw=randwrite
iodepth=32
bs=4k
size=5G
numjobs=64
runtime=30
group_reporting
write_bw_log
# 测试使用AIO库，O_DIRECT模式，跳过磁盘缓冲，IO队列长度32个，数据块大小4 k，文件段长度5 GB，并发线程64
# 个，运行时间30秒
测试结果
Jobs: 64 (f=64): [w...w] [100.0% done] [0K/7,827K /s] [0/2K iops] [eta 00m:00s]
job1: (groupid=0, jobs=64): err= 0: pid=15047
write: io=188MB, bw=6,372KB/s, iops=1,592, runt= 30152msec
slat (usec): min=2, max=3,632K, avg=591585.39, stdev=19860.05
clat (msec): min=1, max=19,859, avg=11598.58, stdev=207.34
bw (KB/s) : min= 0, max= 723, per=0.12%, avg= 7.35, stdev= 1.33
cpu : usr=0.00%, sys=0.15%, ctx=4953, majf=0, minf=1549
IO depths : 1=0.1%, 2=0.3%, 4=0.5%, 8=1.1%, 16=2.1%, 32=95.9%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=99.9%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
issued r/w: total=0/48031, short=0/0
lat (msec): 2=0.01%, 4=0.01%, 10=0.05%, 20=0.19%, 50=1.26%
lat (msec): 100=4.23%, 250=1.48%, 500=7.57%, 750=2.67%, 1000=9.99%
lat (msec): 2000=64.94%, >=2000=7.61%
Run status group 0 (all jobs):
WRITE: io=188MB, aggrb=6,371KB/s, minb=6,524KB/s, maxb=6,524KB/s,
mint=30152msec, maxt=30152msec
Disk stats (read/write):
sda: ios=1/1267013, merge=0/82090140, ticks=123/114440885,
in_queue=114408225, util=100.00%
测试结果的IOPS为: 在4 k的块大小上每秒有1592次IO写操作
IOPS=1592(4k block)
只写带宽测试
这个测试是面向测试磁盘的最大带宽, 并非最大IO处理能力, 因此测试的时候,
IO操作的block size设定为1 M(需要体现出连续读的特性). 同时, 并发线程数量
需要比较小, 设置为4个.
测试磁盘IO写带宽不一定要使用全盘, 因此测试时产生20 GB数据供读取(4个文件,
每个文件5 GB).
配置
[global]
directory=/data
ioengine=libaio
direct=1
buffered=0
rw=randwrite
iodepth=32
bs=1M
size=5G
numjobs=4
runtime=30
group_reporting
write_bw_log
# 测试使用AIO库，O_DIRECT模式，跳过磁盘缓冲，IO队列长度32个，数据块大小4 k，文件段长度5 GB，并发线程4
# 个，运行时间30秒
测试结果
Jobs: 4 (f=4): [wwww] [25.2% done] [0K/153M /s] [0/149 iops] [eta 01m:32s]
job1: (groupid=0, jobs=4): err= 0: pid=15117
write: io=5,316MB, bw=176MB/s, iops=176, runt= 30196msec
slat (usec): min=30, max=451K, avg=22775.29, stdev=29438.91
clat (msec): min=110, max=1,319, avg=708.21, stdev=71.99
bw (KB/s) : min= 0, max=88888, per=22.56%, avg=40664.39, stdev=6077.90
cpu : usr=0.00%, sys=0.20%, ctx=801, majf=0, minf=89
IO depths : 1=0.1%, 2=0.2%, 4=0.3%, 8=0.6%, 16=1.2%, 32=97.7%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=99.9%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
issued r/w: total=0/5316, short=0/0
lat (msec): 250=2.14%, 500=4.82%, 750=67.27%, 1000=24.02%, 2000=1.75%
Run status group 0 (all jobs):
WRITE: io=5,316MB, aggrb=176MB/s, minb=180MB/s, maxb=180MB/s,
mint=30196msec, maxt=30196msec
Disk stats (read/write):
sda: ios=0/22569, merge=0/13196, ticks=0/4453824,
in_queue=4461112, util=99.66%
测试结果IO写流量为: 176MB/s
读写IOPS测试
这个测试是面向测试磁盘的最大IO个数处理能力, 并非带宽, 因此测试的时候,
IO操作的block size设定为4 k(文件系统的block size). 同时, 并发线程数量需
要比较大, 设置为64个.
测试磁盘IO读IOPS性能时, 为了让结果代表整个磁盘, 需要让测试数据均匀落在
磁盘上, 本次测试的磁盘的大小为394 GB, 按照85%的利用率算(394 GB * 85% =
320GB), 测试前将产生320GB的数据填充到磁盘(一共64个文件, 每个文件5 GB).
配置
[global]
directory=/data
ioengine=libaio
direct=1
buffered=0
rw=randrw
iodepth=32
bs=4k
size=5G
numjobs=64
runtime=30
group_reporting
write_bw_log
# 测试使用AIO库，O_DIRECT模式，跳过磁盘缓冲，IO队列长度32个，数据块大小4 k，文件段长度5 GB，并发线程64
# 个，运行时间30秒
测试结果
job1: (groupid=0, jobs=64): err= 0: pid=15161
read : io=153MB, bw=5,185KB/s, iops=1,296, runt= 30210msec
slat (usec): min=2, max=2,121K, avg=24884.94, stdev=15552.27
clat (msec): min=3, max=3,591, avg=890.40, stdev=39.40
bw (KB/s) : min= 0, max= 170, per=1.44%, avg=74.54, stdev= 3.01
write: io=154MB, bw=5,236KB/s, iops=1,309, runt= 30210msec
slat (usec): min=2, max=1,563K, avg=28801.77, stdev=17512.29
clat (usec): min=112, max=3,023K, avg=774805.25, stdev=41856.48
bw (KB/s) : min= 0, max= 158, per=1.36%, avg=71.22, stdev= 3.77
cpu : usr=0.00%, sys=0.02%, ctx=5476, majf=0, minf=1573
IO depths : 1=0.1%, 2=0.2%, 4=0.3%, 8=0.7%, 16=1.3%, 32=97.5%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=99.9%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
issued r/w: total=39161/39547, short=0/0
lat (usec): 250=1.00%, 500=0.76%, 750=0.21%, 1000=0.11%
lat (msec): 2=0.22%, 4=0.10%, 10=0.60%, 20=0.80%, 50=0.40%
lat (msec): 100=0.02%, 250=0.65%, 500=1.13%, 750=51.00%, 1000=36.54%
lat (msec): 2000=5.38%, >=2000=1.07%
Run status group 0 (all jobs):
READ: io=153MB, aggrb=5,185KB/s, minb=5,309KB/s, maxb=5,309KB/s,
mint=30210msec, maxt=30210msec
WRITE: io=154MB, aggrb=5,236KB/s, minb=5,361KB/s, maxb=5,361KB/s,
mint=30210msec, maxt=30210msec
Disk stats (read/write):
sda: ios=39110/1867755, merge=0/132473196, ticks=4141510/194932578,
in_queue=199086729, util=83.45%
测试结果的IOPS为: 在4 k的块大小上每秒有1296+1309=2705次IO读写操作
IOPS≈2705(4k block)
这个结果将用作MySQL的IOPS参数
MySQL性能图表
本次测试的服务器内存比较大有32GB，因此大量的操作是在内存中完成的（InnoDB引擎的BufferPool
大小设定为20GB），IO热点基本在数据的首次读取和log的写入。
MySQL配置
MySQL 5.5.8配置
[client]
#password = [your_password]
#port = 3306
#socket = /data/mysql-test-5.5/mysql.sock
default-character-set=utf8
[mysqladmin]
default-character-set=utf8
[mysqld]
# generic configuration options
#port = 3307
#socket = /data/mysql-test-5.5/mysql.sock
core
basedir=/usr/local/mysql-5.5.8
datadir=/data/mysql-test-5.5
user=mysql
skip-grant-tables
server_id=1
local_infile=1
character-set-filesystem=utf8
character-set-server=utf8
collation-server=utf8_general_ci
default-storage-engine=InnoDB
innodb_buffer_pool_size=20G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_doublewrite=0
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_files_in_group=2
innodb_log_file_size=256M
innodb_thread_concurrency=0
innodb_flush_method = O_DIRECT
innodb_write_io_threads=24
innodb_read_io_threads=24
innodb_io_capacity=2700 #IOPS
innodb_use_native_aio
max_connections=3000
query_cache_size=0
query_cache_type=0
skip-name-resolve
#table_cache=10000
#log=/logs/mysql-test-5.5/general.log
innodb_log_group_home_dir=/logs/mysql-test-5.5
#log-slow-queries=/logs/mysql-test-5.5/slow.log
#long_query_time=1
[mysqld_safe]
ledir=/usr/local/mysql-5.5.8/bin
MySQL 5.1.54配置
[client]
#password = [your_password]
#port = 3306
#socket = /data/mysql-test-5.5/mysql.sock
default-character-set=utf8
[mysqladmin]
default-character-set=utf8
[mysqld]
# generic configuration options
#port = 3307
#socket = /data/mysql-test-5.5/mysql.sock
core
basedir=/usr/local/mysql-5.1.54
datadir=/data/mysql-test-5.1
user=mysql
skip-grant-tables
server_id=1
local_infile=1
character-set-filesystem=utf8
character-set-server=utf8
collation-server=utf8_general_ci
default-storage-engine=InnoDB
innodb_buffer_pool_size=20G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_doublewrite=no
innodb_max_dirty_pages_pct=80
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_files_in_group=2
innodb_log_file_size=256M
innodb_thread_concurrency=0
innodb_flush_method = O_DIRECT
max_connections=3000
query_cache_size=0
skip-name-resolve
#table_cache=10000
[mysqld_safe]
ledir=/usr/local/mysql-5.1.54/libexec
MyISAM引擎只读测试
这个测试有比较高的读I/O和相对比较高的CPU，主要瓶颈在MySQL的session响应能里。因为使用了
O_DRIECT，所以没有使用操作系统的文件页缓存。MyISAM没有自己的数据页缓存，但是MyISAM表
的索引是可以缓存在内存里的。
图如下：
可以看到，5.5在并发session数超过4-16中的某一个数值以后，性能就维持在5.1的2.5倍左右。得益于
新版本内存管理的改进和并发读I/O的效率。
MyISAM引擎读写测试
这个测试使用全局表锁来保证数据完整性。测试的I/O和CPU都不算很高，主要瓶颈在MyISAM表的全
局表锁上。
图如下：
可以看到，这部分5.5和5.1性能相差无几。主要原因就是全局表锁的关系，锁开销占的比重非常大。
InnoDB引擎只读测试
这个测试是会使用InnoDB的BufferPool，当前配置的BufferPool大小为20GB，足够缓冲整张表和索
引。CPU使用率相对比较高，I/O不高，主要是数据第一次从磁盘读取。
图如下:
1 4 16 32 64 128 256 384 512 768 1024
0
1000
2000
3000
4000
5000
6000 Sysbench: MyISAM ReadOnly 500w
MySQL 5.5.8
MySQL 5.1.54
conns
tps
1 4 16 32 64 128 256 384 512 768 1024
0
50
100
150
200
250
300
350
400
450
500 Sysbench: MyISAM ReadWrite 500w
MySQL 5.5.8
MySQL 5.1.54
conns
tps
可见，在并发session数超过16-32中的某一个值之后，5.5的性能就基本维持在8 k的tps，而5.1的性能
就下降得非常明显，在session数为128之后的效率就一直在1 k tps左右甚至以下了。造成这么明显的性能
差别的原因，不外乎5.5对InnoDB引擎并发线程处理效率的提升和可配置的I/O读线程数和使用系统原生
NIO上。
InnoDB引擎读写测试
这个测试CPU和I/O都有相对以上几个测试高，应该是占用最多的一个测试，原因CPU需要做大量的线
程管理内存管理和锁管理，I/O上有初始数据读和log写入，log写入速度在60MB/s左右。
图如下：
可见，5.5仍在高并发的时候领先于5.1，特别是过了并发session数4以后。不过5.5在并发session数超
过256以后，有比较明显的性能下降，不过仍领先5.1不少。具体性能提升原因和上一个测试差不多，这里
不再赘述。高并发性能下降快的原因，和写log时的锁争用有关，产品化调试时可以调整log数据文件的大
小和个数。
1 4 16 32 64 128 256 384 512 768 1024
0
1000
2000
3000
4000
5000
6000
7000
8000
9000 Sysbench: InnoDB ReadOnly 500w
MySQL 5.5.8
MySQL 5.1.54
conns
tps
1 4 16 32 64 128 256 384 512 768 1024
0
1000
2000
3000
4000
5000
6000
7000 Sysbench: InnoDB ReadWrite 500w
MySQL 5.5.8
MySQL 5.1.54
conns
tps
MySQL InnoDB引擎TPC-C测试
这个测试使用Percona公司的tpcc-mysql，具体数据分布它是模拟标准的TPC-C结果，不过表稍有简化。
它大约的交易比例如下:
1. 新订单（New-Order）
2. 支付（Payment ）43%（最小比例）
3. 订单查询（Order-Status） 4%（最小比例）
4. 交付（Delivery） 4%（最小比例）
5. 库存查询（Stock-Level） 4%（最小比例）
场景是这样的，在2,3,4,5这几类操作在数据库后台进行着的时候，测试新订单的交易能力。从上面可以
看出，留给新订单的最大比例是45%。
测试使用500w的数据基数，64个并发线程，200秒预热时间，测试时间:3000秒。
得出如下图:
图中横坐标为时间轴，以10秒为单位，不过因为数据量很大，因此会有很密的效果。纵坐标为每10秒钟处
理新订单的事务量。可见5.5在平均分布上高于5.1不少，不过标准误差也比较大。具体结果输出可看如下：
MySQL 5.5.8 tpcc-mysql输出
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
<Parameters>
[server]:
[port]: 3306
[DBname]: tpccmysql
[user]: root
[pass]:
[warehouse]: 500
[connection]: 64
[rampup]: 200 (sec.)
[measure]: 3000 (sec.)
10 730
100
190
280
370
460
550
640 820
910
1000
1090
1180
1270
1360
1450
1540
1630
1720
1810
1900
1990
2080
2170
2260
2350
2440
2530
2620
2710
2800
2890
2980
0
500
1000
1500
2000
2500
3000
tpcc-mysql 500w
MySQL 5.5.8
MySQL 5.1.54
time(per 10 sec)
trans per 10 secs
RAMP-UP TIME.(200 sec.)
MEASURING START.
…
<90th Percentile RT (MaxRT)>
New-Order : 0.80 (7.30)
Payment : 0.20 (7.17)
Order-Status : 0.60 (7.18)
Delivery : 1.60 (9.56)
Stock-Level : 0.60 (6.69)
<Raw Results>
[0] sc:445672 lt:1310 rt:0 fl:0
[1] sc:446967 lt:47 rt:0 fl:0
[2] sc:44679 lt:23 rt:0 fl:0
[3] sc:44695 lt:0 rt:0 fl:0
[4] sc:44717 lt:0 rt:0 fl:0
in 3000 sec.
<Raw Results2(sum ver.)>
[0] sc:445758 lt:1310 rt:0 fl:0
[1] sc:447016 lt:47 rt:0 fl:0
[2] sc:44681 lt:23 rt:0 fl:0
[3] sc:44696 lt:0 rt:0 fl:0
[4] sc:44717 lt:0 rt:0 fl:0
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.35% (>= 4.0%) [OK]
Stock-Level: 4.35% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 99.71% [OK]
Payment: 99.99% [OK]
Order-Status: 99.95% [OK]
Delivery: 100.00% [OK]
Stock-Level: 100.00% [OK]
<TpmC>
8939.640 TpmC
MySQL 5.1.54 tpcc-mysql输出
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
<Parameters>
[server]: 127.0.0.1
[port]: 3306
[DBname]: tpccmysql
[user]: root
[pass]:
[warehouse]: 500
[connection]: 64
[rampup]: 200 (sec.)
[measure]: 3000 (sec.)
RAMP-UP TIME.(200 sec.)
MEASURING START.
…
<90th Percentile RT (MaxRT)>
New-Order : 2.60 (46.03)
Payment : 0.60 (26.53)
Order-Status : 0.60 (26.63)
Delivery : 11.80 (16.49)
Stock-Level : 21.40 (73.26)
<Raw Results>
[0] sc:72248 lt:791 rt:0 fl:0
[1] sc:72996 lt:26 rt:0 fl:0
[2] sc:7282 lt:21 rt:0 fl:0
[3] sc:7272 lt:0 rt:0 fl:0
[4] sc:6362 lt:950 rt:0 fl:0
in 3000 sec.
<Raw Results2(sum ver.)>
[0] sc:72250 lt:791 rt:0 fl:0
[1] sc:72999 lt:26 rt:0 fl:0
[2] sc:7282 lt:21 rt:0 fl:0
[3] sc:7272 lt:0 rt:0 fl:0
[4] sc:6362 lt:950 rt:0 fl:0
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.33% (>= 4.0%) [OK]
Stock-Level: 4.35% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 98.92% [OK]
Payment: 99.96% [OK]
Order-Status: 99.71% [OK]
Delivery: 100.00% [OK]
Stock-Level: 87.01% [NG] *
<TpmC>
1460.780 TpmC
测试结果
综上，MySQL 5.5在性能方面相对MySQL 5.1的确有较大幅度的提升（综合tpmC 5.5相对5.1: 8939
vs 1460），并且5.5有更细粒度的控制参数提供给用户，可根据服务器性能做比较细节的调试。