005、内存刷新机制

MySQL5.6 INNODB存储引擎图

分解成小部分学习。

1、MySQL的redo log buffer写机制

数据库的操作讲究日志先行，即一条DML语句，进入数据库对数据库所作的修改操作，会先写进redo log buffer。同时会写进binlog buffer和修改buffer pool。

在redo log buffer中的数据，写入redo log file时，是顺序写，循环写的模式：即先写redo log file 1，当1号日志文件写满后，再写2 号日志文件，写满之后再写3号文件，当3号文件写满之后，再写1号日志文件，该模式和Oracle日志写的模式类似。

Mysql中的redo log文件：

[root@ning mysql]# pwd
/u01/data/mysql
[root@ning mysql]# ls -lh *logfile*
-rw-rw----. 1 mysql mysql 256M Mar 17 19:51 ib_logfile0
-rw-rw----. 1 mysql mysql 256M Mar 16 20:59 ib_logfile1

redo日志文件的大小，在配置文件中有定义：

[root@ning mysql]# cat /etc/my.cnf|grep file_size
innodb_log_file_size = 256M

MySQL中redo log file的作用：

数据库的崩溃恢复
记录已提交和未提交的事务

数据库崩溃恢复中日志的动作：redo log把已经提交(commit)的内容进行前滚，未提交的内容，激活undo进行回滚，以此保证数据一致性。

2、MySQL的binlog日志

mysql的binlog file是二进制日志文件，相当于oracle 的归档日志文件。

[root@localhost mysql]# ls -lh|grep bin
-rw-rw----. 1 mysql mysql  120 Mar 22 10:13 mysql-bin.000023
-rw-rw----. 1 mysql mysql  120 Mar 22 10:13 mysql-bin.000024
-rw-rw----. 1 mysql mysql   58 Mar 22 10:13 mysql-bin.index

binlog日志文件的命名：mysql-bin.000023，编号之前的'mysql-bin'部分，可自定义名称，编号部分系统定义。

数据库每重启一次就会产生新的binlog，binlog写满之后，也会切换到下一个。

binlog的默认大小是1G，在参数文件中可以查看：

[root@localhost ~]# cat /etc/my.cnf|grep binlog_size
max_binlog_size = 1024M
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /u01/data/mysql/mysql-bin       |
| log_bin_index                   | /u01/data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.01 sec)

binlog的索引文件：mysql-bin.index，记录着binlog日志的索引信息，很重要，不能删除。

[root@localhost mysql]# cat mysql-bin.index 
/data/mysql/mysql-bin.000023
/data/mysql/mysql-bin.000024

binlog日志文件会一致写，直到存储空间使用完毕，可以通过参数设置binlog的保留时间，后续会有说明。

3、删除日志的实验

当前binlog日志文件不能删除，会导致数据库启动不起来。redo log日志可以删除，不影响数据库真正的运行。

删除之前，先进行数据目录备份：

[root@localhost data]# cp -r mysql/ mysql.bak

删除redo log的实验

[root@localhost data]# cd mysql
[root@localhost mysql]# rm ib_logfile*
rm: remove regular file `ib_logfile0'? y
rm: remove regular file `ib_logfile1'? y

重启mysql服务：

[root@localhost mysql]# mysqld_safe --defaults-file=/etc/my.cnf &
[1] 6291
[root@localhost mysql]# 210322 11:40:55 mysqld_safe Logging to '/data/mysql/error.log'.
210322 11:40:55 mysqld_safe Starting mysqld daemon with databases from /data/mysql

查看日志文件：

[root@localhost mysql]# vi /data/mysql/error.log
……
2021-03-22 11:40:56 6924 [Note] Plugin 'FEDERATED' is disabled.
2021-03-22 11:40:56 7f28e23b6720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

查看进程：

[root@localhost mysql]# ps -ef|grep mysql
root      6291  2781  0 11:40 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql     6924  6291  0 11:40 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --open-files-limit=3072 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

可见redo log日志文件删除并不影响数据库的启动。

删除当前binlog的实验

[root@localhost data]# cd mysql
[root@localhost mysql]# rm mysql-bin.000025
rm: remove regular file `mysql-bin.000025'? y

重启mysql服务：

[root@localhost mysql]# mysqladmin -uroot -p shutdown
Enter password: 
210322 14:37:17 mysqld_safe mysqld from pid file /data/mysql/localhost.localdomain.pid ended
[root@localhost mysql]# mysqld_safe -defaults-file=/etc/my.cnf
sed: -e expression #1, char 25: unknown option to `s'
210322 14:37:54 mysqld_safe Logging to '/data/mysql/error.log'.
210322 14:37:54 mysqld_safe Starting mysqld daemon with databases from /data/mysql
210322 14:37:57 mysqld_safe mysqld from pid file /data/mysql/localhost.localdomain.pid ended

