Use MySQL5.7 to achieve 500,000 high-performance queries per second

[Introduction] This article provides the details of the article MySql5 7 achieves 50W queries per second and the benchmark results, explaining my early talk on Mysql Connect. Review the history of improvements to MySQL InnoDB. You can find it easily. It has never been so read-only in the stable versions of MySQL 5 and 6. This article provides details and benchmark results of the article "MySql5.7 achieves 50W queries per second", explaining my earlier talk on Mysql Connect.

Review the improvement history of MySQL/InnoDB. You can find it easily. In the MySQL 5.6 stable version, it has never been so fast in read-only. It is easy to understand and has good scalability in read-only (RO). I also look forward to it reaching a higher level in read+write (RW). (Especially when reading data is the main work of the database)

However. We are also very happy with the performance of RO in MySQL 5.6. In version 5.7, the main work is focused on read+write (RW), because the processing of big data has not yet met our expectations. But RW depends on RO. Able to increase speed again. Through continuous improvement, the InnoDB team is strongly promoting and optimizing the performance per second of version 5.7.

The following will explain it to you in order

In fact, there are two ways to control internal links in read-only workload in MySQL:

Use a single table: MDL, trx_sys and lock_sys (InnoDB)
Multiple tables: trx_sys and lock_sys (mainly InnoDB)
Any very The workload of fast single table range testing is mainly due to locking caused by MDL linking. Multiple tables will be limited due to InnoDB internals (different tables will be protected by different MDL locks, so the link bottleneck in MDL will be reduced in this case). But again, it depends on the size of the workload - a measurement with more read-only work than usual will perform better in MySQL 5.6 (such as Sysbench OLTP_RO), while a smaller workload and faster query (such as Sysbench Point-Selects (using foreign keys to fetch a record) will make all links difficult, and can only be measured in 16-core-HT, and performs poorly in 32-core.. but any such as Point-Select The test workload will allow you to see the maximum performance possible with all MySQL internals working together (starting with the SQL parser, terminating with fetching the row value)... on your given MySQL version and given HW Depending on the configuration, this may also reach the maximum SQL queries per second (QPS) rate.

The best result we obtained on Mysql5.6 was 250,000 queries per second, which was also the best result obtained using SQL statement queries on Mysql/InnoDb during that period.

Of course, such high speed can only be achieved when using the 'read-only transaction' function (a new feature on Mysql5.6); in addition, AUTOCOMMIT=1 needs to be used, otherwise the CPU will be easily wasted on startup Transactions, commit transactions, you will actually lose the overall performance of the system.

So the first improvement introduced on Mysql5.7 is 'Auto-discovery of read-only transactions' (actually every InnoDb transaction is considered read-only until there is a DML declaration before it External) function ---, which greatly simplifies the read-only transaction function and saves users and developers time. They no longer have to manage whether to use the read-only transaction function. However, using this function you still cannot achieve the potential optimal query rate per second of Mysql, because CPU time is still wasted in the process of opening and ending status of transactions.

At the same time, Percona uses different solutions to solve the problem of "transaction list" management (TRX-list) and the slow problem of trx_sys mutual exclusion links in InnoDB. Percona's solution performs well when handling high loads of Point-Selects with transactions, but MySQL 5.7 performs mediocrely (but I won't publish the results for 5.7 because its code is not public)... So, at least I can now Do some comparisons:

Observations:

8 tables in MySQL5.6, Percona 5.5 and MySQL5.7 Use the same Roint-Select-TRX read-only test (using transactions) (2013.5 results)

At the same time, you can also see that in the same Under the 16-core-HT configuration, we are still far from the peak result of 250,000/s.

MySQL5.6 has extended link times in trx_sys mutual exclusion access, and the number of requests per second will be reduced since 64 users.

Percona5.5 can maintain the load for a long time, and the requests per second only start to decrease at 512 users

When MySQL5.7 has been maintained for a period of time, the requests per second have still not decreased (you can’t see it in this picture for more concurrent users)...
However, it is clear that if you want to get the maximum potential query rate per second with MySQL, transactions should be avoided.

Let’s take a look at what our maximum query rate per second was in May 2013.

Tested on eight tables at the same point, but did not use MySQL5.6:

Observation:

• The above test is to keep MySQL5.6 always executed on 16 cores, and then 16 cores-HT, 32 cores , 32-core-HT.

• As you can see, the maximum query rate per second is greater than expected - 275,000 per second on MySQL

• The maximum result has reached 16-core-HT.

• However, the result on 32-core is not as good as that on 16-core-HT (due to Competition interrupts, configurations with 2 CPU threads in the same core can better manage thread competition - so the real concurrency is still saved on 16 threads instead of 32 cores)

The same test on MySQL5.7 looks very different, because the time period of lock_sys mutual exclusion link in 5.7 is already very low, and the trx_sys mutual exclusion related code also gets the first change:

Observation results:

• First of all, you can see that the performance of 5.7 under the same 16-core-HT configuration is already better than that of 5.6 After

• #, there is no obvious enhancement under the 32-core configuration!

• The maximum request reached 350,000/second under the 32-core-HT configuration!

• From the above special (aggressive) read-only load test situation, it can be easily seen that the results we get in 32 cores are better than those in 16 , and at the same time we have not enabled hyperthreading (on 32-core-HT)... awesome! ;-)

On the other hand, it is clear that there is still room for improvement. The contention on trx_sys is still ongoing. We're not fully using the CPU power to do useful work (there are still many CPU cycles spent on lock rotation)...but the results are much better now than before, and much better than 5.6, so there is no reason to keep mining to improve In this aspect of performance, we are mainly focused on improving the performance of read and write workloads where we once spent huge amounts of space.

At the end of May, during our performance session, Sunny added several new changes to try_sys mutex contention, and since then the maximum query per second (QPS) can reach 375K ! This isn't enough of a performance improvement for 5.7, right? ;-)

At the same time, we continue to exchange opinions with the Percona team who suggest other ways to manage the TRX list. Their solution looks very interesting, but on 5.5, such code cannot show higher performance. The number of queries per second (QPS) that can be performed, and the maximum number of queries per second (QPS) that can be performed on such code on 5.6 (Percona Server 5.6 has been tested) will not be greater than on MySQL 5.6. However, the discussion brings up an interesting point: what impact does it have on read-only performance if there are some read and write workloads running at the same time? ...and, even under the same test conditions, the MySQL 5.7 code still runs significantly better, the effect is very obvious (you can see my analysis here, however, again, I can't show 5 during this time .7, since its code has not yet been released to the public - perhaps in a future article)..

Since this also has an impact on any pure read and write load, so There was enough motivation to rewrite the entire TRX list related code in the way Sunnys had wanted to for a long time, however, the experience was nothing short of obsessive!

;-)) Day after day, we were pleased to see our query-per-second graph gradually getting higher and higher, until we hit the same 32-core hyper-threaded server. 440K queries can be performed per second!

The number of results obtained by Selecting 8 tables on 5.7 Development Milestone Release 2:

No explanation required ..;-))

However, there is a slight oddity - we tried to analyze all the bottlenecks and the impact of code changes with Sunny using different tools. And in some tests, to my surprise Sunny observed a higher number of queries per second than I did. This "strangeness" is related to the following factors:

• Under high load, the current 5.7 code runs close to the hardware limit (mainly the CPU), so every instruction is very important!

• If you use a Unix socket or IP port, the difference will be very obvious!

• Sysbench itself uses 30% of the CPU time, but the same test load using an older version of Sysbench (with a shorter code path) will only use 20% %CPU, the remaining 10% is used on the MySQL server.

• Therefore, with the same test load, using Unix sockets instead of IP ports, and using Sysbench-0.4.8 instead of Sysbench-0.4.13, we will get every Over 500K queries per second! - Easy enough, isn't it? ;-))

Let’s compare the “before” and “after” differences

观察结果：

• 通过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

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

• Sysbench-0.4.13-lux86

• Sysbench-0.4.8-lux86

使用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 Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --mysql-table-engine=INNODB  --db-driver=mysql Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --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 Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --mysql-table-engine=INNODB  --db-driver=mysql Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on Use MySQL5.7 to achieve 500,000 high-performance queries per second
        --oltp-read-only=on run  > /tmp/test_$n.log &

The above is the detailed content of Use MySQL5.7 to achieve 500,000 high-performance queries per second. For more information, please follow other related articles on the PHP Chinese website!