Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL IO SSD attempt at optimization     - Oracle table compression Technology Introduction (Database)

- Java Builder mode (Programming)

- Linux file content inspection - cat, tac, no, more, less, head, tail, od (Linux)

- Docker in the development and practice of IFTTT (Server)

- Talk about jsonp (Programming)

- HTTPS and SSH and use the difference between the way: Git User's Manual (Linux)

- Linux file system structure Introduction (Linux)

- Tree Traversals Again (Programming)

- CentOS 6.4 Telecom ADSL dial-up network configuration (Linux)

- Compile and install GCC 4.8.1 + GDB 7.6.1 + Eclipse in CentOS 6.4 in (Linux)

- Share Practical Tutorial GitHub (Linux)

- Java string intern constant pool resolution Introduction (Programming)

- RedHat Redis Linux installation (Database)

- Build the first ASP.NET 5 Web project in Mac OS X Yosemite 10.10.3 (Server)

- Linux System Getting Started Learning: DeVeDe installed on Linux to create a video DVD (Linux)

- How do I use Linux development environment (Linux)

- CentOS 6.6 install Oracle 11gR2 database (Database)

- Ubuntu 14.04 install the NVIDIA driver + CUDA + MATLAB (Linux)

- Debian 7.7 Installation and Configuration (Linux)

- Tmux Getting Start (Linux)

 
         
  MySQL IO SSD attempt at optimization
     
  Add Date : 2017-09-20      
         
       
         
  1. Background

Before reading this article, readers should be noted that, in order to maintain privacy with the D segment MySQL server instead of the full IP, and omitted some of the private information.

A project, because I / O appears regularly volatility. Every 15 minutes floor time, innodbBuffPoolPagesFlushed parameter monitoring alternating peaks and troughs, disk I / O The same is true, and until 100%. After investigation, it ruled out the possibility triggers, events, memory, front-end program timer, the system crontab. Final positioning of InnoDB log switch, but it is completely the impact caused by logging, tracking and needs further analysis.

Find where the problem may be an attempt on the 24 main library made the following adjustments:

Close Query Cache;
Setting InnoDB Log size to 1280M;
Innodb_max_dirty_pages_pct set to 30, innodb_io_capacity 200 remain unchanged.
After doing the above adjustments, I / O has stabilized, there is no longer any major fluctuations.

For insurance purposes, A projects decided with SSD models, the main library migration, while 24 were migrating from the library 27. After the migration is complete, the new main library 39 for SSD and MySQL InnoDB parameters were optimized. After the completion of the handover procedure, again optimized for SSD and MySQL InnoDB parameters. That is to say before and after the on-line optimization, observed I / O status.

2, SSD characteristics

As we all know, it is better than the average performance of SAS SSD. SSD can solve the I / O bottleneck, but the Internet industry always weigh the benefits and costs. Currently in-memory database is a major trend in this area, on the one hand, more and more applications are migrated to NoSQL. On the other hand, important data is always landing, the traditional mechanical hard disk can not meet the current high concurrency, requiring large-scale data. Overall, on the one hand, in order to improve performance, as much as possible of the data memory, which is the InnoDB storage engine core principles of continuous improvement. Subsequent versions of MySQL has been optimized for SSD. On the other hand, as far as possible on the SSD.

SSD so mysterious, then we take a look at what it features:

Random reading ability is very good, sequential read performance in general, but better than regular SAS disks;
There is no delay time of disk seeks, random write and sequential write response delay is insignificant.
erase-before-write properties, resulting in write amplification, affect write performance;
Write wear characteristics, use Wear Leveling algorithms to extend life, but will also affect reading performance;
Read and write I / O response delay unequal (read much better than writing), while the average disk read and write I / O response delay difference is small;
Continuous write better than random write performance, such as writing a lot more than 1M order of 128 8K immediately write better, because then writing will bring a lot of erasing.
To sum up, that is, random read performance than the sequential read performance, and write performance compared with continuous random write performance, write amplification will issue the same position too many times to insert easily cause damage.

3, SSD-based database optimization

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

Reducing repeatedly erased the same position, that is, for the InnoDB Redo Log. Because Redo Log in to save ib_logfile0 / 1/2, these log files are duplicate, switching back and forth, it will definitely bring rewritable same position;
Reducing discrete written into Append or batch writing, that is, for the data file;
Increasing the amount of sequential writes.
Specifically, we can make the following adjustments:

Modify the system I / O scheduling algorithm NOOP;
Improve each log file size is 1280M (adjusted innodb_log_file_size);
By constantly adjusting innodb_io_capacity and let innodb_max_dirty_pages_pct floor and I / O level reached equilibrium;
Close innodb_adaptive_flushing, see the results;
Modify innodb_write_io_threads and innodb_read_io_threads.
For the system I / O scheduling algorithm is explained as follows. System I / O scheduling algorithm has four, CFQ (Complete Fairness Queueing, completely fair queuing I / O scheduler), NOOP (No Operation, elevator scheduling program), Deadline (deadline scheduler), AS (Anticipatory, expected I / O scheduler).

Next, the several scheduling algorithms do a brief introduction.

CFQ for each process / thread create a separate queue to manage requests generated by the process, which means that each process a queue, the queue scheduling between each time slice scheduling, in order to ensure that each process can be well assigned to I / O bandwidth, I / O scheduler each execution of a process of four requests.

After NOOP implements a simple FIFO queue, it's like the elevator work as the main method of I / O requests are organized, when there is a new request comes in, it will request to merge the most recent request, in order to ensure that requests for the same medium.

Deadline ensures that service requests within a deadline, this deadline is adjustable, and the default read-write period is shorter than the deadline, thus preventing the write operation could not be read and starved to death phenomenon.

AS Deadline is essentially the same, but after the last read operation, wait 6ms, in order to proceed to other I / O request scheduling. You can be booked from the application of a new read request, a read operation to improve execution, but at the expense of some of the write operation. It will insert a new I / O operations per 6ms, whereas some of the lower inflow will be merged into a capital inflow, with the write latency for maximum write throughput.

In the SSD or Fusion IO, the easiest NOOP but may be the best method, because the other three optimization algorithm is based on shortening seek time, and no so-called solid state disk seek time and I / O response time is very short .

Or use the data to speak of it, the following is the next SSD for different I / O scheduling algorithms do I / O performance tests, both IOPS.

I / O Type NOOP Anticipatory Deadline CFQ
Sequential Read 22256 7955 22467 8652
Sequential Write 4090 2560 1370 1996
Sequential RW Read 6355 760 567 1149
Sequential RW Write 6360 760 565 1149
Random Read 17905 20847 20930 20671
Random Write 7423 8086 8113 8072
Random RW Read 4994 5221 5316 5275
Random RW Write 4991 5222 5321 5278
You can see, on the whole, NOOP algorithm is slightly better than other algorithms.

Then explain the need to adjust the parameters of InnoDB meanings:

innodb_log_file_size: InnoDB log file size;
innodb_io_capacity: When the buffer is flushed to disk, refresh the number of dirty pages;
innodb_max_dirty_pages_pct: control of the Dirty Page Buffer Pool in the proportion of;
innodb_adaptive_flushing: adaptive flushing dirty pages;
innodb_write_io_threads: InnoDB write it on a background thread processing data page I / O (input) the number of requests;
innodb_read_io_threads: InnoDB use a read on a background thread processing data page I / O (output) requests.

4, A project MySQL master-slave relationship diagram
Yzone

5, before switching tuning program

Prior to application, just 24 of 39 from the library, so the pressure is not high IO, the following adjustments can explain fundamental change. It should be noted that the following adjusted average interval of 30 minutes.

 

5.1 modify the system IO Scheduling Algorithm

The default I / O scheduling algorithm is CFQ, we are trying to modify it. As for why the changes can be viewed in Section 3.

Specific practices are as follows, it should be noted that, please make adjustments according to the actual situation, such as your system disk is probably not sda.

echo "noop"> / sys / block / sda / queue / scheduler
If you want permanent, you need to change /etc/grub.conf, add elevator, examples are 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
This step is done after the adjustment, view the 39 I / O status, and no significant change.

 

5.2 modify innodb_io_capacity = 4000

In doing this parameter adjustments before we 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
Modify as follows:

SET GLOBAL innodb_io_capacity = 4000;
Articles on the network for SSD optimization, MySQL innodb_io_capacity aspects need to be set to 4000 or higher. In practice, however, this business UPDATE more, modify the amount of each of about 20K, and basically discrete write. innodb_io_capacity achieve 4000, SSD did not bring a big performance boost to the entire system. On the contrary, but to make IO excessive pressure, until even more than 80%.

 

5.3 modify innodb_max_dirty_pages_pct = 25

Modify as follows:

SET GLOBAL innodb_max_dirty_pages_pct = 25;
After revision 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
Prior has innodb_max_dirty_pages_pct to 30, here will innodb_max_dirty_pages_pct down to 25%, in order to see on purpose I / O of dirty data. Modification is the result, I / O fluctuate, innodbBuffPoolPagesFlushed also fluctuate. However, since 24 of the 39 are from the library, yet it does not, all the pressure is not big enough, dirty data is not enough, so do not see the effect of adjusting this parameter.

 

5.4 modify innodb_io_capacity = 2000

Modify method does not repeat them.

After revision 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 case innodb_io_capacity 4000, I / O pressure is too high, so as to adjust the innodb_io_capacity 2000. After adjustment, w / s up to about 2000, however, and I / O until still high, the maximum time of 70%. At the same time we can see that, I / O fluctuation amplitude decreases, innodbBuffPoolPagesFlushed same.

 

5.5 modify innodb_io_capacity = 1500

Modify method does not repeat them.

After revision 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
I / O continued volatility, we then continued to fall innodb_io_capacity, adjusted to 1500. I / O until reduced, I / O continues to decrease volatility, innodbBuffPoolPagesFlushed same.

 

5.6 Close innodb_adaptive_flushing

Modify as follows:

SET GLOBAL innodb_adaptive_flushing = OFF;
After revision 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 the floor is still abnormal, then we can try to shut innodb_adaptive_flushing, let MySQL intervention landing. Adjustments result, the dirty floor or ground, and not affected by I / O pressure, adjusting this parameter is invalid.

 

5.7 Open innodb_adaptive_flushing

Modify as follows:

SET GLOBAL innodb_adaptive_flushing = ON;
After revision 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 adjustment, close innodb_adaptive_flushing no effect, or keep the default open and let it continue to function this function.

 

5.8 Setting innodb_max_dirty_pages_pct = 20

Modify method does not repeat them.

After revision 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 will be down to 20 innodb_max_dirty_pages_pct observe stale data. Because InnoDB Buffer Pool is set to 40G, 20% is 8G, this time the pressure is less than this threshold, the adjustment parameters has no effect. But when business is busy, you can see the effect, floor frequency will be increased.

 

5.9 Setting innodb_io_capacity = 1000

Modify method does not repeat them.

After revision 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 these adjustments, we need is a balanced IO, some room to other processes. So the innodb_io_capacity set to 1000, then you can see I / O until maintained at around 10%, the parameters of the system to stabilize.

We need to do further follow-up monitoring, tracking, analysis and optimization.

6, after the program switch Tuning

Low peak in business, about 1:00, with the research and development done a switch. After switching from the primary relationship you can see sect.

6.1 Setting innodb_max_dirty_pages_pct = 30, innodb_io_capacity = 1500

Modify method does not repeat them.

After revision 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
In innodb_io_capacity of 1000, innodb_max_dirty_pages_pct environment for the next 20, I / O until there are slight fluctuations, and continued alternating peaks and troughs, this situation is undesirable. innodbBuffPoolPagesFlushed relatively stable, but innodbBuffPoolPagesDirty continued to rise, not decline. We made the following adjustments: innodb_max_dirty_pages_pct = 30, innodb_io_capacity = 1500. After the adjustment is completed, innodbBuffPoolPagesDirty stabilized, I / O until relatively stable.

6.2 Setting innodb_max_dirty_pages_pct = 40, innodb_io_capacity = 2000

Modify method does not repeat them.

After revision 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
For the current I / O case, I made the following adjustments: innodb_max_dirty_pages_pct = 40, innodb_io_capacity = 2000.

6.3 Analysis

For the above two adjustments, we have to analyze the data through a combination of monitoring I / O status.

28 8:00 am to 19:00, when the dirty data rises, that is, the more data in memory, then the floor will be very little, showing a stable trend; when dirty data remains unchanged, that is dirty reach the innodb_max_dirty_pages_pct limit (innodb_buffer_pool_size to 40G, innodb_max_dirty_pages_pct 40%, that is, the dirty data in memory up to 16G, each Page 16K, then innodbBufferPoolDirtyPages up to 1000K), the floor will increase, showing an upward trend, so it above picture will appear in the graph.

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
 

7 Summary

The SSD and MySQL InnoDB for parameter optimization, summed up, that is the following three:

Modify the system I / O scheduling algorithm;
Analysis of I / O, the dynamic adjustment innodb_io_capacity and innodb_max_dirty_pages_pct;
Trying to adjust innodb_adaptive_flushing, to see the effect.
For innodb_write_io_threads and innodb_read_io_threads tuning we do not do this, I believe is adjusted to 8 or 16, the system I / O performance will be better.

Also, note the following:

The method described in the article network limitations and contextual, not cronies, not blind obedience, to make any adjustments to be business priorities. To ensure the smooth running of services is the most important performance are followed;
Any adjustments should be based on supporting data and rigorous analysis, based on the otherwise all talk;
Such tuning is very meaningful, is really bring value, so they need to work harder and do as much as possible to understand why such adjustments.
End of the paper, said a little more interesting. An article mentioned before SSDB. SSDB underlying Google's LevelDB, and support Redis protocol. LevelDB design is completely fit SSD design ideas. First of all, as far as possible into continuous writing; secondly, continue to add data files, continue to prevent the same position erase. In addition, SSDB name made very interesting and a very high standard. I guess it is hoped that users will SSDB OF apply it on the SSD.
     
         
       
         
  More:      
 
- GitHub multiplayer co-development configuration (Linux)
- Ubuntu will be written in a command file, executable file, source command (Linux)
- How to convert images, audio and video formats on Ubuntu (Linux)
- Git and GitHub use of Eclipse and Android Studio (Programming)
- Nine tips to protect the security of Linux desktop (Linux)
- Install the Red Hat Container Development Kit on OSX (Server)
- Shell Scripting Basics (Linux)
- CentOS / Linux SWAP partitions added (Linux)
- Use Elasticsearch + Logstash + Kibana set up centralized log Practice Analysis Platform (Server)
- IronPython and C # to interact (Programming)
- Using C / C ++ extensions Python (Programming)
- Linux (RHEL6 CENTOS6 OLE6) VNC-SERVER Installation and Configuration (Server)
- How to migrate MySQL to MariaDB under linux (Database)
- HTTPS Encryption Algorithm (Linux)
- Oracle to create an external table (Database)
- Define and modify strings principle in Python (Programming)
- Difference Docker mirror and containers (Server)
- Simple steps allows you to build a more secure Linux server (Linux)
- JavaScript basic types and type conversion (Programming)
- The difference between IPython and Python (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.