Implementation of MySQL-based database cluster system [figure] _ MySQL-mysql tutorial

Implementation of a MySQL-based database cluster system [figure] Mysql Cluster

MySQL tutorial is: implementation of the MySQL-based database cluster system. 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.