Performance testing of MySQL read-write separation middleware atlas

The company recently to read and write the separation of middleware, intends to now more popular middleware for performance testing. The first Test is atlas.

This test is divided into two parts, (1) The performance comparison between Atlas and direct DB, (2) The effect of event-threads parameters on the performance of Atlas

First, Introduction

Atlasis byQihoocompanyWebplatform Infrastructure Team development maintenance based on aMySQLThe data middle-tier project for the protocol. it inMySQLofficial launch ofMysql-proxy 0.8.2based on the version, a large number ofBug, adding a lot of feature features.

Main functions:

1. Read and write separation

2. load Balancing from the library

3.IP Filter

4. Automatic sub-table

5.DBA smooth up and down lines DB

6. automatically remove the DB of the outage

Atlas relative to the official Mysql-proxy The advantages

1. Span style= "font-size:15px;font-family: ' The song body '; color: #333333; background: #FFFFFF;" > all Lua code with c rewrite, lua management interface only

2. overriding the network model, threading model

3. realize a true connection pool

4. optimized lock mechanism, dozens of times times performance improvement

Second: Experimental environment description

Service	Ip	Cpu	Memory	System
Atlas	172.31.38.151	8	32G	CentOS 6.7
Master	172.31.18.104	8	32G	CentOS 6.7
Slave1	172.31.18.105	8	32G	CentOS 6.7
Sysbench/salve2	172.31.38.150	8	32G	CentOS 6.7

Three: Atlas the installation and configuration

1, in the 172.31.38.151 deploy on this server Atlas, Download atlas2.2.1

Https://github.com/Qihoo360/Atlas/releases

2 , installation

SUDORPM-IVH atlas-2.2.1.el5.x86_64.rpm

3 , change the configuration file

Cd/usr/local/mysql-proxy/conf

Cat TEST.CNF

[Mysql-proxy]

Admin-username = Admin

Admin-password = 123456

Proxy-backend-addresses = 172.31.18.104:3306

Proxy-read-only-backend-addresses = 172.31.18.105:[email protected], 172.31.38.150:[email protected]

PWDs = user1:/izxz+0groa=

Daemon = True

Log-level =debug

Log-path =/usr/local/mysql-proxy/log

Sql-log = On

KeepAlive = True

Event-threads =16

Log-level = Message

Instance = Test

Proxy-address = 0.0.0.0:1234

Admin-address = 0.0.0.0:2345

charset = UTF8

4, start Atlas

Cd/usr/local/mysql-proxy/bin

# sudo./mysql-proxyd Test Start

Ok:mysql-proxy of Test is started

See if the service process is in:

Ps-ef | grep mysql-proxy

Root 6874 1 0 01:16? 00:00:00/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/test.cnf

Root 6875 6874 0 01:16? 00:00:00/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/test.cnf

Root 6886 2714 0 01:16 pts/0 00:00:00 grep mysql-proxy

5 , enter the Admin interface:

in the master-slave server, respectively give Atlas Authorized Administrative users:

Grant all privileges on * * to [e-mail protected] identified by ' 123456 ';

mysql-h127.0.0.1-p2345-uadmin-p123456

Four: Performance comparison test for Atlas vs. Direct Connect db

The test set up three control groups:

1,atlas forwarding SQL to Mater

2, Atlas forwards SQL to a primary two-slave MySQL cluster

3, Direct Connect db

The test tool is sysbench, using the OLTP test script.

Perform the following command to test the Sysbench connection to Atlas:

./bin/sysbench--test=/usr/sysbench/tests/db/oltp.lua \

--oltp-skip-trx=on \

--num-threads=1 \

--max-requests=80000 \

--oltp-test-mode=nontrx \

--db-driver=mysql \

--mysql-db=sbtest \

--mysql-host=172.31.38.151 \

--mysql-port=1234 \

--mysql-user=user1 \

--mysql-password=123456 \

--oltp-nontrx-mode=select \

--db-ps-mode=disable \

--mysql-ignore-errors=1062 Prepare (Run cleanup)

The above command is Sysbench performs 80,000 random select operations, and these 80,000 operations are non-transactional. You can perform update and insert operations by modifying the--oltp-nontrx-mode option. By modifying the--num-threads parameter, you can adjust the number of concurrent test threads.

Run the following command to test the direct-connect DB:

./bin/sysbench--test=/usr/sysbench/tests/db/oltp.lua \

--oltp-skip-trx=on \

--num-threads=1 \

--max-requests=80000 \

--oltp-test-mode=nontrx \

--db-driver=mysql \

--mysql-db=sbtest \

--mysql-host=172.31.18.104\

