阿里巴巴DBA谈MySQL优化与新特性（转载）

转载自：

以下是阿里巴巴B2B 彭立勋主题为“浅谈伪分布式数据库架构”的演讲全文：
阿里巴巴DBA谈MySQL优化与新特性
▲阿里巴巴B2B 彭立勋
　　我们看今天的主题，第一个是挖掘MySQL的性能，我们会在从系统的各个方面，讲出一些方法把MySQL的尽可能的调优。另外随着MySQL5.5的发布，我们会讲一些Plugin的新特性，还有Percona带来的改进，下面是我们分布式的研究。
　　我们对MySQL下面几个方面做了一些改进，一个是优化编译系统，ICC取代GCC，另外一个是优化文件系统，XFS取代EXT3，还有优化存储，SSD取代SAS。
Handler Socket取代SQL。

　　我们看从ICC取代GCC，我们为什么取代ICC的方案，因为ICC更可以有效的利用，在纯浮点运算的效果高。还有一些ICC内置英特尔Math Lib数学函数库，提升数学函数效率。数学函数在一个以运算为主的系统中，它对系统整理的效率影响是非常大的，后面是英特尔自己重写了整个库。他遇到一些线程能不崩溃的时候影响非常大，但ICC显示即使是线程中出现一些情况，在导致主程序发展里面少的很多，所以最后决定采用ICC编译MySQL的源码。不适用GCC的原因就是它的优化等级比较低的。


　　这是ICC跟GCC的对比，上面是2亿次浮点运算的速度，分别是GCC3、GCC4，GCC3的时候我们看到，执行的时间进行2亿次浮点运算大概是跟另外一个比例参照的话，
ICC只要跑1.246秒，如果以GCC3为主的话，ICC是它的1.205倍，就是在完全不动的情况下，可以获得20.5%的性能提升。

　　另外测了一下GCC4，无论在稳定上和效率上较GCC3都有下降，所以它就不考虑了，等成熟一段时间再说。我们对数据库方面的测试是我用ICC就通过O3优化，在这样的情况下对100M的数据进行SQL聚合计算，如果以最慢为基准的话，ICCO3优化提升大约是34%的性能。这样带有比较复杂的预算系统来说，用ICC会让你预算跑的更快。

　　我们看一下下一个就是XFS取代EXT3，本人认为XFS并没有对EXT3提升非常多，这个我自己感觉也差不太多，XFS在小的随机读的时候跟EXT3差别不大，但如果遇到表特别大，单表数据特别大的时候，XFS比EXT3的优势完全体现出来了。下一代文件系统就是BTSF完全也是采用这种方式，也是更高效的速度方式，无论找任何一个点最差的时候就是若干的时候就可以找到。EXT3虽然是一个日志系统，XFS做的时候也是这样做的，像XFS开日志与不开日志的情况基本上没有什么影响。另外XFS采用了延迟分配，你在内存操作的时候，都把这些零散的数据块进行组装，组装成一个大块的时候再写入磁盘当中。所以当我们数据在XFS上，可以看到虽然我在不断往里面插很多数据，但看到TPS很低，内存很大，就在内存中缓存了这些操作。磁盘中只是写了一个日志而已。而且万一开始之后，重新启动自检的速度非常快，如果EXT3的话至少要2个小时，XFS2分钟就可以启动了。

　　这是其中的一个测试案例，就是我们用64K顺序INSERT效率，在这种情况下，我们比较了插入速度，XFS大概是295行每秒，EXT3是210行每秒，大概快了40%。

