Configuring the innodb_flush_neighbors parameter in Mysql

Hello everyone. Today we wanted to comment with you a Mysql parameter that is little known and that can be very important in the way that the Scriptures are made to our disk system.

This parameter is innodb_flush_neighbors, but what does this parameter do?

This parameter is used to indicate to the Mysql as we want to make the copy of the modified pages that we have in memory to disk. Actually, it helps us to indicate that it does with similar pages that are in the same extent (group of pages) that the manager needs to copy.

The values that it allows are:

  • 1. When you copy a page from the Innodb buffer to disk, find pages that can belong to the same extension that are contiguous and copy them together. Is the default parameter up to 8.0.2.
  • 2. Not only copy the contiguous pages, but look for all the pages that are in the same extent even if they are not contiguous and copy them to disk.
  • 0 disables functionality.

 

What advantage does it bring to have the value to 1 to come by default?.

When data from the tables were stored on HDD disks contiguous block writing significantly reduced the input/output overhead as it was a much less costly operation on these disks than the writing of individual blocks in Different moments. As we have commented, this configuration was used with traditional disks, but one of the most commonly used improvements that is usually implemented is the use of SSD disks. On these disks, this writing mode does not produce benefits, but can backfire in environments with a large volume of write operations. It is for this reason that from the 8.0.3 version the default parameter is 0 and it has to be changed in the case of using HDD disks.

For Pre-8.0.3 versions where SSD discs are used, it is advisable to review this parameter and leave it at 0.

We hope you will be useful.

Greetings.

DBA Team.