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

回顾历史的MySQL InnoDB /改进。你可以很容易找到它。在MySQL 5.6稳定版从来没有这么快在只读的速度，很容易理解，和只读（RO）具有很好的扩展。它也将达到一个高水平的读写（RW）。（尤其是当读取数据是数据库的主要工作）

不过，我们也非常高兴地看到在MySQL 5.6中的RO的性能。在5.7版中，主要的工作集中在读写（RW），因为我们没有在处理大数据达到了我们的预期。但RW取决于RO，又能提高速度。InnoDB团队优化5.7版本每秒的性能通过不断改进。

以下将依次解释

实际上，MySQL只读工作控制中有两种类型的内部链接：

使用一个单一的表：MDL，trx_sys，和lock_sys（InnoDB）
多个表：trx_sys和lock_sys（主要是InnoDB）
任何快速的单表范围测试的工作量主要是由于MDL链接锁，多个表将由InnoDB内部有限的（不同的表会被不同的MDL锁，所以在这种情况下，在MDL的链路瓶颈将减少），但它也取决于工作负荷测量的大小只会比一般的mysql5.6性能会更好的工作（如过oltp_ro），同时减少工作量，快速查询（如过点选择（一个外键的记录将使所有链接））变得很困难，只能在16核-羟色胺测定，性能较差。在32个核测试中如点选择，但任何工作量都将在所有MySQL内部组件一起工作，将让您看到可能达到最大性能（从SQL解析器开始，并结束获取值）。在你的MySQL版本和硬件配置，也可以达到每秒最大SQL查询（QPS）率。

我们相处的mysql5.6最好的结果是每秒25万个查询，这也是使用MySQL InnoDB / SQL，查询效果最好。

当然，只有在使用只读事务的功能达到这样高的速度（新mysql5.6）；此外，需要使用自动提交= 1或CPU将很容易浪费在开始一个事务，提交事务，你将实际系统的总体性能损失。

因此，首先介绍了mysql5.7的改善是只读事务的自动发现（事实上，每个InnoDB事务是只读到另外一个DML语句）功能，大大简化了只读事务功能，节省时间的用户和开发者，他们没有是否为只读事务管理功能。然而，使用这种功能，你仍然不能实现MySQL每秒查询率最好的潜力，因为CPU时间是开放和结束交易过程浪费。

同时，Percona的使用不同的方法来解决数据管理问题（TRX列表）和慢trx_sys互斥链接在InnoDB，Percona的解决方案能够很好的处理点选择高负荷，但mysql5.7执行一般（但我不会公布5.7个结果，因为它的代码是不公共）。所以，至少，我现在可以做一些比较了。
观察结果：

在mysql5.6 8桌，Percona 5.5和mysql5.7，相同点选择TRX只读测试（交易）（2013.5个月的结果）
同时，您可以看到，在相同的16个核心- HT配置下，我们还远远没有达到峰值25万秒/秒的效果。
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万5000次。
最大的结果达到了16核心HT。
然而，32个核心上的结果不如16个核心的结果好。（由于竞争，中断在同一个内核线程2cpu配置可以更好地管理线程竞争，所以真正的并发仍然保存在16线代替32芯）。

虽然这样做对mysql5.7相同的测试，它看起来完全不同，因为在5.7，对lock_sys互斥链接的时间是非常低的，和trx_sys互斥代码也首次改变。
观察结果：

首先，您可以看到5.7的性能优于5.6在相同的16核心HT配置。
之后，在32个核心配置中没有明显的增强！
在32个核心- HT配置下达到35万秒的最大请求！
从上面的特殊的（积极的）只读负载测试案例，可以看出，在32个核心我们得到更好的结果比16，我们还没开始超线程在32核-羟色胺。 uff1b uff09

另一方面，它仍然是清晰的，仍有改进的余地，trx_sys的争夺仍在继续。我们没有充分的利用CPU的能力做有用的工作（还有很多在锁转动CPU周期）…但是现在它比以前好多了，而且比5.6要好得多，所以没有理由继续在这方面提高采矿的性能，我们主要集中在我们所写的文字上，加载了巨大的空间来提高阅读。

月底，是我们会晴到try_sys互斥竞争表演时增加了一些新的变化，由于每秒最高可查询（QPS）可以达到375k！这足以提高5.7的性能吗-）；

