InnoDB 内存（一）

文章目录[显示]

如下图：左面最大的就是 buffer pool 里面放数据和索引，也可以理解为 buffer cache
还有其他的内存块（change buffer， double write buffer，page hash ，adaptive hash index（自适应哈希索引），redo log buffer 等等）

主要的四块：buffer pool ，redo log adaptive change buffer，double write （2K 比较小）

（从一个大哥那里偷的图）地址是：

http://blog.csdn.net/jarvan_song/article/details/53022944

1521637711(1).jpg

内存：

innodb buffer pool size 一般设置为物理内存的 50%-80% （5.7 以后的buffer pool 可以在线的动态修改，之前版本设置的不合适都要重启实例）buffer pool 以 trank 为最小单位，默认是128K，高并发的时候肯定不够。
设置太大，会导致操作系统剩余的内存太少，最后可能导致swap。
每个缓冲区池管理其自己的数据
使用多个buffer pool instance 降低并发内存争用，提高并发性（也就是把多个内存块，分成多个小的内存块）（buffer pool 需要自己去设置多个instance 才会拆分，不会自动去分多个instance）（参数为innodb_buffer_pool_instances=n）（每个instance 会自己管理自己的 free list（空闲列表），flush list（刷新列表），LRU list 及其他，并且自己的instance 会控制自己的 buffer pool mutex 负责并发控制，不是全局的）
page 采用hash 算法自动分配到多个 instance 中读写

可以设置多个 instance （实例）的有四个参数： innodb_buffer_pool_instances， matadata_locks_hash_instances，table_open_cache_instances，innodb_adaptive_hash_index_parts（自适应哈希索引也可以多个分区）

实例启动

在内存启动的时候会预加载，也就是说我们在实例关闭的时候设置两个选项
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_at_shutdown
这样就可以在实例关闭的时候把 buffer pool 里面的热数据dump 出来，成立一个文件，等实例再启动的时候再 load 进去，这样就可以确保我们的实例重启之间的热数据能尽快的加载到内存里面，从而避免读雪崩。

为什么会使用预加载？

因为如果一旦实例重启的，可能之前的数据都没了，然后所有的读数据都有可能需要从物理上读取，也就是本来很多的读都从逻辑读都变成了物理读，所以性能会很差，这两个选项可以避免此种情况发生（5.7 以上支持）

产生的文件为 ib_buffer_pool
在实例关闭的时候，会把 buffer pool里面 page的索引记录下来，因为一个page 只需要记录他的 space id ，page number（这两个参数就可以找到对应的是那个page），然后启动的时候再把ib_buffer_pool 读出来，其中 dump 很快，load 的话可能会相对来说慢一些。

buffer pool 管理

innodb buffer pool 刷新的机：innodb_flush_method=O_DIRECT

LRU机制（最近最少使用）
buffer cache 有两个队列分别是，Young，Old
。新产生的优先放在 buffer pool的Young page里面，当放入的时间超过innodb_old_blocks_time （默认是1秒，单位是毫秒），并且没有再次被访问的话，会从young 队列里面，移出到 Old队列
Old 队列默认占比 innodb_old_blocks_pct=37（默认37% ）

redo log

redo log ib_logfile 的文件个数是由innodb_log_files_in_group这个选项配置决定的，至少要求是 >=2，文件序列号从0开始，从ib_logfile0到 ib_logfileN，文件为顺序写入，循环使用，当达到最后一个文件末尾时候，会从第一个文件开始顺序复用，redo文件切换时，会执行一次checkpoint（刷redo log，刷 dirty page）。

为什么 redo log ib_logfile 的个数要大于等于2？

因为 innodb的 redo log 是循环使用的，在一个log进行checkpoint 的时候另一个 log 就需要被启用，所以说至少要求两个 log file，才不会在切换的时候造成事务的不可用。

redo 记录的是新的状态；undo记录的是旧的状态

redo log 主要是记录事务操作的变化，记录的是数据被修改之后的值（undo记录的是数据被修改之前的值）
5.7 开始不会记录临时表空间上的变化，因为从5.7 开始有独立的临时表空间，而临时表空间文件只需要回滚，不需要前滚，所以不会记录在 redo上，只需要记录再undo上。
redo 跟undo类似，更多记录的是逻辑操作（比如说某一个 space id 某一个 page number的某一个key number对他进行操作，做了什么操作，修改之前是什么样的，修改之后是什么样的，不需要把全部的物理块都记录下来，所以说 redo log 相对来说要小很多，类似binlog，如果该page 本身破坏了，则无法恢复出正确的数据，所以需要用到 double write buffer，先写入到 double write buffer 里面。
数据库里面的数据修改的时候，产生的redo会先放在 redo log buffer里面，然后再刷盘（先生成 redo log buffer，再根据选项innodb_flush_log_at_trx_commit 来决定刷盘的机制和频率）
redo 有一个 LSN（log sequence number），是一个递增的整数，但不是顺序递增，是根据实际事务的大小来增加的，表示每次生成的 redo log 总的字节数，比如说当前的redo log 的大小是2048，那新生成的一个事务的字节数是20 个字节，那么 redo log 就变成 2068，如果再来一个事务 50个字节，那么就变成 2118
每次写盘后（也就是事务提交以后） redo log是否及时刷盘，由 innodb_flush_log_at_trx_commit 来决定的，下面会说明
5.5 版本以后，redo 的大小不会特别影响 crash recovery的耗时（5.5 以前 crash recovery 的时候会把整个的 redo log 全部扫一遍，然后再决定要从哪里开始恢复），5.5 以后只会影响 checkpoint 频率，设置较大可减少I/O的消耗，如果 redo log 很大，就不会频繁的进行 checkpoint，如果 redo log 很小的话，就会频繁的进行checkpoint

