Optimization of MySQLIO under SSD

Before reading this article, you should note that, in order to maintain privacy, use segment D of the MySQL server to replace the complete IP address and omit some private information. Project A, because

Before reading this article, you should note that, in order to maintain privacy, use segment D of the MySQL server to replace the complete IP address and omit some private information. Project A, because

1. Background

Before reading this article, you should note that, in order to maintain privacy, use segment D of the MySQL server to replace the complete IP address and omit some private information.

Project A, due to regular and violent fluctuations in I/O. Once every 15 minutes, the innodbBuffPoolPagesFlushed parameter monitoring peaks and troughs alternate. The same is true for disk I/O, And the until reaches 100%. After troubleshooting, the possibility of triggers, events, stored procedures, front-end program timers, and system crontab is eliminated. It is finally positioned as InnoDB log switch, but whether it is completely caused by logs remains to be further tracked and analyzed.

Locate the possible cause of the problem and try to make the following adjustments on the 24 master database:

After the above adjustments, I/O became stable and there was no larger fluctuation.

For the sake of insurance, project A decided to use an SSD-equipped model to migrate the master database, and migrate the 24 slave database 27. After the migration is completed, the SSD and MySQL InnoDB parameters are optimized on the new master database 39. After the program is switched, optimize the SSD and MySQL InnoDB parameters again. That is to say, optimization is performed before and after the launch to observe the I/O status.

2. SSD features

As we all know, the average performance of SSD is better than that of SAS. SSD can solve the I/O bottleneck, but the Internet industry always has to weigh the benefits and costs. Currently, memory databases are a major trend in this field. On the one hand, more and more applications are migrating to NoSQL. On the other hand, important data is always implemented, and traditional mechanical hard disks cannot meet the current high-concurrency and large-scale data requirements. In general, on the one hand, to improve performance, data should be made into memory as much as possible, which is also the core principle for the continuous improvement of the InnoDB Storage engine. SSD has been optimized in subsequent MySQL versions. On the other hand, try to use SSD.

SSD is so mysterious. Let's see what features it has:

In summary, the random read performance is better than the continuous read performance, and the continuous write performance is better than the random write performance. The write amplification problem may occur. Too many inserts at the same position may cause damage.

3. SSD-based database Optimization

For SSD-based database optimization, we can do the following:

Specifically, we can make the following adjustments:

The following is an explanation of the system I/O scheduling algorithm. There are four system I/O scheduling algorithms: CFQ (Complete Fairness Queueing, fully Fair Queuing I/O scheduling program), NOOP (No Operation, elevator scheduling program), Deadline (End Time Scheduler), AS (Anticipatory, I/O Scheduler ).

This section briefly introduces the preceding scheduling algorithms.

CFQ creates a queue for each process/thread to manage the requests generated by the process. That is to say, each process has a queue, and scheduling between queues uses time slices for scheduling, to ensure that each process can be well allocated to I/O bandwidth, the I/O scheduler executes four requests of a process each time.

NOOP implements a simple FIFO queue, which organizes I/O requests like the elevator master method. When a new request arrives, it combines requests to the nearest request to ensure that the request is sent to the same media.

Deadline ensures that the service request is made within the Deadline, which can be adjusted. The default read period is shorter than the write period, this prevents the write operation from getting starved to death because it cannot be read.

Essentially, AS is the same AS Deadline. However, after the last read operation, it takes 6 ms to schedule other I/O requests. You can reserve a new Read Request from the application to improve the execution of read operations, but at the cost of some write operations. It inserts a new I/O operation in each 6 ms, and merges some lower-case streams into one upper-case stream, in exchange for the maximum write throughput.

In SSD or Fusion IO, the simplest NOOP may be the best algorithm, because the optimization of the other three algorithms is based on shortening the tracing time, SSD does not have the so-called tracing time and the I/O response time is very short.

Let's talk about data. The following is an I/O performance test for different I/O Scheduling Algorithms in SSD, all of which are IOPS.

I/O TypeNOOPAnticipatoryDeadlineCFQ

Sequential Read222567955224678652

Sequential Write4090256013701996

Sequential RW Read63557605671149

Sequential RW write63607605621349

Random Read17905208472093020671

Random write7423808681121372

Random RW read4994522151095275

Random RW Write4991522253215278

As you can see, the NOOP algorithm is slightly better than other algorithms.

Next we will explain the meaning of the InnoDB parameters to be adjusted:

4. MySQL master-slave relationship diagram of Project

Project A MySQL master-slave relationship 1:

Project A MySQL master-slave relationship diagram

Yzone

5. tuning before program Switching

Before the PROGRAM switch, 39 is only a 24-hour slave database, so the IO pressure is not high. The following adjustments cannot explain fundamental changes. Note that the average interval of the following adjustments is about 30 minutes.

5.1 modify the system IO Scheduling Algorithm

The default I/O scheduling algorithm is CFQ. We try to modify it first. For more information about the modification, see section 3rd.

The specific method is as follows. Please make adjustments according to the actual situation. For example, the disk in your system may not be sda.

To take effect permanently, change/etc/grub. conf and add elevator. For example:

After this step is completed, check the 39 I/O status, and there is no significant change.

5.2 modify innodb_io_capacity = 4000

Before making this parameter adjustment, let's take a look at the current MySQL Configuration:

The modification method is as follows:

In the network article, for SSD optimization, MySQL needs to set innodb_io_capacity to 4000 or higher. However, in fact, this service has a large number of updates, and each modification volume is about 20 K, and it is basically a discrete write. Innodb_io_capacity reaches 4000, and SSD does not significantly improve the performance of the entire system. On the contrary, the IO pressure is too high, and the until May even reach more than 80%.

5.3 innodb_max_dirty_pages_pct = 25

The modification method is as follows:

The modified MySQL Configuration:

Previously, innodb_max_dirty_pages_pct has been set to 30. Here, innodb_max_dirty_pages_pct has been lowered to 25% to view the effect of dirty data on I/O. As a result, I/O fluctuates, while innodbBuffPoolPagesFlushed also fluctuates. However, since 39 is a 24-hour slave database, there is no switchover yet, and all the pressure is not high enough, and there is not enough dirty data, so adjusting this parameter does not show the effect.

5.4 modify innodb_io_capacity = 2000

The modification method is not described in detail.

The modified MySQL Configuration: