在MySQL Connect 2013大会上,我描述了在 Facebook中MySql5.6的应用,解释了我们添加在 Facebook MySQL 5.6 源码分支的新功能. 在这篇文章里,我将描述一下在全表扫描的时候怎样才能更快。
dd if=/data/mysql/dbname/large_table.ibd of=/dev/null bs=16k iflag=direct你可能会发现即使在商业HDD服务器上,你可以达到高于比100 MB/s 乘以"驱动器数目"的速度。超过1GB/s并不少见。
mysql> SELECT buf_warmup ("db1", "large_table"); /* loading into buf pool */ mysql> SELECT * FROM large_application_table; /* in-memory select */
buf_warmup() 是一个用户自定义函数,用来读取数据库“db1"的表”large_table"的整个ibd文件。该函数需要花费时间将ibd文件从硬盘读取,但因为是顺序读取的,所以比随机读取要快的多。在我的测试当中,比普通的线性预读取快差不多5倍左右。
这证明ibd文件的顺序读取能够有效的改善吞吐率,但也存在一些缺点:
这看起来是一个足够好的解决方案,但我们的数据库设计团队想出了一个更好的解决方法叫做“逻辑预读取”(Logical Read Ahead),所以我们并不选择UDF的方法。
逻辑预读取解决了物理预读取所存在的问题。LRA使InnoDB仅读取主键page(不需要读取二级索引页面),并且每一次预读取页面的数量是可以控制的。除此之外,LRA对SQL语法不需要做任何修改。
为了使LRA工作,我们需要增加两个session变量。一个是"innodb_lra_size",用来控制预读取叶子页面(page)大小。另外一个是"innodb_lra_sleep",用来控制每一次预读取之间休眠多长时间。我们用512MB~4096MB的大小以及50毫秒的休眠时间来进行测试,到目前为止我们还没有遇到任何严重问题(例如崩溃/阻塞/不一致等)。这些session变量仅在需要进行全表的时候进行设置。在我们的应用中,mysqldump以及其他一些辅助脚本启用了逻辑预读取。
我们注意到,另外一个导致性能问题的原因是InnoDB 每次i/o仅读取一个页面,即使开启了预读取技术。每次仅读取16KB对于顺序读取来说实在是太小了,效率相比大的读取单元要低很多。
在版本5.6中,InnoDB默认使用Linux本地I/O。如果一次提交多个连续的16KB读请求,Linux在内部会将这些请求合并,读操作能够更有效的执行。不幸的是,InnoDB一次只会提交一个页面的i/o请求。我提交了一个bug report#68659.正如bug report中所写,在一个当代的HDD RAID 1+0环境中,如果我一次性提交64个连续的页面读取请求,我可以获得超过1000MB/s的硬盘读取速度;如果每次只提交一个页面读取请求,我们仅可以获得160MB/s的硬盘读取速度。
为了使LRA在我们的应用环境中更好的工作,我们修正了这个问题。在我们的MySQl中,InnoDB在调用io_submit()之前会提交多个页面i/o请求。
Table size | Without LRA | With LRA | Improvement |
10GB | 10 min 55.00 sec | 1 min 15.20 sec | 8.71x |
50GB | 52 min 44.42 sec | 6 min 36.98 sec | 7.97x |
Table size | Without LRA | With LRA | Improvement |
1GB | 7 min 33 sec | 24 sec | 18.8x |
- 在mydqldump中启用逻辑预读取 : diff
对于全表扫描来说InnoDB的工作效率不高,所以我们对它做了一定的修改。我在两方面进行了改进,一是实现了逻辑预读取;一是实现了一次提交多个async read i/o请求。对于我们生产环境中的数据库表来说,我们获得了8-18倍的性能提高,这对于减少备份时间、模式修改时间等来说是非常有用的。我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。
评论删除后,数据将无法恢复
评论(15)
引用来自“宏哥”的评论
引用来自“eechen”的评论
引用来自“weipengfei”的评论
引用来自“eechen”的评论
“我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。”
Oracle未必不收,Oracle可指望着MySQL在Windows上K掉SQL Server:
http://www.mysql.com/why-mysql/windows/index.html.zh
Oracle也不可能因MySQL的这点改善而活不成,说不定他们会内部自己实现,只在商业版提供有这个特性。Oracle是想通过社区版继续推广和扩大MySQL影响,通过商业版和技术支持把钱收回来。
就算Oracle不收,提交给MariaDB这个MySQL分支被接纳的可能性也很大,Google和淘宝就像MariaDB贡献过代码。
连数据库都没有见过
拿着菜刀喊着要打航母的玩意
一边YY去吧
引用来自“宏哥”的评论
引用来自“eechen”的评论
引用来自“weipengfei”的评论
引用来自“eechen”的评论
“我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。”
Oracle未必不收,Oracle可指望着MySQL在Windows上K掉SQL Server:
http://www.mysql.com/why-mysql/windows/index.html.zh
Oracle也不可能因MySQL的这点改善而活不成,说不定他们会内部自己实现,只在商业版提供有这个特性。Oracle是想通过社区版继续推广和扩大MySQL影响,通过商业版和技术支持把钱收回来。
就算Oracle不收,提交给MariaDB这个MySQL分支被接纳的可能性也很大,Google和淘宝就像MariaDB贡献过代码。
连数据库都没有见过
拿着菜刀喊着要打航母的玩意
一边YY去吧
引用来自“eechen”的评论
引用来自“weipengfei”的评论
引用来自“eechen”的评论
“我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。”
Oracle未必不收,Oracle可指望着MySQL在Windows上K掉SQL Server:
http://www.mysql.com/why-mysql/windows/index.html.zh
Oracle也不可能因MySQL的这点改善而活不成,说不定他们会内部自己实现,只在商业版提供有这个特性。Oracle是想通过社区版继续推广和扩大MySQL影响,通过商业版和技术支持把钱收回来。
就算Oracle不收,提交给MariaDB这个MySQL分支被接纳的可能性也很大,Google和淘宝就像MariaDB贡献过代码。
连数据库都没有见过
拿着菜刀喊着要打航母的玩意
一边YY去吧
引用来自“weipengfei”的评论
引用来自“eechen”的评论
“我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。”
Oracle未必不收,Oracle可指望着MySQL在Windows上K掉SQL Server:
http://www.mysql.com/why-mysql/windows/index.html.zh
Oracle也不可能因MySQL的这点改善而活不成,说不定他们会内部自己实现,只在商业版提供有这个特性。Oracle是想通过社区版继续推广和扩大MySQL影响,通过商业版和技术支持把钱收回来。
就算Oracle不收,提交给MariaDB这个MySQL分支被接纳的可能性也很大,Google和淘宝就像MariaDB贡献过代码。
引用来自“eechen”的评论
“我希望这些特性能够在InnoDB中获得Oracle官方支持,至少是主要的MySQL分支。”