Atlas of mysql proxy

Atlas of mysql proxy

Atlas is a MySQL-based data intermediate layer project developed and maintained by Qihoo 360 and the Infrastructure Team of the Web Platform department. On the basis of the official MySQL-Proxy0.8.2 version of MySQL, it modified a lot of bugs and added a lot of features.

Main functions:

1. read/write splitting

2. Slave database load balancing

3. IP address filtering

4. Automatic table sharding

5. DBA can smoothly bring online and offline DB

6. automatically remove the down Database

Advantages of Atlas over the official mysql-proxy:

1. rewrite all LUA code in mainstream processes with C, and Lua is only used to manage interfaces.

2. Rewrite the network model and thread model

3. implemented a true connection pool

4. optimized the lock mechanism and improved the performance by dozens of times.

In addition, mysql-proxy cannot be read from the slave database after the master database is down, while atlas can still read from the slave database after the master database is down.

From a personal perspective, if the current master-slave architecture only requires read/write splitting and does not require other automatic table sharding or other fancy functions, Atlas is superior to mysql-proxy in terms of stability and reliability, after all, there is no stable version of mysql-proxy.

See https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md for details

Test architecture:

Master: 10.10.32.1 mysql5.6core2 * 2

Slave1: 10.10.32.2 mysql5.6 core2 * 2

Slave2: 10.10.32.3 mysql5.6 core2 * 2

Atlas: 10.10.32.4 mysql5.6core2 * 2

1. Atlas Installation

1. rpm Installation

In:

Rpm-ivh Atlas-2.2.1.el6.x86_64.rpm

Note:

(1) Atlas can only be installed and run on 64-bit Systems

(2) Centos 5.x installation Atlas-XX.el5.x86_64.rpm, Centos 6.x installation Atlas-XX.el6.x86_64.rpm (if you do not need to install a non-sharding version Atlas-2.2.1)

(3) the backend mysql version must be later than 5.1. We recommend that you use mysql5.6.

2. modify the configuration file

Atlas depends on a configuration file (test. cnf ). You need to configure the file before running Atlas. The installation directory of Atlas is/usr/local/mysql-proxy. Go to the conf directory under the installation directory and you can see that there is already a file named test. the default configuration file of cnf, we only need to modify some configuration items in it, do not need to write a configuration file from the beginning.

The configuration example and description are as follows:

 

Vim/usr/local/mysql-proxy/conf/test. cnf [mysql-proxy] # non-essential configuration items with # management interface username admin-username = admin # Management Interface password admin-password = admin # Atlas backend connection the IP address and port of the MySQL master database, you can set multiple entries and separate them with commas (,). proxy-backend-addresses = 10.10.32.1: 3306 # IP address and port of the MySQL slave database connected to the Atlas backend. The number following @ indicates the weight, used for Server Load balancer. If this parameter is omitted, the default value is 1. You can set multiple entries and separate them with commas (,). # proxy-read-only-backend-addresses = 127.0.0.1: 3305 @ 1proxy-read-only-backend-addresses: 3306 @ 1, 10.10.32.3: 3306 @ 1 # user name and its pair The encrypted MySQL password should be encrypted using the encryption program encrypt In the PREFIX/bin directory. The downstream user1 and user2 are examples, replace it with your MySQL user name and encrypted password! # Pwds = user1: + jKsgB3YAG8 =, user2: GS + tr4TPgqc = pwds = atlas:/iZxz + 0 GRoA = # Set the running mode of Atlas and set it to true as the daemon mode, if this parameter is set to false, the foreground mode is used. Generally, the value is set to false during development and debugging, and the value is set to true during online running. If this parameter is set to true, spaces are not allowed after it is set to true. Daemon = true # Set the running mode of Atlas. When set to true, Atlas starts two processes, one being monitor and the other being worker. After the worker unexpectedly exits, it automatically restarts, if it is set to false, There is only worker and no monitor. Generally, it is set to false during development and debugging, and the online Runtime is set to true. There cannot be spaces after true. Keepalive = true # Number of worker threads, which has a great impact on the performance of Atlas. You can set event-threads = 2 # log level as appropriate, there are five levels: message, warning, critical, error, and debug: log-level = message # log storage path: log-path =/usr/local/mysql-proxy/log # SQL log switch, optional values: OFF, ON, and REALTIME. OFF indicates that SQL logs are not recorded, ON indicates that SQL logs are recorded, and REALTIME indicates that SQL logs are recorded and written to disks in real time, the default value is OFF # SQL-log = OFF # Slow log output settings. When this parameter is set, only logs with execution time exceeding SQL-log-slow (unit: ms) are output. If this parameter is not set, all logs are output. # SQL-log-slow = 10 # Instance name, used to differentiate multiple Atlas instances on the same machine # instance = test # working interface IP address and port proxy-address = 0.0.0.0 of the Atlas listener: 1234 # management interface IP address and port admin-address = 0.0.0.0: 2345 # Table sharding settings for Atlas listeners. In this example, "person" indicates the database name, "mt" indicates the table name, and "id" indicates the table sharding field, 3 is the number of sub-tables. You can set multiple sub-tables separated by commas (,). If no table exists, you do not need to set this item # tables = person. mt. id.3 # default character SET. After this item is SET, the client no longer needs to execute the set names statement # charset = utf8 # IP address of the client that allows connection to Atlas, which can be a precise IP address or an IP segment, separated by commas (,). If this option is not set, all IP connections are allowed. Otherwise, only IP connections in the list are allowed. # client-ips = 127.0.0.1, 192.168.1 # IP address of the physical Nic of LVS attached to Atlas (note that it is not a virtual IP address). If LVS is configured with client-ips, this parameter must be set, otherwise, do not set # lvs-ips = 192.168.1.1.

