MySQL日志设置优化

日志产生的性能影响
由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源，所以对于日志的优化策略，在MySQL性能调优中也是至关重要的。
在默认情况下，系统仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO损耗、提高系统性能的目的。

一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL中执行的每一条Query都记录到日志中，会给该系统带来比较大的IO负担，而带来的实际效益却并不很大。

二进制日志（Binlog）相关参数及优化策略
查看binlog相关参数

mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | MIXED                |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |

binlog_cache_size：代表在事务过程中容纳二进制日志SQL语句的缓存大小，如果系统中经常会出现多语句事务，可以尝试增加该值的大小，以获得更优的性能。
可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。

mysql> show status like '%binlog%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_cache_disk_use      | 1     |
| Binlog_cache_use           | 12    |
| Binlog_stmt_cache_disk_use | 0     |
| Binlog_stmt_cache_use      | 0     |
| Com_binlog                 | 0     |
| Com_show_binlog_events     | 0     |
| Com_show_binlogs           | 0     |
+----------------------------+-------+

max_binlog_cache_size：Binlog能够使用的最大cache内存大小。

max_binlog_size：Binlog最大值，默认为1G，一般来说设置为512MB或1GB，但不能超过1GB

sync_binlog：这个参数对于MySQL系统来说是至关重要的，它不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

sync_binlog=0，当事务提交之后，MySQL仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步。
sync_binlog=n，在进行n次事务提交之后，MySQL将执行一次fsync之类的磁盘同步指令，通知文件系统将Binlog文件的缓存刷新到磁盘。

在MySQL中系统默认的设置是sync_binlog=0，即不做任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。因为一旦系统崩溃（Crash），在文件系统缓存中的所有Binlog信息都会丢失。而当设置为“1”时，最安全但性能损耗最大。因为当设置为“1”时，即使系统崩溃（Crash），最多只会丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

可以通过下面这些参数将从Master端到Slave端的Binlog量控制到尽可能地少，从而减小Master端到Slave端的网络流量，减少IO线程的IO量，还能减少SQL线程的解析与应用SQL的数量，最终达到改善Slave上的数据延时问题

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，可带通配符设置。

上面这8个参数中的前面两个是设置在Master端的，而后面6个参数则是设置在Slave端的。

慢查询日志（Slow Query Log）相关参数及使用建议
默认为关闭，可以在my.cnf的 [mysqld] 下增加：slow_query_log=1开启

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | ON                                              |
| slow_query_log_file | /usr/local/mysql/var/qyi-5cda5b769d8f7-slow.log |
+---------------------+-------------------------------------------------+

# slow_query_log：是否开启慢查询日志
# slow_query_log_file：慢查询日志文件名

查看需要记录执行时间超过多长的Query，默认为10s

mysql> show variables like 'long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
# long_query_time：制定慢查询阈值, 单位是秒，且当版本 >=5.5.X ，支持毫秒。例如 0.5 即为 500ms，只会记录大于该值的日志。

可以在mysql配置文件中修改：

# 在my.cnf的 [mysqld] 下增加：long_query_time = 0.5

mysql> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+

Slow Query Log的数据量比较小，带来的IO损耗也就较小，所以开启慢查询日志没有binlog的影响大。
如果系统CPU资源足够丰富可以一直开启，但如果CPU资源比较紧张，完全可以在大部分时候关闭该功能，只须要间断性地打开Slow Query Log功能来定位可能存在的慢查询。