MySQLInnoDB存储引擎基本管理20201018

MySQLInnoDB存储引擎基本管理

概述

MySQL 在整体架构上分为 Server 层和存储引擎层。

其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

单点（Single），适合小规模应用，复制（Replication），适合中小规模应用，集群（Cluster），适合大规模应用。

一 MySQL存储引擎与InnoDB体系架构介绍

存储引擎: 称为表类型（说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法）

InnoDB和MyISAM是在使用MySQL最常用的两个表类型。

MySQL用得比较多的就三种存储引擎:MylSAM、InnoDB、MEMORY

。

MySQL 5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。MySQL 8.0以后废弃了MylSAM。

 

 

 

 

 

1.1区别MyISAM，InnoDB，MEMORY

1.1.1 MyISAM

MyISAM：是5.5以前默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

这种存储引擎不支持事务，不支持行级锁（支持表锁），只支持并发插入的表锁，主要用于高负载的select。

注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

 

1.1.2 InnoDB：

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，支持行级锁、使用了B+Tree索引、支持自动增长列，支持外键约束。用于事务处理应用程序，具有众多特性，包括ACID事务支持。(提供行级锁)，5.5以后默认使用InnoDB存储引擎。存储形式为：.frm 表定义文件 .ibd 数据文件

1.1.3 Memory

· Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

使用存在于内存中的内容来创建表。

每个memory表只实际对应一个磁盘文件，格式是.frm，该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。因为它的数据是放在内存中的，但是一旦服务关闭，表中的数据就会丢失掉。

存储引擎默认使用哈希( HASH )索引,其速度比使用B-+Tree型要快。

Hash索引结构:其检索效率非常高,索弓|的检索可以一次定位。

B-Tree索引:需要从根节点到枝节点,最后才能访问到页节点这样多次的I0访问。

所以Hash索弓|的查询效率要远高于B-Tree索引。

虽然Hash索引效率高,但是Hash索引本身由于其特殊性也带来了很多限制和弊端,功能有限,支持也有限。

· BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

· Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

· Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

· Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

· Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

· Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

 

1.2 InnoDB存储引擎-内存结构

 

 

1.2.1 InnoDB Buffer Pool

InnoDB Buffer Pool :不仅仅缓存索引数据,还会缓存表的数据,而且完全按照数据文件中的数据快结构信息来缓存,这一点和Oracle SGA中的database buffer cache非常类似。所以，InnoDB Buffer Pool对InnoDB存储引擎的性能影响之大就可想而知了。

需要说明index page 即包括索引也包括数据（数据记录缓存）

Insert buffer 主要是内存写磁盘，index page主要是解决读磁盘，缓存数据。

1.2.2 Additional Memory Pool: ;

其参数innodb_ additional mem_ pool_ size 是InnoDB用来保存数据字典信息和其他内部数据结构的内存池的大小,单位是byte ,参数默认值为8M。数据库中的表数量越多,参数值应该越大,如果InnoDB用完了内存池中的内存,就会从操作系统中分配内存，同时在error log中打入报警信息,这个参数以后会被弃用。

1.2.3 redo buffer

innodb_ log_ _byffer_ size的大小: (默认8M)

将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中的3种情况:

1、Master Thread每一秒将重做日志缓冲刷新到重做日志文件 ;

2、每个事务提交时会将重做日志缓冲刷新到重做日志文件;

3、当重做日志缓冲池剩余空间小于1/2时 ,重做日志缓冲刷新到重做日志文件。

1.2.4 二进制日志缓冲区( Binlog Buffer )

主要用来缓存由于各种数据变更操做所产生的Binary Log信息。为了提高系统的性能，MySQL并不是每次都是将二进制日志直接写入Log File，而是先将信息写入BinlogBuffer中，当满足某些特定的条件之后再一次写入Log File文件中。

二进制日志和重做日志的对比3 :

类型

