MySQL performance tuning and Architecture Design-the idea and solution of the High Availability design in Chapter 17th, mysql Tuning

MySQL performance tuning and Architecture Design-the idea and solution of the High Availability design in Chapter 17th, mysql Tuning

Chapter 1 High Availability design ideas and solutions

Preface:

The database system is the core part of an application system. To ensure the overall availability of the system, the database system cannot have any problems. For an enterprise-level system, the availability of the database system is particularly important. Once a database system encounters a problem and cannot provide services, all systems may not be able to continue working. Unlike a problem in the central sub-system of the software, only a certain function may not be able to continue the service. Therefore, a successful database architecture also needs to be fully considered in the high-availability design. This chapter describes how to build a highly available MySQL database system and compares various solutions and solutions.

17.1 use Replication for high-availability architecture

Readers and friends who do maintenance should be aware that any device (or service), as long as it is a single point, has a great security risk. Because once this device (or service) crash, it is difficult to have a backup device (or service) to replace its functions in a short time. Therefore, a slightly important server or application system will have at least one backup to quickly Replace the backup to provide services in case of exceptions.
For databases, master-slave configuration is a very common design concept. For MySQL, it can be said that it is born with the advantage of implementing this function, because its Replication function is widely used in practical applications to implement the master-slave configuration function.
I think most of the MySQL environments that everyone is exposed to use MySQL Replication to replicate databases between two (or more) MySQL servers. Some of them are designed to enhance system scalability and meet performance requirements for read/write splitting. Or, it is designed to act as the Master/Slave server, ensuring that applications can be switched to the slave server in a short period of time after the host crash.
Using MySQL Replication to replicate databases has been described in detail in the previous chapter 13th. It also introduces the implementation principles and methods of multiple Replication architectures. Previously, we mainly introduced Replication in terms of scalability. Here, I will mainly introduce how to solve the problem of high reliability by using multiple Replication architectures in terms of high system availability.

17.1.1 general Master-Slave solves basic Master-Slave Design

As mentioned in the previous sections, the general Master-Slave architecture is the most common architecture method used in many systems. The architecture design not only solves the scalability of the system to a large extent, but also improves the performance and guarantees the reliability of the system.
In the architecture design of copying one or more Slave behind a common Master, when one of our Slave fails to provide services, we still have at least one MySQL Server (Master) it can provide services, so that all database-related businesses cannot run. If there is more than one Slave, the remaining Slave can continue to provide services without any interference.
Of course, you need to pay attention to this point during design, that is, the overall service capability of our MySQL database cluster should at least ensure that it can support the system load when it lacks a MySQL Server. Otherwise, everything will be empty talk. However, from the perspective of system design, leaving a certain amount of space for system processing capability is a basic requirement. Therefore, in a short period of time, less than one MySQL Server in the system should be able to support normal services.

As shown in, when a Slave C in our Slave cluster experiences a crash fault, the entire system changes only because the replication from Master to Slave C is interrupted, the client application's Read requests cannot access Slave C any more. At that time, all other MySQL servers could work normally without any adjustment. All client-side Read requests are handled by Slave A and Slave B.

17.1.2 solution to the Master spof

The above architecture can easily solve Slave faults and continue to provide services without any adjustments. However, what happens when a problem occurs with our Master? When the Master node fails, all the client Write requests cannot be processed.
At this time, we can have the following two solutions: one is to switch one of the Slave to the Master to provide external services, at the same time, all other Slave will be copied through the new MASTER using the CHANGE Master command. Another solution is to add a Master, that is, the Dual Master solution.
Let's take a look at the first solution, and switch an Slave to a Master to solve the problem,

