Experience of using 360 atlas production environment

Time:2021-6-13

1、 Atlas introduction

Atlas is an open source MySQL proxy of 360. The following is the official introduction:

Atlas is a data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Web Platform Department of Qihoo 360 company. On the basis of MySQL proxy 0.8.2, which is officially released by mysql, it modifies a lot of bugs and adds a lot of features. At present, the project has been widely used in 360 company. Many MySQL services have been connected to the atlas platform, and the number of read-write requests carried by the project is up to billions every day. At the same time, more than 50 companies have deployed atlas in the production environment, and more than 800 people have joined our developer communication group, and these numbers are increasing.

https://github.com/Qihoo360/A…_ZH.md

The following is its GitHub code base:https://github.com/Qihoo360/A…

Main functions:

1. Read write separation

2. Slave load balancing

3. IP filtering

4. Automatic meter Division

5. DBA can smooth up and down dB

6. Automatically remove the down dB

“Automatic table division” needs quotation marks, which is no problem for new tables;

If a table with historical data needs to be split, Atlas will not help us split it, so we need to write our own tool for migration.

2、 Installation

1. To download the corresponding version from the official website, we chose 2.2.1;

https://github.com/Qihoo360/A…

There are two ways to divide a table: one is to divide a table on a single machine, and the other is to support cross machine table division. You can choose according to the situation. We choose a single table division, that is, a total table is divided into multiple sub tables, and the sub tables and the total table are all on a MySQL instance.

2. Installation

Because it is an RPM installation, you can directly use the RPM command to install it

rpm -i Atlas-2.2.1.el6.x86_64.rpm 

The default installation directory is  / usr/local/mysql-proxy。

Start command

 /usr/local/mysql-proxy/bin/mysql-proxyd test start

Which instance does test represent

The configuration file is in usr / local / MySQL proxy / conf, and each configuration file represents an instance;

3. Configuration description

The following are common configuration items:

Configuration item explain
admin-username Background administrator account
admin-password Background administrator password
proxy-backend-addresses MySQL instance, multiple items separated by (comma)
pwds The password must be encrypted with the encryption program encrypt in the bin directory of the installation directory, just like the password of the MySQL instance
event-threads The number of working threads has a great impact on the performance
sql-log The switch of SQL log can be set to off, on and realtime. Off means not to record SQL log, on means to record SQL log, realtime means to record SQL log and write to disk in real time
proxy-address Working interface IP and port monitored by Atlas
tables tables

Key parameters:

Proxy back end addresses: address of back end MySQL instance

Tables: sub table parameters, format:

Database name. Table name. Sub table field. Sub table quantity

Let’s take a chestnut as an example. If we are doing a community, the main function of the community is to post and reply. There are mainly two tables (just for demonstration, not to add all the fields of the real scene)

Post table (posts)

Field name type explain
tid int Post ID
title varchar(200) Post title
content text Post content

Replies

Field name type explain
pid int Reply ID
tid int Post ID
uid int User ID
content text Reply content
create_time datetime Insert time

Suppose these tables are in the database forum,

If we want to divide the replies into tables, tables should be set like this

forums.replies.tid.64

The above indicates that replies are divided into tables. The field of the table is TID, that is, all replies with the same TID will be in the same table, with a total of 64 tables.

3、 Stepped on the pit

1. Atlas does not support the compression option. The following connections are not allowed

mysql_connect($dbhost, $dbuser, $dbpw, 1, MYSQL_CLIENT_COMPRESS);

Correct writing

mysql_connect($dbhost, $dbuser, $dbpw, 1);

2. Count statement

After splitting a table, the result returned by count will be multiple values of the query for multiple tables (if the count result is 0, it will not be returned). The specific examples are as follows (currently there are 4 sub tables)

Experience of using 360 atlas production environment

three   If the main table is deleted after the table is divided, the query without the table field will report an error (as shown in the figure below); If you keep the main table, you are querying the main table data.

Experience of using 360 atlas production environment

4. Paging problem

Take the above scenario as an example, if you want to query page 2 of the record with uid 123 and tid 100-200 from the reply table (this is how to query the user’s reply in discuz), 10 items will be displayed on each page, in reverse order of time, it is possible to return empty;

Why is that so? Imagine a scenario where the user has 40 replies in total. Suppose they are evenly distributed in four tables, with 10 records in each table, because the offset of 10-20 records is taken from each sub table, and the sub table return is empty. In fact, the user has data. The correct way is to take the first 20 records from each table, merge them, and then paginate them.

I have explained this in another article,MySQL middleware 360 Atlas

4、 Summary

1. If you are a new table and foresee a large amount of data in the future, you can use atlas to solve the problem of data volume;

2. For old tables, you have to write your own scripts to import data and check data;

3. If there are paging queries, you need to rewrite them yourself;

4. Make sure that all the queries in your scenario have the sub table field as where. If not, you need to write your own tool to synchronize the data of the sub table to the master table;

You can see that atlas needs to do a lot of things if it needs to be productized. If you are sure that none of the above is a problem, use it boldly~

Past highlights:

How to break fastdfs out of sync

Dubbo 2.7 trial

Redis port supports prefix migration

Expand redis: add redis command

Experience of using 360 atlas production environment