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

This article introduces you how to optimize MySQL IO under Mysql SSD, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Background

Before reading this article, readers should note that in order to maintain privacy, replace the full IP with segment D of the MySQL server and omit some private information.

Project A fluctuates regularly and violently due to Imax O. Every 15 minutes, the innodbBuffPoolPagesFlushed parameters monitor peaks and troughs alternately, as does disk Imax O, and the until reaches 100%. After investigation, the possibility of triggers, events, stored procedures, front-end program timers and system crontab are excluded. It is finally located as InnoDB log switching, but whether it is completely caused by the log remains to be further tracked and analyzed.

Find out the possibility of the problem and try to make the following adjustments on the 24 main library:

Close Query Cache

Set the InnoDB Log size to 1280m

Set the innodb_max_dirty_pages_pct to 30pr innocence. The capacity will remain the same.

After making the above adjustment, Igamo tends to be stable, and there are no major fluctuations.

To be on the safe side, Project A decided to use a model with SSD to migrate the main library and 24 from library 27. After the migration is completed, the parameters of SSD and MySQL InnoDB are optimized on the new main library 39. After the program switching is completed, the parameters of SSD and MySQL InnoDB are optimized again. In other words, the optimization is carried out before and after the launch, and the status of Imax O is observed.

SSD characteristics

It is well known that the average performance of SSD is better than that of SAS. SSD can solve the bottleneck of I / O, but the Internet industry always has to weigh benefits and costs. At present, in-memory database is a major trend in this field. On the one hand, more and more applications will migrate to NoSQL. On the other hand, important data always falls to the ground, and the traditional mechanical hard disk can no longer meet the current requirements of high concurrency and large-scale data. Generally speaking, on the one hand, in order to improve performance, data should be memorized as much as possible, which is the core principle of continuous improvement of InnoDB storage engine. Subsequent versions of MySQL have optimized SSD. On the other hand, go to SSD as much as possible.

SSD is so mysterious, let's take a look at its features:

Random read ability is very good, continuous read performance is average, but better than ordinary SAS disk.

There is no delay time for disk seek, and there is little difference in response latency between random writes and continuous writes.

Erase-before-write feature, which causes write magnification and affects write performance

Write wear characteristics, the use of Wear Leveling algorithm to prolong life, but also affect the performance of reading

The response latency of read and write is not equal (read is much better than write), while the difference between read and write of ordinary disk is small.

Continuous writing performs better than random writing, for example, 1m sequential writing is much better than 128 8K random writes, because random writing brings a lot of erasure.

To sum up, random read performance is better than continuous read performance, continuous write performance is better than random write performance, there will be the problem of write amplification, too many times of insertion in the same position can easily lead to damage.

Database Optimization based on SSD

Based on SSD database optimization, we can do the following:

Reduce repeated erasures to the same location, that is, Redo Log for InnoDB. Because Redo Log is saved in ib_logfile0/1/2, these log files are duplicated. Switching back and forth will inevitably lead to repeated overwrites in the same location.

Reduce discrete writes, convert to Append or bulk writes, that is, for data files

Increase the amount of sequential writes.

Specifically, we can make the following adjustments:

Modify the system Icano scheduling algorithm to NOOP

Increase the size of each log file to 1280m (adjust innodb_log_file_size)

By constantly adjusting innodb_io_capacity and innodb_max_dirty_pages_pct to achieve equilibrium between landing and Imax O levels.

Close innodb_adaptive_flushing to see the effect

Modify innodb_write_io_threads and innodb_read_io_threads.

The following explanation is made for the system Icano scheduling algorithm. There are four scheduling algorithms in the system: CFQ (Complete Fairness Queueing), NOOP (No Operation), Deadline (deadline scheduler), AS (Anticipatory).

The following is a brief introduction to the above scheduling algorithms.

CFQ creates a separate queue for each process / thread to manage the requests generated by the process, that is, each process has a queue, and the scheduling between the queues is scheduled using a time slice to ensure that each process is well allocated to the bandwidth of the IGAMO, and the IMacro scheduler executes four requests of a process at a time.

NOOP implements a simple FIFO queue that organizes Imax O requests like the elevator master method, and when a new request arrives, it merges the request after the most recent request to ensure that the request is the same medium.

Deadline ensures that requests are served within a deadline, which is adjustable, while the default read period is shorter than the write deadline, which prevents write operations from starving because they cannot be read.

AS is essentially the same as Deadline, but after the last read operation, you have to wait for 6ms before you can continue scheduling other IAS O requests. You can subscribe to a new read request from the application to improve the execution of read operations, but at the expense of some writes. It inserts a new Icano operation in each 6ms and merges some small write streams into one large write stream, trading write latency for 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 seek time, while SSDs have no seek time and the response time is very short.

