Atlas is a data middleware project based on the MySQL protocol, developed and maintained by the Infrastructure Team of the Web Platform Department at Qihoo 360. Based on MySQL-Proxy version 0.8.2 released by Oracle, it has fixed numerous bugs and added many new features. The project is widely used within Qihoo 360, with many MySQL business services integrated into the Atlas platform, handling billions of read/write requests daily.
Key Features:
* Read/Write Splitting
* Slave Load Balancing
* IP Filtering
* SQL Statement Blacklist/Whitelist
* Automatic Table Sharding
Q & A
——————-
Q: Does it support multiple character sets?
A: This was our first improvement over the original MySQL-Proxy. Supporting local requirements is essential.
Q: Automatic read/write splitting is great, but sometimes I want to read immediately after writing. What if there is master-slave replication latency?
A: You can add /*master*/ before the SQL statement to force read requests to the master database.
Q: If the master database goes down, are read operations affected?
A: No! Asking this question suggests you have used the official MySQL-Proxy. Unfortunately, the official version did not solve this problem.
Q: Does checking the backend DB status block normal requests?
A: No. In Atlas, the detection thread performs checks asynchronously. Even if a DB goes down, it will not block the main process. In Atlas, no exception blocks the main process! As mentioned above, the official version would also disappoint you.
Q: I want to take a DB offline without stopping the MySQL server. What should I do?
A: You can manually bring backend DBs online or offline via the management interface. Atlas prioritizes the administrator’s wishes.
Q: I want to add a DB to the cluster without affecting online access. Is this possible?
A: Yes, this can be easily achieved through the management interface.
Q: Compared to the official MySQL-Proxy, what other improvements does Atlas have?
A: This is a difficult question to answer comprehensively. We have made dozens of improvements in performance, stability, reliability, and maintainability. Below, we list some of the major changes.
VS Official MySQL-Proxy
——————-
1. Replaced all Lua code in the main process with pure C implementation; Lua is only used for the management interface.
2. Rewrote the network model and thread model.
3. Implemented a true connection pool.
4. Optimized locking mechanisms, improving performance by tens of times.
……
Origin of the Name:
Atlas, the giant from Greek mythology who held up the sky on his shoulders, brother of Prometheus, one of the tallest and strongest gods, punished to hold up the heavens after failing to rebel against Zeus. We hope this system can stand firm on the backend DBs and support the frontend applications.
I. Installing dbproxy Atlas
https://github.com/Qihoo360/Atlas/wiki/Atlas%E7%9A%84%E5%AE%89%E8%A3%85
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el5.x86_64.rpm
rpm –ivh Atlas-2.2.1.el5.x86_64.rpm
Precautions:
(1). Atlas can only be installed and run on 64-bit systems.
(2). For CentOS 5.X, install Atlas-XX.el5.x86_64.rpm; for CentOS 6.X, install Atlas-XX.el6.x86_64.rpm.
(3). If executing `sudo rpm –i Atlas-XX.el6.x86_64.rpm` results in an error like: “file /usr/local/mysql-proxy/bin/encrypt from install of Atlas-2.0.1-1.x86_64 conflicts with file from package Atlas-1.0.3-1.x86_64”, it means Atlas-1.0.3-1.x86_64 was previously installed. You need to execute: `sudo rpm –e Atlas-1.0.3-1.x86_64` to remove the old version, then execute `sudo rpm –i Atlas-XX.el6.x86_64.rpm` to install the new version.
(4). The backend MySQL version should be greater than 5.1; MySQL 5.6 is recommended.
II. Modifying Configuration Files
cd /usr/local/mysql-proxy/conf
cp tests.cnf dbproxy.cnf
vi dbproxy.cnf
Atlas requires a configuration file (test.cnf) to run. Before running Atlas, you need to configure this file. The installation directory is /usr/local/mysql-proxy. Enter the conf directory under the installation directory, where you will see a default configuration file named test.cnf. You only need to modify certain configuration items in it; there is no need to write a configuration file from scratch.
1. Configuration Example and Explanation:
[mysql-proxy]
(admin-username = user)
(Mandatory, default value is fine) Username for the management interface.
(admin-password = pwd)
(Mandatory, default value is fine) Password for the management interface.
(proxy-backend-addresses = 192.168.0.12:3306)
(Mandatory, configure according to actual situation) IP and port of the master database.
(proxy-read-only-backend-addresses = 192.168.0.12:3306@2,192.168.0.13:3306@4,192.168.0.14:3306@4)
(Optional, configure according to actual situation) IP and port of slave databases. The number after @ represents weight, used for load balancing. If omitted, the default is 1. Multiple items can be set, separated by commas. If you want the master database to also handle read requests, simply add the master database information to the following configuration item.
(pwds = dbproxy: /iZxz+0GRoA=)
(Mandatory, configure according to actual situation) Username and its corresponding encrypted MySQL password. The password is encrypted using the encrypt program in the PREFIX/bin directory. The username and password are separated by a colon. This user must be created and a password set on both master and slave databases (the username and password must be consistent on master and slave databases). For example, if the username is myuser and the password is mypwd, executing ./encrypt mypwd results in HJBoxfRsjeI=. If there are multiple users, separate them with commas. Set as shown in the line above.
(daemon = true)
(Mandatory, default value is fine) Atlas running mode. Set to true for daemon mode, false for foreground mode. Generally set to false during development/debugging and true for production.
(kkeepalive = true)
(Mandatory, default value is fine) Sets whether Atlas starts two processes: monitor and worker. When set to true, Atlas starts two processes; monitor automatically restarts worker if it exits unexpectedly. When set to false, only worker runs, without monitor. Generally set to false during development/debugging and true for production.
(event-threads = 4)
(Mandatory, configure according to actual situation) Number of worker threads. Recommended to set to 2 to 4 times the number of CPU cores.
(log-level = message)
(Mandatory, default value is fine) Log level, divided into five levels: message, warning, critical, error, debug.
(log-path = /usr/local/mysql-proxy/log)
(Mandatory, default value is fine) Path for storing logs.
(sql-log = OFF)
(Mandatory, configure according to actual situation) Switch for SQL logging. Can be set to OFF, ON, or REALTIME. OFF means no SQL log recording. ON means SQL log recording, where log flushing is buffer-based; logs are written to disk only when the buffer is full. REALTIME is for debugging, meaning SQL logs are recorded and written to disk in real-time. Default is OFF.
(#sql-log-slow = 10)
(Optional) Slow log output settings. When this parameter is set, logs only output records where execution time exceeds sql-log-slow (unit: ms). If not set, all logs are output.
(wait-timeout = 10)
(Optional) Settings for closing inactive client connections. When this parameter is set, Atlas actively closes connections that have been inactive for ‘wait-timeout’ seconds. Unit: seconds.
(proxy-address = 0.0.0.0:1234)
(Mandatory, default value is fine) IP and port of the work interface monitored by Atlas.
(admin-address = 0.0.0.0:2345)
(Mandatory, default value is fine) IP and port of the management interface monitored by Atlas.
(#tables = person.mt.id.3)
(Optional) Table sharding settings. In this example, person is the database name, mt is the table name, id is the sharding field, and 3 is the number of sub-tables. Multiple items can be set, separated by commas. If no sharding is needed, this item does not need to be set. Sub-tables need to be created in advance, with names formatted as tablename_number, where the number ranges from [0, num_sub_tables-1]. In this example, sub-table names are mt_0, mt_1, mt_2.
(#charset = utf8)
(Optional) Default character set. If not set, the default character set is latin1.
(client-ips = 127.0.0.1, 192.168.1)
(Optional) IPs allowed to connect to Atlas. Can be specific IPs or IP segments, separated by commas. If not set, all IPs are allowed to connect; otherwise, only IPs in the list are allowed.
(#lvs-ips = 192.168.1.1)
(Optional, rarely needed) Physical NIC IP of the LVS attached in front of Atlas (note: not the virtual IP). If LVS is present and client-ips is set, this item must be set; otherwise, it can be omitted.
2. Important Configuration Explanations
The following configuration parameters play a crucial role in performance and normal operation and must be set correctly.
(1) Thread Count
Set via event-threads. Too small prevents full utilization of multi-core CPU performance; too large causes unnecessary thread switching overhead. It is recommended to set it to the number of CPU cores.
(2) Minimum Idle Connections (Not required in version 2.x and above, required in 1.x)
Set via min-idle-connections. Too small may cause errors under high concurrency; too large makes it hard to see the effects of read/write splitting during testing. It is recommended to set it slightly larger than the peak concurrent clients. See “Detailed Configuration Parameters” for more details. The configuration example above is for Atlas 2.X version, which does not have this option. For Atlas 1.X version configuration files, this option needs to be added.
3. Optional Configuration Explanations
The following items can be set or left at default values, with little difference.
(1) Atlas Work Port
Configured via proxy-address. For example, proxy-address = 0.0.0.0:1234 means clients should use port 1234 to connect to Atlas to send SQL requests.
(2) Atlas Management Port
Configured via admin-address. For example, admin-address = 0.0.0.0:2345 means DBAs should use port 2345 to connect to Atlas to perform operational management tasks.
(3) Management Interface Username and Password
Set via admin-username and admin-password. These are used to enter the Atlas management interface and are unrelated to the MySQL connection behind it, so they can be set arbitrarily without requiring any changes to MySQL configuration.
(4) Log Level
Configured via log-level, divided into five levels: message, warning, critical, error, debug.
(5) Log Path
Configured via log-path, e.g., log-path = /usr/local/mysql-proxy/log.
III. Adding Access Users to Backend MySQL
CREATE USER ‘dbproxy’@’192.168.0.11’ IDENTIFIED BY ‘***’;
GRANT USAGE ON * . * TO ‘dbproxy’@’192.168.0.11’ IDENTIFIED BY ‘***’ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT SELECT , INSERT , UPDATE , DELETE , LOCK TABLES ON `c1gstudio` . * TO ‘dbproxy’@’192.168.0.11’;
Encrypt Password
bin/encrypt 123456
/iZxz+0GRoA=
Add iptables Rules
iptables
iptables -A INPUT -s 192.168.0.0/255.255.255.0 -p tcp -m tcp –dport 1234 -j ACCEPT
IV. Starting
1. Start dbproxy
cd /home/mysql/dbproxy/bin
./mysql-proxyd dbproxy start
# /usr/local/mysql-proxy/bin/mysql-proxyd dbproxy start
# /usr/local/mysql-proxy/bin/mysql-proxyd dbproxy stop
# /usr/local/mysql-proxy/bin/mysql-proxyd dbproxy restart
2. Access Test
mysql -udbproxy -h192.168.0.11 -P1234 -p
password
mysql-> show databases;
mysql> show variables like “server_id”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 19 |
+—————+——-+
1 row in set (0.00 sec)
mysql> show variables like “server_id”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 20 |
+—————+——-+
1 row in set (0.00 sec)
V. Using Atlas Management Interface
# mysql -uadmin_user -padmin_password -h127.0.0.1 -Padmin_port
mysql -uadmin -h192.168.0.11 -P2345 -p
password
# View management command parameters:
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 |
+—————————-+———————————————————+
# Check DB status:
mysql> select * from backends;
+————-+——————-+——-+——+
| backend_ndx | address | state | type |
+————-+——————-+——-+——+
| 1 | 192.168.0.12:3306 | up | rw |
| 2 | 192.168.0.12:3306 | up | ro |
| 3 | 192.168.0.13:3306 | up | ro |
| 4 | 192.168.0.14:3306 | up | ro |
+————-+——————-+——-+——+
VI. Performance Test Results
From the comparison of QPS and completion time for three types of SQL operations processed by Atlas, it is reasonable to set the event-threads parameter to 2-4 times the number of CPUs. Setting event-threads to twice the number of CPUs brings a significant increase in QPS, while setting it to four times the number of CPUs also increases QPS but not as noticeably. For the time taken to process each SQL operation, increasing the event-threads value does not have a significant effect.
Therefore, we recommend users: If you pursue the QPS of Atlas processing SQL requests, set the event-threads value to 2-4 times the number of CPUs. If you pursue the completion time of Atlas processing SQL requests, set the event-threads value to the number of CPUs.
The following text mainly tests the performance gap between forwarding SQL requests through Atlas and sending SQL requests directly to the DB, and the impact of the event-threads value on Atlas performance. From the test results, Atlas performs excellently with no obvious performance degradation. During testing, it was found that Atlas belongs to CPU-intensive tasks. Compared to disk IO and memory usage, Atlas consumes the most CPU resources. Therefore, it is recommended to prioritize server CPU performance when deploying Atlas.