mysql binlog日志优化及思路

mysql binlog日志优化及思路

 

在数据库安装完毕，对于binlog日志参数设置，有一些参数的调整，来满足业务需求或使性能最大化。Mysql日志主要对io性能产生影响，本次主要关注binlog 日志。

 

查一下二进制日志相关的参数  

 

   mysql> show variables like '%binlog%';

+--------------------------------+----------------------+

 

| Variable_name                  | Value                |

 

+--------------------------------+----------------------+

 

| binlog_cache_size              | 1048576              |

 

| innodb_locks_unsafe_for_binlog | OFF                  |

 

| max_binlog_cache_size          | 18446744073709547520 |

 

| max_binlog_size                | 1073741824           |

 

| sync_binlog                    | 0                    |

 

+--------------------------------+----------------------+

5 rows in set (0.14 sec)

  www.2cto.com  

其中innodb_locks_unsafe_for_binlog 参数是innodb存储引擎特有的与binlog相关的参数，默认是关闭的

 

Binlog_cache_size:默认大小是37268即32K.我们计算一下上面binlog_cache_size参数大小，1048576/1024/1024 = 1M，根据事务需要调整大小。次参数表示在事务中容纳二进制日志sql语句的缓存大小。那么怎么理解二进制日志缓存，就是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间。我们可以通过以下两个状态变量判断binlog_cache_size的使用情况：

 

1  binlog_cache_use: Thenumber of transactions that used the temporary binary log cache.（使用二进制日志缓存的事务的数量）

  www.2cto.com  

  2   Binlog_cache_disk_use: The number oftransactions that used the binary log cache but that exceeded the value of binlog_cache_size andused a temporary file to store changes from the transaction.

 

 使用二进制日志缓存并且值达到了binlog_cache_size设置的值，用临时文件存储来自事务的变化这样的事务数量

 

Max_binlog_cache_size: 默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。

Max_binlog_size: 1073741824=1G  binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。这根oracle redo日志有点不一样（感兴趣的可以google）。Mysql记录的是mysql数据库逻辑变化信息，称之为event，实际上就是引起数据库变化的query语句。

 

Sync_binlog: 参数不仅影响数据库的性能，而且还影响数据库数据的完整性。Sync_binlog参数选项有如下选择

  www.2cto.com  

1 Sync_binlog=0,这意味着当事务提交之后，mysql不做fsync（文件系统同步）之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

2 sync_binlog=n,当每进行n次提交之后，mysql将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

 

Sync_binlog=0,这是数据库默认设置，性能也是最好的,也是最危险的。一旦系统crash，在binlog_cache中的所有binlog信息都会被丢失。当synn_binlog=1的时候，是最安全性能消耗也最大，完成一个事务，同步一次。系统crash了，也只是丢掉了未完成的事务信息。没坐过这方面的测试，设置为1和0在性能方面的差距，有做过的说在高并发系统中，两种设置性能可高达5倍甚至更多。

 

 这需要我们来做出选择，是要性能最好，还是最安全。

 资料：《mysql性能调优与架构设计》

  www.2cto.com  

 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

 

 

作者 aeolus_pu