mysql 5.5中与CPU和I/O使用相关的四个参数

╰半夏微凉° 2022-08-06 05:05 3阅读 0赞

mysql 5.5中很多很多配置参数，其中有一些参数根据特定的应用场景进行调整，会对应用性能和服务器资源利用率产生很大的影响。

**一、参数名称**

对于由排序操作和大并发的应用逻辑会有较大影响的参数有：

key\_buffer\_size = 128M   -》 由默认1M，修改为 128M

myisam\_sort\_buffer\_size = 128M  -》 由默认的1M，修改为128M

sync\_binlog             = 1       -》  由默认的1，每1次都将binlog与硬盘同步，修改为30，则等待30次才将binlog与硬盘同步

innodb\_flush\_log\_at\_trx\_commit = 2   -》 由默认值1（每次事务提交都写入硬盘，修改为2，可能会有1-2秒数据丢失，但CPU的利用率和性能会提升）

这四个参数中，前两个参数是调整内存，后两个是调整binlog刷新时间和频率，这些参数都间接与CPU资源消耗相关。

**二、参数含义**

这几个参数的具体含义如下：

**key\_buffer\_size**

key\_buffer\_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理。比例key\_reads /key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key\_read%’获得）。

key\_buffer\_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created\_tmp\_disk\_tables得知详情。

对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）

提升性能的建议:  
1.如果opened\_tables太大,应该把my.cnf中的table\_cache变大  
2.如果Key\_reads太大,则应该把my.cnf中key\_buffer\_size变大.可以用Key\_reads/Key\_read\_requests计算出cache失败率  
3.如果Handler\_read\_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用  
4.如果Threads\_created太大,就要增加my.cnf中thread\_cache\_size的值.可以用Threads\_created/Connections计算cache命中率  
5.如果Created\_tmp\_disk\_tables太大,就要增加my.cnf中tmp\_table\_size的值,用基于内存的临时表代替基于磁盘的

myisam\_sort\_buffer\_size = 128M

\# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

该排序缓冲区在对于有较大表结构变化时，就会使用；如果业务系统中会定时进行很多alter table等操作，且比较慢的时候，就可以确认并修改该参数。

\# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

**sync\_binlog **

该参数属于mysql日志参数中的一个设置，可以从mysql数据库的整体日志中来进行看待：

## 日志产生的性能影响 ##

由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源。

在之前介绍MySQL物理架构的章节中，我们已经了解到了MySQL的日志包括错误日志（ErrorLog），更新日志（UpdateLog），二进制日志（Binlog），查询日志（QueryLog），慢查询日志（SlowQueryLog）等。当然，更新日志是老版本的MySQL才有的，目前已经被二进制日志替代。

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

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

## Binlog 相关参数及优化策略 ##

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

![复制代码][Image 1]

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|
    +--------------------------------+------------+

![复制代码][Image 1]

“binlog\_cache\_size"：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的binlogcache空间。如果读者朋友的系统中经常会出现多语句事务的华，可以尝试增加该值的大小，以获得更有的性能。当然，我们可以通过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-statementtransactionrequiredmorethan'max\_binlog\_cache\_size'bytesofstorage”的错误。

“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或者运行“USEDATABASE”中所指定的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线程增加了RelayLog的写入量。但是仍然可以减少Slave的SQL线程在Slave端的日志应用量。虽然性能方面稍有逊色，但是在Slave端设置复制过滤机制，可以保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或者复制出错的问题。

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

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

![复制代码][Image 1]

![复制代码][Image 1]

“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）或者性能影响很少，我们就不在此过多分析了，至于各个存储引擎相关的日志，我们留在后面“常用存储引擎优化”部分再做相应的分析。

转自 《MySQL性能调优与架构设计》

**innodb\_flush\_log\_at\_trx\_commit **

同样的，该参数和sync\_binlog一样，并不是单一存在的一个参数，而是与其他innodb的参数相互对应和关联的。

与innodb相关的重要参数有：

innodb\_buffer\_pool\_size  
       如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key\_buffer\_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key\_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb\_additional\_pool\_size   
       这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。

innodb\_log\_file\_size  
       对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb\_log\_buffer\_size   
       默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb\_flush\_log\_at\_trx\_commit  （这个很管用）   
       抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。   
  
