大幅优化MySQL查询性能的奇技淫巧

发布于 2016-04-18 11:18:46 | 170 次阅读 | 评论: 0 | 来源: 网友投递

Mysql关系型数据库管理系统

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

这篇文章主要介绍了大幅优化MySQL查询性能的方法,作者根据实际运行时间比对分析了InnoDB等几个重要的MySQL性能优化点,极力推荐!需要的朋友可以参考下

回顾 MySQL / InnoDB 的改善历史。你能很容易发现。在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速，它很容易搞懂，以及在read-only（RO）有着良好的扩张性。也很期待它在read+write（RW）上达到一个较高水平。（特别是在读取数据是数据库主要工作的时候）

然而。我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中，主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望。但是RW依赖RO下。能够再次提高速度。 InnoDB 团队通过不断的改进，强烈的推进优化着5.7这个版本的每秒的性能。

下面就按顺序为大家讲解

事实上，在MySQL中只读工作量控制内部链接的方式有以下两种：

用单个表：MDL，trx_sys和lock_sys(InnoDB)
多表：trx_sys和lock_sys(主要是InnoDB)

任何很快的单表范围测试的工作量主要由于MDL链接导致锁住。而多表将会由于InnoDB内部构件限制(不同的表将由不同的MDL锁保护，所以这种情况下MDL中的链接瓶颈将会降低)。但是同样，也要看工作量的大小--一个比一般多的只读工作测量将会在MySQL5.6中表现的会更好(如Sysbench OLTP_RO),同时在工作量少而快的查询(如Sysbench Point-Selects(用外键去取一个记录))将会使所有链接变得困难，而且只能在16核-HT中测量，而在32核中表现很差..但是任何如Point-Select测试的工作量将在所有MySQL内部构件一起工作是会让你看到可能达到最大的性能(开始用SQL解析器，终止与取行值)..在你给定的MySQL版本和给定的HW配置下，这也可能达到最大SQL 查询/每秒(QPS)率。

在Mysql5.6上我们获得的最佳结果是25万个查询每秒，这也是那段时间Mysql/InnoDb上使用SQL语句查询得到的最好的结果了。

当然，只有在使用‘只读事务'功能才能达到这么高速度（Mysql5.6上的新功能）；另外，需要使用AUTOCOMMIT=1，否则CPU就会被轻易地浪费在启动事务、提交事务上，你会实际上损失系统的整体性能。

因此，在Mysql5.7上介绍的第一个改进是‘只读事务的自动发现'（实际上每个InnoDb事务都被认为是只读的直到有一个DML声明在此之外）功能---，这很大程度上简化了只读事务功能，节省了用户和开发者的时间，他们不用再去管理是否采用只读事务功能。但是，使用这个功能你仍然不能达到Mysql潜在的最佳每秒查询率，因为CPU时间还是浪费在事务的开启、结束状态处理过程当中。

同时，Percona用不同的的方案来解决“事务列表”管理(TRX-列表)及在InnoDB中trx_sys互斥链接慢的问题。Percona的解决方案在用事务处理Point-Selects高负载时能表现良好，但MySQL5.7表现一般(但我不会公布5.7的结果，因为它的代码不公开)...所以，至少我现在可以做一些比较：

观察结果:

在MySQL5.6，Percona 5.5和MySQL5.7中的8个表中用同样的Roint-Select-TRX只读测试(用事务)(2013.5月的结果)
同时你也可以看到，在同样的16核-HT配置下我们离峰值25万/s的结果还很远。
MySQL5.6在trx_sys互斥访问中延长了链接时间，而且自从64个用户后每秒的请求数将减少。
Percona5.5能维持很长的时间的负载，每秒请求在512个用户时才开始减少
当MySQL5.7已经保持一段时间时，每秒请求依然没有减少(对于更多用户并发的情况你在这幅图里是看不到的)...

然而，很明显，如果用MySQL想要得到最大的潜在每秒查询速率，事务应当避免。

让我们来看一看这是2013年5月我们的每秒最大查询速率。

在同一点八张表进行测试，但是没有使用MySQL5.6的事物：

观察：

上面的测试是保持MySQL5.6始终执行在16核上，然后是16芯-HT,32核，32芯-HT.
正如你所看到的，最大的每秒查询速率比预期的还要大 -—— 在MySQL上是每秒27.5万
最大的结果已经达到16芯-HT.
然而在32核上的结果并没有16芯-HT上的好（由于竞争中断，在相同内核中，具有2CPU线程的配置能够更好的管理线程竞争——所以真正的并发性仍保存在16线程，而不是32核上）

而在MySQL5.7上做同样的测试却看起来大有不同，因为在5.7中lock_sys互斥链接的时间段已经很低了，同时trx_sys互斥相关代码也得到第一次变化的情形：

观察结果：

首先你可以看到5.7在同样的16核-HT配置下的性能已经比5.6的要好
之后，在32核配置下没有明显的增强！
在32核-HT配置下达到了35万/秒的最大请求！
从上面特殊(具有攻击性)只读负载测试的情况下可以容易看出我们在32核中得到的结果要比16的好，同时我们还没有启动超线程(在32核-HT)...牛吧！；-）

从另一方面来讲，仍然有改进的空间这点还是很清晰的。有关trx_sys的争用仍然在持续。我们没有充分的使用CPU的能力来做有用的工作（仍然有许多CPU周期用在锁的轮转）...不过现在的结果比以前好多了，并且比5.6好很多，因此没有理由继续挖掘来提高这方面的性能，我们主要集中在我们曾经花费了巨大的空间的读写负载的性能提高上。

