大幅优化MySQL查询性能的奇技淫巧 |
|
本文标签：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链接导致锁住 。而多表将会由于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的结果，因为它的代码不公开)...所以，至少我现在可以做一些比较：
观察结果:
让我们来看一看这是2013年5月我们的每秒最大查询速率 。 在同一点八张表进行测试，但是没有使用MySQL5.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上的结果，因为它的代码还没有对大众公布－也许会在以后的一篇文章中给出）.. ;-)) 日复一日，我们很高兴的看到我们的每秒可进行的查询图逐渐变高，直到在同一个32核的超线程服务器上达到了每秒可进行的查询440K! 5.7开发里程碑发布2上进行的Select 8个表所得到的结果数：
不需要说明..;-)) 然而，有一个小小的令人奇怪的地方－我们试图与Sunny通过不同的工具分析所有瓶颈和代码更改所带来的影响 。而且在某些测试里，令我吃惊的是Sunny观察到比我更高的每秒可进行的查询数..这个“奇异之处”与下面因素相关：
让我们来比较“之前”和“之后”的差异
观察结果：
还有什么呢？ 我可能只提到：kudos Sunny和整个MySQL的开发团队； 让我们看一下现在选择8张表工作负载的情况下的最大每秒查询 。
每个引擎都在以下配置下进行测试：
最好的结果是来自任意两个特定的组合间的比较 。通过对数据库引擎的比较，我得到了下面的一个图表，这个图表我在以前的文章中已经提到过了 。
面是一些评论：
具有什么样的扩展性呢？
答案是简单的：MySQL5.7是唯一在此基础上进行扩展的 。 如果使用ip端口和一个重量级的Sysbench-0.4.13，会得到如下的结果：
QPS只是稍微的略低一点，但是总体的趋势是完全一样的 。 可扩展性也是非常的相似：
注意：对一个单表绑定过多的工作负载是不好的：
还有很多挑战摆在我们面前;-)
复制代码 代码如下: 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 # buffers # innodb # perf special # monitoring
复制代码 代码如下: LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.8 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \
--oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \ --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \ --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \ --mysql-table-engine=INNODB --db-driver=mysql \ --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \ --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \ --oltp-read-only=on run > /tmp/test_$n.log &
复制代码 代码如下: LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.13 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \ --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \ --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 \ --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \ --mysql-table-engine=INNODB --db-driver=mysql \ --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \ --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \ --oltp-read-only=on run > /tmp/test_$n.log & |
