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

This article introduces the relevant knowledge of "how to refresh the adjacency page of the key features of Innodb". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Working principle

When a dirty page is refreshed, the innodb storage engine detects all pages in the extent of the page, and if it is a dirty page, refresh it together. The benefit of this is obvious. Through AIO, multiple IO writes can be merged into a single IO operation, which increases the amount of writing and reduces the physical write IO, so this working mechanism has a significant advantage in traditional mechanical disks.

1. The number of writes is increased without increasing the number of writes.

2. Accelerate the collection of dirty pages.

3. Make full use of the 1m write characteristics of double write.

4. When this function is turned on, you will find that the value of wrqm (merge write) in iostat will be relatively high.

2. Problem consideration

1. Is it possible to write a not-so-dirty page that will soon turn into a dirty page?

2. SSDs have high IOPS. Do you still need this feature?

To this end, the InnoDB storage engine has provided the parameter innodb_flush_neighbors since version 1.2.x to control whether this feature is enabled. For traditional mechanical hard drives, it is recommended to enable this feature, while for solid state drives with ultra-high IOPS performance, it is recommended to set this parameter to 0, that is, to turn this feature off.

3. Parameter control: innodb_flush_neighbors

Mysql > show variables like 'innodb_flush_neighbors' +-+ | Variable_name | Value | +-+-+ | innodb_flush_neighbors | 1 | + -+

1, which means that the ability to refresh adjacent pages is turned on, along with refreshing adjacent dirty pages that are located in the same extend area on disk in buffer pool.

0, which means to turn off refreshing adjacency pages

2, which means to refresh dirty pages that are located in the same extend area on disk in buffer pool.

4. The influence of Flush neighbor page

1. This function is more suitable for systems with frequent insert.

2. For systems with frequent update, this feature may bring some side effects.

1. Update refreshes other pages by the way

2. For update frequent tables, these pages are immediately dirty, wasting the write load.

That's all for "how to refresh the adjacency page with key features of Innodb". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.