Let's talk about it in terms of data. Here are the performance tests of Icando O for different scheduling algorithms under SSD, all of them IOPS.

I/O TypeNOOPAnticipatoryDeadlineCFQSequential Read222567955224678652Sequential Write4090256013701996Sequential RW Read63557605671149Sequential RW Write63607605651149Random Read17905208472093020671Random Write7423808681138072Random RW Read4994522153165275Random RW Write4991522253215278

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

Next, I will explain the meaning of the InnoDB parameters that need to be adjusted:

Size of the innodb_log_file_size:InnoDB log file

Innodb_io_capacity: flushes the number of dirty pages when the buffer is flushed to disk

Innodb_max_dirty_pages_pct: controls the percentage of Dirty Page in Buffer Pool

Innodb_adaptive_flushing: adaptively refresh dirty pages

Innodb_write_io_threads:InnoDB uses background threads to process the number of requests written on the data page that are written to the input O (input)

Innodb_read_io_threads:InnoDB uses background threads to process the number of read I _ output requests on the data page.

A project MySQL master-slave diagram

The master-slave relationship of project A MySQL is shown in figure 1:

A project MySQL master-slave diagram

Tuning before program switching

Before the program switch, 39 is only a 24 slave library, so the IO pressure is not high, and the following adjustments do not explain the fundamental change. It is important to note that the average interval between the following adjustments is about 30 minutes.

1 modify the IO scheduling algorithm of the system

The default scheduling algorithm of the system is CFQ, which we try to modify first. As to why the change is made, you can see section 4.

The specific methods are as follows, it should be noted that please make adjustments according to the actual situation, for example, the disk in your system is probably not sda.

Echo "noop" > / sys/block/sda/queue/scheduler

If you want to take effect permanently, you need to change / etc/grup.conf and add elevator. The example is as follows:

Kernel / vmlinuz-x.x.xx-xxx.el6.x86_64 ro root=UUID=e01d6bb4-bd74-404f-855a-0f700fad4de0 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun1

6 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=noop rhgb quiet

After this step adjustment is done, look at the status of 39 I Pot O, and there is no significant change.

2 modify innodb_io_capacity = 4000

Before adjusting this parameter, let's take a look at the current MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 200

Innodb_max_dirty_pages_pct 30

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

The modification method is as follows:

SET GLOBAL innodb_io_capacity = 4000

According to the articles on the network, for SSD optimization, MySQL needs to set innodb_io_capacity to 4000 or higher. However, in fact, this business has more UPDATE, each time the amount of modification is about 20K, and is basically discrete writing. The performance of the whole system has not been greatly improved when the innodb_io_capacity reaches 4000m SSD. On the contrary, it makes the IO pressure too much, and the until even reaches more than 80%.

3 modify innodb_max_dirty_pages_pct = 25

The modification method is as follows:

SET GLOBAL innodb_max_dirty_pages_pct = 25

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 4000

Innodb_max_dirty_pages_pct 25

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Previously, innodb_max_dirty_pages_pct has been set to 30, and the innodb_max_dirty_pages_pct has been lowered to 25% here, in order to see the impact of dirty data on Icano. As a result of the modification, ICompo fluctuates as well as innodbBuffPoolPagesFlushed. However, since 39 is the slave library of 24, it has not been switched yet, all the pressure is not large enough, and there is not enough dirty data, so adjusting this parameter does not have any effect.

4 modify innodb_io_capacity = 2000

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 2000

Innodb_max_dirty_pages_pct 25

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Because the innodb_io_capacity O pressure is too high when the innodb_io_capacity is 4000, the innodb_io_capacity is adjusted to 2000. After adjustment, the maximum value of Wmax is no more than 2000, and the I-until is still on the high side, reaching a peak of 70%. At the same time, we can see that the fluctuation range of Ithumb O decreases, as does innodbBuffPoolPagesFlushed.

5 modify innodb_io_capacity = 1500

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1500

Innodb_max_dirty_pages_pct 25

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Innodb_io_capacity O continues to fluctuate, and we continue to downgrade it to 1500. The fluctuation range of I-until continued to decrease, and so did innodbBuffPoolPagesFlushed.

6. Close innodb_adaptive_flushing

The modification method is as follows:

SET GLOBAL innodb_adaptive_flushing = OFF

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1500

Innodb_max_dirty_pages_pct 25

Innodb_adaptive_flushing OFF

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Since there is still an anomaly in the landing, we can try to turn off innodb_adaptive_flushing and not let MySQL interfere with the landing. The result of the adjustment is that whether the dirty data should fall to the ground or not is not affected by the pressure of Iripple O, and it is not valid to adjust this parameter.

7 Open innodb_adaptive_flushing

The modification method is as follows:

SET GLOBAL innodb_adaptive_flushing = ON

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1500

