MySQL中innodb存储引擎相关系统参数及其持久化

╰+攻爆jí腚メ 2022-05-23 13:16 40阅读 0赞

**MySQL中innodb存储引擎相关系统参数及其持久化**

**MySQL中innodb存储引擎相关系统**

**1、系统参数查看**

使用show variables可以查看MySQL系统参数及其配置值

MySQL数据存放目录：datadir

mysql> show variables like 'datadir';

\+---------------+-----------------+

| Variable\_name | Value          |

\+---------------+-----------------+

| datadir      | /var/lib/mysql/ |

\+---------------+-----------------+

1 row in set (0.00 sec)

**2、innodb存储引擎相关的参数**

mysql> show variables like 'innodb%';

\+-----------------------------------------+------------------------+

| Variable\_name                          |Value                 |

\+-----------------------------------------+------------------------+

| innodb\_adaptive\_hash\_index             | ON                    |

| innodb\_additional\_mem\_pool\_size        | 1048576               |

| innodb\_autoextend\_increment            | 8                     |

| innodb\_autoinc\_lock\_mode               | 1                     |

| innodb\_buffer\_pool\_size                | 8388608               |

| innodb\_checksums                       | ON                    |

| innodb\_commit\_concurrency              | 0                     |

| innodb\_concurrency\_tickets             | 500                   |

| innodb\_data\_file\_path                  | ibdata1:10M:autoextend |

| innodb\_data\_home\_dir                   |                       |

| innodb\_doublewrite                     | ON                    |

| innodb\_fast\_shutdown                   | 1                     |

| innodb\_file\_io\_threads                 | 4                     |

| innodb\_file\_per\_table                  |ON                    |

| innodb\_flush\_log\_at\_trx\_commit         | 1                     |

| innodb\_flush\_method                    |                       |

| innodb\_force\_recovery                  | 0                     |

| innodb\_lock\_wait\_timeout               | 50                    |

| innodb\_locks\_unsafe\_for\_binlog         | OFF                   |

| innodb\_log\_buffer\_size                 | 1048576               |

| innodb\_log\_file\_size                   | 5242880               |

| innodb\_log\_files\_in\_group              | 2                     |

| innodb\_log\_group\_home\_dir              | ./                    |

| innodb\_max\_dirty\_pages\_pct             | 90                    |

| innodb\_max\_purge\_lag                   |0                     |

| innodb\_mirrored\_log\_groups             | 1                     |

| innodb\_open\_files                      | 300                   |

| innodb\_rollback\_on\_timeout             | OFF                   |

| innodb\_stats\_method                    | nulls\_equal           |

| innodb\_stats\_on\_metadata               | ON                    |

| innodb\_support\_xa                      | ON                    |

| innodb\_sync\_spin\_loops                 | 20                    |

| innodb\_table\_locks                     | ON                    |

| innodb\_thread\_concurrency              | 8                     |

| innodb\_thread\_sleep\_delay              | 10000                 |

| innodb\_use\_legacy\_cardinality\_algorithm | ON                    |

\+-----------------------------------------+------------------------+

36 rows in set (0.00 sec)

**3、innodb常见参数解析**

**innodb\_additional\_mem\_pool\_size|1048576**   设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小

**innodb\_autoextend\_increment   | 8 **      系统表空间文件每次扩展的大小（M）

**innodb\_log\_buffer\_size  | 1048576  **      InnoDB用来往磁盘上的日志文件写操作的缓冲区大小。相对合理值是从1MB到8MB。默认是1MB

**innodb\_lock\_wait\_timeout |50   **         MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)，即InnoDB事务在被回滚之前可以等待一个锁定的超时秒数

**innodb\_doublewrite      | ON   **        默认情况下，InnoDB存储所有数据两次，第一次存储到doublewrite缓冲，然后存储到数据文件

**innodb\_data\_home\_dir    |**              共享表空间的路径。不设置这个值，默认是MySQL数据目录，即datadir

**innodb\_file\_io\_threads  | 4  **             InnoDB中文件I/O线程的数量，正常情况下保持默认值4即可

**innodb\_flush\_log\_at\_trx\_commit| 1  **        日志持久化相关参数，设置了事务提交方式的分配原则，也就是innodb的redo的刷盘策略：redolog有一个自己的logbuffer，所以事务会先写到logbuffer，然后写到pagecache，最后再写到磁盘中去。

默认为值１，这个参数的设置对MySQL单机情况下数据的持久化具有重要意义，对innodb的性能有很大的影响，它决定了事务提交方式、持久化方式以及服务崩溃后的修复能力。

值为1时：innodb每次事务提交时都会把logbuffer的数据写入日志文件中，并将数据刷到磁盘中去。即每次commit都写入并持久化日志，可以做到不丢任何一个事务。此时数据最安全。