[`innodb_flush_log_at_trx_commit`][innodb_flush_log_at_trx_commit]

If the value of [`innodb_flush_log_at_trx_commit`][innodb_flush_log_at_trx_commit] is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).

A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any [mysqld][]process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, `InnoDB`'s crash recovery is not affected and thus crash recovery does work regardless of the value.

Note

For the greatest possible durability and consistency in a replication setup using `InnoDB` with transactions, use `innodb_flush_log_at_trx_commit=1`, `sync_binlog=1`, and `innodb-safe-binlog` in your master server `my.cnf` file.

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell[mysqld][] that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the`InnoDB` database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix commandhdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

由此可见，后两个参数主要是控制日志写入和刷新时间和频率来影响性能的，这里就有一个数据安全性和性能的权衡了，将这些值设置的非常低，每次事务都写入日志，并刷新binlog，但会大大增加IO次数和CPU消耗；如果 设置的非常高，能够等待多次事务后再进行写入和刷新，这样会提高性能，但存在一定的数据丢失风险。

这时就要结合业务特点，对mysql数据库进行整体考虑，即考虑安全性，又能使性能达到比较高的水平。

**三、调整效果**

在测试库中，上面四个参数初始情况是这样的：

key\_buffer\_size = 1M

myisam\_sort\_buffer\_size = 1M

sync\_binlog             = 1

innodb\_flush\_log\_at\_trx\_commit = 1

这时由于系统会有定时大量的alert操作和长查询排序操作，而且日志级别设定的都比较低，在有800-1000个用户进行并发压力测试的时候，CPU使用率达到90%以上，进而IO也会出现一些等待，mysql性能也比较低；同时，对比一台VMware虚拟机上的mysql slave从库，由于binlog写入过于频繁，slave可以读取到master的日志，但由于IO和CPU性能远低于高配物理机的master性能，slave一直追不上master的日志同步点，而且落后从库越来越多，以致于失去了主从同步的意义。

根据业务情况，并且确认安全性和性能后，对上面的参照调整成如下状态：

key\_buffer\_size = 128M

myisam\_sort\_buffer\_size = 128M

sync\_binlog             = 30

innodb\_flush\_log\_at\_trx\_commit = 2

参数调整后，再次进行压力测试，CPU使用率在10%左右，没有超过20%；slave从库也多次执行binlog后再刷新，慢慢就赶上了master的日志状态，主从之间达到的数据同步。

**四、修改方法**

对于上面四个参数的修改，有两种方法：

1.修改my.cnf，重启后生效：这种方法适用于mysql服务可以停止并重启的情况，配置文件修改后，重启mysql服务，会重新读取my.cnf中的配置，使参数生效：

2.直接在mysql实例中进行参数修改：这种方法适用于mysql服务需要一直运行，不能重启的线上系统；这四个参数都是动态参数，修改之后可以直接生效，生效后可以在my.cnf中也进行相应修改，以保证重启后参数仍升生效。

命令修改方法如下：

mysql> show variables like 'key\_buffer\_size';

修改 key\_buffer\_size 为 128M，可以动态修改

mysql>set GLOBAL key\_buffer\_size = 134217728;

mysql> show variables like 'key\_buffer\_size';

show variables like 'myisam\_sort\_buffer\_size';

修改 myisam\_sort\_buffer\_size 为 128M，可以动态修改，过段时间生效

set GLOBAL myisam\_sort\_buffer\_size = 134217728;

show variables like 'myisam\_sort\_buffer\_size';

show variables like 'sync\_binlog';

修改binlog写入次数为30，可以动态修改

set GLOBAL sync\_binlog=30;

show variables like 'sync\_binlog';

show variables like 'innodb\_flush\_log\_at\_trx\_commit';

修改事务提交次数为2，可以动态修改

mysql> set GLOBAL innodb\_flush\_log\_at\_trx\_commit=2;

mysql> show variables like 'innodb\_flush\_log\_at\_trx\_commit';

注意：对于上述参数的修改，是结合实际的业务和环境进行合理调整才可以取得较好的效果。大家可以对参照的意义和作用进行了解后，结合自己所在的业务环境进行合理调整，才可以达到良好的优化效果。

[Image 1]: 
[innodb_flush_log_at_trx_commit]: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
[mysqld]: http://dev.mysql.com/doc/refman/4.1/en/mysqld.html