Implementation of MySQL-based database cluster system (Source: ibm dw)

Is your webapp system using a MySQL database system? Does your customer always complain that the page results are very slow? Is the load of your MySQL system always in a very high state? This article will provide you with a method to share the load of the MySQL system, and a derived mysql-ha-proxy development project. Using the methods provided in this article, you will obtain the efficient operation of the MySQL system with minimal source code changes.

Section 1 Status Quo of Database Cluster Technology

Currently, the database cluster system has been successfully applied and has a wide range of applications: Oracle oracle9 and IBM DB2. Oracle9 adopts the shared-storage technology. DB2 chooses the shared-nothing technology.

The theoretical basis of the latest database cluster system is distributed computing, which distributes data to each node. All computing nodes process data in parallel and summarize the results. This method is undoubtedly the most perfect. However, you still cannot implement all functions.

For the technologies of shared-storage and shared-nothing, refer to relevant information on the Oracle and IBM websites.

Section 2 Current Database Application Status

Currently, the database application status is roughly divided into two categories. The first category is that the data volume is less than GB, the database is frequently accessed, and requests are intensive. Applications of the web app type, such as websites and forums. These web app applications access the database frequently. The database needs to frequently append data to thousands of queries per second, at the same time, the data response speed requirement is relatively high. The other type is used for scientific computing and storage of historical data. The data volume is usually several hundred GB. These applications access the database with the following features: Mostly query operations, data is batch, scheduled, and centralized import into the database. The database has many records and has accumulated a large amount of data, there is no high requirement on the database response speed.

Problems exposed in Section 3

The first type of application generally uses a Server Load balancer cluster to support more accesses because of frequent access requests. However, for databases, cluster operations cannot be implemented, the number of requests per second increases. As the server load increases, the speed of responding to a single request slows down. If the library file is large, when a write operation occurs, the lock table takes a long time and affects the access efficiency.

The second type of applications is mainly because the data file is too large and each time data processing takes a lot of time. If an incorrect statement is written, it takes several hours to redo the query.

Section 4 how to solve

