- 🌙 实例启动
🌙 buffer pool 管理
🌙 redo log
🌙 事务在 redo ,binlog 中的逻辑过程
🌙 undo
如下图:左面最大的就是 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
内存:
- 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截图
如果机器 掉电了的话,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 设置大于两个 的概念。
innodb_max_undo_log_size
控制最大undo 表空间文件的大小,超过这个值才尝试 truncate undo logs,truncate 后的undo logs 大小默认恢复为10M。
innodb_max_purge_lag=0
表示最多允许purge的延迟
- 设置为0 表示,允许无限制的延迟(建议设置为0)
- 其他值,如果设置为2000,也就是说我们现在等待 purge的队列最大可以2000,如果超过2000,所有新产生的 DML操作都会被阻塞一小段时间
阻塞的时间算法为:(当前purge lag的数量/最大的purge lag数量)*10-5,单位 毫秒
为什么innodb会变慢
有可能是innodb_max_purge_lag 参数设置了非0,导致 阻塞,导致整个实例变慢。
流下了看不懂的泪水