二进制日志:记录MySQL数据库相关的日志记录，包括InnoDB , MyISAM等其它存储引擎的日志。重做日志:只记录InnoDB存储弓|擎本身的事务日志。

内容

二进制日志:记录事务的具体操作内容,是逻辑日志。重做日志:记录每个页的更改的物理情况。

时间

二进制日志:只在事务提交完成后进行写入,只写磁盘一次,不论这时事务量多大。

重做日志:在事务进行中,就不断有重做日志条目(redo entry)写入重做日志文件。

1.2.5 Doube Write :

Doube Write :

是innodb表空间ibdata中一块连续的128 page=2M的存储空间 ，它的作用的是处理产生partial write时候的data recovery。

比如:如果发生了极端情况(断电)，InnoDB再次启动后，发现了一个Page数据已经损坏那么此时就可以从doublewrite buffer中进行数据恢复了。

它的主要工作原理:

A . dirty page刷新到数据文件之前，先刷到double write buffer里。

B .然后将page内容刷新到数据文件中。

 

1.2.6 二进制日志和重做日志的对比 :

1类别

重做日志:只记录InnoDB存储引擎本身的事务日志。

二进制日志:记录MySQL数据库相关的日志记录，包括InnoDB，MyISAM等其它存储引擎的日志。

2内容

二进制日志:记录事务的具体操作内容，是逻辑日志。

重做日志:记录每个页的更改的物理情况。

3时间

二进制日志∶只在事务提交完成后进行写入，写磁盘一次，不论这时事务量多大。

重做日志:在事务进行中，就不断有重做日志条目(redo entry)写入重做日志文件。

1.3 InnoDB存储引擎-逻辑存储结构

Oracle是表空间、段、区、块

MySQL是表空间、段、区、页

表空间:所有的数据都放在表空间里面。

段:表空间有若干各段组成，常见的有数据段/索引|段/回滚段等

区:每64个连续的页组成区，因此区大小正好为1M。

页:页是InnoDB磁盘管理的最小单位,固定大小为16K。

行: InnoDB表中数据按行存储。

1.3.1表空间

表空间:所有数据都是存放在表空间中的, 启用了参数innodb_file_per_table ,则每张表内的数据可以单独放到一个表空间中,每张表空间内存放的只是数据,索引和插入缓冲，其他类的数据,如undo信息，系统事务信息，二次写缓冲等还是存放在原来你的共享表空间。

1.3.2段(segment)

段(segment) :常见的segment有数据段、索引段、回滚段。innodb是索引聚集表，所以数据就是索引,索引就是数据,那么数据段即是B+树的页节点(leaf node segment) ,索|段即为B+树的非索引节点(non-leaf node segment) ,而且段的管理是由引擎本身完成的。

1.3.3区(extend)

区(extend)：区是由64个连续的页主成,每个页大小为16K，即每个区的大小为(64* 1 6K)=1MB,对于大的数据段，mysql每次最多可以申请4个区,以此保证数据的顺序性能。

1.3.4页(page)

页(page)页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。

常见的类型有:

数据页B-tree Node ;

undo页Undo Log Page ;

系统页System Page ;

事务数据页Transaction system Page ;

插入缓冲位图页Insert Buffer Bitmap ;

插入缓冲空闲列表页Insert Buffer freeBitmap ;

未压缩的二进制大对象页Uncompressed BLOB Page ;

压缩的二进制大对象页Compressed BLOB Page.

1.3.5 行

行:innodb存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页最多可以存放16K/2 ~ 200行,也就是7992个行。

二 InnoDB存储引擎的常用参数配置

2.1 启用innodb存储引擎

默认就是InnoDB

show variables like 'default_stor%';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| default_storage_engine | InnoDB |

+------------------------+--------+

1 row in set (0.00 sec)

在my.cnf加入参数即可，重启mysql服务

default-storage-engine=INNODB

2.2 INNODB重要参数

show variables like '%innodb%';

