一、binlog
查看binlog的相关参数:show variables like "%binlog%"
binlog_cache_size
Binlog Cache 用于在打开了二进制日志(binlog)记录功能的环境,是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说,如果我们的数据库中没有什么大事务,写入也不是特别频繁,2MB~4MB是一个合适的选择。但是如果我们的数据库大事务较多,写入量比较大,可与适当调高binlog_cache_size。同时,我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够,是否有大量的binlog_cache由于内存大小不够而使用临时文件(binlog_cache_disk_use)来缓存了。
Binlog_cache_use记录了使用缓冲写二进制日志的次数,binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。
max_binlog_cache_size
通过max_binlog_cache_size 限制binlog_cache_size最大大小(当单个事务过大的时候MySQL 会申请更多的内存)。当所需内存大于max_binlog_cache_size 参数设置的时候,MySQL 会报错:“Multi-statement transaction required more than ‘max_binlog_cache_size’bytes of storage”。
max_binlog_size binlog日志最大值,一般来说设置为512M或者1G,但不能超过1G。
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倍甚至更多。
大家都知道,MySQL的复制(Replication),实际上就是通过将Master端的Binlog通过利用IO线程通过网络复制到Slave端,然后再通过SQL线程解析Binlog中的日志再应用到数据库中来实现的。所以,Binlog量的大小对IO线程以及Msater和Slave端之间的网络都会产生直接的影响。
MySQL中Binlog的产生量是没办法改变的,只要我们的Query改变了数据库中的数据,那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢?当然不是,在MySQL复制环境中,实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的,分别为:
Binlog_Do_DB:设定哪些数据库(Schema)需要记录Binlog;
Binlog_Ignore_DB:设定哪些数据库(Schema)不要记录Binlog;
Replicate_Do_DB:设定需要复制的数据库(Schema),多个DB用逗号(“,”)分隔;
Replicate_Ignore_DB:设定可以忽略的数据库(Schema);
Replicate_Do_Table:设定需要复制的Table;
Replicate_Ignore_Table:设定可以忽略的Table;
Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置;
Replicate_Wild_Ignore_Table:功能同Replicate_Ignore_Table,可带通配符设置;
二、Slow Query Log
我们为了定位系统中效率比较地下的Query语句,则需要打开慢查询日志,也就是SlowQueryLog。
slow_query_log=on
long_query_time=2 (记录超过的时间,默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
log-long-format (如果设置了,所有没有使用索引的查询也将被记录)