Performance comparison of MySQL centos 6 vs 7

I. Introduction

It is planned to upgrade the os of the database server from centos 6 to centos 7 this year. According to practice, we will perform a performance stress test before the upgrade. This article shares our pressure test records and results.

Two pressure test preparation

2.1 Pressure measurement method####

Use sysbench-0.5 to construct 10 tables, each with 500w data, and the parallelism is 12 24 36 48 60 72 respectively. Each concurrent stress test is 4 hours, a total of 24 hours. The pressure test command is as follows

/opt/yz-sysbench/bin/sysbench --test=/opt/yz-sysbench/share/sysbench/oltp.lua --oltp-tables-count=10 --oltp-table-size=5000000 - -mysql-db=sysbench --mysql-user=sysbench --mysql-password=sysbench --mysql-socket=/srv/my3306/run/mysql.sock --max-time=14400 --max-requests=0 --num-threads=12 --oltp-test-mod e=complex run

Note: sysbench is installed locally, and there is almost no time loss of network interaction for stress testing.

2.2 Target data

Because it is to pressure test database performance, this time mainly monitor database performance qps tps, host io, cpu and other performance indicators.

Three pressure test data###

3.1 qps

3.2 insert /delete

In the oltp mode of sysbench, the ratio of insert and delete is the same, and the tps data per second of the pressure test is the same, so they are put together.

3.3 update performance####

3.4 cpu io contrast####

Centos 7's cpu load is slightly lower than centos6, and io utilization is higher than centos 6.

3.5 Hot spot spike scene####

It is recommended to read the article first [optimization plan for hot commodity update] (https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450906&idx=1&sn=597580c87ee626985da829367e5fd133&scene=21#wechat_redirect)

In order to improve the [data security] (https://cloud.tencent.com/solution/data_protection?from=10680) of the database, MySQL sets two parameters to control the strategy of data placement. Our current setting is to write data to disk every time a transaction is submitted. The benefit is data security. In the most extreme case, a transaction will be lost, and its negative impact will cause a large number of IO operations.

Another point is that the concurrent execution of the update of the same row by the database will be blocked by other sessions that already hold the lock, and it needs to be judged whether it will cause a deadlock due to its own participation. This time complexity is O(n), if it is 1000 For each request, each thread has to check whether it is deadlocked with other 999 threads. If other threads do not hold other locks, about 50w times (calculation method 999+998+...+1). This kind of lock waiting and checking for deadlock conflicts brings huge time costs.

Core optimization parameters

sync_binlog=0--Hand over to the system to brush binloginnodb_flush_log_at_trx_commit=0--Flush redo buffer to disk innodb every second_deadlock_detect=OFF       --Turn off deadlock detection innodb_lock_wait_timeout=2

Stress test scenario:

CREATE TABLE `seckill`(`id`int(11) DEFAULT NULL,`num`bigint(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into seckill(id,num)values(1,200000000000000);
mysqlslap  -uroot -h127.0.0.1-P 3316--concurrency=72--create-schema='test'--query='begin;update seckill set num=num-1 where id =1 and num>1;commit;'--number-of-queries=500000

The main simulation database is in high concurrency scenarios, and the number of concurrency is set to: 72, 96, 144, 192, 256 active sessions concurrent.

In the spike scenario, the performance of centos 7 is against the sky, under the same conditions, it is more than three times that of centos 6. After parameter tuning, centos 7 can be updated concurrently as high as 1w or more. Of course, products in the production environment Deduction, there are multiple queries and insert, update, performance will be reduced. But year-on-year, there will be considerable performance improvements.

3.4 analysis####

From the pressure test results, the performance improvement of qps centos7 is obvious. Qps increased by 20%-40%. There is also a performance improvement of more than 20% in tps. Looking forward to the performance in the production environment (using complex business SQL, it is estimated that the performance improvement is not as obvious as sysbench).

Four summary###

It is very interesting to test the new system. What optimizations does Centos 7 have that affect MySQL performance? Leave a tail for this, and disassemble it next time. Friends who have already used centos 7 or redhat 7 in production are also welcome to leave a message to share the problems you have encountered or other impacts on operation and maintenance.

Five eggs###

Recently I started to learn the technology of MGR, and I happened to meet Mr. Ye Jinrong and former colleague Feng Guangpu to give a course-"In-depth understanding of MySQL Group Replication"

This course is divided into three modules: basic, advanced, and practice. It systematically and comprehensively learns and masters the various knowledge points of MGR from the basic principles, implementation details, practical guidance and other aspects. The course also introduces the characteristics and limitations of MGR, applicable scenarios, and conflicts. Detection principle, flow control mechanism, performance optimization, etc. **This course is the first in the industry and can be learned online through Zhishutang online classroom. **Original price is 68 yuan, register now to enjoy the first month special price for only 48 yuan.

Teacher Feng is the person in charge of a large e-commerce database, ensuring the stable and efficient operation of online database clusters, and is responsible for the construction of database automation operation and maintenance platform, and is keen to share and exchange open source database technologies.

Scan the QR code below