Research And Analysis On Database Cluster Middleware MySQL Proxy

Today's Internet application presents the characteristic that the frequency of random access and database query is very high, while the data update frequency is relatively low. The Internet application services of higher quality are required, however, the traditional database server has been difficult to adapt to current needs. Under this kind of demanding, the technology of server cluster with high reliability, availability and expansibility arises. However, the occurrence of the technology also brings a series of problems, such as the load balance, reading/writing splitting, transparent access, etc. MySQL Proxy is such a database cluster middleware which is designed to solve these problems above. With MySQL Proxy, programmers only need to connect to the middleware to access to the database cluster, instead of understanding the structure of the cluster. It's the database cluster Middleware's responsibility to handle the load balance, reading/writing splitting, etc.MySQL Proxy is deeply studied in the thesis, and some defects existed in the two functions are found. The two defects are the load balance and the read/write splitting. In order to improve these defects, the main research work is introduced as followings:(1) The cluster system is built by using MySQL Proxy and MySQL Replication, and in order to solve the inconsistence of master-slave in the reading/writing splitting, the flip-flop approach is proposed.(2) A load balance algorithm based on probability distribution and the residual load rate is designed. The actual load of each server node and the processing performance of the server itself are considered in the algorithm, which reflects the real-time process capability of each node well. The algorithm balances the server load and makes each node play its maximum processing performance. Thereby the system processing performance is enhanced, and the system average response time is reduced.(3) The dynamic feedback model and the current popular cluster system are analyzed deeply in the thesis, and the extent system framework of MySQL Proxy is designed and implemented. The system firstly collects the node load information, and then generates the probability distribution of available nodes by the improved load balancing algorithm. Finally, it forwards the requests according to probability distribution.(4) Finally, the experimental and testing environment of the whole system is set up. The database cluster system is tested by JMeter, a commonly used performance testing tool. The experimental data is obtained by comparing the new Platform with MySQL Proxy which collocates with the minimum number of connections equalization algorithm. At last, the results are analyzed and the conclusion is drawn.The study shows that, the extended platform can obviously reduce the average response time of the users' request and improve the system throughput and improve the stability of the cluster.