查看日志文件，日志文件中记录了错误信息：

2021-03-22 14:37:55 8034 [Note] InnoDB: 5.6.35 started; log sequence number 1638944
/usr/local/mysql/bin/mysqld: File '/data/mysql/mysql-bin.000025' not found (Errcode: 2 - No such file or directory)
2021-03-22 14:37:55 8034 [ERROR] Failed to open log (file '/data/mysql/mysql-bin.000025', errno 2)
2021-03-22 14:37:55 8034 [ERROR] Could not open log file
2021-03-22 14:37:55 8034 [ERROR] Can't init tc log
2021-03-22 14:37:55 8034 [ERROR] Aborting

2021-03-22 14:37:55 8034 [Note] Binlog end

查看进程，发现不存在：

[root@localhost mysql]# ps -ef|grep mysql
root      8061  2781  0 14:39 pts/1    00:00:00 grep mysql

说明当前binlog文件删除之后，数据库无法启动。

解决方法，将mysql-bin.index中，被误删除的binlog注释，再启动即可。

4、内存区的刷新机制

查看正在执行的sql：

[root@localhost ~]# mysqladmin -uroot -proot pr
Warning: Using a password on the command line interface can be insecure.
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 11 | root | localhost |    | Query   | 0    | init  | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

或

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 12 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

影响redo log buffer刷新的第一个因素

查看commit参数，innodb_flush_log_at_trx_commit：

mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| binlog_order_commits           | ON    |
| innodb_api_bk_commit_interval  | 5     |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

innodb_flush_log_at_trx_commit参数的值影响redo log buffer的刷新，该参数有3个值：

0：每隔1秒刷新一次
1：默认值，实时刷新，只要有写入，就刷新到redo log file，该值也是最安全的设置
2：交由操作系统管理

第二个影响redo log buffer刷新的条件是master thread，见下文。

数据脏页刷新到磁盘

1、日志组切换：当redo日志写满，并且切换到下一个日志时，会产生检查点checkpoint，将buffer pool中的数据脏页写入磁盘。

2、innodb_max_dirty_pages_pct参数值，该参数是脏页占有buffer pool的比例：

mysql> show variables like '%dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_max_dirty_pages_pct     | 50    |
| innodb_max_dirty_pages_pct_lwm | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

该值表示，当脏页占比达到50%的时候，触发写机制，将脏页写入磁盘。需要经过压力测试报告后确定，一般在25-50之间。

binlog的刷新机制

1、sync_binlog，该参数值表示binlog的刷新条件。

mysql> show variables like '%sync_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

该参数有0,1,2……N个值。

0：交由操作系统管理
1：实时刷新
2：每2个事务刷新一次，n代表每N个事务刷新一次。

5、MySQL线程

MySQL的四大IO线程：insert buffer thread，read thread，write therad，redo log thread

purge thread

该线程作用是删除无用的undo page，通过当前系统自行判断该删除哪些undo page。

innodb_purge_threads参数默认值是1，参数值代表purge thread线程的数量。

mysql> show variables like '%purge%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| gtid_purged                |       |
| innodb_max_purge_lag       | 0     |
| innodb_max_purge_lag_delay | 0     |
| innodb_purge_batch_size    | 300   |
| innodb_purge_threads       | 1     |
| relay_log_purge            | ON    |
+----------------------------+-------+
6 rows in set (0.00 sec)

MySQL数据库中，undo数据文件在共享表空间文件中：

[root@ning mysql]# ls -lh ibdata1
-rw-rw----. 1 mysql mysql 1.0G Mar 23 20:21 ibdata1

有时该文件占用空间大小很大，有可能就是Undo数据占用，如果innodb_purge_threads参数设置为1，造成无用的undo数据无法及时清除，就会导致该文件变大，这时就可以调整该参数的大小，建议直接调整到最大值：32，线程数在1-32之间根据系统需求自动调起，并不是直接启用32个线程。

page cleaner thread

该线程的作用是刷新脏页的线程，将脏页的数据写进数据文件。

master thread