值为2时：每次事务提交时MySQL都会把logbuffer的数据写入日志文件中，但是fsync(刷到磁盘)操作并不会同时进行。该模式下,MySQL在对日志文件的fsync(刷到磁盘)操作每秒发生一次。即每次提交日志都写到内存，每1s持久化一次。但是，由于进程调用方面的问题，并不能保证每秒１００％的发生。因此其在性能上是最快的，但操作系统崩溃或掉电才会删除最后一秒的事务。

值为0时：logbuffer将每秒执行一次写入日志文件操作，并且fsync刷盘。但是在每次事务提交时，并不会主动触发写入磁盘操作。即每隔1秒写入并持久化一次日志，mysqld进程的崩溃会删除崩溃前最后一秒的事务。

注意：该参数和sync\_binlog、innodb\_support\_xa参数共同保障了MySQL数据持久化和可靠性，双一模式下，MySQL数据最安全可靠，即sync\_binlog=1（写一个binlog，同步到磁盘一次）、innodb\_flush\_log\_at\_trx\_commit=1

**innodb\_support\_xa       | ON  **        默认为ON，此时会使用二阶段提交协议来保证binlog和innodb的一致，以加固MySQL数据的可靠性。

**innodb\_buffer\_pool\_size | 8388608   **    InnoDB用来缓存它的数据和索引的内存缓冲区的大小,这个值设得越高，访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的80%

**innodb\_log\_file\_size    | 5242880    **  日志组里每个日志文件的大小。默认是5MB。

**innodb\_log\_files\_in\_group|2**             日志组里日志文件的数目。正常情况下保持默认值2即可

**innodb\_log\_group\_home\_dir|./**           InnoDB日志文件的目录路径。如果你不指定任何InnoDB日志参数，默认的是在MySQL数据目录里创建两个5MB大小名为ib\_logfile0和ib\_logfile1的文件。

**innodb\_table\_locks      | ON  **      开启LOCKTABLES时会在表上持有两个锁，一个是MySQL层持有的锁，另一个是InnoDB层持有的锁,InnoDB引擎在事务中持有的锁会在事务提交或回滚时释放，因此当autocommit=1时执行LOCKTABLE没有意义，因为持有的锁会马上释放,可以在my.cnf文件里设置innodb\_table\_locks=0来屏蔽该参数。

**innodb\_thread\_concurrency|8**          并发数限制，InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制

**innodb\_open\_files       | 300**        指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。

**innodb\_data\_file\_path   | ibdata1:10M:autoextend**   共享表空间的定义和它的尺寸.存储为共享表空间时，所有表存储在同一个文件，存储为一个ibdata1文件，默认10M大小，可扩展。

**innodb\_file\_per\_table |ON**            为ON时设置为独立表空间，单个表存放为一个tablename.ibd的文件，反之设置为共享表空间。

**MySQL数据持久化**

**1、 参考innodb\_flush\_log\_at\_trx\_commit参数解析**

**2、 MySQL持久化说明**

数据库事务正确执行遵循四个基本要素：ACID，即：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

其中，MySQL对持久化的实现方法是Writeahead logging，即修改数据库时，不直接修改数据库内容，而是将修改完的数据写入日志中，并同步到磁盘上，并不影响其他读进程。

如果数据库崩溃，只需重启后扫描日志文件，然后更新到数据库中，就可以实现数据恢复。在MySQL中，innodb存储引擎支持事务，而myisam不支持事务。

**3、undo日志与redo日志**

在数据库系统Log buffer中，存放undo日志与redo日志。

**undo日志：**

undo log用于存放数据修改被修改前的值，当事务提交一半时撤销日志，未走完日志撤销，根据撤销日志就可以把修改日志还原回原来日志。

当数据库修改出现异常时，可以使用undo日志来实现回滚操作，保证事务的一致性。

**redo日志：**

redo log用于存放数据修改被修改后的值，它有一个自己的日志缓冲区（logbuffer），所以redo会先写到logbuffer，然后写到文件缓存pagecache（高速缓冲存储器，在linux读写文件时，它用于缓存文件的逻辑内容，从而加快对磁盘上映像和数据的访问），最后再写到磁盘中去。

使用innodb存储引擎时，当数据库对数据做出修改，会先把数据页从磁盘读到bufferpool中，然后在bufferpool中进行修改，被修改后的bufferpool中的数据页与磁盘上的数据页内容不一致，该数据称为dirtypage （脏数据），redo log用于记录数据修改后的记录，采用顺序记录的方式。

如果这个时候发生非正常的DB服务重启，那么这些数据并未写入内存中，也没有同步到磁盘文件中（同步到磁盘文件是个随机IO），从而造成数据丢失，这个时候就可以根据redo文件的记录内容，重新刷新到磁盘文件，实现数据恢复。此外，因为bufferpool中的数据记录到redolog，是一个顺序IO，所以有效提高了事务提交的速度。