今天主要研究下关于mysql的重做日志(事务日志)的相关参数的设置问题,其中主要涉及到了三个参数的问题,
一:该参数决定着mysql事务日志文件(ib_logfile0)的大小;
:当一个日志文件写满后,innodb会自动切换到另外一个日志文件,而且会触发数据库的检查点(关卡),这会导致innodb缓存脏页的小批量刷新,会明显降低innodb的性能。由于日志切换更频繁,也就直接导致更多的缓冲冲水,由于日志切换的时候是不能缓冲冲洗的缓冲区写不下去,导致没有多余的缓冲区写重做,那么整个MYSQL就挂住,还有一种情况是如果有一个大的事务,把所有的日志文件写满了,还没有写完,这样就会导致日志不能切换(因为实例恢复还需要,不能被循环复写)这样MYSQL就挂住了。可以根据文件修改时间来判断日志文件的旋转频率、旋转频率太频繁,说明日志文件太小了。
:设置很大以后减少了检查站,并且由于重做日志是顺序I/O,大大提高了I/O性能。但是如果数据库意外出现了问题,比如意外宕机,那么需要重放日志并且恢复已经提交的事务(也就是实例恢复中的前滚,利用重做从演变化来恢复缓冲区缓存中的数据),如果日志很大,那么将会导致恢复时间很长,甚至到我们不能接受的程度。
如果对Innodb数据表有大量的写入操作,那么选择合适的innodb_log_file_size值对提升MySQL性能很重要,
。
具体依据如下:我经常设置为64 - 512 mb
首先在业务高峰期,计算出1分钟写入的重做量,然后评估出一个小时的重做量;
MariaDB[(一)]比;寻呼机grep日志# #使用页面之后,执行的命令只显示日志开头的
寻呼机设置为“grep日志”
MariaDB[(一)]比;显示引擎innodb状态\ G选择睡眠(60);显示引擎innodb状态\ G;
日志刷新到1149269980149
1行集(0.00秒)
1行集(1分钟0.00秒)
日志刷新到1149270019005 1行集(0.00秒) MariaDB[(一)]比;nopager 寻呼机将stdout MariaDB[(一)]比;选择(4578062739081 - 4578062739081)/1024/1024 MB; + - - - - - - - - - - - - + | MB | + - - - - - - - - - - - - + | 3.51767349 | + - - - - - - - - - - - - + 1行集(0.00秒) 注意日志序列号,这是写入事务日志的总字节数,所以,现在你可以看到每分钟有多少MB日志写入(这里的技术适用于所有版本的MySQL,在5.0及更高版本,你可以从显示全球地位的输出看Innodb_os_log_written的值)。 通过计算后得到每分钟有3.5米的日志写入。 根据经验法则。通常我们设置重做日志大小足够大,能够容纳1个小时的日志写入量。 1小时日志写入量=3.5 * 60=210米,由于默认有两个日志重做日志文件ib_logfile0和ib_logfile1。在日志组中的每个重做日志文件的大小一致,并以循环的方式写入.innodb存储引擎先写重做日志文件0,当达到文件的最后时,会切换到重做日志1,并检查站。以此循环。 所以我们可以大约设置innodb_log_file_size=110米。注意:在innodb1.2.x版本之前,重做日志文件总的大小不得大于等于4 g,而1.2.x版本将该限制扩大到521克了。 该参数控制日志文件数。默认值为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:日志文件。/ib_logfile2并不存在:要创建新的 110124年14:06:23(错误的)插件InnoDB的init函数返回错误。 110124年14:06:23(错误的)插件InnoDB的登记作为一个存储引擎失败了。 该参数确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。 通过innodb_buffer_pool_size,对于比较小的建议是设置一样大。但是,对于比较大的通过innodb_buffer_pool_size,不建议这么设置,这会存在一个潜在的问题,那就是当mysql挂掉时,恢复数据需要很久,造成大量的停机时间,当我们调整通过innodb_buffer_pool_size大小时,innodb_log_buffer_size和innodb_log_file_size也应该做出相应的调整。 在事务被提交并写入到表空间磁盘文件上之前,事务日志存储在InnoDB的重做日志文件里。这些日志位于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
1行集(1分钟0.00秒)
日志刷新到1149270019005 1行集(0.00秒) MariaDB[(一)]比;nopager 寻呼机将stdout MariaDB[(一)]比;选择(4578062739081 - 4578062739081)/1024/1024 MB; + - - - - - - - - - - - - + | MB | + - - - - - - - - - - - - + | 3.51767349 | + - - - - - - - - - - - - + 1行集(0.00秒) 注意日志序列号,这是写入事务日志的总字节数,所以,现在你可以看到每分钟有多少MB日志写入(这里的技术适用于所有版本的MySQL,在5.0及更高版本,你可以从显示全球地位的输出看Innodb_os_log_written的值)。 通过计算后得到每分钟有3.5米的日志写入。 根据经验法则。通常我们设置重做日志大小足够大,能够容纳1个小时的日志写入量。 1小时日志写入量=3.5 * 60=210米,由于默认有两个日志重做日志文件ib_logfile0和ib_logfile1。在日志组中的每个重做日志文件的大小一致,并以循环的方式写入.innodb存储引擎先写重做日志文件0,当达到文件的最后时,会切换到重做日志1,并检查站。以此循环。 所以我们可以大约设置innodb_log_file_size=110米。注意:在innodb1.2.x版本之前,重做日志文件总的大小不得大于等于4 g,而1.2.x版本将该限制扩大到521克了。 该参数控制日志文件数。默认值为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:日志文件。/ib_logfile2并不存在:要创建新的 110124年14:06:23(错误的)插件InnoDB的init函数返回错误。 110124年14:06:23(错误的)插件InnoDB的登记作为一个存储引擎失败了。 该参数确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。 通过innodb_buffer_pool_size,对于比较小的建议是设置一样大。但是,对于比较大的通过innodb_buffer_pool_size,不建议这么设置,这会存在一个潜在的问题,那就是当mysql挂掉时,恢复数据需要很久,造成大量的停机时间,当我们调整通过innodb_buffer_pool_size大小时,innodb_log_buffer_size和innodb_log_file_size也应该做出相应的调整。 在事务被提交并写入到表空间磁盘文件上之前,事务日志存储在InnoDB的重做日志文件里。这些日志位于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