Innodb_max_dirty_pages_pct 25

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

After the above adjustments, turning off innodb_adaptive_flushing has no effect, so leave it on by default and let this feature continue to work.

8 set innodb_max_dirty_pages_pct = 20

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1500

Innodb_max_dirty_pages_pct 20

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Then we lowered the innodb_max_dirty_pages_pct to 20 to observe the dirty data. Because the InnoDB Buffer Pool is set to 40G, 20%, that is, 8G, the pressure at this time does not reach this threshold, so adjusting the parameters has no effect. But when the business is busy, you can see the effect, and the landing frequency will increase.

9 set innodb_io_capacity = 1000

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1000

Innodb_max_dirty_pages_pct 20

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

After the above adjustments, what we need is a balanced IO to give other processes some leeway. So the innodb_io_capacity is set to 1000, at this time, we can see that the I until is maintained at about 10%, and the parameters of the whole system tend to be stable.

Further monitoring, tracking, analysis and optimization should be done in the follow-up.

Tuning after program switching

At the low peak of business, around 1: 00 in the morning, a switch was made with R & D. The master-slave relationship after switching can be seen in section 5.

1 set innodb_max_dirty_pages_pct = 30 _ 0 _ capacity = 1500

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 1500

Innodb_max_dirty_pages_pct 30

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

Under the condition that the innodb_io_capacity is 1000 innocent maxillary dirtyweights pagestrees PCT is 20, the I until fluctuates slightly, and the peaks and troughs continue to alternate, which is undesirable. InnodbBuffPoolPagesFlushed is relatively stable, but innodbBuffPoolPagesDirty continues to rise, there is no downward trend. Therefore, the following adjustments have been made: innodb_max_dirty_pages_pct = 30, innocence, innocence, iostasis capacity = 1500. After the adjustment is completed, the innodbBuffPoolPagesDirty tends to be stable, and the I-until is also stable.

2 set innodb_max_dirty_pages_pct = 40, innodbharmless capacity = 2000

The modification method will not be described in detail.

Modified MySQL configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 2000

Innodb_max_dirty_pages_pct 40

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

In view of the current situation, the following adjustments have been made: innodb_max_dirty_pages_pct = 40, innodency capacity = 2000.

3 Analysis

In view of the above two adjustments, we analyze the status of Ipicuro by combining the monitoring data.

The following is the dirty page data of the cache, as shown in figure 2:

Fig. 2 dirty data of the main database

The following is the landing of dirty data, as shown in figure 3

Fig. 3 the landing of dirty data in the main database

From 8: 00 a.m. to 7: 00 p.m. on the 28th, when the dirty data rises, that is, there is more data in memory, then there will be less landing, showing a steady trend. When the dirty data remains the same, that is, the dirty data reaches the limit of innodb_max_dirty_pages_pct (innodb_buffer_pool_size is 40%, that is, the maximum amount of dirty data in memory is 16g, and each Page is 16K, the innodbBufferPoolDirtyPages is up to 1000K), the landing will increase, showing an upward trend, so the curve in the above image appears.

This is the final configuration:

Innodb_buffer_pool_size 42949672960

Innodb_log_file_size 1342177280

Innodb_io_capacity 2000

Innodb_max_dirty_pages_pct 40

Innodb_adaptive_flushing ON

Innodb_write_io_threads 4

Innodb_read_io_threads 4

To sum up, there are three ways to optimize the parameters of SSD and MySQL InnoDB:

Modify the system Icano scheduling algorithm

Analyze the situation of innodb_io_capacity O, adjust innodb_io_capacity and innodb_max_dirty_pages_pct dynamically

Try to adjust the innodb_adaptive_flushing to see the effect.

We have not done any tuning for innodb_write_io_threads and innodb_read_io_threads at the moment, and I believe the performance of the system will be better if it is adjusted to 8 or 16.

Also, you should pay attention to the following points:

The method introduced in the network article has limitations and scene, can not be trusted, can not be followed blindly, any adjustment should be based on business priority. Ensuring the smooth operation of the business is the most important, and performance is the second.

Any adjustment must be based on the support of data and rigorous analysis, otherwise it will be empty talk.

This kind of tuning is very meaningful and can really bring value, so it takes more effort and tries to figure out why it is so adjusted.

At the end of the article, it is more interesting to say something. SSDB was mentioned in a previous article. The underlying layer of SSDB adopts LevelDB of Google and supports Redis protocol. The design of LevelDB is completely in line with the design idea of SSD. First of all, convert to continuous writing as much as possible; second, keep adding data files to prevent continuous overwriting in the same location. In addition, the acquisition of the name SSDB is also very interesting and high-level. I guess the author also wants users to apply SSDB to SSD.

On how to optimize the MySQL IO under Mysql SSD to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.