　　下一个是SSD取代SAS，就是英特尔SSD盘，最高可以读几万的IO。但很多情况下读写是不对称的，这也是因为SSD的一些特性，SAS盘里是有覆盖的，将一块数据改掉原来的数据块。因为是词嘛，所以原来的不用管，但在SSD上是不行的，SSD哪怕要修改一个位，也要512K的位改掉，再把这个整个写入SSD上，所以说SSD上读写能够是不对称的，写是相对较慢的。
　　但是有一个更新的革命性的产品将读写不对称的这部分解决了，就是IO，改变了目前磁盘与通信的架构，不再是通过主板的口连接CPU，而是他们插PCI槽，是一张卡，是PCI的存储片，通过PCI的总线直接与CPU进行交互。这里可以测到读写基本对称，都可以突破4万左右。所以这是我们下一步在高负载的系统上准备采用的方案，就是PCI卡作为二级缓存。后来经过一些调优，我们基本上可以达到8块SSD盘效果的三分之二左右，所以我们认为直接写到SAS上是没有问题的。就把这个容量全部空出来作为二级缓存，二级缓存这个方式一般都是facebook这种。任何写操作就通过facebook插进，直接写入盘，读取的时候直接在上面读。通过这样的方式减少IO。
　　这个测试，8K行K-V方式效率，SSD可以看到2万999行，每秒大概写600多行左右，峰值大概是七八千。SAS是读是七千一，写的话大概是将近四千的样子。

      下一个是Handler Socket取代SQL，绕过费时的SQL解析，简单SQL通过API直接访问InnoDB，不再用SQL这个词，这样对一个简单的SQL性能提升是非常大的。就是说你所有往他那个断口发送的信息都截流了，再传给InnoDB，不会对MySQL本身太多的干扰。我们做了一些简单的Handler Socket与SQL处理效率的对比，左边是处理的行数，千行为代表，平均响应的时间是六秒，最大的时间是十毫秒，平均下来响应是比较快的。如果是SQL每秒处理一行大概三百个左右，插入的时候，如果是走SQL的话是8100行每秒左右。如果走Handler Socket的话，大概是三万一千行，我们仅仅绕过SQL解析就带来快4倍的性能提升。这个性能提升是非常大，处理完全是一样的处理，我们可以看到，在数据库中把SQL去掉我们速度可以提升多少。另外一个是Handler Socket的，这个量很大，平均响应的时间是下降的。因为每秒处理得量非常多，这也是内部应该锁定的问题，太快的时候，是需要一些时间，这样可能会带来一些轻微的阻塞，但相对于轻微的时间来说我们认为这个是可以接受的。一般我们认为低于1毫秒的情况都是可以接受的。而在SQL的话低于100毫秒都是可以接受的。因为在用户感觉来说，10毫秒跟100毫秒的用户感觉来说没有必要，所以没有感觉抠的那么细。

　　然后我们看一下InnoDB Plugin的新特性，一般来说我们的页面设置大小都是8K，因为这种系统随机读的操作比较多，顺序读操作比较少的，或者是当存储用的情况。随即修改情况比较多的情况下，我们使用新的页面格式，8K左右页面大小，相对小的话速度会快一些。

　　另外一个是如果使用扩展页，他必须保证你一行之内，一个配置之内必须放的下两行，因为页里面只有一行的话，这个数就是一条限定法文，没有起到任何法文效率，所以里面至少放两行。如果两行的话，那你行很大，就使用扩展页。把你全部通过一个指针引出去。

      一般我们的经验告诉我们文本类型压缩比例是非常高的，文本压缩一般是5到10倍的压缩，本来要读10页，现在要读1页，这样读的页数少了很多，对你写带来的代价完全是值得的。


　　下一个是开发的IO参数，在InnoDB里面有一个参数，里面会有四个循环，主要的就是一个主循环，每一秒钟要刷最多100页到磁盘。但InnoDB最初设计的时候，是很小型系统的时候做的这种设计。但我们现在有了这个强大的SSD系统之后，这个系统就远远落后我们磁盘所承受的能力，这时候就要把量提升。所以在InnoDB Plugin就把刷新页的数量参数做出来了，其中一个就是IO容量，原来的情况是每秒刷100个，看完这个是每秒是刷100乘以这个IO容量，你设为一千就是一千，一百就是一百，这个默认就是一百。

　　同时InnoDB把读写的性能开了多个，但个读写性能不是我们访问的那个，而是日志和页刷新读取的IO线程。最初的情况下，InnoDB为了减少一些并发的问题，应该是一个线程，后来改成了四个线程，现在我们SSD盘上四个线程依然不能发挥威力，所以直接自己调，要么自己写多少，一般来说我们这边的和数差不多，不要超过那个和数，超过和数的话可能有一些切换，那样代价高起来了。所以我们建议跟和数差不多。

　　那个是对InnoDB最直接的带来效率提升就是脏页刷新方式。之前InnoDB不管怎样都是走一套固定的刷新流程刷新，这样你压力很大的时候就会发现你磁盘的IO，TPS是非常的振荡。最早的时候，5.1的时候刷新（刷盘）的方式是有一些问题的，必须攒到一个很大的量，到70%。70%的时候刷一大片，这样导致磁盘的压力非常大。就对性能产生很大的振荡，如果这时候振荡的高峰正好与你的业务高峰重合。

　　然后看这两种，一个是InnoDB自己的改进。就选“是”、“否”这样打开，然后通过日志剩余量空间除以日志产生速度算出切换前可以使用的时间。还有必须在日志切换前完成所有脏页的刷盘，所以可以计算出刷新的速度。除了这个以后，还有一个刷新机制，最后就会把这三条的量减去InnoDB自己的替换Flush的速度减掉，然后就可以知道日志切换之前把这个刷完了。就不用等到70%再启动。

　　我们看一下
这个是Adaptive（自适应刷新），
None是关闭，
reflex（反射）是脏页少于1/2则不刷新，超过1/2则使用Weak Flush，超过四分之三，则使用Strong flush，大于八分之七将不断的刷新。
Estimate，脏页少于二分之一则不断刷新，超过二分之一则基于未刷新的脏页量、日志产生速度、刷新页的Modified Age来计算刷新速度，大于八分之一将不断刷新。

　　我们也看一下自适应刷新的方式，这个InnoDB的情况大家应该可以看到，非常的振荡。Strong flush这个就是InnoDB自己做的横线。这样可以看到，上面的线振荡也是有一些比默认flush的看法是大一些，整体效率有提高，线条数量的增加，时间的退役量逐渐的稳定下来，波动量慢慢的趋于稳定。但这样的flush我们也看到相反的InnoDB的方式，时间慢慢的推移，振动反而是慢慢的增长的趋势。这里一共是跑了30分钟。

　　这是MySQL InnoDB里面IO流程。这个可能看不太清楚，最主要是中间红色的地方，就是InnoDB做的改进，很关键的一点就是那个循环里面，发现最近一秒的操作，再往后脏页比例的时候，开始就走入这条线。开始的话就按我刚刚说的算法预算，如果满足的话就在后台进行相应的算盘刷新。其中反复刷新的就是在最右边的一块。这个代码在InnoDB里面叫SRV.SRV.CC。这里面有四个循环也标记标出来。

　　后面看Percona带来的改进，这个就是Percona自己做的东西，第一个提升很大的就是提升Buffer pool的扩展性。这个原则上写应该是不应该读的，InnoDB读写应该是不冲突的，但因为Buffer pool在这里。再往后是提高IO的扩展性，除了我刚刚讲的之外，还有可以增加许多变量去调整IO到最佳状态。

　　后面是多个回滚段，未提供一致性读，InnoDB将事务修改的数据写到回滚段。回滚段被一个独立的Mutex保护，这直接导致了写密集型的工作并发不高。然后是更高的并发数，InnoDB里面有一个事务槽或者回滚槽，在一个DB里面就提供了2047，加了两倍。

　　这个是Percona带来的改进二，专用的Purge线程，如果有很多事务，Purge线程清理空间不够快，共享表空间将急剧增长。不这将导致性能严重下降，甚至可能用完所有的磁盘空间，XtraDB使用了专用的线程来清理undo space，这对undo space的清理速度可以提升很多。尽管这可能使整体的性能降低，但是可以大大提高稳定性，因而整体性能略微降低是值得的。

　　另一个是DoubleWrite(双写)的缓冲，XtraDB提供了一个选项将DoubleWrite放在一个独立的磁盘来提升并发性能。这样的话XtraDB就把它拆出来，DoubleWrite就不于undo space用了，就避免跟undo space争抢这个磁盘的性能。

　　下面是删除过多的函数调用。当MySQL从socket读数据时，将产生很多fcntl调用，并导致并发性能下降，Percona移出了多余的调用。

　　后面我讲一下我们现在正在研究一个新的数据库分布架构，我们居于一个业务场景，我们会发现我们大多数的系统，数据量非常庞大，但真正变动的数据是很少的，不多，一天的变动数据一台机器绝对放的下，而且可能还有余量，甚至内存都可以放下。这样我们怎么做呢?把内存拆为两部分，一个是静态服务器，一个是动态服务器，静态服务器是每天不动的，动态服务器记录我当天的变更，比如说半天或者说一天统一的集中到我们的静态服务器当中。保证我们的静态服务器做一个一次性完整的更新，而查询的时候，静态服务器一批数据，动态服务器一批数据，两批数据进行组合，组合完成以后再把这个数据呈现给用户。这样的话就避免一个分布式事务。因为所有的变更都只在一台服务器上做，没有跨越其他的节点，就是标准的本地的事务。
　　这是我们设计的一个程序结构，就机遇这样的架构。首先第一很多的分布规则，分布规则定义好了以后，所有东西插入进来都会读取我们这个分布规则。……动态服务器这些数据就会通过我们配置的规则，路由到里面批量的做…，当我们查询的时候，我们通过分布规则一读就知道数据在哪个静态库中，另外一个读我们的动态库，当两个数据都到了之后，对它进行一个合并，把…的合作填进去，…的操作也填充入，这样组合的结构，统一的返回给用户，这样就完全避免了分布失误的问题。
　　另外，这里还有一个好处是，我们可以实现在线DDL，你有一个变更只要提前一天告诉我就可以了，我在静态服务器上可以先做这个DDL，这个DDL不阻塞读，只堵塞写。12点前这个结构变更完了，这个对用户完全是透明的，动态服务器晚上刷到静态服务器的时候，静态服务器就相对空闲，这时候就把DDL做掉，这时候基本上可以做到无堵塞的DDL。
　　这是具体的一些操作，先做哪后做哪，这样按照这个东西做的话，几乎做所有的都是不堵塞的，只要算好时间就可以了。就不会像目前那种简单的分布式的架构，又读又写。


总结：
ICC编译器取代GCC编译器（编译MySQL源码  我们可以做） 
XFS文件系统取代EXT3文件系统（我们可以做）
SSD取代SAS
Handler Socket取代SQL（我们可以做）
新的页面格式，8K左右页面，修改MySQL源代码
文本类型压缩，相当于SQL Server的行压缩和页压缩（我们可以做）