作者：贺春旸
出版社：机械工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
MySQL管理之道：性能调优、高可用与监控（光盘内容另行下载，地址见书封底）试读：
前言
为什么要写这本书
很久以前，我就梦想着自己有一天能写一本书出来，但始终未能成行，直到今天，我终于鼓足勇气着手写作，把多年工作的积累毫无保留地写出来，展现给大家，这不仅对他人的技术成长会有些许帮助，同时对自己来说，也是一个重新学习的过程，何乐而不为？
2010年我有幸加入了梦寐以求的飞信公司，并以此正式开始了我的MySQL职业生涯。工作中能实际接触到海量数据、大并发是我在该领域发展的很重要的因素，工作的驱使和个人的兴趣成为我每天坚持学习MySQL的源动力。对于MySQL，目前业内用得最多的是MySQL5.1.X版本，事实上，MySQL5.5已经推出两年多了，其性能方面有了显著的提升，但市面上介绍MySQL该版本的图书还寥寥无几，大家都是通过阅读英文手册去获取新的知识，因此我决定基于这个版本来写作，把自己学到的新东西做一个系统性的总结。
读者对象
本书是一本面向应用的数据库类书籍，是日常工作中的积累。本书主要面向使用MySQL InnoDB存储引擎作为数据库后端的运维人员和DBA。书中的大部分例子都是生产环境的实战，相信会对读者今后的工作有所帮助。
要想更好地学习本书的内容，要求具备以下条件：
❑有一定的SQL基础。
❑掌握基本的MySQL操作和Linux操作，以及数据库基本原理。
❑接触过Shell脚本语言。
如何阅读本书
本书从知识结构上分为五大部分：
第一部分（第1～2章）详细介绍了MySQL5.5的新特性、注意事项、安装和升级方法，以及半同步复制的日常管理维护，并针对MySQL5.5与MySQL5.1进行了性能测试和功能测试。
第二部分（第3～6章）为故障诊断与优化，内容为生产环境下MySQL故障处理以及性能调优，包括表设计阶段范式的理解、字段类型的选取、采用表锁还是行锁、MySQL默认的隔离级别与传统SQL Server以及Oracle数据库默认的隔离级别的区别、SQL语句的优化以及合理利用索引等，然后讲解了my.cnf配置文件的优化、如何定期整理数据库碎片，最后介绍了硬件的优化、内存对InnoDB引擎所起的决定性作用，以及MySQL5.6的新特性等内容。
第三部分（第7～8章）为高可用软件的搭建与维护，包括企业级高可用集群RHCS、Heartbeat+DRBD和Keepalived+MySQL Replication一主一从模式的搭建与维护，故障切换脚本、MySQL+MMM读写分离架构搭建与维护，以及批量管理上百台MySQL服务器等内容。
第四部分（第9～10章）为主流监控软件的搭建与维护，包括监控软件Cacti和Nagios的安装与使用，以及线上MySQL服务器的监控脚本等内容。
第五部分（第11章）为项目案例讲解，内容包括生产环境数据库碎片整理方案、用户信息表水平拆表方案、阿里巴巴中间件Cobar水平拆表方案。
每个部分可以单独作为一本迷你书阅读，如果你未接触过MySQL5.5，建议从第一部分开始阅读。
勘误和支持
由于作者的水平有限，编写的时间也很仓促，书中难免会出现一些错误或者不准确的地方，恳请读者批评指正。你可以将书中的错误发到我的邮箱chunyang_he@sohu.com，我将尽量提供最满意的解答。如果你有更多的宝贵意见，也欢迎你发送邮件至我的邮箱，很期待能够听到你们的真挚反馈。
致谢
在飞信3年多的时间里，我得到了很多帮助。在这里感谢任志修经理对我的信任，让我始终挑战运维一线，使我得到了成长；感谢我的同事周海平（www.MySQLpub.com）鼓励我，让我把知识展现出去；还要感谢我的同事吴炳锡把我介绍到了飞信，而且在工作中给了我很多鼓励。没有你们的帮助，这本书是完成不了的。
同时感谢我的朋友邱治军参与监控部分的编写；感谢51CTO博客总编曹亚莉、博客管理员张丽妍多次邀请我参加51CTO举办的数据库大会，拓展了我的知识视野；感谢51CTO博客管理员高阳帮我处理图片；感谢前同事李才荣提供Linux批量管理的工具（Python）；感谢前同事李冉（乐蜂网高级DBA）提供事件调度器故障切换后的注意事项；另外，还要感谢给我提建议的一些朋友，谢谢你们。
感谢机械工业出版社华章公司的编辑杨绣国，感谢你的魄力和远见，在这一年多的时间中始终支持我的写作，你的鼓励和帮助引导我顺利完成全部书稿。
谨以此书献给我最亲爱的家人，以及众多热爱MySQL的朋友们。贺春旸2013年8月于北京第一部分MySQL5.5新特性篇第1章 MySQL5.5介绍第2章 半同步复制第1章MySQL5.5介绍
MySQL是一个中小型的关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。由于它具有性能高、成本低、可靠性好等特点，近几年已经成为最流行的开源数据库，被广泛地应用在Internet上的中小型网站中。而且，随着MySQL的不断成熟，现在它也逐渐用于更多大规模的网站和应用了，比如，维基百科、Google和Facebook等。非常流行的开源软件组合"LAMP"中的"M"指的就是MySQL。
这几年，MySQL的版本在不断变更，可以说是有了翻天覆地的变化，在之前的4.0版本中，没有存储过程、触发器、函数、事件，对CPU多核的支持也不好，在经历了5.0和5.1两个过渡版本后，到5.5版，其性能和功能上已经得到了很大的改善，主要体现在CPU多核处理上有了很大提高，宕机恢复时间减少，可快速创建索引，并具有半同步复制功能等方面，目前这个版本很稳定。2013年春节，5.6版本已经出了GA版，但这里不推荐直接将其用于生产环境中，因为该版本还有许多未知Bug在修复，建议1年后再开始应用，不要充当小白鼠，本书中也只会针对该版部分功能上的新特性进行介绍。
本章主要讲解的是MySQL5.5和InnoDB的一些增强性能，这些增强性能极大地提高了系统和MySQL的性能。下面将详细介绍每一个关键的增强性能及其实现过程。
为了不误导读者，保证全文的准确性，下面的内容会结合MySQL5.5官方手册《14.4.New Features of InnoDB1.1》来讲解，帮助大家认识MySQL5.5中一些较为重要的改变，其中也许会有疏漏的地方，不到之处请大家访问http://dev.MySQL.com/doc/refman/5.5/en/innodb-5-5.html，参考相关的英文文档。1.1 性能上的显著改变1.1.1 MySQL5.5默认存储引擎的调整
在MySQL5.1.X之前的版本中，默认的存储引擎是MyISAM，每个MyISAM在磁盘上会将数据存储成三个文件。第一个文件是表结构文件，它的名字以表的名字开始，其扩展名会指出文件类型，为.frm文件存储表定义；第二个文件是数据文件，其扩展名为.MYD(MYData)；第三个文件是索引文件，其扩展名是.MYI(MYIndex)。
MyISAM存储引擎的特点是表级锁、不支持事务和全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是在大并发、重负荷生产系统上，表锁的特性显得有些力不从心，并且如果系统出现宕机、MySQLd进程崩溃，MyISAM引擎表很容易受到损坏，这时不得不用外部命令MyISAMchk去修复它。
从MySQL5.5.X开始，默认的存储引擎变更为InnoDB Plugin引擎。
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级，并且也在SELECT语句提供一个与Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。之所以没有在InnoDB中扩大锁定的需要，是因为InnoDB中的行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY（外键）。在SQL查询中，你可以自由地将InnoDB类型的表与其他MySQL表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB是为处理巨大数据量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB存储引擎被完全整合到了MySQL服务器中，不过，为了能在主内存中缓存数据和索引，InnoDB存储引擎会维持它自己的缓冲池。InnoDB会在一个表空间中存储它的表和索引，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，在MyISAM表中，每个表是被存在分离的文件中的。另外，InnoDB表可以是任何尺寸，即使是在文件尺寸被限制为2GB的操作系统上。
InnoDB存储引擎应用在众多高压力、高并发的大型数据库站点上。比如，著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix,Inc.在InnoDB上存储着超过1TB的数据，还有一些其他站点在InnoDB上处理平均每秒800次插入/更新的负荷。
随着InnoDB存储引擎的崛起，更多公司为其提供了patch补丁，使其性能发挥至极致，包括Google公司、Percona公司、Sun Microsystems公司等，在MySQL5.1.X版本里，你可以自己选择是否加载打过补丁后的InnoDB Plugin（版本1.0.X），InnoDB Plugin较Built-in版本新增了很多特性，在后面会一一介绍，这里不再叙述。我之前用的MySQL5.1.43二进制版中，InnoDB Plugin已经包含在其/usr/local/MySQL/lib/plugin/目录下了，如图1-1所示。图1-1 InnoDB Plugin补丁
修改my.cnf配置文件，采用InnoDB Plugin，添加如下内容：ignore_builtin_innodbplugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
重启MySQL服务后，登录"MySQL>select @@innodb_version;"验证是否成功，这个是MySQL5.1的配置方法。
而在MySQL5.5.X版本里，你可以省去上面的那些操作步骤，直接修改my.cnf配置文件，添加如下内容即可：innodb_file_per_table=1innodb_file_format=barracudainnodb_strict_mode=1
登录到MySQL里，执行下面命令验证是否成功。MySQL> select @@version;+-----------------+| @@version |+-----------------+| 5.5.19 |+-----------------+1 row in set (0.00 sec)MySQL> select @@innodb_version;+------------------+| @@innodb_version |+------------------+| 1.1.8 |+------------------+1 row in set (0.00 sec)
关于InnoDB Plugin配置介绍，参见MySQL5.5手册：
在MySQL5.1里，Built-in InnoDB文件格式是Antelope，在MySQL5.5里InnoDB Plugin文件格式要调整为Barracuda。Barracuda格式支持表压缩功能，TRUNCATE TABLE的速度比以前要快。
图1-2和图1-3是官方提供的InnoDB和MyISAM的压力测试结果。图1-2 Read/Write结果图1-3 Read Only结果
Sysbench是一个模块化、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。它主要包括以下几种方式的测试：
❑CPU性能
❑磁盘I/O性能
❑调度程序性能
❑内存分配及传输速度
❑POSIX线程性能
❑数据库性能（OLTP基准测试）
目前Sysbench主要支持MySQL、PostgreSQL、Oracle这3种数据库。下面来看看它的安装步骤。
首先，在http://sourceforge.net/projects/Sysbench网站下载源码包。
接下来，按照以下步骤安装：# tar zxvf Sysbench-0.4.8.tar.gz# cd Sysbench-0.4.8# . /configure --with-MySQL-includes=/usr/local/MySQL/include --with-MySQL-libs=/usr/local/MySQL/lib # make && make install
在Sysbench读和写及只读压力测试中，服务器分别采用了6、12、18、24、30、36核的CPU来进行对比，其并发连接数均设置为64。从图1-2和图1-3中可以看出，随着CPU核数的增加，InnoDB的吞吐量反而越好，反观MyISAM，其吞吐量几乎没有什么变化，显然，MyISAM的表锁定机制降低了读和写的吞吐量。由此，也说明了为什么在MySQL5.5.X版本中InnoDB被设置为默认的存储引擎。
下面附上官方InnoDB的参数：--innodb_purge_threads=1 --innodb_file_format=barracuda --innodb-buffer-pool-size=8192M --innodb_support_xa=FALSE --innodb_flush_method=O_DIRECT --innodb-flush-log-at-trx-commit=2 --innodb-log-file-size=2000M --innodb-log-buffer-size=64M --innodb-io-capacity=200 --skip-innodb-adaptive-hash-index --innodb-read-io-threads=8 --innodb-write-io-threads=8 --innodb_change_buffering=all --innodb_stats_on_metadata=off --innodb-buffer-pool-instances=12 --skip-grant-tables--max_tmp_tables=100 --query_cache_size=0 --query_cache_type=0 --max_connections=1000 --max_prepared_stmt_count=1048576 --sort_buffer_size=32768
关于InnoDB Plugin介绍，请参考MySQL5.5手册：1.1.2 充分利用CPU多核的处理能力
在MySQL5.1.X版本中，innodb_file_io_threads参数默认是4，该参数在Linux系统上是不可更改的，但Windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入输出）请求的数量。
在MySQL5.5.X版本中，或者说是在InnoDB Plugin1.0.4以后，就用两个新的参数，即innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。
假如CPU是2颗8核的，那么可以设置：innodb_read_io_threads = 8innodb_write_io_threads = 8
如果数据库的读操作比写操作多，那么可以设置：innodb_read_io_threads = 10innodb_write_io_threads = 6
也就是说，你可以根据情况加以设置。注意
这两个参数不支持动态改变，需要把该参数加入my.cnf里，修改完后重启MySQL服务，允许值的范围是1～64。
调整完成后，你可以用命令"show engine innodb status\G;"来查看调整结果，如下所示：show engine innodb status--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)
关于innodb_read_io_threads和innodb_write_io_threads参数的含义，请参见MySQL5.5手册：1.1.3 提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
在MySQL5.1.X版本中，由于代码写死，因此最多只会刷新100个脏页到磁盘、合并20个插入缓冲，即使磁盘有能力处理更多的请求，也只会处理这么多，这样在更新量较大（比如大批量INSERT）的时候，脏页刷新可能就会跟不上，导致性能下降。
而在MySQL5.5.X版本里，innodb_io_capacity参数可以动态调整刷新脏页的数量，这在一定程度上解决了这一问题。
innodb_io_capacity参数默认是200，单位是页。该参数设置的大小取决于硬盘的IOPS，即每秒的输入输出量（或读写次数）。
至于什么样的磁盘配置应该设置innodb_io_capacity参数的值是多少，大家可参考表1-1。
说明：此参数支持动态改变，但需要SUPER权限。
另外，还可以动态调整该参数，如：SET GLOBAL innodb_io_capacity = 2000;
关于innodb_io_capacity参数介绍，请参见MySQL5.5手册：1.1.4 增加自适应刷新脏页功能
这个功能是在InnoDB Plugin中引入的。InnoDB刷新脏页的规则是在如下三种情况下才会把InnoDB_Buffer_Pool的脏页刷入磁盘：
❑当超过innodb_max_dirty_pages_pct设定的值时。
❑重做日志ib_logfile文件写满了以后。
❑机器空闲的时候。
当写操作很频繁的时候，重做日志ib_logfile切换的次数就会很频繁，只要有一个写满了，就会将脏页大批量地刷入磁盘，而这会对系统的整体性能造成不小的影响。为了避免过大的磁盘I/O，innodb_adaptive_flushing会自适应刷新，它使用了一个全新的算法，以便根据重做日志ib_logfile生成的速度和刷新频率来将脏页刷入磁盘，这样重做日志ib_logfile还没有写满时，也可以刷新一定的量。
innodb_adaptive_flushing参数是默认开启的，可动态更新。
关于innodb_adaptive_flushing参数介绍，请参见MySQL5.5手册：1.1.5 让InnoDB_Buffer_Pool缓冲池中的热数据存活更久
InnoDB_Buffer_Pool缓冲区有两个区域，一个是sublist of new blocks区域（经常被访问的数据——热数据），一个是sublist of old blocks区域（不经常访问的数据）。当用户访问数据时，如果缓冲区里有相应的数据则直接返回，否则会从磁盘读入缓冲区的sublist of old blocks区域，然后再移动到sublist of new blocks区域，并通过LRU最近最少使用算法来踢出旧数据页。
但是这其中也许会存在一个问题，假如有些sql语句做统计用全表扫描，例如select * from t1，或者做一次MySQLdump，这时就会进入sublist of new blocks区域，把一些真正的热数据“踢走”，这样就会造成缓冲区的数据进进出出，导致磁盘I/O频繁。
所以从MySQL5.5.X版本开始，innodb_old_blocks_pct参数可以控制进入缓冲区sublist of old blocks区域的数量，默认是37，占整个缓冲池的比例为3/8。当全表扫描一个大表，或者做MySQLdump时，就可以将innodb_old_blocks_pct设置得小些，例如，设置innodb_old_blocks_pct=5，使数据块进入少量sublist of old blocks区域，并移动到sublist of new blocks区域，从而让更多的热数据不被踢出。当你访问一个小表，或者select查询结果很少时，则可以保持默认的innodb_old_blocks_pct=37，或者设置得更大，比如innodb_old_blocks_pct=50。
在该过程中，还会涉及另一个参数innodb_old_blocks_time。在访问sublist of old blocks区域里的数据块时，并不是马上就移动到sublist of new blocks区域，而是会先让数据停留在sublist of old blocks区域innodb_old_blocks_time（微秒），然后再移动到sublist of new blocks区域，这样就保证了sublist of new blocks区域里的数据不会马上被踢出。
用"show engine innodb status\G;"命令可以查看当前的信息：Total memory allocated 1107296256; in additional pool allocated 0Dictionary memory allocated 80360Buffer pool size 65535Free buffers 0Database pages 63920Old database pages 23600Modified db pages 34969Pending reads 32Pending writes: LRU 0, flush list 0, single page 0Pages made young 414946, not young 29306731274.75 youngs/s, 16521.90 non-youngs/sPages read 486005, created 3178, written 1605852132.37 reads/s, 3.40 creates/s, 323.74 writes/sBuffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000Pages read ahead 1510.10/s, evicted without access 0.00/sLRU len: 63920, unzip_LRU len: 0I/O sum[43690]:cur[221], unzip sum[0]:cur[0]
其中，状态信息如下所示：
❑Old database pages 23600：在旧区域存放着多少个页。
❑Pages made young：移动到新区域的有多少个页。
❑Pages made not young：没有移动到新区域的有多少个页。
❑youngs/s：每秒移动到新区域的有多少个页。
❑non-youngs/s：每秒没有移动到新区域的有多少个页。
❑young-making rate：移动到新区域的比例。
❑young-making not rate：没有移动到新区域的比例。
如果你没有全表扫描，发现youngs/s的值很小，那么就应该增大innodb_old_blocks_pct或者减少innodb_old_blocks_time。如果你进行了全表扫描，发现non-youngs/s的值很小，那么就应该增大innodb_old_blocks_time。
关于innodb_old_blocks_pct和innodb_old_blocks_time参数的介绍，请参见MySQL5.5手册：1.1.6 InnoDB的数据恢复时间加快
一般情况下，MySQL/InnoDB都是运行在普通的PC Server+Linux（UNIX）上，虽然不期待它具有小型机+AIX的高可用性，但想尽一切办法缩短MySQL的不可用时间仍然是DBA的目标。
根据经验，主机OS崩溃、硬件故障，仍然是影响MySQL可用性的最主要因素，如果这些故障都恢复了，另一个非常耗时的恢复就是InnoDB自己的恢复时间。
一般主机发生一次重启，正常大约需要小于5分钟的时间，但此时InnoDB的恢复可能需要40分钟或者更久（这依赖于Buffer Pool、脏页面比例、TPS等因素）。试想，如果每次都能够把故障恢复时间控制在10分钟之内，那么通过应用容错、Cache支持等办法，用户体验和可用时间都将有进一步的提升。
在MySQL5.5版本里，通过算法和内存管理上的改进，做到了将故障恢复时间大大缩短，这也就意味着以后事务日志redo log最大可以设置为4GB（xtraDB可以超过4GB），这样就在很大程度上降低了I/O需求，刷脏页的频率降低了，从而极大地提高了InnoDB的写性能。
在MySQL5.5里，你无须任何操作即可实现快速恢复。下面，针对MySQL5.1.59 InnoDB和MySQL5.5 InnoDB Plugin1.1.X，来一次破坏性实验，验证一下恢复时间。
实验前提：两台机器均为虚拟机，内存1GB，两台机器的参数设置一致。如下所示：innodb_log_file_size = 300Minnodb_log_files_in_group = 3innodb_log_buffer_size = 16Minnodb_max_dirty_pages_pct = 75innodb_force_recovery = 0innodb_buffer_pool_size = 600Minnodb_flush_log_at_trx_commit = 0
下面开始测试。
首先来看看命令及参数，可以看到，其中有16个并发连接，最大请求1万个，表记录有9百万条。Sysbench --test=oltp --MySQL-table-engine=innodb \--oltp-table-size=9000000 \--max-requests=10000 \--num-threads=16 \--MySQL-host=127.0.0.1 \ --MySQL-port=3306 \--MySQL-user=root \--MySQL-password=123456 \--MySQL-db=test \--MySQL-socket=/tmp/MySQL.sock prepare
接着另开一个终端，执行sleep120;pkill-9 MySQLd，让其运行2分钟后，强杀MySQL进程，然后再启动MySQL，下面来看看恢复的过程：
MySQL5.1恢复的过程：120630 21:19:19 MySQLd_safe Starting MySQLd daemon with databases from /usr/local/MySQL/data120630 21:19:19 [Note] Plugin 'FEDERATED' is disabled.120630 21:19:19 InnoDB: Initializing buffer pool, size = 600.0M120630 21:19:19 InnoDB: Completed initialization of buffer poolInnoDB: Log scan progressed past the checkpoint lsn 0 337236631120630 21:19:20 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pages from the doublewriteInnoDB: buffer...InnoDB: Doing recovery: scanned up to log sequence number 0 342479360InnoDB: Doing recovery: scanned up to log sequence number 0 347722240InnoDB: Doing recovery: scanned up to log sequence number 0 352965120InnoDB: Doing recovery: scanned up to log sequence number 0 358208000InnoDB: Doing recovery: scanned up to log sequence number 0 363450880InnoDB: Doing recovery: scanned up to log sequence number 0 368693760.........InnoDB: Doing recovery: scanned up to log sequence number 0 756666880120630 21:20:03 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completedInnoDB: Doing recovery: scanned up to log sequence number 0 761909760InnoDB: Doing recovery: scanned up to log sequence number 0 767152640.........InnoDB: Doing recovery: scanned up to log sequence number 0 997839360InnoDB: Doing recovery: scanned up to log sequence number 0 999899389120630 21:21:56 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed120630 21:22:48 InnoDB: Started; log sequence number 0 999899389120630 21:22:48 [Note] Event Scheduler: Loaded 0 events120630 21:22:48 [Note] /usr/local/MySQL/bin/MySQLd: ready for connections.Version: '5.1.59' socket: '/tmp/MySQL.sock' port: 3306 MySQL Community Server (GPL)
MySQL5.5恢复的时间：120630 21:45:39 MySQLd_safe Starting MySQLd daemon with databases from /usr/local/MySQL/data120630 21:45:39 [Warning] You need to use --log-bin to make --binlog-format work.120630 21:45:39 [Note] Plugin 'FEDERATED' is disabled.120630 21:45:39 InnoDB: The InnoDB memory heap is disabled120630 21:45:39 InnoDB: Mutexes and rw_locks use InnoDB's own implementation120630 21:45:39 InnoDB: Compressed tables use zlib 1.2.3120630 21:45:39 InnoDB: Using Linux native AIO120630 21:45:39 InnoDB: Initializing buffer pool, size = 600.0M120630 21:45:40 InnoDB: Completed initialization of buffer pool120630 21:45:40 InnoDB: highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 125985128120630 21:45:41 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pages from the doublewriteInnoDB: buffer...InnoDB: Doing recovery: scanned up to log sequence number 131227648InnoDB: Doing recovery: scanned up to log sequence number 136470528InnoDB: Doing recovery: scanned up to log sequence number 141713408InnoDB: Doing recovery: scanned up to log sequence number 146956288.........InnoDB: Doing recovery: scanned up to log sequence number 540172288InnoDB: Doing recovery: scanned up to log sequence number 545415168120630 21:46:03 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completedInnoDB: Doing recovery: scanned up to log sequence number 550658048InnoDB: Doing recovery: scanned up to log sequence number 555900928.........InnoDB: Doing recovery: scanned up to log sequence number 755130368InnoDB: Doing recovery: scanned up to log sequence number 755420180InnoDB: 1 transaction(s) which must be rolled back or cleaned upInnoDB: in total 7621 row operations to undoInnoDB: Trx id counter is 600120630 21:47:10 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completedInnoDB: Starting in background the rollback of uncommitted transactions120630 21:47:21 InnoDB: Rolling back trx with id 424, 7621 rows to undoInnoDB: Progress in percents: 1120630 21:47:21 InnoDB: Waiting for the background threads to start 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100InnoDB: Rolling back of trx id 424 completed120630 21:47:22 InnoDB: Rollback of non-prepared transactions completed120630 21:47:22 InnoDB: 1.1.8 started; log sequence number 755420180120630 21:47:23 [Note] Event Scheduler: Loaded 0 events120630 21:47:23 [Note] /usr/local/MySQL/bin/MySQLd: ready for connections.Version: '5.5.19' socket: '/tmp/MySQL.sock' port: 3306 MySQL Community Server (GPL)
再来看看两个版本各自的恢复时间：MySQL5.1 恢复时间 MySQL5.5 恢复时间3分28秒 1分44秒
从上面的结果来看，MySQL5.5恢复的时间要比MySQL5.1快两倍多。当然，这个结果涉及的因素有很多，比如，Buffer Pool、脏页面比例、TPS等，实际操作时，需要根据自己的情况来测试。
关于加快InnoDB的恢复时间，请参见MySQL5.5手册：1.1.7 InnoDB同时支持多个BufferPool实例
InnoDB用来缓存它的数据和索引的内存缓冲区的大小。把innodb_buffer_pool_size参数值设得越高，访问表中数据需要的磁盘I/O就越少。在一个专用的数据库服务器上，最高可以将这个参数值设置为机器物理内存大小的80%。尽管如此，建议还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。
innodb_buffer_pool_size是InnoDB性能的决定性因素，如果你的数据库大小小于innodb_buffer_pool_size设置的缓冲池大小，那么此时数据库的性能是最好的，因为客户端访问的数据都在内存里。
InnoDB_Buffer_Pool缓冲池复制管理着free list（初始化空闲页，为每一个page指定一个block头结构，并初始化各种mutex与rw-lock，将page加入Buffer_Pool的free list链表，等待分配）、flush list（缓冲池产生的脏页（数据库被修改，但未写入磁盘），当innodb_max_dirty_pages_pct超过设置的值时，会把修改时间较早的page刷进磁盘）、LRU（在内存中但最近又不用的数据块，按照最近最少使用算法，MySQL会根据哪些数据属于LRU而将其移出内存，从而腾出空间来加载另外的数据）等，当InnoDB_Buffer_Pool缓冲池达到好几十GB时，如果某个线程正在更新缓冲池，将会造成其他线程必须等待的瓶颈。
在MySQL5.5里，可以通过innodb_buffer_pool_instances参数来增加InnoDB_Buffer_Pool实例的个数，并使用哈希函数将读取缓存的数据页随机分配到一个缓冲池里面，这样每个缓冲区实例就可以分别管理着自己的free list、flush list和LRU，也就可以解决上面的问题了。
简单地说，就是以前是由一个人负责管理上百台服务器，当任务较多时，务必会出现差错，领导安排的任务不能在指定的时间里完成，现在改由多个人一起管理，每个人分配的机器数量少了，管理上也轻松了，这样可以更有效地提高工作效率。注意
innodb_buffer_pool_size必须大于1GB，生成InnoDB_Buffer_Pool多实例才有效，最多支持64个InnoDB_Buffer_Pool实例。
修改my.cnf配置文件，设置3个Buffer_Pool实例，添加的内容如下：innodb_buffer_pool_instances = 3
调整后，你可以用命令"show engine innodb status\G;"来查看：--------------BUFFER POOL AND MEMORY--------------Total memory allocated 1282572288; in additional pool allocated 0Dictionary memory allocated 22706Buffer pool size 76799Free buffers 76657Database pages 142Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 142, created 0, written 00.16 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 875 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 142, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]-----------------INDIVIDUAL BUFFER POOL INFO--------------------BUFFER POOL 0Buffer pool size 25600Free buffers 25506Database pages 94Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 94, created 0, written 00.08 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 917 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 94, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 25599Free buffers 25551Database pages 48Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 48, created 0, written 00.08 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 750 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 48, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 25600Free buffers 25600Database pages 0Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 0, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 0, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]
试读结束[说明：试读内容隐藏了图片]