--mysql-port=3306 \

--mysql-user=user1 \

--mysql-password=123456 \

--oltp-nontrx-mode=select \

--db-ps-mode=disable \

--mysql-ignore-errors=1062 Prepare (Run cleanup)

Using Sysbench to test the number of concurrent threads, perform 80,000 select,update and insert three operations respectively. Each group of tests repeats three times the average.

The following is a QPS comparison of three scenarios:

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/85/97/wKiom1epgpCgErq3AAA-N73VXK4009.png-wh_500x0-wm_3 -wmp_4-s_1653540083.png "title=" T1.png "alt=" Wkiom1epgpcgerq3aaa-n73vxk4009.png-wh_50 "/>

The line chart for the above data corresponds to the following:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/85/96/wKiom1epexSxza__AADY9NqsGVA787.png-wh_500x0-wm_3 -wmp_4-s_2008015126.png "title=" Qps.png "alt=" Wkiom1epexsxza__aady9nqsgva787.png-wh_50 "/>

At the same time, the average time of each SQL operation (in MS, the shorter time, the better system performance) was tested under different concurrent threads of Sysbench. The specific data comparison is as follows:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/85/97/wKiom1epgqfh3x_fAABBlYmv2VA886.png-wh_500x0-wm_3 -wmp_4-s_2171402102.png "title=" T2.png "alt=" Wkiom1epgqfh3x_faabblymv2va886.png-wh_50 "/>

The line chart for the above data corresponds to the following:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/85/96/wKioL1epeyzz3-yfAACOebBECH0390.png-wh_500x0-wm_3 -wmp_4-s_3963373376.png "title=" Res.png "alt=" Wkiol1epeyzz3-yfaacoebbech0390.png-wh_50 "/>

From the above statistical results:

(1) In the case of only the main library, forwarding SQL via Atlas is reduced by approximately 40%-50% compared to the direct-attached DB performance

(2) With the introduction of load balancing from the library through Atlas, there is no advantage in the concurrency when it is small, but with the increase in concurrency, Atlas has a distinct advantage.

(3) from the average response time of each SQL, after introducing load balancing from the library, the response time of Atlas in the concurrent hours is approximately 1-1.5 times times, but when high concurrency, the response time of Atlas is significantly less than that of direct-attached db.

From the above test results, we know that Atlas is suitable for a high concurrency scenario with a master multi-slave.

V: Effect of event-threads parameters on Atlas Performance

Event-threads is the number of worker threads created when working in Atlas, and the difference in this value setting has a noticeable effect on the performance of Atlas. Set Event-threads to 1,4,8,16,24,32,40,48 for testing, and Atlas forwards the QPS for select requests and completes the comparison of each SQL operation time. The specific comparison results are as follows:

QPS data:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/85/97/wKiom1epgrzhqL2lAAArVmrJsPU755.png-wh_500x0-wm_3 -wmp_4-s_4281270112.png "title=" T3.png "alt=" Wkiom1epgrzhql2laaarvmrjspu755.png-wh_50 "/>

The above data corresponding to the stitches diagram is:

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/85/96/wKioL1epe1Hg_DE0AACUIYEp6rc464.png-wh_500x0-wm_3 -wmp_4-s_4076092344.png "title=" E-qps.png "alt=" Wkiol1epe1hg_de0aacuiyep6rc464.png-wh_50 "/>

Average response time:

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/85/97/wKioL1epgtTDT3LaAAAq7CFaRDE480.png-wh_500x0-wm_3 -wmp_4-s_3392499178.png "title=" T4.png "alt=" Wkiol1epgttdt3laaaaq7cfarde480.png-wh_50 "/>

The above data corresponding to the stitches diagram is:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/85/96/wKiom1epe22DSPDDAACWoL6uj3M562.png-wh_500x0-wm_3 -wmp_4-s_3936931391.png "title=" E-res.png "alt=" Wkiom1epe22dspddaacwol6uj3m562.png-wh_50 "/>

The result of the test shows that the Event-threads parameter has little effect when the concurrency is small, but with the increase of concurrency, the event-threads parameter is set 1, 4 o'clock, when the QPS of Atlas is significantly lower than the event-threads set to >=8, The average response time also increased significantly. However, when the events-threads is set to 2-6 times the CPU and CPU cores, the average response time between QPS and SQL is very different. Thus, event-threads cannot be set to less than the core number of CPUs.

Reference: https://github.com/Qihoo360/Atlas/wiki/Atlas%E7%9A%84%E6%80%A7%E8%83%BD%E6%B5%8B%E8%AF%95

Performance testing of MySQL read-write separation middleware atlas