Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to explore the highly available extensible architecture based on MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

With the rapid growth of the amount of information, the storage of information has become a crucial technology in this era. How to ensure that data storage technology can adapt to the growth rate of information and our high dependence on information has become a very important topic. In this paper, from the perspective of database architecture, through the idea of building a distributed data layer with open source data storage software, we expect to achieve a low-cost, highly available and scalable data layer architecture.

Traditional database architecture

Throughout the traditional commercial database software, most of them are based on centralized architecture, and there are few architectures based on distributed design concept. The most important feature of these traditional database software is to centralize all the data in one database and rely on large high-end equipment to provide high processing power and expansibility.

In terms of scalability, the centralized database architecture mainly depends on the expansion ability of the host and the storage devices that store data, that is to say, it is difficult to achieve good horizontal expansion by relying on the vertical expansion ability of the hardware itself. And its reliability is also based on hardware equipment, mainly through the way of Share Storage. As we all know, the traditional commercial database represents the RAC of the manufacturer Oracle, which is a very typical centralized architecture of Share Everything.

We can use figure 1 to simply describe the typical architecture of the traditional database: the traditional architecture is mostly shared by two hosts on the host side, and usually one of the hosts uses storage to be managed through database software. In this architecture, only the database on one host (except RAC) can provide services, and the other host can only serve as hot backup redundancy and cannot start the database instance to provide services. Therefore, its processing capacity depends entirely on the maximum expansion capacity of this host, and it is difficult to increase the processing capacity by increasing the number of hosts. After all, the expansion capacity of a single host is limited, and even the mainframes of some manufacturers also have their expansion limitations. In addition, the dependence of the traditional architecture on high-end equipment will undoubtedly lead to a substantial increase in the cost of the system, and may even lead to the "kidnapping" of the system by mainframe and hardware manufacturers, which will have to continuously increase the input cost.

Scalable and highly reliable based on MySQL

As a leader of open source database, MySQL is very different from traditional commercial database software in terms of the design ideas of the software itself and the architecture ideas commonly used by the majority of users. MySQL abandons the traditional Share-Everything idea and adopts the Share-Nothing idea. The Replication implementation mechanism of MySQL and the architecture design of MySQL Cluster embody this idea. Because of this, it not only brings very flexible architecture design ideas to MySQL in terms of scalability and high reliability, but also allows our database to get rid of the dependence on high-end equipment and use PC Server with much higher performance-to-price ratio.

Expandability

In terms of improving scalability, the most commonly used way is to copy the data of the same MySQL to one or more MySQL hosts asynchronously through MySQL's own Replication function, and let these MySQL hosts provide query services at the same time. With each additional replicated node, the query processing capacity is also increased accordingly, and the processing capacity of the new node is the increased processing capacity of the whole system. Because MySQL Replication is mainly logical, there can be multiple manufacturers' hardware or different OS in the same cluster, so it can be completely free from any software / hardware platform restrictions and get rid of the dependence on a single platform.

People may be dissatisfied with the functional features of MySQL Replication, and then use third-party open source software, or even self-developed replication software developed by parsing its open source communication protocols, to replicate data in real time (or asynchronously) to achieve the same or better effect of Replication. Traditional database may also have a certain function to achieve data replication, but compared with MySQL, because it only depends on the characteristics of the database itself, there are some deficiencies in the flexibility and controllability of the architecture.

Finally, the idea of data segmentation (horizontal / vertical), which has to be said to improve scalability, can also be flexibly used in the MySQL database, whether it is vertical segmentation in the way of functional modules, or horizontal segmentation in the way of HASH segmentation of a specific key (field), or through the Partition function of the database itself, it can be well implemented on MySQL. Of course, whether the data is dispersed before segmentation, or the data routing and merging after segmentation are inseparable from the cooperation and cooperation of the application layer, unless it is realized through MySQL Cluster. For the architecture that improves scalability, there will be a more intuitive presentation in figure 2.