First, we should optimize the hardware, software, programs, indexes, and SQL statements. If the problem still cannot be solved, we should consider the database system cluster (parallel processing.

For the first type of applications, when the database server runs normally and the load is low, the applications are satisfied with the database system status. However, when the database system load is too high, it will take longer to complete the request, which does not meet the requirements of the system. Since the load is caused by too many requests, we will share the request to allow some requests to access another server and reduce the load of a single server to solve the problem.

For the second type of applications, distributed computing systems are needed to solve the problem. General systems are powerless.

Section 5 Solutions to the first type of application problems in "Linux + Apache + PHP + MySQL"

Solution to one actual case:

I encountered this problem during my work. Our Web Server is a cluster composed of three Linux + Apache + PhP machines. MySQL runs on the sun450, 2 GB memory platform. Because the web traffic is almost full during peak hours, loadavg (that is, the number of processes in the running state within one minute) is between 10 and 20, it is reflected that a large number of requests are suspended when accessing the database, resulting in a failed request, the next request comes in again, and the last vicious circle. Loadavg will soar to more than 800 in an instant. The database is even worse. The loadavg has more than 300 threads, and the database has many threads. the CPU is busy switching the thread status. At this time, it will not be good unless the restart MySQL. After the SQL statement optimization is completed, the problem still cannot be solved. We have added a database server to maintain data synchronization between the two databases through the MySQL data synchronization mechanism, modified some PHP programs that only read operations will occur. connecting these programs to another database can be considered as splitting the load. The problem was initially solved. However, as the business grew bigger, we added multiple servers, modified many programs, separated their database read operations, and accessed different servers.

Section 6 Proposal of the mysql-ha-proxy Solution

It is very painful to separate the system load by modifying the program. The project is huge and cannot be mistaken, because besides the master server, data can be written or modified, other servers can only synchronously update their own data. Therefore, if you perform write operations on those databases, the results will be disastrous.

If we can have a program to sort the SQL statement, it is sent to different servers based on its type (read/write), and then the result is returned. Using a proxy similar to HTTP, we do not need to modify the source program to share the load, or the table status (available/locked) to determine which server the request should be allocated, which is better than what we can achieve by modifying the source program.

Section 7 Communication Between MYSQL client and server

Looking around, I did not find an article about the MySQL communication protocol. It seems that only the source program of MySQL is analyzed. So I found the MySQL 3.23.49 code and opened the sniffer tool. MySQL communication protocols may have changed many times. In version 3.23.49, the communication protocol version is 10.

After a simple analysis of the communication protocols, the rules are as follows, and some of them are not perfect. As I really don't have much time to carefully study the MySQL code, I only know this.

Offset	Region	Type	Length (byte)	Description
0	Head	Data Length	3
1
2
3		Flag	1	= 0 common information = 1 multi-segment information = 2 authentication return > 2 ending words
4	Data	CMD code	1
5		Message	Datalength-1

Definition of CMD code and message when flag is 0 or 2

Response Code	Type	Message structure
00	Status Code	Offset	Type	Length (byte)
		0	Affect rows	2
0a	Server version number	Offset	Type	Length (byte)
	Valid only when you connect to the server. The server immediately returns the information of a data segment.	0	Versionstring	8	End of '/0'
		8	Session ID	4	32 bits
		12	Unknown	11
FF	Returned information when an error occurs	Offset	Type	Length (byte)
		0	Errcode	2
		2	Errmsg	End
Fe	End of multipart Information Transmission	Null

The format in which the client submits data to the server:

Offset	Region	Type	Length (byte)
0	Head	Data Length	3
1
2
3		Compressed	1
4	Data	Command ID	1
5		Command data	Data Length-1

Description of command ID and command data:

ID	Type	Data format
0	Com_sleep
1	Com_quit	Null
2	Com_init_db	Database Name
3	Com_query	Stand query string
4	Com_field_list	Table name [1, 128] wildcard [2, 128]
5	Com_create_db	Database Name
6	Com_drop_db	Database Name
7	Com_refresh	Options (BITs)
8	Com_shutdown	Null
9	Com_statistics	Null
10	Com_process_info	Null
11	Com_connect
12	Com_process_kill	Sid [4]
13	Com_debug	Null
14	Com_ping	Null
15	Com_time
16	Com_delayed_insert
17	COM_CHANGE_USER	[User] [passwd] [dB]
18	Com_binlog_dump
19	COM_TABLE_DUMP
20	Com_connect_out

Section 8 how the client passes Server user authentication

After the protocol analysis is complete, I try to make it work, but authentication is troublesome. when MySQL server connects to the client, it will first return a packet to the client, it contains the Protocol version number, version information, sessionid, and an 8-byte key. The client uses this key to encrypt the password, and then sends the username, password, database to be opened, and other information to the server. This completes authentication. I don't know how the client uses this key for encryption, so I plan to skip the password. After I reorganize the client data packet and remove the password information, I succeeded, however, mysql users in the cluster do not have a password, and the security is more or less problematic. However, these servers are all placed behind the HA, and there is no external IP address, so it should not be a problem, but it is more or less a defect.

But I always need to know whether the user's password is correct? What should we do? Use a dedicated MySQL instance to complete password authentication. Install a MySQL server with minimal resources for mysqlauth (dedicated authentication server). When the client connects, the first packet of mysqlauth is returned to the client, which includes the key, then the client will use this key, encrypt the password, and send the authentication information back. At this time, the mysqlha system will forward this information to mysqlauth and keep one copy of it, if the authentication is successful, reorganize the retained copy, remove the password information, and then use the restructured authentication information to connect to the server in the cluster.

Section 9 system structure and process

In the figure, HA is a high-reliability system built using HEARTBEAT (see http://www.linuxvirtualserver.org/for specific implementation methods /). Proxy is a MySQL-proxy system, and mysqlauth is a dedicated authentication server. The red RealServer is the main server, which can update data and synchronize data to other realservers.

Describes the client authentication process.

Describes the process of establishing a connection with the RealServer after the authentication fails and the authentication is passed.

Describes how the system processes SQL query requests after a connection is established.

Conclusion

I have basically completed mysql-proxy program development, but it is still in the test phase. The latest version is 0.0.4, And the next version is still being revised. Starting from version 0.0.3, MySQL-proxy has been able to completely run the SQL-scripts provided by Mysql itself, but this SQL-scripts can only provide single point of performance, the MySQL System of the cluster is not tested.

The system provides a program for dynamically collecting loadavg on the RealServer and then reporting it to the MySQL proxy. However, I did not test this part, therefore, the request allocation method I used in the previous test is polling. If two RealServer systems with the same load are automatically rotated between them.

You can download the mysql-proxy source code from my website: http://netsock.org/bbs/mysql-ha-clusterproject. I will also publish some testing data there.

How to perform a system test?

Since it is a cluster dedicated to systems such as Linux + Apache + PHP + MySQL, we should find a practical application to run and view it, And then simulate a large number of accesses for testing.

It may be good to choose a forum system. vBB is widely used and popular. Simulate access using the AB provided by Apache.

The minimum environment of the test system is: (five machines)

1 x Apache + PHP

1 x AB

1 x MySQL proxy + MySQL Auth Server

2 x MySQL Real Server

References:

The slides in Section 9 can be obtained in the http://www.netsock.org/mysqlha/mysql-ha.ppt

The latest source code can be obtained in the http://www.netsock.org/mysqlha/mysql-proxy_0.0.4.zip

Installation instructions can refer to the http://netsock.org/bbs/showthread.php? Threadid = 5

The running result of an SQL-statement can be in the http://netsock.org/bbs/showthread.php? Threadid = 9.

Author profile:

Xu Chao, working in Tom. com Beijing, is engaged in network system technical support and system maintenance. I am dedicated to the development of network applications based on netsocket Technology in my spare time. Development Site: http://netsock.org/bbs/ is currently developing projects including: socketchat, MySQL-ha-proxy, PHP session Server