MySQL日志设置优化

        在安装完MySQL 之后，肯定是需要对MySQL 的各种参数选项进行一些优化调整的。虽然MySQL 系统的伸缩性很强，既可以在有很充足的硬件资源环境下高效的运行，也可以在极少资源环境下很好的运行，但不管怎样，尽可能充足的硬件资源对MySQL 的性能提升总是有帮助的。在这一节我们主要分析一下MySQL 的日志（主要是Binlog）对系统性能的影响，并根据日志的相关特性得出相应的优化思路。

日志产生的性能影响

由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO 资源，所以对于日志的在之前介绍MySQL 物理架构的章节中，我们已经了解到了MySQL 的日志包括错误日志（ErrorLog），更新日志（Update Log），二进制日志（Binlog），查询日志（Query Log），慢查询日志（Slow Query Log）等。当然，更新日志是老版本的MySQL 才有的，目前已经被二进制日志替代。

在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO 损耗提高系统性能的目的。但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是MySQL很多存储引擎进行增量备份的基础，也是MySQL 实现复制的基本条件。有时候为了进一步的性能优化，定位执行较慢的SQL 语句，很多系统也会打开慢查询日志来记录执行时间超过特定数值（由我们自行设置）的SQL 语句。

一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL 中执行的每一条Query 都记录到日志中，会该系统带来比较大的IO 负担，而带来的实际效益却并不是非常大。一般只有在开发测试环境中，为了定位某些功能具体使用了哪些SQL 语句的时候，才会在短时间段内打开该日志来做相应的分析。所以，在MySQL 系统中，会对性能产生影响的MySQL 日志（不包括各存储引擎自己的日志）主要就是Binlog 了。

Binlog 相关参数及优化策略

我们首先看看Binlog 的相关参数，通过执行如下命令可以获得关于Binlog 的相关参数。当然，其中也显示出了“ innodb_locks_unsafe_for_binlog”这个Innodb 存储引擎特有的与Binlog 相关的参数：

mysql> show variables like '%binlog%';

| Variable_name | Value |

| binlog_cache_size | 1048576 |

| innodb_locks_unsafe_for_binlog | OFF |

| max_binlog_cache_size | 4294967295 |

| max_binlog_size | 1073741824 |

| sync_binlog | 0 |

“binlog_cache_size"：在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。如果读者朋友的系统中经常会出现多语句事务的华，可以尝试增加该值的大小，以获得更有的性能。当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。

“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误。

“max_binlog_size”：Binlog 日志最大值，一般来说设置为512M 或者1G，但不能超过1G。该大小并不能非常严格控制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 倍甚至更多。

大家都知道，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，可带通配符设置；

通过上面这八个参数，我们就可以非常方便按照实际需求，控制从Master 端到Slave 端的Binlog量尽可能的少，从而减小Master 端到Slave 端的网络流量，减少IO 线程的IO 量，还能减少SQL 线程的解析与应用SQL 的数量，最终达到改善Slave 上的数据延时问题。

实际上，上面这八个参数中的前面两个是设置在Master 端的，而后面六个参数则是设置在Slave 端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系，但是对于优化MySQL 的Replication 来说都可以启到相似的功能。当然也有一定的区别，其主要区别如下：

● 如果在Master 端设置前面两个参数，不仅仅会让Master 端的Binlog 记录所带来的IO 量减少，还会让Master 端的IO 线程就可以减少Binlog 的读取量，传递给Slave 端的IO 线程的Binlog量自然就会较少。这样做的好处是可以减少网络IO，减少Slave 端IO 线程的IO 量，减少Slave端的SQL 线程的工作量，从而最大幅度的优化复制性能。当然，在Master 端设置也存在一定的弊端，因为MySQL 的判断是否需要复制某个Event 不是根据产生该Event 的Query 所更改的数据所在的DB，而是根据执行Query 时刻所在的默认Schema，也就是我们登录时候指定的DB 或者运行“USE DATABASE”中所指定的DB。只有当前默认DB 和配置中所设定的DB 完全吻合的时候IO线程才会将该Event 读取给Slave 的IO 线程。所以如果在系统中出现在默认DB 和设定需要复制的DB 不一样的情况下改变了需要复制的DB 中某个Table 的数据的时候，该Event 是不会被复制到Slave 中去的，这样就会造成Slave 端的数据和Master 的数据不一致的情况出现。同样，如果在默认Schema 下更改了不需要复制的Schema 中的数据，则会被复制到Slave 端，当Slave 端并没有该Schema 的时候，则会造成复制出错而停止；

● 而如果是在Slave 端设置后面的六个参数，在性能优化方面可能比在Master 端要稍微逊色一点，因为不管是需要还是不需要复制的Event 都被会被IO 线程读取到Slave 端，这样不仅仅增加了网络IO 量，也给Slave 端的IO 线程增加了Relay Log 的写入量。但是仍然可以减少Slave的SQL 线程在Slave 端的日志应用量。虽然性能方面稍有逊色，但是在Slave 端设置复制过滤机制，可以保证不会出现因为默认Schema 的问题而造成Slave 和Master 数据不一致或者复制出错的问题。

Slow Query Log 相关参数及使用建议

再来看看Slow Query Log 的相关参数配置。有些时候，我们为了定位系统中效率比较地下的Query语句，则需要打开慢查询日志，也就是Slow Query Log。我们可以如下查看系统慢查询日志的相关设置：

mysql> show variables like 'log_slow%';

| Variable_name | Value |

| log_slow_queries | ON |

1 row in set (0.00 sec)

mysql> show variables like 'long_query%';

| Variable_name | Value |

| long_query_time | 1 |

1 row in set (0.01 sec)

“ log_slow_queries ” 参数显示了系统是否已经打开Slow Query Log 功能， 而“long_query_time”参数则告诉我们当前系统设置的Slow Query 记录执行时间超过多长的Query。在MySQL AB 发行的MySQL 版本中Slow Query Log 可以设置的最短慢查询时间为1 秒，这在有些时候可能没办法完全满足我们的要求，如果希望能够进一步缩短慢查询的时间限制，可以使用Percona 提供的microslow-patch（件成为msl Patch）来突破该限制。msl patch 不仅仅能将慢查询时间减小到毫秒级别，同时还能通过一些特定的规则来过滤记录的SQL，如仅记录涉及到某个表的Slow Query 等等附加功能。打开Slow Query Log 功能对系统性能的整体影响没有Binlog 那么大，毕竟Slow Query Log 的数据量比较小，带来的IO 损耗也就较小，但是，系统需要计算每一条Query 的执行时间，所以消耗总是会有一些的，主要是CPU 方面的消耗。如果大家的系统在CPU 资源足够丰富的时候，可以不必在乎这一点点损耗，毕竟他可能会给我们带来更大性能优化的收获。但如果我们的CPU 资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开Slow Query Log 功能来定位可能存在的慢查询。

MySQL 的其他日志由于使用很少（Query Log）或者性能影响很少，我们就不在此过多分析了，至于各个存储引擎相关的日志，我们留在后面“常用存储引擎优化”部分再做相应的分析。