When the Master node fails to crash, all Write requests from the original client to the Master node will no longer be processed. The replication from the original Master node to the Slave will naturally be interrupted. At this time, we select a Slave to switch it to the Master. If Slave A is selected, we replace the other Slave B and Slave C with the CHANGE MASTER TO command TO continue the replication from the new Master, that is, the original Slave. At the same time, all write requests from the application end are directed to the new Master. For Read requests, we can remove the Read requests to the new Master or retain them.
One of the biggest drawbacks of this solution is that there are many switching steps and the implementation is complicated. In addition, when the Master node fails to crash, the replication progress of all our Slave instances is not necessarily the same, and there may be a small number of differences. At this time, it is also a headache to select which Slave is used as the Master. Therefore, the controllability of this solution is not very high.
Let's take a look at the second solution, that is, to solve the Master problem through Dual Master. To make it simple and clear, we will only draw the diagram of the Master part here, as shown below:

We build A Dual Master environment using two MySQL servers. Under normal circumstances, Write requests from all clients are written to Master A, and then Master A is copied to Master B through Replication. Once Master A encounters A problem, all Write requests are forwarded to Master B. Under normal circumstances, when Master B encounters a problem, in fact, both the database and client requests will not be substantially affected.
Here, some readers may think about how the application automatically redirects requests to Master B when there is A problem with Master? In fact, it is very simple. We only need to set a vip through the corresponding hardware devices such as F5 or Cluster management software such as Heartbeat. Normally, this VIP points to Master, once A crash exception occurs on Master A, the system automatically switches to Master B. All applications on the front end access the Master through this VIP. This not only solves the IP address switching problem of the application, but also ensures that the application can only see one Master at any time, avoiding the possibility of data disorder during multi-point writing.
The biggest feature of this solution is that after the Master node fails, the processing is relatively simple and the controllability is relatively large. The disadvantage is that you need to add a MySQL server and invest more in the cost.

17.1.3 Dual Master works with cascade replication to solve high availability in case of abnormal faults

Through the preceding architecture analysis, we have obtained the solutions for Slave faults and the single point of failure (spof) of the Master node. Now we can use the Dual Master architecture combined with cascade replication to obtain an overall solution to solve the overall system reliability problem.
The introduction of this architecture scheme has been described in detail in the previous sections. Here we mainly analyze the perfection and exception switching methods for high reliability.

As shown in, first consider Slave exceptions. In this architecture, the processing of Slave exceptions is the same as that of the general Master-Slave architecture, you only need to remove a Server Load balancer node in the access configuration of the application's access to the server Load balancer cluster. It can be easily implemented through application self-judgment or hardware solutions such as F5.
Next we will look at the solution after the fault crash of Master. For example:

When Master A encounters A fault crash, the replication between Master A and Master B will be interrupted. All Write requests sent from the client to Master A must be forwarded to Master B. The implementation of this steering action can be achieved through the VIP method described in solution 2 described in the previous section. Since all the previous Slave is replicated from Master B, the Slave cluster will not be affected, and all the client's Read requests will not be affected, the entire process can be completely automated without human intervention. However
There is A risk that when Master A crash is used as the Slave IO thread, if Master B has not read the binary log of Master A, data loss may occur. To completely solve this problem, we can only use a third-party patch (developed by google) to mirror the MySQL binary log to Master B, in order to completely avoid data loss.
What happens when the Master B encounters a crash fault? As shown in:

What if the fault is not caused by Master B but Master? First, we can determine that all our Write requests will not be affected, and all Read requests will still be able to access normally.
However, all Server Load balancer copies will be interrupted, and the data on the server Load balancer will start to lag. In this case, we need TO perform the change master to operation on all Slave instances and then copy them from Master.
Because the replication of all Slave Data sources cannot lead the original data source, you can compare the timestamp information in the Relay Log on the Slave with the timestamp information in Master A to find the exact replication start point, it will not cause any data loss.

17.1.4 Dual Master and cascade replication solve online DDL Change Problems