Note:

 

(1) pwds = atlas:/iZxz + 0 GRoA =

The username and password must be set on master, slave1, and slave2.Because all reads and writes are performed through this account. In addition,/usr/local/mysql-proxy/bin/encrypt encrypts the password, for example:

 

[root@test bin]# ./encrypt 123456/iZxz+0GRoA=

(2) proxy-read-only-backend-addresses can set the load balancing weight for the back-end mysql slave database, which is changed according to the server configuration.

 

(3) the event-threads item setting is too small to fully utilize the performance of the multi-core CPU, resulting in unnecessary thread switching overhead,Recommended Number of CPU Cores.

(4) ip filtering can be set for client-ips.

(5) If lvs-ips is available, it is set for Load Balancing Atlas.

3. Manage backend mysql through the Management Interface

 

[root@test ~]#  mysql -h10.10.32.4 -P2345 -uadmin -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.0.99-agent-adminCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select * from help;+----------------------------+---------------------------------------------------------+| command                    | description                                             |+----------------------------+---------------------------------------------------------+| SELECT * FROM help         | shows this help                                         || SELECT * FROM backends     | lists the backends and their state                      || SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id || SET ONLINE $backend_id     | online backend server, ...                              || ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               || ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                || REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        || SELECT * FROM clients      | lists the clients                                       || ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  || REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               || SELECT * FROM pwds         | lists the pwds                                          || ADD PWD $pwd               | example: "add pwd user:raw_password", ...               || ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       || REMOVE PWD $pwd            | example: "remove pwd user", ...                         || SAVE CONFIG                | save the backends to config file                        || SELECT VERSION             | display the version of Atlas                            |+----------------------------+---------------------------------------------------------+16 rows in set (0.00 sec)

After logging on to the management interface, we can perform mysql online/offline operations as prompted, which is very convenient.

 

4. Simple Test of read/write splitting

 

[root@test ~]#  mysql -h10.10.32.4 -P1234 -uatlas -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 257Server version: 5.0.81-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 3     |+---------------+-------+1 row in set (0.00 sec)mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 2     |+---------------+-------+1 row in set (0.00 sec)mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 3     |+---------------+-------+1 row in set (0.00 sec)mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 2     |+---------------+-------+1 row in set (0.00 sec)mysql> 

 

After logging on to atlas, you can create a database. We can view the server_id of slave1 and slave2 and find that they are evenly allocated, this is consistent with the configured "proxy-read-only-backend-addresses = 10.10.32.2: 3306 @ 1, 10.10.32.3: 3306 @ 1", and the slave database's load balancing weight is 1, therefore, it is an average distribution.

 