redo 刷盘

redo log buffer 刷新条件：（有可能一秒刷一次，有可能每个事务提交刷一次）

master thread 每秒进行刷新
设置 innodb_flush_log_at_timeout（默认一秒）
当发现 redo log buffer 使用大于一半的时候也可能会进行刷新
或者是事务提交时进行刷新-------innodb_flush_log_at_trx_commit={0|1|2}

innodb_flush_log_at_trx_commit

0 ：表示事务提交的时不立刻把 redo log buffer 写盘，而是每秒刷盘。
1 ：表示事务提交时立刻将 redo log buffer 写入磁盘并且必须刷盘。（建议使用）
2 ：表示事务提交时仅将 redo log buffer 写入操作系统缓存，但是不立刻刷盘，而是每秒刷盘。

sync_binlog

指的是MySQL 的二进制日志（binary log）同步到磁盘的频率，一般配合innodb_flush_log_at_trx_commit 使用，有个双一设置后面会说。

不同于innodb_flush_log_at_trx_commit ，sync_binlog的取值可以使 0到 N 任意值

0：表示当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而是让文件系统自行决定什么时候来做同步，或者cache满了之后才刷到磁盘。所以说性能也是最好的。但是容易丢数据。
1：表示每进行一次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入道磁盘里。防止丢数据。(建议)
n：表示每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

什么叫双一模式？

innodb_flush_log_at_trx_commit=1，并且 syn_binlog=1，也就是俗称的双一模式，及时刷盘，防止丢失，以保证数据可靠性，但是如果已经启用主从 GTID复制，尤其是，半同步复制的话，可以不用设置为双 1 因为DTID已经保证了至少有一个slave 跟主库保持事务的一致，并不用担心丢数据

下图是Percona Live的PPT截图

Percona Live

如果机器掉电了的话，1 是不会丢数据，2 会丢差不多一秒的数据，如果机器不掉电 mysql 实例挂掉的话，事务并不会丢失。0 尽管机器没有掉电， mysql的实例宕机的话也会丢一秒的数据

redo 个别参数

innodb_log_buffer

通常 8-32 MB就足够了，大概就是能缓存一秒内的所有的事务就够了，也可以查看设置的够不够，查看选项 innodb_log_waits，如果不够了查看这个参数如果大于 0 ，表示显示等待，意思是 redo log buffer 不够用了，可以适当调大。

[[email protected] ~]# mysqladmin -uroot -p ext| grep wait
| Innodb_buffer_pool_wait_free                  | 0                                                |
| Innodb_log_waits                              | 0                                                |
| Innodb_row_lock_current_waits                 | 0                                                |
| Innodb_row_lock_waits                         | 0                                                |
| Table_locks_waited                            | 0                                                |
| Tc_log_page_waits                             | 0                                                |

innodb_log_file_size

5.5 以前 redo log 最大的大小只能设置为小于 4G，建议设置成 512M - 1G。
5.5 以上版本，redo log 可以设置为好几个G，一般设置为 512 MB-4GB。
MySQL 5.6以上如果是正常的重启，redo log 会自己判断当前的 redo log file size 跟 redo log 文件的大小做对比，比如说实际文件大小为 1G，但是重启的时候不小心把配置文件改成了2G，这个时候会自动检查配置文件变成了 2G，把旧的 redo log 删掉，重新生成一个2G的新的 redo log。

innodb_log_files_in_group

至少2个文件，一般来说两个三个文件都可以，5.5 以前的版本，这参数设置为2就可以了，因为太多的话每次都需要扫描所有的 redo log ，恢复起来很慢

`redo VS binlog`

redo 是物理+逻辑日志，binlog 是逻辑日志
redo是按照事务发起的时间顺序存储，而binlog 是按照事务提交时间顺序存储
redo log file 循环使用，binlog 每次新增一个文件
binlog 更像是 oracle 里面的redo 归档，因为 oracle 里面的 redo 是物理的。

事务在 redo ，binlog 中的逻辑过程