When we use the Dual Master cascade replication combination architecture, it can also be solved for a fatal injury to MySQL, that is, online DDL changes. If we need to add a field to a table tab, we can implement it in the above architecture as follows:
1. Select a Server Load balancer instance in the Slave cluster to temporarily stop providing services, change the instance, and then return the instance to the cluster to continue providing services;
2. Repeat the first step to complete all Slave changes;
3. Pause the replication of Master B, disable the binary log recording function of the current session, change it, and then start the replication;
4. Switch all application requests to Master A to Master B through VIP switching;
5. Disable the binary log recording function of the current session of Master A and then change it;
6. Finally, switch the VIP from Master B to Master A. So far, all the changes have been completed.
Note the following points during the change process:
1. The whole Slave cluster needs to be able to support all services when there is one less MySQL;
2. It is easy to add or remove a MySQL instance in the Slave cluster. You can adjust the application configuration online;
3. the VIP switching between Dual Masters is simple and the switching time is short, because this switching process will cause applications to be unable to access the Master database within a short period of time.
4. When the Master B is changed, the Slave cluster data delay may occur for a short period of time. Therefore, if a single host is changed for a long time, it is necessary to change the data in the early morning when the business volume is low. If necessary, you may even need to switch all Slave instances to Master B before changing Master B.
Of course, even so, due to the VIP switching between Master A and Master B, we still have the situation that applications cannot perform write operations in A short period of time. Therefore, this solution can only solve the problem of MySQL online DDL to a certain extent. In addition, when the number of clusters is large and each cluster has a large number of nodes, the entire operation process will be very complicated and long. Currently, there is no perfect solution for MySQL online DDL. We can only expect MySQL to solve this problem as soon as possible in subsequent versions.

17.2 use MySQL Cluster for overall high availability

In the previous chapter, we have detailed the features of MySQL Cluster and the installation, configuration, and maintenance content. Here, we will introduce how to use the features of MySQL Cluster to improve the overall availability of our system. MySQL Cluster is a complete distributed architecture system and supports multi-point redundant data storage and real-time data synchronization. Therefore, it can be said that he is born to have the conditions for high reliability. Whether he can meet the requirements in practical applications is mainly to set up and configure the system properly.
The architecture of MySQL Cluster mainly consists of two clusters, namely, SQL node (mysqld) and NDB node (data node ), both layers must ensure high reliability to ensure overall reliability.

The following describes the high reliability of MySQL Cluster in two aspects.

17.2.1 high reliability of SQL nodes

The SQL node in MySQL Cluster is actually a multi-node mysqld service and does not contain any data. Therefore, the SQL node Cluster is highly replaceable like any other common application Server. You only need to install the MySQL Server that supports the MySQL Cluster.
When an SQL node in the cluster crash is removed, it will not cause any data loss because it is only an application service. You only need to configure the front-end application data source to be compatible with the crash of a host in the cluster and then automatically remove the host from the cluster. In fact, this is very easy for the application server, whether it is through the development of a function agent or through the hardware-level load balancing device, it can be very easy to do. Of course, the premise is that the remaining SQL nodes can bear the overall load.

For example, after SQL 1 crash, only one of the many data access methods is interrupted. In fact, there are still many ways to obtain the required data. In addition, because SQL is highly replaceable, it is very easy to replace. Even if you replace the entire host, it can be completed in a short time.

17.2.2 high reliability of NDB nodes

There is a precondition for data redundancy in MySQL Cluster. First, you must ensure that there are enough nodes. In fact, at least two nodes are required to ensure data redundancy, when storing redundant data, the MySQL Cluster ensures that the redundancy of the same data is stored on different nodes. MySQL Cluster partitions data only when redundancy is ensured.
Assume that we have four NDB nodes, data is divided into four partitions for storage, redundant data storage, and two copies of each data storage, that is to say, if the NoOfReplicas parameter in the NDB configuration is set to 2 or 4 nodes, it will be divided into 2 NDB groups.
The distribution of all data is similar to the following:

In this configuration, assume that An NDB node (for example, NDB 1) in the NDB Group 0 has a problem, and part of the data (for example, part 1) is broken, since each copy of data has a redundant copy, it does not affect the system, or even does not require human operation, MySQL can continue to provide services normally.
What if some data on both of our nodes is damaged? For example:

