Does your webapp system are using a MySQL database system? Is your customer always complained that the page results feedback very slow? Is your Mysql system not always maintained in a very high state? This article will provide you with a method of sharing the load of the MySQL system, and a mysql-ha-proxy development project that is derived. Using the methods provided herein, you will change the efficient operation of the MySQL system with minimal source code.
The status quo of the first quarter database cluster technology
At present, the database cluster system is successful, and the application range is relatively widely: Oracle's Oracle9 and IBM DB2. Oracle9 uses Shared-Storage technology, DB2 selects Shared-Nothing technology, both of which are long.
The theoretical basis of the latest database cluster system is distributed computing, distributes data to each node, all computing nodes parallel to process data, summarize the results. This way is undoubtedly perfect. But it is still unable to implement all functions.
For Shared-Storage and Shared-Nothing, please refer to Oracle and the relevant information on the IBM website.
Section 2 current database application status
The current database application conditions are roughly divided into two categories. The first category is below 100g, and the database access is frequent, requesting dense. Mainly the application of the web app type, such as website, forum, etc. These Web App Types The application access database is: Access is frequent, the database should be accepted for thousands of queries per second, and data is often added frequently, while the response speed of the data is relatively high. Another category is used for scientific calculations, storage history data, and data is often reached several hundred g. These applications are characterized by: Multi-query operation, data is batch, timed, concentrated into the database, database records, have accumulated a lot of data, and there is no high demanding response to the database.
Problems in Section III
The first type of application is frequently frequent, and for more access, Web Server generally uses a load balancing cluster, but for the database, since the cluster operation cannot be implemented, the request is increasing per second, with The increase in server load, responds to a single request, slower, if the library file is relatively large, there is a matter of obsolete, and the lock time is too long and other things affect access efficiency.
The second type of application, mainly the data file is too large, and each processing data requires a lot of time. If you write a statement, you need to spend a few hours to keep inquiry.
How to solve the fourth quarter
First, you should optimize from the hardware, software, procedures, indexes, SQL statements. If you still don't solve the problem, we should consider the database system cluster (parallel processing).
For the first class, the application is still satisfactory to the database system in the case where the database server is running normally, the load is not high. However, after the database system has too high, the time before the completion of the request will be completed, and the requirements for the system are not required. Since the load is caused by excessive requests, we take the way of sharing the request, let a part of the request to access another server, so that the load of the single server is reduced, thereby solving the problem.
For a second type of application, a system that requires distributed computing is solved, and the general system is powerless.
Section 5 for the solution for the first class application problem of "Linux Apache PHP MySQL"
A practical case solution: I met such a problem among my work, our web server is a cluster of three machines of Linux Apache PHP, and MySQL runs on the platform of Sun450, 2G memory. Since the Web's access is almost full of loads during peak, Loadavg (the number in the Running state within one minute) is between 10-20, which is reflected in a large number of requests are hanging when accessing the database. Live, causing a request without completion, the next request comes in, and finally the vicious circle. Loadavg will soar to more than 800 instantly. The database is worse, and the Loadavg reaches more than 300, the database's thread is very much, and the CPU is busy switching the thread state. This time unless RESTART MySQL, it will not be good. After the SQL statement is optimized, it is still unable to solve the problem. We add a database server, synchronous mechanism through MySQL, so that the data on the two databases is synchronized, and some part will only read the operation. The PHP program allows these programs to connect another database, which is partially separated by the load, and the problem has been initially solved. But later the business is big, we have added multiple servers, modify a lot of programs, separating them to the database read operation, accessing different servers. Section 6 Prospect of MySQL-HA-PROXY scheme
By modifying the program, it is a very painful thing. It is a painful thing, the project is very painful, and it cannot be mistaken, because in addition to the primary server can write, modify the data, and other servers can only update their own Data, so if you write to those databases, the result will be catastrophic.
If we can have a program sorting SQL statement, transfer to different servers according to his type (read / write), and then return the result. With a way similar to HTTP, we do not need to share the load by modifying the source program. If you can again depend on the load status of the server, or the status of the table (available / locked), it should be judged. This request is allocated to which server, which is better than the effect of our modified source program.
Section 7 How to communicate between MySQL Client and Server
I don't find an article about the MySQL communication protocol. It seems that only the source program of Mysql is analyzed. So I found code for MySQL 3.23.49, open the Sniffer tool. Mysql's communication protocol may change many times, in the version of 3.23.49, the version of the communication protocol is 10.
Simplely analyzed the communication agreement, now the rules are as follows, some places are not very perfect, because I really don't have much time to study my mysql code carefully, I only know this.
SERVER's response data format for Client requests:
Offset area type length (byte) Description 0HeadData length3 1 2 3 Flag1 = 0 normal information = 1 multi-segment information = 2 authentication Return> 2 End Word 4DataCMD Code1 5 MessageDatalength - 1
CMD Code and Message definition when flag = 0, 2
CMDCode Type Message Structure 00 Status Code Offset Type Length (Byte) 0Affect Rows2 0A Server Version Number Offset Type Length (Byte) Only when it is just connected to Server, Server will immediately return to a data segment 0Versionstring8nd of '/ 0' 8session ID432BITS 12UNKNOWN11 FF When an error occurs, return information Offset Type Length (Byte) 0ERRRCODE2 2ERRMSGEND FE Multi-segment information Transfer Empty Client Submitted data to Server:
Offset area Type Length (byte) 0HeadData Length31 2 3 compressed14datacommand ID15command DataData Length - 1
Command ID with Command Data:
Data Format Type ID 0COM_SLEEP 1COM_QUITNULL2COM_INIT_DBDatabase name3COM_QUERYstand query string4COM_FIELD_LISTtable name [128] wildcard [128] 5COM_CREATE_DBDatabase name6COM_DROP_DBDatabase name7COM_REFRESHoptions (bits) 8COM_SHUTDOWNNULL9COM_STATISTICSNULL10COM_PROCESS_INFONULL11COM_CONNECT 12COM_PROCESS_KILLsid [4] 13COM_DEBUGNULL14COM_PINGNULL15COM_TIME 16COM_DELAYED_INSERT 17COM_CHANGE_USER [user] [passwd] [db] 18 COM_BINLOG_DUMP 19COM_TABLE_DUMP 20COM_CONNECT_OUT
Section 8 Client How to Certified by Server
The protocol analysis is complete, I tried to let it work, but the part of the certification encounters trouble, MySQL Server will return to the client when connecting it, Mysql Server will first return to the client, including the version number, version information, sessionID , A 8-byte key is why this key is. Client will use this key to encrypt the password, then send the username, password, the information you need to open to the server, so that the authentication is completed. I don't know how the client uses this key to encrypt, so I intend to skip passwords, I will reorganize the client's packet, after the information of Password, I succeeded, but the mysql users in the cluster are not password, There are fewer security problems, but these servers are placed behind the HA. There is no external IP address, it should be a lot, but more less is a shortcoming.
But I always know if the user's password is correct? How to do it? Use a dedicated MySQL to complete password authentication. Install a minimized resource Mysql Server to do mysqlauth (dedicated authentication server), return Mysqlauth's first packet to the client when the Client connection is connected, which is of course a key, and then the Client will use this key After encrypting the password, send the authentication information back. At this time, the mysqlha system will forward this information to mysqlauth, and he retains a copy. If the authentication passes, the reserved number will be reorganized, remove the password information, Then use the reorganized authentication information to connect the server in the cluster. Structure and process of the ninth system
In the figure, HA is a high reliability system established using a HeartBeat (please refer to http://www.linuxvirtualserver.org/). Proxy is a MySQL-Proxy system, MySQLAUTH is a dedicated authentication server. Red RealServer is the primary server, which can be updated, while synchronizing data to other Realserver.
The above figure is the case where the Client authentication process is
The above figure is the process of establishing a connection with RealServer after passing and authentication
After the above figure, after the connection is established, the system handles the process of processing SQL Query requests.
Section 10 Conclusion
I have now basically completed the development of MySQL-Proxy programs, but is still in the test phase, the latest version is 0.0.4, and the next version is still revised. Starting from version 0.0.3, MySQL-Proxy has been able to run the SQL-Bench provided by MySQL itself, but this SQL-Bench can only provide a single point of performance, and does not provide test function for the cluster's MySQL system.
The system provides a dynamically capture of the loadavg on RealServer and then feeds back to MySQL Proxy programs, but because of this part I did not test, I used the request allocation used in the previous test to polling mode, if there are two loads The RealServer system will automatically rotate between them.
MySQL-Proxy source code You can download my website to download: http://netsock.org/bbs/ mysql-ha-cluster project. There is also part of the test data, I will also announce there.
How to perform system testing?
Since it is a cluster for systems such as Linux Apache PHP MySQL, you should find an actual application to run, then simulate a lot of access to test.
Choosing a forum system is perhaps, VBB, more, and it is more popular. Analog access is made using APache itself.
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
Reference:
The slides of the ninth section can get the latest version of the source code at http://www.netsock.org/mysqlha/mysql-ha.ppt can be at http://www.netsock.org/mysqlha/mysql-proxy_0.0.4 .zip get an installation instructions can be referred to http://netsock.org/bbs/showthread.php?threadid=5 A SQL-BENCH running result can be at http://netsock.org/bbs/showthread.php?threadID=9 Get the author profile:
Xu Chao, worked in Tom.com Beijing, engaged in network system technical support and system maintenance. Spatant time is committed to the development of network applications based on NetSocket technology. Development Website: http://netsock.org/bbs/ The project currently developed includes: SocketChat, MySQL-HA-Proxy, PHP Session Server