同时，我们继续使用其他的方式来管理Percona团队的TRX列表交流意见和建议，他们计划看起来很有趣，但在5.5，这个代码不显示第二高可以查询号码（QPS），和5.6的代码已经过测试（Percona 5.6）查询服务器每秒最多（QPS）不会超过MySQL 5.6大。然而，这就牵涉到一个有趣的观点：如果有读写负载运行在同一时间，它有什么影响对只读的表现吗此外，一些更好…即使在同一个测试下MySQL 5.7代码仍在运行，效果非常明显（在这里您可以检查我的分析，但是，再一次，这一次我不能显示在5.7个结果，因为它的代码还没有发布给公众-可能这篇文章在未来）。
因为在同一时间，它对任何纯读写负载的影响，所以有足够的动力去重写整个TRX列表相关的代码与舞乐的长期展望。然而，这一经历绝对令人着迷。

一天又一天，我们高兴地看到，我们的查询地图可以增加每一秒，直到查询440k可以在相同的32核超线程服务器每秒进行。

5.7开发里程碑发布了从2进行的8个选择表获得的结果的数量。
不需要解释……-）

然而，有一个小的和奇怪的地方-我们正在试图分析所有的瓶颈和代码变化的影响与阳光与不同的工具。在一些测试中，令我惊讶的是，阳光观察比我更高的查询每秒的数量…奇点与下列因素有关：

在高负载下，5.7代码现在接近硬件限制（主要是CPU），所以每个指令都非常重要！
如果使用UNIX套接字或IP端口，区别将非常明显！
过本身使用30%的CPU时间，但相同的负载测试使用过的旧版本，具有较短的代码路径。它将只使用20% CPU，其余10%将在MySQL服务器上使用。
因此，在相同的试验载荷的情况下，使用UNIX套接字代替IP端口，并使用sysbench-0.4.8代替sysbench-0.4.13，我们会查询每秒超过500K以上！-很容易，不是吗-）；
让我们比较一下上一个和后一个的差别。
观察结果：

CPU的使用减少了过。
MySQL服务器上有更高的CPU可用性。
我们实现了每秒50万的查询。
还有什么

我可以说：荣誉的阳光和开发团队的整个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
每个引擎在以下配置下进行测试：

8核心CPU taskset：HT、16芯、16芯32芯、32芯的HT，HT
并发会话数：8, 16, 32…一千零二十四
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低一点点，但总的趋势是完全相同的。

可伸缩性也非常相似：
注意：单个表绑定太多的工作负载是不好的：

我们之间的争论的减少使争论更加明显。
当负载被绑定到一个单一的表，MDL的辩论将变得越来越重要。
这是预期的，我们将在下一个区域是相同的。
我们面前有许多挑战。—

作为参考，上述测试的硬件配置信息如下：

服务器：32cores HT（双线）英特尔2300MHz，128gb RAM
操作系统：Oracle Linux 6.2
FS：启用ext4挂载noatime，nodiratime，nobarrier

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

#文件

innodb_file_per_table

innodb_log_file_size = 1024m

innodb_log_files_in_group = 3

innodb_open_files = 4000

#缓冲器

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的特殊

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

#监测

innodb_monitor_enable =%

performance_schema =关闭
如果你需要的话，Linux Sysbench的二进制版本在这里：

sysbench-0.4.13-lux86
sysbench-0.4.8-lux86

使用UNIX套接字运行点选择试验过的命令如下（从8平行过程）：

复制代码如下：ld_preload = / usr / / / libjemalloc.so lib64 BMK / sysbench-0.4.8——NUM THREADS = 1美元--测试= OLTP，OLTP表大小= 10000000

——OLTP dist类型=均匀——OLTP表名称= sbtest_10m_ $ N

迈克斯请求= 0 -最大的时间= 2美元-- MySQL插座= / /到ssd_raid0

——MySQL用户=暗淡-- MySQL密码=昏暗-- MySQL DB =过

MySQL InnoDB引擎——表——DB驱动MySQL =

——OLTP点选择= 1 = 0，OLTP简单的范围，OLTP和范围= 0

——秩序范围= 0，联机事务处理OLTP不同范围= 0 - OLTP跳过TRX =上

——OLTP只读=运行> / / test_ TMP n.log美元
使用IP端口运行点选择试验过的命令如下（从8平行过程）：

复制代码如下：ld_preload = / usr / / / libjemalloc.so lib64 BMK / sysbench-0.4.13——NUM THREADS = 1美元--测试= OLTP，OLTP表大小= 10000000

——OLTP dist类型=均匀——OLTP表名称= sbtest_10m_ $ N

迈克斯请求= 0 -最大的时间= 2美元-- MySQL主机= 127.0.0.1 -- MySQL端口= 5700

——MySQL用户=暗淡-- MySQL密码=昏暗-- MySQL DB =过

MySQL InnoDB引擎——表——DB驱动MySQL =

——OLTP点选择= 1 = 0，OLTP简单的范围，OLTP和范围= 0

——秩序范围= 0，联机事务处理OLTP不同范围= 0 - OLTP跳过TRX =上

——OLTP只读=运行> / / test_ TMP n.log美元