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

This article introduces the knowledge of "how to configure MySQL memory buffer pool". 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!

Memory buffer pool

MySQL is assigned to 512MB RAM; by default at startup, in which InnoDB buffer pool is used to cache table data, indexes and other auxiliary buffer pools. in order to efficiently read a large number of reads, buffer pool partitions pages; to efficiently manage cache, buffer pool uses a variety of LRU (least recently used) algorithms to string adjacent page into chains to manage hot and cold data.

1) configure InnoDB Buffer Pool Size

Innodb_buffer_pool_size is a parameter that controls the size of the buffer pool and is generally recommended for 50% to 70% of the system memory. The default is 128m. Innodb_buffer_pool_size parameter 5.7 can be adjusted online, which must be equal to or an integral multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

For example:

Mysql > SET GLOBAL innodb_buffer_pool_size=402653184

Or my.cnf

[mysqld]

Innodb_buffer_pool_size=402653184

L innodb_buffer_pool_chunk_size parameter defaults to 128m and can only be adjusted at startup (command line or configuration file) in 1m units

Shell > mysqld-- innodb-buffer-pool-chunk-size=134217728

Or my.cnf

[mysqld]

Innodb_buffer_pool_chunk_size=134217728

Innodb_buffer_pool_instances default value is 1

L innodb_buffer_pool_instances defaults to 8 (or 1 if innodb_buffer_pool_size

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

Buffer pool resizing progress is also logged in the server error log

3) preload buffer pool

To preload buffer pool when the server starts, enable the option:

Innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup.

This is the end of "how to configure MySQL memory buffer pool". 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.