到了5月底，也就是我们的性能会议期间，Sunny给try_sys互斥争用增加了几个新的更改，从那以后最大的每秒可进行的查询（QPS）可达到375K！这是不是对5.7进行了足够的性能提高，对吗？;-)

同时，我们继续与建议用其他方式管理TRX列表的Percona团队交换了意见，他们的方案看起来非常有趣，不过在5.5上，这样的代码却不能展示出更高的每秒可进行的查询数（QPS），而且在5.6上的这样代码（曾经测试过Percona Server 5.6）最大的每秒可进行的查询数（QPS）也不会比在MySQL 5.6上大。然而，讨论涉及到一个有趣的观点：如果同时有一些读写负载在运行的话，它对只读性能有什么影响呢？...而且，即使在同样的测试条件下MySQL 5.7代码仍然运行的要好一些，效果是非常明显的（你可以在这儿查看我的分析，然而，再次说明一下，这段时间内我不能展示５.7上的结果，因为它的代码还没有对大众公布－也许会在以后的一篇文章中给出）..

由于这儿同时对任何纯粹的读写负载也有影响，因此有足够的动机以Sunnys很长时间所期待的那样重新写整个TRX列表相关的代码，然而，这种经历简直让人痴迷！

;-)) 日复一日，我们很高兴的看到我们的每秒可进行的查询图逐渐变高，直到在同一个32核的超线程服务器上达到了每秒可进行的查询440K!

5.7开发里程碑发布2上进行的Select 8个表所得到的结果数：

不需要说明..;-))

然而，有一个小小的令人奇怪的地方－我们试图与Sunny通过不同的工具分析所有瓶颈和代码更改所带来的影响。而且在某些测试里，令我吃惊的是Sunny观察到比我更高的每秒可进行的查询数..这个“奇异之处”与下面因素相关：

在高负载下，现在的５.7代码都运行在接近硬件极限（主要是CPU)的位置，因此每条指令都非常重要！
如果使用的Unix套接字或者IP端口，那么区分就会非常明显！
Sysbench自身使用了30%的CPU时间，不过同样的测试负载使用的是（具有更短的代码路径的）老版本的Sysbench的话，它将只使用20%CPU，剩余的10%用在MySQL服务器上。
因此，同样测试负载的情况下，使用Unix套接字而不是IP 端口，并且使用Sysbench-0.4.8替代Sysbench-0.4.13的话，我们将得到每秒可进行的查询数超过500K!-很容易，不是吗？;-))

让我们来比较“之前”和“之后”的差异

观察结果：

通过Sysbench降低了CPU的使用率。
在MySQL服务器上具有更高的CPU可用性。
我们实现了50万每秒查询。

还有什么呢？

我可能只提到：kudos Sunny和整个MySQL的开发团队；

让我们看一下现在选择8张表工作负载的情况下的最大每秒查询。

MySQL-5.7.2 (DMR2)
MySQL-5.6.14
MySQL-5.5.33
Percona Server 5.6.13-rc60.5
Percona Server 5.5.33-rel31.1
MariaDB-10.0.4
MariaDB-5.5.32

每个引擎都在以下配置下进行测试：

CPU taskset: 8核-HT,16核，16核-HT,32核，32核-HT
并发会话数：8，16，32 ... 1024
InnoDB自旋等待延时：6，96

最好的结果是来自任意两个特定的组合间的比较。通过对数据库引擎的比较，我得到了下面的一个图表，这个图表我在以前的文章中已经提到过了。

面是一些评论：

对Mysql5.7的巨大差距结果不需要做过多的评论，因为这是很明显的。
那么，有趣的是基于MySQL5.5的代码库引擎没有任何的接近MySQL5.6的结果。
这已经证实了在使用MySQL5.6的代码库引擎之后，Percona Server达到了MySQL5.6的水平，然而MariaDB-10仍然还在探索的路上。
因此，毫无疑问，MySQL5.6是代码的基石!
MySQL5.7是在MySQL5.6基础上的再一次优化扩展。

具有什么样的扩展性呢？

答案是简单的：MySQL5.7是唯一在此基础上进行扩展的。

如果使用ip端口和一个重量级的Sysbench-0.4.13，会得到如下的结果：

QPS只是稍微的略低一点，但是总体的趋势是完全一样的。

可扩展性也是非常的相似：

注意：对一个单表绑定过多的工作负载是不好的：

减少InnoDB间的争论使得其他的争论更加的明显。
当负载是绑定在一张单表上时候，MDL的争论将变得更加主导。
这是预期希望的，我们在下一个DMRS上将保持不变。

还有很多挑战摆在我们面前;-)
作为参考，我上述测试的硬件配置信息如下：

Server : 32cores-HT (bi-thread) Intel 2300Mhz, 128GB RAM
OS : Oracle Linux 6.2
FS : 启用"noatime,nodiratime,nobarrier"挂载的EXT4

my.conf:

max_connections=4000

 key_buffer_size=200M

 low_priority_updates=1

 table_open_cache = 8000

 back_log=1500

 query_cache_type=0

 table_open_cache_instances=16

# files
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000

# buffers
innodb_buffer_pool_size=32000M
innodb_buffer_pool_instances=32
innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=32K

# innodb
innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
innodb_spin_wait_delay= 6 / 96

# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0

# monitoring
innodb_monitor_enable = '%'
performance_schema=OFF

使用UNIX socket来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

使用IP端口来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

大幅优化MySQL查询性能的奇技淫巧

Mysql关系型数据库管理系统

后端技术

前端技术

数据库

热门框架

常用IDE

其他