亲宝软件园·资讯

展开

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

人气:0
这篇文章主要介绍了大幅优化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链接导致锁住。而多表将会由于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的结果，因为它的代码不公开)...所以，至少我现在可以做一些比较：

2015625105834295.png (915×518)

 观察结果:


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

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

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

2015625105905243.png (931×517)

 观察：


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

2015625105930124.png (932×520)

 观察结果：


从另一方面来讲，仍然有改进的空间这点还是很清晰的。有关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个表所得到的结果数：

2015625110006777.png (860×342)

 不需要说明..;-))

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

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

2015625110030474.png (845×389)

 观察结果：

还有什么呢？

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

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

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

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

2015625110054565.png (720×248)

面是一些评论：

具有什么样的扩展性呢？

2015625110113878.png (720×248)

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

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

2015625110135850.png (720×248)

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

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

2015625110203829.png (720×248)

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

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


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


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


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

复制代码 代码如下:
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 &


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

复制代码 代码如下:
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 &

加载全部内容
相关教程
猜你喜欢
用户评论