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

This article mainly explains "what is the loading method of thermal data after mysql 5.6". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the loading method of hot data after mysql 5.6"?

Failure phenomenon: after the database restart, happened to encounter the business peak, the number of connections is full, resulting in the user login has been waiting, at this time the database is in an unresponsive state.

Fault analysis: when the database is restarted, the hot data in the database buffer needs to be preheated by the innodb itself. If the buffer is too big, it will take a long time for innodb to preheat it. At this time, the hot data needs to be found on the disk, which brings a lot of pressure to the disk. When you encounter a bottleneck, you will wait all the time, resulting in unresponsive data.

Solution: before version 5.6, after restarting mysql, load the hot data into buffer manually. Such as: select count (*) from table_name (hot data table)

After version 5.6, a new feature is provided to quickly warm up the buffer_pool buffer pool. Add several parameters to my.cnf: # innodb_buffer_pool_load_at_startup = on defaults to turning off OFF. If this parameter is enabled, MySQL loads local hot data into the InnoDB buffer pool when the MySQL service is started.

# innodb_buffer_pool_load_now = off defaults to turning off OFF. Manually loaded into the InnoDB cache pool.

# innodb_buffer_pool_filename = ib_buffer_pool if the InnoDB warm-up function is enabled and the MySQL service is stopped, MySQL saves the hot data in the InnoDB buffer pool to the root directory of the database. The default file name is ib_buffer_pool.

# innodb_buffer_pool_load_aborr = off defaults to turning off OFF. If this parameter is enabled, MySQL will not load the hot data from the local hard disk into the InnoDB buffer pool even if the InnoDB warm-up feature is enabled.

After innodb_buffer_pool_dump_at_shutdown=on # 5.7.7, the default is on, which flushes hot data to disk when the database is closed.

# innodb_buffer_pool_dump_now = off defaults to off, which manually exports hot data from the innodb buffer pool to disk

# innodb_buffer_pool_dump_pct = 40 defaults to turning off OFF. If this parameter is enabled, when the MySQL service is stopped, InnoDB saves the percentage of hot data in the InnoDB buffer pool to the local hard disk. Before 5.7.6, the hot data in the InnoDB buffer pool is 100. 5.7.7 starts with 25%, that is, 25% hot data in the cache is saved.

In particular, the two parameters innodb_buffer_pool_load_now and innodb_buffer_pool_dump_now can be executed directly on the command line, directly import and export hot data manually, and can be customized according to the business environment. For example, innodb_buffer_pool_load_aborr abort can be performed directly during execution.

For example, set global innodb_buffer_pool_load_now=1;-- directly loads thermal data

Set global innodb_buffer_pool_load_aborr=1;--- aborts loading

When you close mysql, the hot data in memory is saved in the disk ib_buffer_pool file, which is located in the data directory.

Ps: hot data is exported to disk only when the mysql service or pkill mysql is shut down normally. Machine downtime or pkill-9 mysql will not be exported.

At this point, I believe you have a deeper understanding of "what is the loading method of thermal data after mysql 5.6". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.