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

This article mainly introduces what are the new features of innodb in MySQL, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

4.1 innodb buffer dump feature enhancement

5.7.5 A new innodb_buffer_pool_dump_pct parameter is added to control the proportion of innodb buffer pages actively used in each innodb buffer dump. The default value of the previous version is 100%. When a dump is triggered, it will contain all the pages in the dump innodb buffer pool. If you enable a new parameter such as 40, there are 100 in each innodb buffer pool instance, dumping 40 pages in each innodb buffer instance at a time.

Note: when innodb finds that the innodb backend io resources are tight, it will actively reduce the proportion of this parameter setting.

4.2 support for multithreading dirty pages

In version 5.6.2 of MySQL, MySQL separates the threads of dirty pages from master threads. After version 5.7.4, MySQL system supports multithreading of dirty pages, and the number of processes is controlled by the innodb_page_cleaners parameter, which cannot be dynamically modified. The minimum value is 1, and the maximum value supports 64 Magi 5.7.7 and the previous default value is 1. After version 5.7.8, the default parameter is 4. When multithreading is enabled, the system allocates refresh innodb buffer instance dirty pages to each idle thread that brushes dirty pages. If innodb_page_cleaners > innodb_buffer_pool_instances is set, the system will automatically reset to innodb_buffer_pool_instances size.

4.3 dynamically adjust innodb buffer size

From version 5.7.5, MySQL supports dynamic adjustment of innodb_buffer_pool_size without rebooting the system. The process of resize is to migrate the pages to the new memory space in units of chunk (the size of each chunk defaults to 128m), and the migration progress can be checked through Innodb_buffer_pool_resize_status. Keep in mind that the size of the entire resize is in chunk units. The size of innodb_buffer_pool_chunk_size is calculated as innodb_buffer_pool_size / innodb_buffer_pool_instances, and the newly adjusted value must be an integral multiple of innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances. If it is not an integer multiple, the system adjusts the value to greater than the maximum value of the product of the two.

Examples

Mysql > SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

+-+

| | Variable_name | Value |

+-+

| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. | |

+-+

1 row in set (0.00 sec)

Mysql > set global innodb_buffer_pool_size=128*1024*1024

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

+-+

| | Variable_name | Value |

+-+

| | Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 160702 23:53:51. | | |

+-+

1 row in set (0.00 sec)

Mysql > set global innodb_buffer_pool_size=256*1024*1024

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

+-+

| | Variable_name | Value |

+-+

| | Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 160702 23:54:19. | |

+-+

1 row in set (0.00 sec)

The general process of online adjusting the log record of bp size

A calculate the bpsize to be adjusted

B disable AHI and clean up all index caches

C Withdrawing block is the free block that can be used by traversing the freelist

D lock the entire buffer pool

E Migration reassign chunk/ delete chunk that can be released

F set innodb_buffer_pool_size to the new value

G restart AHI

2016-07-02T15:40:44.724495Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).

2016-07-02T15:40:44.724546Z 2 [Note] InnoDB: Resizing buffer pool from 134217 (new size: 268435456 bytes)

2016-07-02T15:40:44.724559Z 0 [Note] InnoDB: Disabling adaptive hash index.

2016-07-02T15:40:44.724979Z 0 [Note] InnoDB: disabled adaptive hash index.

2016-07-02T15:40:44.725029Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.

2016-07-02T15:40:44.725040Z 0 [Note] InnoDB: Latching whole of buffer pool.

2016-07-02T15:40:44.725210Z 0 [Note] InnoDB: buffer pool 0: resizing with chunks 1 to 2.

2016-07-02T15:40:44.735439Z 0 [Note] InnoDB: buffer pool 0: 1 chunks (8192 blocks) were added.

2016-07-02T15:40:44.735511Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 268435456.

2016-07-02T15:40:44.735561Z 0 [Note] InnoDB: Re-enabled adaptive hash index.

2016-07-02T15:40:44.735586Z 0 [Note] InnoDB: Completed resizing buffer pool at 160702 23:40:44.

This feature is one of the most anticipated features of many MySQL DBA. After the online dynamic expansion, there is no need to do database switching, which indirectly enhances the stability of the system and the well-being of DBA life. Of course, the introduction in this article is a little sketchy.

4.4 supports global tablespaces

Global tablespaces can be shared by all database tables, and compared to file-per-table tablespaces. Using shared tablespaces can save memory on metadata. (more in-depth understanding of shared tablespaces is mainly about size shrinkage)

Mysql > CREATE TABLESPACE `youzan_ com`

-> ADD DATAFILE 'youzan_com.ibd' FILE_BLOCK_SIZE = 16k

Query OK, 0 rows affected (0.02 sec)

Mysql > use yang

Database changed

Mysql > create table yztb (id int primary key not null, val char (10)) engine=innodb default charset=utf8 TABLESPACE youzan_com

Query OK, 0 rows affected (0.04 sec)

Mysql > create database youzan default charset utf8

Query OK, 1 row affected (0.02 sec)

Mysql > use youzan

Database changed

Mysql >

Mysql > create table yztb (id int primary key not null, val char (10)) engine=innodb default charset=utf8 TABLESPACE youzan_com

Query OK, 0 rows affected (0.03 sec)

4.5 the line format defaults to DYNAMIC

Starting from MySQL 5.7.9, the row format DYNAMIC replaces COMPACT as the default row format of the innodb storage engine. MySQL provides a new parameter innodb_default_row_format to control the line format of Innodb.

4.6 support for native partition tables

In versions prior to MySQL 5.7.6, MySQL created a ha_partition handler for each partition when creating partition tables. Since MySQL 5.7.6, MySQL supports native partition tables and creates only one partition-aware handler for partition tables, which enhances the memory savings used by partition tables. What about the partition table created by the old version after upgrading to the new version? Don't panic, after 5.7.9, MySQL provides the following upgrade methods to solve this problem:

ALTER TABLE... UPGRADE PARTITIONING.

Of course, friendly reminder: from my personal understanding, it is not recommended to use partition tables without a suitable automatic maintenance partition table system. After four years of work experience, I have fallen into a hole in the partition table several times.

4.7 support for truncate undo logs

MySQL version 5.7.5 begins to support undo log in truncate undo tablespaces. Innodb_undo_log_truncate= [ON | 1] must be set to enable this feature. The general principle is that the system must set up at least two undo tablespaces (innodb_undo_tablespaces=2 during initialization) to clean up undo logs switching. The advantage of this feature is that it solves the problem that the ibdata file has been growing and reduces the use of space in the system.

Thank you for reading this article carefully. I hope the article "what are the new features of innodb in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you 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.