MySQL architecture selection for OLTP applications_PHP tutorial

MySQL architecture selection for OLTP applications

Before we made up our minds to migrate the core enterprise applications from enterprise-level databases to open source database products and use local disks instead of shared storage. I think we must face and answer the following questions before we can truly carry out open source and put our ideas into practice. Let's take a look at a few questions we must answer before migrating OLTP applications to a MySQL database:
(1) Are there temporary inconsistencies in the data after the standby database is allowed to take over the service in extreme circumstances? (Under the master-slave architecture, after the master database crashes, there may be a problem that some write operations are not synchronized in time with the standby database)?
(2) When the MySQL database fails, the application service will also be interrupted for 3-30 seconds. Is this scenario acceptable?
(3) Do we have high requirements on the scalability, throughput capacity, response time and user experience of the database?
Only by answering the above three questions, the following three types of OLTP type MySQL architecture design solutions can truly have reference and practical significance. Let’s take a look at the open source solutions that the author is currently considering that are suitable for OLTP applications.
Option 1, multi-master synchronous replication solution PXC
PXC, namely Percona Xtradb Cluster, uses the Galera engine to achieve synchronous data replication, reading and writing between multiple nodes and ensure high availability of database services and data consistency. Its architecture is as follows:

1. Advantages of PXC
(1) Synchronous data replication
(2) Multiple nodes that can read and write at the same time, but need to be divided into databases in advance. table, allowing each node to write different tables or libraries respectively
(3) It can ensure strict data consistency
(4) Suitable for business systems that read more and write less
2. Disadvantages of PXC
( 1) XA transactions are not supported
(2) Cluster throughput/performance depends on the slowest responding node, and transaction efficiency is more than an order of magnitude lower than the master-slave architecture
(3) Need to be adjusted
( 4) Only supports InnoDB engine
(5) All tables must have primary keys
(6) Large transactions are not allowed
(7) Explicit lock operations such as LOCK TABLE are not supported
(8) There are write conflicts, lock conflicts, and deadlocks. It cannot solve the hotspot update problem and has poor scalability
(9) If the amount of concurrent transactions is large, the official recommendation is to use the InfiniBand network to reduce the bottleneck caused by network delay.
(10) Multiple third-party plug-ins need to be introduced, and the integration complexity is high
Option 2, master-slave replication solution MHA
MHA stands for Master High Availability Manager and Tools for MySQL, which is a MySQL high-availability management tool. The purpose is to maintain high availability and data consistency of the Master database. Its biggest feature is that it can repair the difference logs between multiple slaves, and finally make all slaves keep data consistent, and then select a slave database as the new master and point other slaves to it.
Its architecture is as follows, please refer to:

1. Advantages of MHA
1. Automatically monitor Master failover and data synchronization between nodes after failure
2. No There is no performance loss, and it is suitable for any storage engine
3. It has automatic data compensation capability, which can ensure the consistency of data to the greatest extent when the main database crashes abnormally
4. It can achieve application-level active-active in the same city
2. Disadvantages of MHA
1. If the main server hardware fails or cannot be accessed through ssh, failover may result in the loss of current data
2. The switching time is long, the entire switching time takes about 9-12s
Solution 3: Master-master replication solution MM
uses MySQL to natively support master-slave one-way replication and master-master two-way replication. This architecture solves the problems of single point in the master database and write bottlenecks. Its architecture is as follows, please refer to:

1. Advantages of MM architecture
1. Supports fast switching, usually switching to standby machine within 3 seconds
2. Configuration management is simple and hassle-free Third-party plug-ins are required
2. Disadvantages of MM architecture
1. If the database server hardware fails, the current operation data may be lost
Summary of the above solutions
(1) For the PXC architecture, it has many advantages but The shortcomings are also very obvious. Its core advantage is to ensure the consistency of data on each node. The disadvantage is that it has problems in scalability, lock conflicts, and write expansion. In order to ensure data consistency, PXC requires each node to The data must be written to the disk before it is completed, so there is an efficiency issue. That is to say, the response time of each transaction depends on the slowest node in the entire cluster, and it has very high requirements on network quality. Another question is that we need to consider clearly, where is the direction of our open source? The question is whether to follow Percona, a niche branch open source community, or to follow the development of the mainstream MySQL official open source community.
(2) The advantage of the MHA architecture is that the MHA plug-in is used to solve the single point problem of the main library and try to ensure the data consistency between the taken over slave library and the main library after the downtime after the main library hangs up. The data synchronization function is native. The disadvantage is that zero data loss cannot be guaranteed after the master database fails over. In fact, a more accurate statement here should not be that data loss should be inconsistent between the master database and the slave database.
Under the following circumstances, MHA can ensure the data consistency between the node after takeover and the main database:
(1) In the absence of hardware failure, the data can be retrieved from the repaired main database and the data can be retrieved by the DBA Manually restore the standby database to ultimately achieve data consistency;
(2) If it is just a database failure, MHA has the ability to automatically synchronize all implemented data to the standby database to achieve zero data loss;
(3) Directly use MySQL's semi-synchronization mechanism and two-stage submission to ensure data consistency. This method is similar to the implementation of PXC
(3) The advantages and disadvantages of the MM dual-master architecture are similar to MHA, and they are all native to MySQL. Data synchronization mechanism. The difference is that the MM architecture has a shorter switching time when the primary fails. The disadvantage is that there is more possibility of data inconsistency. In addition, in the MM architecture, we can also try to introduce the MHA data compensation tool to minimize data inconsistency problems caused by primary and secondary switching, or directly use MySQL's semi-synchronization mechanism to ensure data consistency.

http://www.bkjia.com/PHPjc/1095031.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1095031.htmlTechArticleMySQL architecture selection for OLTP applications. We are determined to migrate core enterprise applications from enterprise-level databases to open source database products. , before using local disks instead of shared storage. I think I...