As shown in, if two nodes with corrupted data belong to the same NDB Group, the entire MySQL Cluster can still provide services as long as the damaged data does not contain identical data. However, if the damaged data contains the same data, even if there are only a few parts, it will cause a problem in the MySQL Cluster and cannot provide services completely normally. In addition, if the node with corrupted data is in two different NDB groups, it is very lucky that no matter which part of the data is damaged, it will not affect the normal service of the MySQL Cluster.
Some readers may say that if our hardware fails, the entire NDB will be crash? Yes, there may be such a problem, but we don't have to worry about it either ,:

Assuming that the entire NDB node crash due to hardware (or software) failure, because MySQL Cluster ensures that each copy of data is not on the same host, so even if all the hosts are crash, MySQL Cluster can still provide services normally, as shown in, even if the entire NDB 1 node is crash, each copy of data can be retrieved through the NDB 2 node.
So what will be the result of the two crash nodes at the same time? First of all, we can be certain that if the two crash nodes are in the same NDB Group, there is no way for MySQL Cluster, because both redundant data is lost. However, as long as the two crash nodes are not in the same NDB Group, MySQL Cluster will not be affected and can continue to provide normal services. As shown in:

We can know from the situations listed above that MySQL Cluster can indeed achieve very high reliability. After all, the probability of two NDB nodes that store the same data at the same time is too small, if this can be met, it will be unfortunate.
Of course, in earlier versions of MySQL Cluster, all data needs to be loaded into the memory for normal operation. Due to the limited memory space, there are very few users. Although the new version only supports loading all the index data into the memory, the actual success stories are not many, and the development time is not too long, therefore, many users and friends are still cautious about MySQL Cluster, and most of them are still in the testing stage.

17.3 use DRBD to ensure high data security and reliability

17.3.1 DRBD Introduction

For a lot of friends, the use of DRBD may not be very familiar, but more or less may have some knowledge. After all, DRBD is introduced as an important way to achieve High Availability in MySQL in the High Availability and Scalability chapter of MySQL's official documentation manual. Although this content was not added to the MySQL document manual until the middle of last year, DRBD itself has become a high-reliability solution for many application scenarios long before, it has also been used by many MySQL users for a long time.
In short, DRBD is an open-source Cluster software that synchronizes data images of Block devices over the network. It is also known as network RAID 1. DRBD refers toblock devicesdesigned asa building block toform high availability (HA) clusters. this is done by inserting ing a whole block device via an assigned network. it is shownas network raid-1-DRBD. The following is an overview of DRBD:

We can see that DRBD is between the file system and the disk medium. It captures all the IO operations of the upper-level file system and then calls the I/O module in the kernel to read and write the underlying disk media. After the DRBD captures the write operation on the file system, it writes data to the local disk and uses the TCP/IP protocol to access the network device (NIC) of the local host) transmits IO to the network device of the remote host. When the DRBD of the remote host monitors the transmitted IO information, the data is immediately written to the disk device maintained by the DRBD. So far, the entire IO is complete.
In fact, DRBD has three replication modes (or levels) to choose from when processing remote data writing. Different replication modes ensure the reliability of Remote Data Writing. Three levels of options can be selected through the protocal of the general configuration section of DRBD. Different replication modes actually affect the actual meaning of an IO completion.
Because when we use DRBD, an I/O complete identifier (DRBD returns I/O complete) indicates that both local write and Remote Write concurrent processes return the complete identifier. The following describes the meaning of the three replication modes in detail:
Protocol A: This mode has the lowest reliability and is an asynchronous mode. When we use this mode for configuration, the process that writes Remote Data sends the data to the TCP send buffer of the local host through the TCP/IP protocol, that is, the return is complete.
Protocol B: This mode is more reliable than Protocol. Because the remote writing thread will wait until the network information transmission is complete, that is, the data is received by the remote DRBD and the return is complete.
Protocol C: The replication mode of Protocol C is a fully synchronous replication mode. It is returned only when the remote DRBD completely writes data to the disk.
Compared with the above three replication modes, the C mode ensures the consistency of data between the two ends, regardless of any exceptions. If the B mode is used, the loss part may not be completely written to the disk after the remote host suddenly loses power, and the local and remote data may be inconsistent. When we use the replication mode, there may be more risks. As long as the local network device suddenly fails to work (including the host power-off), data that will be written to the remote host will be lost, resulting in data inconsistency.
Because the time required by different modes for the Remote Write process to return completion information is different, it also directly determines the performance of IO write. The description of the three modes clearly shows that IO write performance is inversely proportional to the degree of reliability. Therefore, when you consider setting this parameter, You need to carefully evaluate the impact of various aspects and try to get a performance that meets both the actual scenario and the reliability requirements.
The installation and deployment of DRBD and related configuration instructions are described in the MySQL official documentation and my personal website blog (http://www.jianzhaoyang.com, we can also find the most comprehensive description on the DRBD official website, so we will not introduce it here. The following describes some special features and restrictions of DRBD for your reference.

17.3.2 DRBD features and restrictions

DRBD is widely used, and even written to the MySQL Documentation Manual as one of the highly available solutions officially recommended, mainly because of its various high reliability features and stability. The following describes some important features of DRBD.
1. A wide range of configuration options can be used in our application scenarios. Whether it's a balance between reliability and performance, or data security, whether it's a local disk or a network storage device, whether it wants to automatically resolve exceptions, or want manual control, etc, can be solved through a simple configuration file.
Of course, rich configurations bring great flexibility and require the user to have a sufficient understanding of them. Otherwise, it is difficult to decide how to configure these parameters. Fortunately, the parameters of DRBD by default have actually met most typical requirements and do not need to be set for each parameter to run;
2. For data inconsistency between nodes, DRBD can use certain rules for re-synchronization. In addition, DRBD can check and compare the data at a fixed time point by configuring relevant parameters to determine whether the data is consistent and mark the inconsistent data. At the same time, you can choose whether DRBD solves the inconsistency problem on its own or whether we manually decide how to synchronize the data;
3. If an exception occurs during running, the crash at one end does not affect the normal operation of the other end. All data changes after an exception are recorded in related log files. After the crash node recovers, It can automatically synchronize the changed data during this period. In order not to affect the normal synchronization of new data, you can also set the speed during the recovery process to ensure that the Network and other devices do not encounter performance problems;
4. Support for multiple file system types. DRBD not only supports various conventional file systems, but also supports distributed file systems such as GFS and OCFS2. In addition, when using a distributed file system, you can also provide all IO operations for each knot.
5. Provide support for LVM. DRBD can use logical devices provided by LVM or physical devices provided by itself to external devices. This greatly facilitates O & M personnel to manage storage devices.
6. All I/O Operations ensure the I/O sequence absolutely. This is also a very important feature for databases, especially some database software that has extremely strict requirements on data consistency.
7. Multiple transmission protocols are supported. Starting from DRBD 8.2.7, DRBD supports Ipv4, IPv6, and SuperSockets for data transmission.
8. Supports Three-Way replication. Since DRBD 8.3.0, DRBD supports replication between three nodes, which is similar to cascade replication.
Of course, DRBD has a large number of favored features, but it also has some restrictions. Below are some of the more important limitations of DRBD:
1. for conventional file systems (non-distributed file systems), DRBD can only support single-Primary mode. In single-Primary mode, only the data of one node can provide I/O services externally.
Dual Primary mode is supported only when a distributed file system such as GFS or OCFS2 is used.
2. sbw.brain solution. For some special reasons, the processing after the DRBD connection between the two hosts is interrupted and both sides run with the Primary role is not very stable. Although the DRBD configuration file can be configured to automatically resolve the Split Brain, but according to my previous test, not every solution is very satisfactory. In some cases, A node may be completely invalid.
3. Restrictions on the number of replication nodes. In the latest DRBD version, only replication between three nodes is supported.
The preceding restrictions are several restrictions that may have a greater impact on users. Of course, DRBD still has many other restrictions. Before you decide to use DRBD, you still need to perform sufficient tests to ensure that it will not cause problems after going online.

17.4 other high-availability design solutions

In addition to the above high-availability solutions, there are actually many options for you to choose from, such as RaiDB, shared storage solution (SAN or NAS) and so on.
For RaiDB, many readers may still be unfamiliar. It is called Redundant Arrays of Inexpensive Databases. That is, the concept of Raid is used to manage database data. Therefore, RaiDB also has the concept of database Sharding. Like disk Raid, Raid B also has a variety of Raid, such as RaiDB-0, RaiDB-1, RaiDB-2, RaiDB-0-1 and RaiDB-1-0. Commercial MySQL database solution provider Continuent uses RaiDB in its database solution. You can take a few pictures to clearly understand the Raid modes of Raid B.
RaiDB-0:

RaiDB-1:

RaiDB-2:

RaiDB-0-1:

RaiDB-1-0:

You should be clear about how data is distributed and how it works in Raid mode. As for why this can ensure high availability, it is the same as the disk's Raid to ensure high data reliability. Of course, to really understand it, the premise is that everyone has a clear concept of Raid.
The shared storage solution is a relatively expensive solution. The host running MySQL Server does not store data, but uses the shared protocol (FC or NFS) to Mount the disk space on the remote storage device to a local disk.
Why can shared storage solutions meet high reliability requirements? First, the data does not exist on the local MySQL Server host. Therefore, when the local MySQL Server host encounters any faults, data will not be lost, you can use other hosts to retrieve the data on the storage device. Second, whether through SAN or NAS as shared storage, the storage itself has a variety of high-availability solutions, so that no data is lost at all. Even if the connection channel between the MySQL Server and the shared storage has many mature high-availability solutions to ensure the high availability of the connection. Because the shared storage solution is contrary to the low-cost enterprise-level high-performance and high-reliability solution I personally advocate using MySQL, and considering the length issues, I will not go into details here.
If you are interested in this aspect, you can use other books to learn more about SAN storage and NAS storage.

17.5 comparison of advantages and disadvantages of various high availability solutions

Readers may have discovered from the introduction of various High Availability design schemes that each of them has its own unique advantages, but there are also more or less restrictions. In fact, this is also quite normal. After all, nothing can be perfect. We can only make full use of our own advantages to solve our own problems, rather than relying on a certain solution to solve all problems once and for all. This section analyzes the advantages and disadvantages of the above solutions for your reference in the selection process.
1. MySQL Replication
Advantage: simple deployment, easy implementation, and complex maintenance are features inherent in MySQL. The switchover between the master and slave nodes is convenient. You can use a third-party software or write a simple script to automatically switch between the master and slave nodes.
Disadvantage: If the Master host hardware fails and cannot be recovered, some data that is not transmitted to the Slave may be lost;
2. MySQL Cluster
Advantage: high availability and excellent performance. Each data point must have at least one copy on different hosts, and redundant data copies are synchronized in real time.
Disadvantage: The maintenance is complicated, the product is still relatively new, and some bugs exist. Currently, it is not necessarily applicable to core online systems.
3. DRBD disk Network image Solution
Advantage: powerful software, data across physical host images at the underlying fast device level, and different levels of synchronization can be configured according to performance and reliability requirements. IO operations are sequential and can meet the stringent data consistency requirements of the database.
Disadvantage: the non-distributed file system environment cannot support simultaneous visibility of image data. The performance and reliability are in conflict and cannot be applied to environments with demanding performance and reliability requirements. The maintenance cost is higher than that of MySQL Replication.

Conclusion 17.6

This chapter focuses on the two high-availability solutions of MySQL and the DRBD solution officially recommended by MySQL. It also includes the advantages and disadvantages of each solution. I hope this information will help readers. However, the high availability solution of MySQL is far from the centralized solution described above. There are also a large number of other solutions for readers to study and explore. The power of open source is enormous, and the power of Open Source contributors is endless.

 

From: MySQL performance tuning and architecture design, Jian Chaoyang

Reprinted please indicate the source:

Author: JesseLZJ
Source: http://jesselzj.cnblogs.com