该线程是mysql数据库的主线程，负责管理其它所有线程，mysql 5.1及以前，只有master线程负责数据库所有刷新工作，mysql 5.5之后，分出了其他线程缓解数据库的压力，这时master线程负责管理其他线程。

Master thread 内部有几个循环：

1.Loop主循环

2.Background loop后台循环

3.Flush loop刷新循环

4.Suspend loop暂停循环

Master thread 会根据数据库运行的状态来在这些循环中进行切换。

master线程包含四个循环，其中重要的两个循环：loop主循环和background loop后台循环。

loop主循环，大多数操作都在这个循环中，包含两类操作：1秒的操作和10秒的操作。

每秒的操作：

1.日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）（*）

2.合并插入缓冲（可能）（insert buffer 根据当前系统IO压力的情况）

3.至多刷新100个innodb缓冲池中的脏页到磁盘（可能）（根据缓冲池中脏页比例 innodb_max_dirty_pages_pct ）

4.如当前没用户活动，切换到background loop(可能)

10秒的操作：

1.刷新100个脏页到磁盘（可能）

2.合并至多5个插入缓冲(insert buffer)（总是）

3.将日志缓冲刷新到磁盘(redo log buffer)（总是）

4.删除无用的undo页（总是）

5.刷新100个或者10个脏页到磁盘（总是）

6.产生1个检查点（总是）

如何查看数据库的繁忙程度：

mysql> show engine innodb status \G;  --该命令是为了查看当前数据库的运行状态
……
Per second averages calculated from the last 6 seconds --代表该命令是过去6秒内产生的，即该命令的信息并不是实时的
……
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 3139 srv_idle  --其中active的占比越高，数据库越繁忙
……
LOG
---
Log sequence number 1638432
Log flushed up to   1638432
Pages flushed up to 1638432
Last checkpoint at  1638432  --日志先行，Log sequence number的号应该比其他的号大，若接近或者相同，则数据库不繁忙

Background loop:若当前没有用户活动或者数据库关闭的时候，就会切换到这个循环。

1.删除无用的undo页（总是）

2.合并20个插入缓冲（总是）

3.跳回到主循环（总是）

4.不断刷新100个页，直到符合条件

注意：若启用了innodb存储引擎，却没有使用innodb存储引擎的表，那么master thread 总是处于挂起状态.

6、buffer pool 中其他内存区

buffer pool中还有数据字典信息，data dictionary，基本上全部保存在ibdata1文件中，在information_schema库中。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.09 sec)

lock info，锁的信息；

additional memory pool：额外内存池，5.6.7版本以后，基本上已经废弃。

query cache:缓存的是静态sql

innodb存储引擎总结

1、主体系结构：默认7个后台线程，4个io thread(insert buffer、log、read、write)，一个master thread(优先级最高)，1个锁(lock)监控线程，一个错误监控线程，可通过show engine innodb status查看。新版本已对默认的read thread和write thread分别增大到4个，可通过show variables like 'innodb%_io_thread%'查看；

2、几个buffer结构：缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool)，具体配置可由show variables like 'innodb_buffer_pool_size'、show variables like 'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_size'来查看；

3、buffer pool：用来存放各种数据的缓存包，包括索引页(index page)、数据页(data page)、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)等。工作方式总是将数据文件按页(每页16k)读取到缓冲池，然后按最近最少被使用(LRU)的算法来保留在缓冲池中的缓存数据。如果数据文件需要修改，总是首先修改在缓冲池中的页(发生修改后即为脏页)，然后再按照一定的频率将缓冲池的脏页刷新到文件。通过show engine innodb status;来查看。

4、表空间文件：innodb默认的表空间文件为ibdata1，可通过show variables like 'innodb_file_per_table'查看每个表是否产生单独的ibd表空间文件，但是单独的表空间文件仅存储该表的数据、索引和插入缓存等信息，其余信息还是存放在默认的表空间中。

5、redo log:重做日志文件，在实例和介质失败时，重做日志文件就能排上用场，如数据库掉电。innodb存储引擎会使用重做日志恢复到掉电前的时刻，此时来保证数据的完整性。参数innodb_log_file_size制定了重做日志文件的大小、innodb_log_file_group制定了日志文件组中重做日志文件的数量、innodb_log_group_home_dir制定了日志文件所在的路径，默认在数据库数据目录下。

来自为知笔记(Wiz)

posted @ 2021-06-04 15:37 有点菜大人阅读(253) 评论(0) 收藏举报

刷新页面返回顶部

有点菜大人