未加星标

Testing performance of MySQL Group Replication

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Testing performance of MySQL Group Replication

mysql Group Replication is a hot topic in MySQL ecosystem.

We have been evaluating it in , and it is now time to evaluate its performance, especially now that is GA.

In our setup, we wanted to evaluate how it performs compared to standard asynchronous MySQL replication. Therefore MySQL 5.7.17 was first tested with regular async replication, MTS, semi-sync, and finally Group Replication.

Setup

The first testing environment is a 3 nodes "cluster", with 1 master and 2 async slaves in the same network.

Each node has Intel Xeon CPU E5-2660 v2 @ 2.20GHz , 20 cores.

For benchmark we used sysbench running on a 4th node, using a database with 8 tables with 10M rows each, therefore the dataset completely fits in memory.

sysbench was executed as:

./sysbench --num-threads=64 --max-time=300 --max-requests=0 --test=./lua/oltp_update_index.lua --mysql-user=sbtest --mysql-password=sbtest --mysql-host=10.11.22.42 --mysql-port=5717 --oltp-table-size=10000000 --oltp-tables-count=8 --report-interval=10 --oltp-auto-inc=off run

This specific benchmark is being executed only with 64 connections because the number of connections is not relevant at this stage, as the bottleneck is the single-threaded replication.


Testing performance of MySQL Group Replication

While the master was able to process 47332 trx/sec, the slaves were only able to process around 2065 trx/sec .

Assuming replication lag is not ideal, we should try to not exceed the number of transactions that the slave is able to proceed.

Parallel replication

Since MySQL 5.6 it is possible to enable slave parallel workers (or multi-threaded slave, MTS).

Although, several limitations apply (see the doc).

In MySQL 5.7 a new variable slave_parallel_type controls the algorithm used for MTS, and the two possible values are DATABASE (5.6 style) and LOGICAL_CLOCK .

We enabled 16 parallel workers with slave_parallel_workers=16 . See doc .

For this specific workload, slave_parallel_type=DATABASE does not show much benefit as all tables are in the same schema while slave_parallel_type=LOGICAL_CLOCK shows a great performance boost.


Testing performance of MySQL Group Replication
Semi-sync replication

Now that we have such base metric, the next configuration I tried was to enable semi-sync, as this is what we use in production.

Interestingly, with semi-sync and slave_parallel_type=LOGICAL_CLOCK there is absolutely no replication lag. As the master is slowed down by semi-sync, with slave_parallel_type=LOGICAL_CLOCK the slave is able to process all the replication events without any lag.

The follow graph shows:

thoughput of the master without semi-sync thoughput of a slave in the same rack without MTS thoughput of a slave in the same rack and slave_parallel_type=DATABASE thoughput of a slave in the same rack and slave_parallel_type=LOGICAL_CLOCK thoughput of the master with semi-sync enabled and slaves in the same rack and slave_parallel_type=LOGICAL_CLOCK thoughput of the master with semi-sync enabled and slaves in different DCs and slave_parallel_type=LOGICAL_CLOCK
Testing performance of MySQL Group Replication
Group Replication

While with async replication we only tested the throughput with 64 connections on a write intensive workload to understand the upper boundaries, with Group Replication we tried several number of connections.

In every test, only one writer node (single master) was used.

Group Replication - same rack

During the first series of tests, the 3 nodes were located on the same rack, therefore minimal network latency.

The results include both throughput and 95% response time.


Testing performance of MySQL Group Replication
Testing performance of MySQL Group Replication
Group Replication - multiple facilities

Running a cluster with all the nodes on the same rack creates a single point of failure, therefore the first series of benchmark was executed just to have a baseline for further benchmark.

During the second series of tests, the 3 nodes were located on 3 different facilities/DC, therefore increased latency.

The results include both throughput and 95% response time.


Testing performance of MySQL Group Replication
Testing performance of MySQL Group Replication
Conclusion

It is possible to draw several conclusions from the above results, some very positive, while others show space for improvement.

the introduction of slave_parallel_type=LOGICAL_CLOCK in 5.7 makes possible to achive replication performance impossible in MySQL 5.6 . If you need high throughput in replication, you should consider to upgrade to 5.7, no matter if using Group Replication or not. the same parallelism algorithm ( slave_parallel_type=LOGICAL_CLOCK ) shows very good performance in Group Replication Not much difference was noticed in throughput and response time between the cases where the 3 nodes are located in the same rack or across 3 different DCs Group Replication flow control makes performance at 32 and 64 connections a bit unstable every 60 seconds there is a performance drop, but fortunately this can be noticed only at high throughput. See bug 84774 FOSDEM 2017

Final note: Remember to join us at FOSDEM and pre-FOSDEM MySQL Day with plenty of talks on Group Replication and other interesting topics, like ProxySQL !

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLCPU
分页:12
转载请注明
本文标题:Testing performance of MySQL Group Replication
本站链接:http://www.codesec.net/view/529880.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(38)