事务提交以后要先写入 redo log buffer
写 trx prepare 标记，表示准备提交。
再将 redo log buffer 里的数据刷新到 redo log ，如果同时有多个事务会都打上了 prepare 标记写到 redo log 里面的话，那么这些事务在从库里面是可以被并行运行。（如果开了并行复制的话）
然后再把事务写binlog
在redo log 写入 trx commit 标记
将写binlog 成功的标记写入 redo log

其中如果写 binlog 的步骤没有完成的话，那么这个事务会被回滚，因为没有写binlog，相当于这个事务在从库没有办法被应用，就会导致主从不一致，所以发生 crush 故障的时候，这个事务会被回滚，则主从库都不会完成事务，不会出现主从不一致的问题。
如果 binlog 写入完成，即便后面的 trx commit 标记没有，那么在 crush recovery 的时候这个事务也会再次被提交，这样才能确保主从库复制数据的一致性，因为有了binlog，所以从库一定会应用这个事务，主库上面也要再确认一下这个事务已经刷盘，已经提交，关键就是看 binlog 有没有写完，只要 binlog 写了，那么这个事务一定会在主从都出现的。

保证主从一致：

slave 上 master & relay info repository 必须是 table ，且设置 relay_log_recovery =1 另外 master 那边设置双1 ，才能保证主从数据一致性。（也就是用innodb 的事务来保证主从的状态的master 和redo log 的状态一致性，才能确保即便实例失败了，但在数据库重新启动的时候也能保证主从事务的一致性），但是只要开了 GTID基本上就可以保证主从数据的一致性。

undo

当我们对记录做了变更操作时，就会产生 undo 记录，记录变更前的旧数据，undo记录中存储的是老版本数据，当一个旧的事务需要读取老版本数据时，为了能够读取到老版本的数据，需要顺着undo 队列找到满足其可见性的记录。当队列很长时，通常是个比较耗时的操作。
记录对数据的变更操作包括 insert / delete / update
- 其中 insert 操作在事务提交前只对当前的事务可见，因此产生的undo 日志可以在事务提交后直接删除，归类为 insert_undo
- 而对于 update /delete 则需要维护多版本信息，在innodb 里， update 和 delete 操作产生的 undo 日志被归为一类，即 update_undo
在 5.6 以前 undo 默认是被记录到系统表空间（ibdata）中，但从 5.6 开始可以使用独立的 undo 表空间。
用于实现MVCC以及回滚
支持在线 truncate 无用的undo logs
- set global innodb_undo_log_truncate=1
- 或者是，当所有的undo 超过innodb_max_undo_log_size时，也会发起一个 truncate 操作
innodb status 里的 history list length （表示已提交事务，但未 purge 的 update undo log ，也就是等待 purge的一个undo 队列，整个值如果太大的话，表示等待被清除的undo 的队列很大，也就是说当前的数据库实例性能可能会有些差，需要去检查到底是什么原因（最大的可能性是：有个很老的事务一直没有提交，导致很多旧版本的undo 不能被清除）

数据库实例性能差，可能为什么？怎么去排查？

可能是有个很老的事务一直没有提交，导致很多旧版本的undo 不能被清除，
排查：select * from information_schema.innodb_trx，desc information_schema.innodb_trx ，看事务启动时间是不是太久远了

"[email protected]:mysql3306.sock  [master_test]>desc information_schema.innodb_trx 
    -> ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |
| trx_state                  | varchar(13)         | NO   |     |                     |       |
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |
| trx_wait_started           | datetime            | YES  |     | NULL                |       |
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_is_read_only           | int(1)              | NO   |     | 0                   |       |
| trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [master_test]>select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 3332
                 trx_state: RUNNING
               trx_started: 2018-03-29 20:19:18
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 409
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

undo 相关参数

5.7 开始

innodb_undo_logs

可以设置undo 表空间的个数，rollback seg 数量，比如将 2G的undo tablespace ，切分成多少个回滚段，默认是128 个，并且实例初始化后不可再修改，8.0 可以修改，每个undo log seg 可以最多存放 1024 个事务（比如说128个回滚段，最高可以支持并发的事务是1024* 128 个并发事务）

innodb_purge_rseg_truncate_frequency

用于控制 purge 回滚段的频率，默认是128 ，表示 purge undo 轮询 128次后，自动进行一次undo的 truncate 操作。后面会讲到128.

innodb_undo_tablespaces

undo log 文件数， innodb 设置独立的undo表空间以后，可以有多个undo log 文件，每个文件默认10M，数量默认0个（也就是说使用共享的，放在ibdata 1 文件里面，如果设置大于 0的话，相当于使用独立的undo 文件），最大95个（因为系统表空间和临时表空间已经占了 33 个，33+95=128个，也就是说最大可以使128个，但是前33 个已经被系统表空间和临时表空间占用了），最小2个（因为我们在对undo log 进行 trancate 清空的时候，其中一个 trancate，另一个也要能用，这样就无需停止业务了，保证事物的并发。类似于 redo log 设置大于两个的概念。