Of course, we can use sysbench to test its performance.

1. Install sysbench

Here we select sysbench-0.4

 

yum install bzr  cd /usr/local/srcbzr branch lp:~sysbench-developers/sysbench/0.4  sysbench-0.4 cd sysbench-0.4./configure --prefix=/usr/local/sysbench-0.4 --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysqlmakemake install

Note: If you cannot find/usr/include/mysql, yum install myql-devel is required.

 

2. Test

Here we mainly test the effect of the event-threads parameter on Atlas in the configuration file and find the optimal value.

Two data indicators of the mysql System: QPS and the average processing time of each SQL request. Use sysbench to send a select request. The procedure is as follows:

Note: The parameter of event-threads is set to 1, 2, 3, and 4 times of the number of CPUs, so it is 2, 4, 6, and 8.

When the number of CPUs is select-2, select-4, select-6, select-8, The sysbench concurrent testing thread is 16, 32, 64,128

(1) prepare stage

 

[root@test ~]#  /usr/local/sysbench-0.4/bin/sysbench --test=oltp --num-threads=8 --max-requests=8000 --oltp-test-mode=nontrx --db-driver=mysql --mysql-db=test --mysql-host=10.10.32.4 --mysql-port=1234 --mysql-user=atlas --mysql-password=123456 --oltp-nontrx-mode=select --db-ps-mode=disable preparesysbench 0.4.12:  multi-threaded system evaluation benchmarkCreating table 'sbtest'...Creating 10000 records in table 'sbtest'...

The preceding command is used by sysbench to execute 80000 random select operations, all of which are non-transactional. You can perform the update and insert operations by modifying the -- oltp-nontrx-mode option. Modify the -- num-threads parameter to adjust the number of concurrent test threads.

 

In this case, sysbench automatically creates a table in the test database:

 

CREATE TABLE sbtest (  id int(10) unsigned NOT NULL AUTO_INCREMENT,  k int(10) unsigned NOT NULL DEFAULT '0',  c char(120) NOT NULL DEFAULT '',  pad char(60) NOT NULL DEFAULT '',  PRIMARY KEY (id),  KEY k (k)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

(2) run stage

 

[root@test ~]#  /usr/local/sysbench-0.4/bin/sysbench --test=oltp --num-threads=8 --max-requests=8000 --oltp-test-mode=nontrx --db-driver=mysql --mysql-db=test --mysql-host=10.10.32.4 --mysql-port=1234 --mysql-user=atlas --mysql-password=123456 --oltp-nontrx-mode=select --db-ps-mode=disable run

The following output is displayed:

 

As shown in:

12917.01 is the number of things per second, representing the criteria for judging the test results, that is, TPS (QPS)

0.78 is the average time consumption of 95%, that is, the average request processing time.

(3) cleanup stage to clear test data.

 

[root@test ~]#  /usr/local/sysbench-0.4/bin/sysbench --test=oltp --num-threads=8 --max-requests=8000 --oltp-test-mode=nontrx --db-driver=mysql --mysql-db=test --mysql-host=10.10.32.4 --mysql-port=1234 --mysql-user=atlas --mysql-password=123456 --oltp-nontrx-mode=select --db-ps-mode=disable cleanupsysbench 0.4.12:  multi-threaded system evaluation benchmarkDropping table 'sbtest'...Done.

The data obtained by each test is shown in the following figure:

 

QPS curve:

Average processing time curve:

Draw the following conclusions from the above two tables (in line with the official conclusion ):

(1) When event-threads is set to double the number of CPUs, QPS increases significantly; when event-threads is set to 4x, QPS increases, but not very obvious; there is no significant difference in the SQL processing time by increasing the event-threads parameter.

Therefore, it is recommended that you set the event-threads value to 2-4 times the number of CPUs if you are pursuing the QPS for processing SQL requests by Atlas. If you are pursuing the completion time for Atlas to process SQL requests, set the event-threads value to the number of CPUs.

The Atlas system is a CPU-intensive task. Compared with the disk I/O and memory usage, Atlas has the most obvious CPU consumption. Therefore, we recommend that you prioritize the server CPU performance when deploying Atlas.