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

MySQL 5.7how to dynamically modify the innodb_buffer_pool size, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The MySQL5.7 version starts to support buffer pool dynamic resizing. Each buffer_pool_instance consists of the same number of chunk (chunks array), and each chunk memory size is innodb_buffer_pool_chunk_size (actually larger than 5%, which is used to store block information in the chuck).

Buffer pool increases and shrinks dynamically in terms of innodb_buffer_pool_chunk_size. Innodb_buffer_pool_size should always be a multiple of innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances before and after adjustment.

The experiments are as follows:

C:\ Users\ duansf > mysql-u root-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show variables like 'innodb_buffer_pool%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_chunk_size | 134217728 | |

| | innodb_buffer_pool_dump_at_shutdown | ON |

| | innodb_buffer_pool_dump_now | OFF |

| | innodb_buffer_pool_dump_pct | 25 | |

| | innodb_buffer_pool_filename | ib_buffer_pool |

| | innodb_buffer_pool_instances | 1 | |

| | innodb_buffer_pool_load_abort | OFF |

| | innodb_buffer_pool_load_at_startup | ON |

| | innodb_buffer_pool_load_now | OFF |

| | innodb_buffer_pool_size | 134217728 | |

+-+ +

10 rows in set, 1 warning (0.61 sec)

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 134217728 |

+-+

1 row in set (0.00 sec)

Expand innodb_buffer_pool_size from 134217728 to 268435456

Mysql > SET GLOBAL innodb_buffer_pool_size=268435456

Query OK, 0 rows affected (0.02 sec)

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 268435456 |

+-+

1 row in set (0.00 sec)

-- View log records (files at the end of .err)

2017-03-09T05:41:50.036769Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 268435456 bytes)

2017-03-09T05:41:50.067742Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).

2017-03-09T05:41:50.068754Z 0 [Note] InnoDB: Disabling adaptive hash index.

2017-03-09T05:41:50.229853Z 0 [Note] InnoDB: disabled adaptive hash index.

2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.

2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Latching whole of buffer pool.

2017-03-09T05:41:50.231853Z 0 [Note] InnoDB: buffer pool 0: resizing with chunks 1 to 2.

2017-03-09T05:41:50.257873Z 0 [Note] InnoDB: buffer pool 0: 1 chunks (8192 blocks) were added.

2017-03-09T05:41:50.274899Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 268435456.

2017-03-09T05:41:50.275895Z 0 [Note] InnoDB: Re-enabled adaptive hash index.

2017-03-09T05:41:50.276895Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:41:50.

The process of increasing buffer pool is roughly as follows:

1. Allocate new memory pages in innodb_buffer_pool_chunk_size

2. Expand the AHI (adaptive hash index) linked list of buffer pool to include the newly allocated pages

3. Add the newly assigned pages to the free list

Reduce innodb_buffer_pool_size from 268435456 to 134217728

Mysql > SET GLOBAL innodb_buffer_pool_size=134217728

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 134217728 |

+-+

1 row in set (0.00 sec)

-- View log records (files at the end of .err)

2017-03-09T05:57:42.759623Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 134217728 bytes)

2017-03-09T05:57:42.759623Z 0 [Note] InnoDB: Resizing buffer pool from 268435456 to 134217728 (unit=134217728).

2017-03-09T05:57:42.761625Z 0 [Note] InnoDB: Disabling adaptive hash index.

2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: disabled adaptive hash index.

2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.

2017-03-09T05:57:42.763627Z 0 [Note] InnoDB: buffer pool 0: start to withdraw the last 8192 blocks.

2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0: withdrawing blocks. (8192,8192)

2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0: withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192 amp 8192)

2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: buffer pool 0: withdrawn target 8192 blocks.

2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: Latching whole of buffer pool.

2017-03-09T05:57:42.768631Z 0 [Note] InnoDB: buffer pool 0: resizing with chunks 2 to 1.

2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: buffer pool 0: 1 chunks (8192 blocks) were freed.

2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: Completed to resize buffer pool from 268435456 to 134217728.

2017-03-09T05:57:42.773633Z 0 [Note] InnoDB: Re-enabled adaptive hash index.

2017-03-09T05:57:42.774648Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:57:42.

The process of reducing buffer pool is roughly as follows:

1. Reorganize buffer pool and prepare to recycle pages

2. Release and delete these pages in innodb_buffer_pool_chunk_size (this process will take a little time)

3. Adjust the AHI linked list to use the new memory address.

Attachment: the detailed process of resize is as follows:

If AHI is enabled, disable AHI

If it's shrinking memory,

Calculate the number of chunk to be shrunk, and delete the specified number of chunk from the tail starting with chunks.

Lock buf_pool

Remove the page of the chunk to be deleted from free_list and put it in the list buf_pool- > withdraw

If the page of the chunk to be deleted is a dirty page, brush it dirty

Reload the page to be deleted in LRU, remove it from LRU, and re-get page's old page from the free list and put it in the list buf_pool- > withdraw

Release the buffer pool lock

If the chunk pages to be shrunk is not fully collected, repeat 2-6

Start resize

Lock all instance's buffer_pool,page_hash

Shrink pool: frees memory to be shrunk in units of chunk

Clear withdraw list buf_pool- > withdraw

Increase pool: assign a new chunk

Reassign buf_pool- > chunks

If you change / shrink more than 2 times, the page hash will be reset and the bucket size will be changed.

Release the buffer_pool,page_hash lock

If you change / shrink more than 2 times, it will restart the memory structure related to buffer pool size, such as lock system (lock_sys_resize), AHI (btr_search_sys_resize), data field (dict_resize), etc.

If AHI is disabled, it is enabled at this time.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.