mysql innodb_log_file_size 和innodb_log_buffer_size参数

千家信息网最后更新 2024年11月27日mysql innodb_log_file_size 和innodb_log_buffer_size参数今天主要研究下关于mysql的redo log（事务日志）的相关参数的设置问题，其中主要涉及到了三个参数的问题， 一：innodb_log_file_size ：该参数决定着mysql事务日志文件（ib_logfile0）的大小； 设置的太小：当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（Checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。由于日志切换更频繁，也就直接导致更多的BUFFER FLUSH，由于日志切换的时候是不能BUFFER FLUSH的， BUFFER写不下去，导致没有多余的buffer 写redo， 那么整个MYSQL就HANG住，还有一种情况是如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换（因为实例恢复还需要，不能被循环复写）这样mysql就hang住了。可以根据文件修改时间来判断日志文件的旋转频率，旋转频率太频繁，说明日志文件太小了。 设置的太大：设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务（也就是实例恢复中的前滚, 利用redo从演变化来恢复buffer cache中的数据），如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。 如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要， 如何去设置合适的innodb_log_file_size 大小呢？ 一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。 具体依据如下：我经常设置为 64-512MB 首先在业务高峰期，计算出1分钟写入的redo量，然后评估出一个小时的redo量； MariaDB [(none)]> pager grep Log ##使用page之后，执行的命令只显示 Log开头的 PAGER set to 'grep Log' MariaDB [(none)]> show engine innodb status\G select sleep(60); show engine innodb status\G; Log sequence number 4578059050533 Log flushed up to 1149269980149 1 row in set (0.00 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 4578062739081 Log flushed up to 1149270019005 1 row in set (0.00 sec) MariaDB [(none)]> nopager PAGER set to stdout MariaDB [(none)]> select (4578062739081-4578059050533)/1024/1024 as MB; +------------+ | MB | +------------+ | 3.51767349 | +------------+ 1 row in set (0.00 sec) 注意Log sequence number，这是写入事务日志的总字节数。所以，现在你可以看到每分钟有多少MB日志写入（这里的技术适用于所有版本的MySQL，在5.0及更高版本，你可以从SHOW GLOBAL STATUS的输出看Innodb_os_log_written的值） 。 通过计算后得到每分钟有3.5M的日志写入。 根据经验法则。通常我们设置redo log size足够大，能够容纳1个小时的日志写入量。 1小时日志写入量=3.5M * 60=210M，由于默认有两个日志重做日志文件ib_logfile0和ib_logfile1。在日志组中的每个重做日志文件的大小一致，并以循环的方式写入。innodb存储引擎先写重做日志文件0，当达到文件的最后时，会切换到重做日志1，并checkpoint。以此循环。 所以我们可以大约设置innodb_log_file_size=110M。注意：在innodb1.2.x版本之前，重做日志文件总的大小不得大于等于4G，而1.2.x版本将该限制扩大到了521G。 二：innodb_log_files_in_group 该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。 需要注意的是：innodb_log_files_in_group是静态的变量，需要以"干净"的方式更改并重新启动，否则mysql启动不起来。也就是说如果想把原来是2的修改成3，这样的话你需要先关闭mysql服务，把原来的ib_logfile0和ib_logfile1文件删掉，然后启动mysql，否则报错如下所示： 直接修改my.cnf将该参数改为3的时候 重启mysql，报错，innodb引擎无法挂载 110124 14:06:23 InnoDB: Log file ./ib_logfile2 did not exist: new to be created 110124 14:06:23 [ERROR] Plugin 'InnoDB' init function returned error. 110124 14:06:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 三：innodb_log_buffer_size 该参数确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。 对于比较小的innodb_buffer_pool_size，建议是设置一样大。 但是，对于比较大的innodb_buffer_pool_size，不建议这么设置，这会存在一个潜在的问题，那就是当mysql挂掉时，恢复数据需要很久，造成大量的停机时间，当我们调整innodb_buffer_pool_size大小时，innodb_log_buffer_size和innodb_log_file_size也应该做出相应的调整。 四：innodb_log_group_home_dir 在事务被提交并写入到表空间磁盘文件上之前，事务日志存储在InnoDB的redo日志文件里。这些日志位于innodb_log_group_home_dir参数设置的目录中，通常我们把这个目录设置与innodb_data_home_dir变量相同。为了获得最佳性能，建议分离innodb_data_home_dir和innodb_log_group_home_dir到单独的物理存储阵列上，这样可以保证IO资源不起冲突，利于服务器处理大量高并发连接。 小结：mysql的事务日志相关的参数，基本介绍完毕了，影响日志刷新的性能的参数innodb_flush_log_at_trx_commit 具体分析innodb_flush_log_at_trx_commit=N的意义： innodb_flush_log_at_trx_commit=0，每次commit时,事务日志写进了innodb log buffer ,然后每秒Log Thread 会将事务日志从innodb log buffer刷新到ib_ogfile(也就刷新到了磁盘）。当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失，这是因为每次commit，事务日志只是写进了innodb log buffer 中，然后是每秒才将innodb log buffer 中的事务日志刷新到磁盘永久保存，所以mysqld进程的崩溃时，innodb log buffer可能会有一秒的日志没有刷新出来，但是在这种情况下，MySQL性能最好； innodb_flush_log_at_trx_commit=2，每次commit时，事务日志写进了innodb log buffer，并同时接着写进os cache, 也就是说每次commit，事务日志写进了os cache中， 然后每秒从os cache刷新到ib_logfile(也就是刷新到了磁盘）。当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失，因为每次commit，事务日志已经进入了os cache,所以mysqld崩溃，事务日志是不会丢失的； 当innodb_flush_log_at_trx_commit设置为1，这是最安全的设置，同时由于频繁的io操作，导致效率是最差的，这时候不管是mysqld，还是操作系统崩溃，都不会丢数据，这是因为每次commit,事务日志都刷新到了磁盘永久保存了；