InnoDB Log 优化

最新推荐文章于 2024-06-22 22:41:26 发布

sunset_ggz

最新推荐文章于 2024-06-22 22:41:26 发布

阅读量250

点赞数

分类专栏： # MySQL SERVER优化文章标签： mysql

本文链接：https://blog.csdn.net/qq_33033819/article/details/106581752

版权

MySQL SERVER优化专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

- - InnoDB Log 优化

InnoDB Log 优化

#查看当前日志的写入情况
mysql> show engine innodb status \G;
...
LOG
---
Log sequence number 11881468478		#上次数据页的修改还没有刷新到日志文件的 lsn 号 
Log flushed up to   11881468478 	#上次成功操作，已经刷新到日志文件的 lsn 号
Pages flushed up to 11881468478 
Last checkpoint at  11881468469 	#上次检查点成功完成时的 lsn 号，也就是恢复的起点
...

1、innodb_flush_log_at_trx_commit

1) 0 , 在事务提交时, InnoDB 不会立即触发将缓存日志写入到磁盘文件的操作,而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。

2) 1 , 在每个事务提交时, InnoDB 立即将缓存中的 redo 日志回写到日志文件,并调用操作系统 fsync 刷新 IO 缓存。

3) 2 , 在每个事务提交时, InnoDB 立即将缓存中的 redo 日志回写到日志文件，但并不马上调用 fsync 来刷新 IO 缓存,而是每秒只做一次磁盘 IO 缓存刷新操作。

注意：

1) 如果设置为 0 , 如果数据库崩溃，最后 1 秒钟的事务重做日志可能会由于未及写入磁盘文件而丢失,这种方式是效率最高的，但也是最不安全的。

2) 如果设置为 2 , 如果数据库崩溃,由于已经执行重做日志写入磁盘操作,只是没有做磁盘 IO 刷新操作,因此只要不发生操作系统崩溃,数据就不会丢失,这种方式是对性能和数据安全的折中,其性能和数据安全介于其他两种方式之间。

2、innodb_log_file_size

一般来说,平均每半个小时写满 1 个日志文件比较合适,通过如下计算 InnoDB 每分钟产生的日志量并估算合适的值。

1) 方法一

mysql> pager grep -i "Log sequence number";
PAGER set to 'grep -i "Log sequence number"'
mysql> show engine innodb status \G select sleep(60);show engine innodb status \G;
Log sequence number 11881468478
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 11884100215
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> nopager
PAGER set to stdout
mysql> select round((11884100215-11881468478)/1024/1024) as MB;
+------+
| MB   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

每半小时的日志量 = 30*3 = 90 MB

2) 方法二

mysql> select @a1:=variable_value as a1 from information_schema.global_status where variable_name='innodb_os_log_written' union all select sleep(60) union all select @a2:=variable_value as a2 from information_schema.global_status where variable_name='innodb_os_log_written';
+----------+
| a1       |
+----------+
| 8256512  |
| 0        |
| 11101184 |
+----------+
3 rows in set, 2 warnings (1 min 0.00 sec)

mysql> select round((@a2-@a1)/1024/1024/@@innodb_log_files_in_group) as MB;                                                                                             +------+
| MB   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

每半小时的日志量 = 30*1 = 30 MB