mysql binlog日志优化及思路 |
在数据库安装完毕,对于binlog日志参数设置,有一些参数的调整,来满足业务需求或使性能最大化。Mysql日志主要对io性能产生影响,本次主要关注binlog 日志。 注意,是每个Client都可以分配设置大小的binlogcache空间。如果读者朋友的系统中经常会出现多语句事务的华,可以尝试增加该值的大小,以获得更有的性能; 我们可以通过以下两个状态变量判断binlog_cache_size的使用情况: ----使用二进制日志缓存的事务的数量 2)binlog_cache_disk_use: The number of transactions that used the binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store changes from the transaction. == 我们看看配置文件: [@hostname ~]# grep 'binlog' /data/mydata/my3306/my3306.cnf [@hostname ~]# 我们看看配置64M是否够用?查看下运行情况: mysql> show status like 'binlog_%'; 运行情况Binlog_cache_use 表示binlog_cache内存方式被用上了多少次,Binlog_cache_disk_use表示binlog_cache临时文件方式被用上了多少次。 Binlog_cache_disk_use现在等于0,表示内存cache是够用的,从来不需要使用到临时文件。如果没有long_transaction的话,觉得64M是偏大的。 --- 当我们执行多语句事务的时候,max_binlog_cache_size如果不够大的话,系统可能会报出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的错误。
该大小并不能非常严格控制Binlog大小,尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候,系统为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进入当前日志,直到该事务结束。 这一点和Oracle的Redo日志有点不一样,因为Oracle的Redo日志所记录的是数据文件的物理位置的变化,而且里面同时记录了Redo和Undo相关的信息,所以同一个事务是否在一个日志中对Oracle来说并不关键。 而MySQL在Binlog中所记录的是数据库逻辑变化信息,MySQL称之为Event,实际上就是带来数据库变化的DML之类的Query语句。 sync_binlog:这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下: sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。 sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。 而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看, 对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
Slow Query Log 相关参数及使用建议 mysql> show variables like 'long_query%'; log_slow_queries---参数显示了系统是否已经打开SlowQueryLog功能,而long_query_time参数则告诉我们当前系统设置的SlowQuery记录执行时间超过多长的Query。 在MySQLAB发行的MySQL版本中SlowQueryLog可以设置的最短慢查询时间为1秒,这在有些时候可能没办法完全满足我们的要求,如果希望能够进一步缩短慢查询的时间限制,可以使用Percona提供的microslow-patch(件成为mslPatch)来突破该限制。 mslpatch不仅仅能将慢查询时间减小到毫秒级别,同时还能通过一些特定的规则来过滤记录的SQL,如仅记录涉及到某个表的SlowQuery等等附加功能。 考虑到篇幅问题,这里就不介绍mslpatch给我们带来的更为详细的功能和使用,大家请参考官方介绍(http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/) 打开SlowQueryLog功能对系统性能的整体影响没有Binlog那么大,毕竟SlowQueryLog的数据量比较小,带来的IO损耗也就较小,但是,系统需要计算每一条Query的执行时间,所以消耗总是会有一些的,主要是CPU方面的消耗。 如果大家的系统在CPU资源足够丰富的时候,可以不必在乎这一点点损耗,毕竟他可能会给我们带来更大性能优化的收获。但如果我们的CPU资源也比较紧张的时候,也完全可以在大部分时候关闭该功能,而只需要间断性的打开SlowQueryLog功能来定位可能存在的慢查询。 MySQL的其他日志由于使用很少(QueryLog)或者性能影响很少,我们就不在此过多分析了,至于各个存储引擎相关的日志,我们留在后面“常用存储引擎优化”部分再做相应的分析。
|