2.2.1 innodb_buffer_pool_size(类似ORACLE的SGA)

用户innodb数据和索引的缓存，默认128M，innodb最重要的性能参数，建议值不超过80%，一般是75%。

(如果数据量小，可以是数据量+10%，数据量20G，物理内存是32G，这时候可以设置buffer pool为22G)

show variables like '%innodb_buffer_pool_size%';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| innodb_buffer_pool_size | 536870912 |

+-------------------------+-----------+

1 row in set (0.00 sec)

 

在my.cnf加入参数即可，重启mysql服务

default-storage-engine=INNODB

innodb_buffer_pool_size=256G

innodb_buffer_pool_size=512M

 

2.2.1 innodb_log_buffer_size（日志缓冲区）

默认是16M就够了。

show variables like '%innodb_log_buffer_size%';

+------------------------+----------+

| Variable_name          | Value    |

+------------------------+----------+

| innodb_log_buffer_size | 16777216 |

+------------------------+----------+

1 row in set (0.00 sec)

2.2.3 innodb_log_file_size

指定重做日志大小,数据库挂了以后的操作。

5.5以前最大是4G，5.6>512G。

小业务256M够了，中型业务一般保持在2G左右，

innodb log顾名思义：即innodb存储引擎产生的日志，也可以称为重做日志文件，默认在innodb_data_home_dir下面有两个文件ib_logfile0和ib_logfile1。MySQL官方手册中将这两个文件叫文InnoDB存储引擎的日志文件；

innodb log的作用：当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性；

innodb log的写原理：

看红色框框的那部分

每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1；

日志组中每个重做日志的大小一致，并循环使用；

InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；

为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面；

（发现以上特性跟ORACLE的连接重做日志文件简直是一样的）

二、innodb log的相关参数

运行脚本：show variables like 'innodb%log%'; 查看重做日志的相关参数

mysql> show variables like 'innodb%log%';

常用设置的参数有：

innodb_mirrored_log_groups  镜像组的数量，默认为1，没有镜像；

innodb_log_group_home_dir  日志组所在的路径，默认为data的home目录；

innodb_log_files_in_group    日志组的数量，默认为2；

innodb_log_file_size              日志组的大小,默认为5M；

innodb_log_buffer_size        日志缓冲池的大小，图上为30M;

三、参数的相关调优

3.1 重做日志文件的大小设置跟ORACLE一样，面临的问题是相似的。

当innodb log设置过大的时候，可能会导致系统崩溃后恢复需要很长的时间；

当innodb log设置过小的时候，当一个事务产生大量的日志的时候，需要多次切换重做日志文件，会产生类似如下的报警；

130702 12:53:13  InnoDB: ERROR: the age of the last checkpoint is 2863217109,

InnoDB: which exceeds the log group capacity 566222311.

InnoDB: If you are using big BLOB or TEXT rows, you must set the

InnoDB: combined size of log files at least 10 times bigger than the

四、重做日志与二进制日志的区别

4.1  记录的范围不同：二进制日志会记录MySQL的所有存储引擎的日志记录（包括InnoDB、MyISAM等)，

而InnoDB存储引擎的重做日志只会记录其本身的事务日志。

4.2 记录的内容不同：二进制日志文件记录的格式可以为STATEMENT或者ROW也可以是MIXED，其记录的都是关于一个事务的具体操作内容。

InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况。

4.3 写入的时间也不同：二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入到重做日志文件中。

 

 

show variables like '%innodb_log_file%';

+---------------------------+-----------+

| Variable_name             | Value     |

+---------------------------+-----------+

| innodb_log_file_size      | 209715200 |

| innodb_log_files_in_group | 2         |

+---------------------------+-----------+

2 rows in set (0.00 sec)

 

 

2.2.4 innodb_flush_log_at_trx_commit

(控制事务的提交方式，控制日志刷新到硬盘的方式)

 

show variables like '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

有3个值:0,1,2默认是1

0:每秒1次写入到log file中，同时会进行文件系统到磁盘的同步操作，但每个事务的提交不会从log buffer到log file。速度快，不安全，出现故障会丢失一秒的事务，比如游戏数据库建议设置为0，不会触发文件系统到磁盘的同步。

1：每个事务的提交commit会从log buffer到LOG file。同时触发文件系统到磁盘的同步操作，同时触发文件系统到磁盘的同步操作。最安全。

2：每个事务的提交commit会从log buffer到LOG file，不会触发文件系统到磁盘的同步。不会触发文件系统到磁盘的同步。但是每秒会有一次文件系统到磁盘的同步。

2.2.5 innodb_flush_method

lfinnodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default  lf innodb_flush_method is set to NULL on Windows, the async_unbuffered option is used by default.

 

 

 

 

show variables like '%innodb_flush_method%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| innodb_flush_method |       |

+---------------------+-------+

1 row in set (0.00 sec)

2.2.6系统表空间与临时表空间路径

show variables like '%innodb%data%file%';

+----------------------------+----------------------------------------------------------+

| Variable_name              | Value                                                    |

+----------------------------+----------------------------------------------------------+

| innodb_data_file_path      | ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G |

| innodb_temp_data_file_path | ibtmp1:200M:autoextend:max:20G  

 

ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G

ibtmp1:1G:autoextend:max:20G 

三  InnoDB buffer pool原理与配置

数据在内存中，读取就快，读硬盘特别慢。

 

3.1 buffer pool 参数

show variables like '%innodb_buffer_pool_size%';

3.1.1 innodb_buffer_pool_instances与innodb_buffer_pool_size参数分析

 

innodb_buffer_pool_size（缓冲池大小）

innodb_buffer_pool_chunk_size（定义InnoDB缓冲池大小调整操作的块大小）

innodb_buffer_pool_instances（InnoDB 缓冲池划分为的区域数-可以并发提高性能）

 

innodb_buffer_pool_size必须始终等于innodb_buffer_pool_chunk_size或 innodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size  或  innodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size 或 innodb_buffer_pool_instances的倍数。

1 innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数。

除32位Windows系统的其它所有平台上innodb_buffer_pool_instances参数的默认值为：

innodb_buffer_pool_size<1G时，默认值为1；

innodb_buffer_pool_size>1G时，默认值为8。

2、在Linux平台上，innodb_buffer_pool_size大于或等于1GB 时，默认值为8 。否则，默认值为1。

innodb_buffer_pool_instances参数的作用，要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为 大于1（默认）的值，最大为64（最大）。仅当您将innodb_buffer_pool_size大小设置为1GB或更大时，此选项才生效 。您指定的总大小将分配给所有缓冲池。为了获得最佳效率，指定的组合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。

3 innodb_buffer_pool_instanes的值最大为64，innodb_buffer_pool_instances 和 innodb_buffer_pool_size的组合，每个缓冲池实例至少为1GB。

1 MySQL企业用户的实际环境（大内存）：

1、在专用数据库服务器上，可以将innodb_buffer_pool_size设置为计算机物理内存大小的80％;

2、在innodb_buffer_pool_size设置比较大的情况下，可以将innodb_buffer_pool_instances的值设置为8-16，或者CPU的个数，保证一个pool 10G以上。

（注意innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数）

 

 

3.2 buffer pool原来与内部结构：

InnoDB缓冲池是通过LRU算法来管理page的。频繁使用的page放在LRU列表的前端，最少使用的page在LRU列表的尾端，缓冲池满了的时候，优先淘汰尾端的page。

   InnoDB在内存中维护一个缓存池用于缓存数据和索引。缓存池可以被认为一条长LRU链表，该链表又分为2个子链表，一个子链表存放old pages(里面存放的是长时间未被访问的数据页)，另一个子链接存放new pages（里面存放的是最近被访问的数据页面）。old pages 默认占整个列表大小的37%（InnoDB_old_blocks_pct参数的默认值为37，取值范围是5~95），其余为new pages占用，如图下图所示。靠近LRU链表头部的数据页表示最近被访问，靠近LRU链表尾部的数据页表示长时间未被访问，而这两个部分交汇处成为midpoint。

这个页第1次读取的时候，该页先放到MID point的位置;

当被读到的第2次，才将这个页放到newpage的首部。

 

MID point > new page

MID point > old page > new page

MIDpoint>oldpage>刷回磁盘

MID point > newpage>oldpage>刷回磁盘

 

show variables like 'innodb_old%';可以查看InnoDB缓冲池结构的参数信息。

innodb_old_blocks_pct:控制old page子链表在LRU链表中的长度。

innodb_old_blocks_time:控制old page子链表的数据页移动到new page 子链表中的时机。

 

nnodb_old_blocks_pct参数是控制进入到sublist of old blocks区域的数量，初始化默认是37.

innodb_old_blocks_time参数是在访问到sublist of old blocks里面数据的时候控制数据不立即转移到sublist of new blocks区域，而是在多少微秒之后才会真正进入到new区域，这也是防止new区域里面的数据不会立即被踢出。

 

 

 

 

3.3 LRU list、free list、flush list

A.free list:启动时，有多个16K的空白页，这些页就存在free list中。

B.LUR list :当读取-个数 据页的时候，就从free list中取出一个页，存放数据，并将这个页放入到LUR list。

C. flush list: 当LUR list中的页第一次被修改时，就将页的指针(page number)放 到flush list（只要被修改过，无论改了多少次），就将页的指针(page number)放 到flush list。

这个页第1次读取的时候，该页先放到MID point的位置;

当被读到的第2次，才将这个页放到newpage的首部。

free list > LUR list>flush list>磁盘> free list

show engine innodb status -来观察LRU列表及Free列表的状态。

 

show engine innodb status\G

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4395630592;

Dictionary memory allocated 28892957

Buffer pool size   262143

Free buffers       0

Database pages     258559

Old database pages 95424

Modified db pages  36012

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 72342127, not young 0

8.82 youngs/s, 0.00 non-youngs/s

Pages read 72300801, created 339791, written 13639066

8.56 reads/s, 0.35 creates/s, 3.79 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 258559, unzip_LRU len: 0

I/O sum[459]:cur[1], unzip sum[0]:cur[0]

Total memory allocated 4395630592;---总分配mysql内存

Dictionary memory allocated 28892957---数据字典内存

Buffer pool size表示缓冲池共有262143个page，即262143 * 16K/1024/1024，约为4GB

Free buffers表示当前Free列表中page的数量

Database pages表示LRU列表中page的数量

Old database pages表示LRU列表中old部分的page数量

Modified db pages表示的是脏页(dirty page)的数量

Pages made young表示LRU列表中page移动到new部分的次数

youngs/s, non-youngs/s表示每秒这两种操作的次数

Buffer pool hit rate表示缓冲池的命中率，该值若小于95%，需要观察是否全表扫描引起LRU污染

LRU len表示LRU中总page数量

可以看到Free buffers与Database pages的和不等于Buffer pool size，这是因为缓冲池中的页还会被分配给自适应哈希索引，Lock信息，Insert Buffer等页，这部分页不需要LRU算法维护。

脏页(dirty page)

LRU列表中的page被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时InnoDB通过Checkpoint机制将脏页刷新回磁盘。而Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，又存在于Flush列表中，二者互不影响。Modified db pages显示的就是脏页的数量。

 

3.4 buffer pool 预热

mysql 5.6 <每次启动buffer pooL中页是空的，每次都需要大量的时间加载新的页到内存中，启动后有一段时间性能差。

mysql 5.6 >每次停机会dump出buffer poo l的数据( SPACE, page number)， 然后启动时load进buffer pool,预热。

MySQL服务启动一段时间后，InnoDB会将经常访问的数据（包括业务数据，管理数据）置入InnoDB缓冲池中，即InnoDB缓冲池保存的是频繁访问的数据（简称热数据）。当InnoDB缓冲池的大小是几十GB甚至是几百GB时，由于某些原因（例如数据库定期维护）重启MySQL服务，如何将之前InnoDB缓冲池中的热数据重新加载到InnoDB缓冲池中？简单地说：如何对InnoDB缓冲池进行预热，以便于MySQL服务器快速地恢复到重启MySQL服务之前的性能状态？

innodb缓冲池预热功能可以加载磁盘上dump下来的buffer信息到内存buffer pool中，这个功能可以加快业务查询（指任何关联该数据的操作，不仅限于select） 速度。如果关闭该功能，在数据库刚打开时，内存是空的，没有加载任何业务数据，初始连接的业务基本上都需要从物理磁盘中读取数据到内存中。

show variables like '%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         | 40             |

| innodb_buffer_pool_filename         | ib_buffer_pool |

| innodb_buffer_pool_instances        | 8              |

| innodb_buffer_pool_load_abort       | OFF            |

| innodb_buffer_pool_load_at_startup  | ON             |

| innodb_buffer_pool_load_now         | OFF            |

| innodb_buffer_pool_size             | 1073741824     |

+-------------------------------------+----------------+

 

innodb_buffer_dump_at_shutdown：默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_at_startup:默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中。

innodb_buffer_pool_load_now:默认为关闭OFF。如果开启该参数，启动MySQL服务时，以手动方式将本地热数据加载到InnoDB缓冲池。

innodb_buffer_pool_dump_now默认为关闭OFF。如果开启该参数，停止MySQL服务时，以手动方式将InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

 

innodb_buffer_pool_dump_pct

#关闭mysql服务时，转储活跃使用的innodb buffer pages的比例，默认25%；配合innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown 两个参数同时使用#

#如果启用新的参数比如40 ，每个innodb buffer pool instance中有100个 ，每次转储每个innodb buffer 实例中的40个pages#

innodb_buffer_pool_filename         | ib_buffer_pool

指定本地缓存文件名字

 

3.5 buffer pool调整

mysql 5.7 <不能在线调整，需要重启才生效。

mysql 5.7 >可以在线调整，需要改my. cnf后重启永久生效。

什么时候需要调整:

1)机器增加物理内存

2)性能原因或历史原因，需要调整。

不要在业务繁忙时间调整，尽量在非业务时间。

调整时，会按块的方式去调整和移动单位是chunk 128M，innodb_ buffer_pool_ chunk_ size

调整innodb_buffer_pool_size大小

show variables like '%innodb_buffer_pool_size';

比如服务器128G内存*0.8=102G。设置不大于80%。

select 100*1024*1024*1024 from dual;

+--------------------+

| 100*1024*1024*1024 |

+--------------------+

|       107374182400 |

+--------------------+

1 row in set (0.00 sec)

 

我的环境是3G，我现在先调整由1G到2G

mysql> set global innodb_buffer_pool_size=2048M;

ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

mysql> set global innodb_buffer_pool_size=2G;

ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

 

mysql> select 2*1024*1024*1024 from dual;

2147483648

 

set global innodb_buffer_pool_size=2147483648;

Query OK, 0 rows affected (0.00 sec)

 

show variables like '%innodb_buffer_pool_size';

+-------------------------+------------+

| Variable_name           | Value      |

+-------------------------+------------+

| innodb_buffer_pool_size | 2147483648 |

+-------------------------+------------+

1 row in set (0.00 sec)

在修改Mysql参数文件-（就可以永久生效了，重启也不怕）

innodb_buffer_pool_size = 2G

更加直观

select * from information_schema.INNODB_BUFFER_pool_stats;