With "Master" as the core, the data is copied to the corresponding MySQL cluster to provide services in three "routes" in different ways. In the actual architecture, Master is a data write point, and the replicated cluster can provide corresponding query requests. In common Web application systems, query requests are much larger than write requests, so it is very suitable to use similar architecture ideas through MySQL database to solve practical scalability problems.

High reliability

In terms of ensuring high reliability, we can also design a variety of very flexible and highly reliable architectures based on MySQL's Replication, with the simple cooperation of the application layer architecture or some open source third-party HA management software.

For the data write point, through the Replication function of MySQL, the two MySQL hosts can be set to the state of double A, and through the HA management software, the state of MySQL can be detected to determine the state of MySQL, and a single service IP address can be provided to the outside to ensure that at any time a MySQL crashes and immediately switches to another MySQL to provide services. This automatic way makes it very convenient for our write point to have high reliability. If our application can automatically judge that when one point fails, it will be better to switch to another point automatically. Basically, it can switch completely transparently to the outside, with little impact on usability, which is much better than the professional HA management of some well-known manufacturers.

For data query points, it is easier to achieve high reliability. Multiple MySQL nodes with identical data copies can be built from either of the two Masters of double A to ensure that multiple MySQL can provide data query services at any time. When a MySQL crashes, the system immediately removes the node from the available nodes. This is very easy to do with the help of application architecture.

Figure 3 is an example of high reliability. 1 describes the basic architecture, 2, 3 and 4 describe the high reliability implementation when the read node fails and any one of the write nodes fails. In terms of high reliability, in addition to controlling the way data is replicated to multiple MySQL hosts, MySQL also provides a more advanced solution-MySQL Cluster, a completely distributed database cluster, and is a very typical Share-Nothing distributed database architecture. The data layer and SQL layer are separated, and each data is stored in two or more copies on different data nodes. All nodes in the whole data layer deal with the data of the whole database together with the idea of distributed computing, which ensures high concurrent processing capacity and high reliability in the way of data redundancy.

Build a data layer based on MySQL, Cache and Search

As the load of Web application system grows faster and faster, it often makes the system overwhelmed, especially the database system. With the increase of attention to the user experience, the response speed and convenience of use have become an inevitable topic. No matter how to optimize it, it is impossible to avoid the problem that the response speed of disk physical Cache O does not match with that of in memory, so it is natural to think of improving the response speed through memory.

The most typical aspect in the convenience of use is data search. The characteristics of relational database determine that it is difficult to provide a full-text search system like Google. Once again, we improve the service with the help of "external forces", and increase the ability of full-text retrieval for the database by using similar search engine systems such as Lucene or Egothor, or by integrating software such as Sphinx with the database.

A database (My-SQL) can be designed to complete persistence and conventional data access functions, a distributed memory Cache system can be used to provide access to data with high response speed and concurrency, and a third-party or self-developed distributed full-text search system can be used to provide full-text search services. in the middle, a unified data access layer can be realized with the help of application architecture. To control operations such as data reading, writing and retrieval, as shown in figure 4: there are many ways to write data in MySQL synchronously (or asynchronously) to Cache and Search systems. For example, if the real-time requirement is not very high, and you do not want too much control logic in Data Proxy Layer, it can be implemented asynchronously through queues. If the real-time requirement is high, the data update in Cache and Search can also be controlled through the application of Data Proxy Layer, and even the data can be updated to the Cache cluster in real time by Trigger through the user-defined function of MySQL. There are many similar ways to come up with, and the key is to choose the most appropriate and simplest way to implement it according to the application scenario.

The architecture doesn't matter the best, only the most appropriate. Any architecture has its applicable scenarios and its corresponding lifecycle. Changes in application scenarios or business volume may lead to a phenomenon that the architecture is insufficient to deal with. To quote a movie line: "if you come out to hang out, you will have to pay it back sooner or later!"

After reading the above, have you mastered how to explore the highly available extensible architecture based on MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.