MySQL参数设置优化

Published on 2016 - 06 - 07

MySQL日志设置优化

这节主要分析一下MySQL的日志(主要是Binlog)对系统性能的影响,并根据日志的相关特性得出相应的优化思路。

日志产生的性能影响

由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源,所以对于日志的优化策略,在MySQL性能调优中也是至关重要的。

MySQL的日志包括错误日志(Error Log)、更新日志(Update Log)、二进制日志(Binlog)、查询日志(Query Log)、慢查询日志(Slow Query Log)等。当然,更新日志是老版本的MySQL才有的,目前已经被二进制日志替代。

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

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

二进制日志(Binlog)相关参数及优化策略

首先看看Binlog的相关参数,通过执行如示例代码1中的命令,可以获得关于Binlog的相关参数。当然,其中也显示出了“innodb_locks_unsafe_for_binlog”这个InnoDB存储引擎特与Binlog相关的参数:

  • “binlog_cache_size”:代表在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎,且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存,注意,是每个客户端都可以分配设置大小的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 of storage”的错误。
  • “max_binlog_size”:Binlog最大值,一般来说设置为512MB或1GB,但不能超过1GB。该设置并不能非常严格控制Binlog的大小,尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务时,系统为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到该事务结束。这一点和Oracle的Redo日志有点不一样,因为Oracle的Redo日志所记录的是数据文件的物理位置的变化,而且里面同时记录了与Redo和Undo相关的信息,所以同一个事务是否在一个日志中对Oracle来说并不关键。而MySQL在Binlog中所记录的是数据库逻辑变化信息,MySQL称之为Event,实际上就是带来数据库变化的DML之类的Query语句以及执行该Query语句的上下文信息。
  • “sync_binlog”:这个参数对于MySQL系统来说是至关重要的,它不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下。
    • 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中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,“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,可带通配符设置。

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

实际上,上面这8个参数中的前面两个是设置在Master端的,而后面6个参数则是设置在Slave端的。虽然前面两个参数和后面6个参数在功能上并没有非常直接的关系,但是对于优化MySQL的Replication来说都可以起到相似的作用。当然也有一定的区别,如:

  1. 如果在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时,则会造成复制出错而停止。
  2. 如果是在Slave端设置后面的6个参数,在性能优化方面可能比在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。可以采用如示例代码2的方式查看系统慢查询日志的相关设置:

  • “log_slow_queries”参数显示了系统是否已经打开Slow Query Log功能,而“long_query_time”参数则告诉我们,当前系统需要记录执行时间超过多长的Query。在MySQL AB发行的MySQL 5.1之前的版本中,Slow Query Log可以设置的最短慢查询时间为1秒。

打开Slow Query Log功能对系统性能的整体影响没有Binlog那么大,毕竟Slow Query Log的数据量比较小,带来的IO损耗也就较小,但是,系统须要计算每一条Query的执行时间,所以消耗总是会有一些的,主要是CPU方面的消耗。如果大家的系统CPU资源足够丰富,可以不必在乎这一点点损耗,毕竟它可能会带来更大性能优化方面的收获。但如果CPU资源比较紧张,完全可以在大部分时候关闭该功能,只须要间断性地打开Slow Query Log功能来定位可能存在的慢查询。

Query Cache优化

谈到Query Cache,恐怕大部分使用过MySQL的人或多或少都会有一些了解,因为在很多人看来它可以帮助我们让数据库的性能产生一个“质”的提升。但真是这样吗?这一节将就如何合理地使用MySQL的Query Cache进行一些相应的分析,并得出部分优化建议。

MySQL的Query Cache实现原理实际上并不是特别复杂,简单来说就是将客户端请求的Query语句(仅限于SELECT类型的Query)通过一定的hash算法进行一个计算,得到一个hash值,存放在一个hash桶中。同时将该Query的结果集(Result Set)也存放在一个内存Cache中。存放Query hash值的链表中每一个hash值所在节点的同时,还存放了该Query所对应的Result Set的Cache所在的内存地址,以及该Query涉及的所有Table的标识等一些其他相关信息。系统接受到任何一个SELECT类型的Query时,首先计算出其hash值,然后通过该hash值到Query Cache中去匹配,如果找到了完全相同的Query,则直接将之前所缓存(cache)的Result Set返回给客户端,完全不须要进行后面的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后,也会通知Query Cache,须要将所有与该Table有关的Query的Cache全部失效,并释放出之前占用的内存地址,以便后面其他的Query能够使用。

从上面的实现原理来看,Query Cache确实是以比较简单的实现带来巨大性能收益的功能。但是可能很多人都忽略了使用QueryCache之后所带来的负面影响。

  1. Query语句的hash运算及hash查找资源消耗。在使用Query Cache,每条SELECT类型的Query到达MySQL之后,都须要进行一个hash运算,然后查找是否存在该Query的Cache,虽然这个hash运算的算法可能已经非常高效,且hash查找的过程也已经足够的优化了,对于一条Query来说消耗的资源确实是非常之少,但是当每秒都有上千甚至几千条Query时,就不能对产生的CPU消耗完全忽视了。
  2. Query Cache的失效问题。如果表的变更比较频繁,则会造成Query Cache的失效率非常高。这里表的变更不仅指表中数据的变更,还包括结构或索引等的任何变更。也就是说每次缓存到Query Cache中的Cache数据可能在被存入后很快就会因为表中的数据被改变而被清除,导致新的相同Query进来后无法使用到之前的Cache。
  3. Query Cache中缓存的是Result Set,而不是数据页,也就是说,存在同一条记录被Cache多次的可能性,从而造成内存资源的过渡消耗。当然,可能有人会说可以限定Query Cache的大小啊。是的,确实可以限定Query Cache的大小,但是这样,Query Cache就很容易造成因为内存不足而被换出,造成命中率下降。

对于Query Cache的上面三个负面影响,如果单独拿出任一个影响来说都不会对整个系统造成多大的问题,并不会让大家对使用Query Cache产生太多顾虑。但是,当将这三个负面影响一起考虑时,恐怕Query Cache在很多人心目中就不再是以前的那把“尚方宝剑”了。

适度使用Query Cache

虽然Query Cache的使用会存在一些负面影响,但也应该相信其存在必定有一定的价值。我们完全不用因为Query Cache的上面三个负面影响就失去了对Query Cache的信心。只要理解了Query Cache的实现原理,在使用Query Cache时完全可以通过一定的手段扬长避短,发挥其优势,并有效地避开其劣势。

首先,须要根据Query Cache失效机制来判断哪些表适合使用Query哪些表不适合。由于Query Cache的失效主要是因为Query所依赖的Table数据发生了变化,可能造成Query的Result Set已经有所改变而导致相关的Query Cache全部失效,那么就应该避免在查询变化频繁的Table的Query上使用,而应该在那些查询变化频率较低的Table的Query上使用。MySQL中针对Query Cache有两个专用的SQL Hint(提示):SQL_NO_CACHE和SQL_CACHE,分别代表强制不使用Query Cache和强制使用Query Cache。可以利用这两个SQL Hint,让MySQL知道我们希望哪些SQL使用Query Cache,哪些SQL不要使用。这样不仅可以让变化频繁的Table的Query浪费Query Cache的内存,同时还可以减少Query Cache的检测量。

其次,对于那些变化非常少,大部分时候都是静态的数据,可以添加SQL_CACHE的SQL Hint,强制MySQL使用Query Cache,从而提高该表的查询性能。

最后,有些SQL的Result Set很大,如果使用Query Cache很容易造成Cache内存的不足,或者将之前一些老的Cache冲刷出去。对于这一类Query有两种方法可以解决,一是使用SQL_NO_CACHE参数来强制它不使用Query Cache,每次都直接从实际数据中去查找,另一种方法是通过设定“query_cache_limit”参数值来控制Query Cache中所缓存的最大Result Set,系统默认为1MB(1048576)。当某个Query的Result Set大于“query_cache_limit”所设定的值时,Query Cache是不会缓存这个Query的。

Query Cache的相关系统参数变量和状态变量

首先看看Query Cache的系统变量,可以通过执行示例代码3中所示命令获得MySQL中与Query Cache相关的系统参数变量:

  • “have_query_cache”:该MySQL是否支持Query Cache;
  • “query_cache_limit”:Query Cache存放的单条Query最大Result Set,默认1MB;
  • “query_cache_min_res_unit”:Query Cache每个Result Set存放的最小内存大小,默认4KB;
  • “query_cache_size”:系统中用于Query Cache内存的大小;
  • “query_cache_type”:系统是否打开了Query Cache功能;
  • “query_cache_wlock_invalidate”:针对于MyISAM存储引擎,设置当有WRITE LOCK在某个Table上时,读请求是要等WRITE LOCK释放资源后再查询还是允许直接从Query Cache中读取结果,默认为FALSE(可以直接从Query Cache中取得结果)。

以上参数的设置中主要是“query_cache_limit”和“query_cache_min_res_unit”须要做一些针对应用的相关调整。如果须要Cache的Result Set一般都很小(小于4KB)的话,可以适当将“query_cache_min_res_unit”参数再调小一些,避免造成内存的浪费,“query_cache_limit”参数则不用调整。而如果须要Cache的Result Set大部分都大于4KB的话,最好将“query_cache_min_res_unit”调整到和Result Set差不多大,“query_cache_limit”的参数也应大于Result Set的大小。当然,可能有些时候难以准确估算Result Set的大小,那么当Result Set较大时,也并不一定得将“query_cache_min_res_unit”设置得和每个Result Set差不多大,设为每个结果集的一半或四分之一大小都可以,要想完全不浪费任何内存确实不太可能做到。

如果想了解Query Cache的使用情况,则可以通过Query Cache相关的状态变量来获取,如通过示例代码4所示的命令:

  • “Qcache_free_blocks”:Query Cache中目前还有多少剩余的blocks。如果该值显示较大,则说明Query Cache中的内存碎片过多了,可能须要寻找合适的机会进行整理;
  • “Qcache_free_memory”:Query Cache中目前剩余的内存大小。通过这个参数可以较为准确地观察出当前系统中的Query Cache内存大小是否足够,是须要增加还是过多了;
  • “Qcache_hits”:多少次命中。通过这个参数可以查看到Query Cache的基本效果;
  • “Qcache_inserts”:多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数可以算出Query Cache的命中率;
  • Query Cache命中率= Qcache_hits / (Qcache_hits + Qcache_inserts );”
  • “Qcache_lowmem_prunes”:多少条Query因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚地了解到系统中Query Cache的内存大小是否真的足够,是否非常频繁地出现因为内存不足而有Query被换出的情况;
  • “Qcache_not_cached”:因为query_cache_type的设置而不能被缓存(cache)的Query数量;
  • “Qcache_queries_in_cache”:当前Query Cache中缓存(cache)的Query数量;
  • “Qcache_total_blocks”:当前Query Cache中的block数量。

Query Cache的限制

由于Query Cache存放的都是逻辑结构的Result Set,而不是物理的数据页,所以在性能提升的同时,也会受到一些特定的限制。

  1. 5.1.17之前的版本不能缓存(cache)绑定变量的Query,但是从5.1.17版本开始,Query Cache已经开始支持绑定变量的Query了;
  2. 所有子查询中的外部查询SQL不能被缓存(cache);
  3. 在Procedure、Function及Trigger中的Query不能被缓存(cache);
  4. 包含很多每次执行可能得到不一样结果的函数的Query不能被缓存(cache)。

鉴于上面的这些限制,在使用Query Cache的过程中,建议通过精确设置的方式来使用,仅让合适的表的数据进入Query Cache,仅让某些Query的查询结果被缓存(cache)。

MySQL Server其他常用优化

除了安装、日志、Query Cache之外,可能影响MySQL Server整体性能的设置还包括很多其他方面,如网络连接、线程管理、Table管理等。这一节将分析除了前面几节内容之外可能影响MySQL Server性能的其他可优化部分。

网络连接与连接线程

虽然MySQL的连接不仅能通过网络方式,还可以通过命名管道的方式,但不论是何种方式连接MySQL,在MySQL中都是通过线程的方式管理所有客户端请求的。每一个客户端连接都会有一个与之对应的连接线程。先看一下与网络连接的性能配置项及对性能的影响。

  • max_connections:整个MySQL允许的最大连接数。

这个参数主要影响的是整个MySQL应用的并发处理能力,当系统中实际需要的连接量大于max_connections时,由于MySQL的设置限制,那么应用中必然会产生连接请求的等待,从而限制了相应的并发量。所以一般来说,只要MySQL主机性能允许,都是将该参数设置得尽可能大一点。一般来说500到800左右是一个比较合适的参考值。

  • max_user_connections:每个用户允许的最大连接数。

上面的参数限制了整个MySQL的连接数,而max_user_connections则是针对单个用户的连接限制。在一般情况下可能较少使用这个限制,可能只有在一些专门提供MySQL数据存储服务,或者是提供虚拟主机服务的应用中须要用到。除了限制对象的区别之外,其他方面和max_connections一样。这个参数的设置完全依赖于应用程序的连接用户数,对于普通的应用来说,完全没有做太多限制,可以尽量放开一些。

  • net_buffer_length:网络包传输中,传输消息之前的net buffer初始化大小。

这个参数主要可能影响的是网络传输的效率,由于该参数所设置的只是消息缓冲区的初始化大小,所以造成的影响主要是当每次的消息都很大时,MySQL总是须要多次申请扩展该缓冲区的大小。系统默认大小为16KB,一般来说可以满足大多数场景,当然如果查询返回数据量非常小,每次网络传输量都很少,而且系统内存又比较紧缺的情况下,也可以适当将该值降低到8KB。

  • max_allowed_packet:在网络传输中,一次消息传输量的最大值。

这个参数与net_buffer_length相对应,只不过是net buffer的最大值。当消息传输量大于net_buffer_length的设置时,MySQL会自动增大net buffer的大小,直到缓冲区大小达到max_allowed_packet所设置的值。系统默认值为1MB,最大值是1GB,必须设定为1024的倍数,单位为字节。

  • back_log:在MySQL的连接请求等待队列中允许存放的最大连接请求数。

连接请求等待队列,实际上是指当某一时刻客户端的连接请求数量过大,MySQL主线程没办法及时给每一个新的连接请求分配(或创建)连接线程时,还没有分配到连接线程的所有请求将存放在一个等待队列中,这个队列就是MySQL的连接请求队列。当系统存在瞬时的大量连接请求时,则应该注意back_log参数的设置。系统默认值为50,最大可以设置为65535。当增大back_log的设置时,同时还须注意OS级别对网络监听队列的限制,因为如果OS的网络监听设置小于MySQL的back_log设置,加大“back_log”设置是没有意义的。

上面介绍了与网络连接交互相关的主要优化设置,下面再来看看与每一个客户端连接相对应的连接线程。

在MySQL中,为了尽可能提高“客户端请求创建连接”这个过程的性能,实现了一个Thread Cache池,将空闲的连接线程存放其中,而不是完成请求后就销毁。这样,当有新的连接请求时,MySQL首先会检查Thread Cache池中是否存在空闲连接线程,如果存在则取出来直接使用,如果没有空闲连接线程,才创建新的连接线程。在MySQL中与连接线程相关的系统参数及状态变量说明如下。

  • thread_cache_size:Thread Cache池中应该存放的连接线程数。

当系统最初启动时,并不会马上就创建如thread_cache_size所设数目之多的连接线程存放在Thread Cache池中,而是随着连接线程的创建及使用,慢慢地将用完的连接线程存入其中。在存放的连接线程达到thread_cache_size值之后,MySQL就不再继续保存用完的连接线程了。

如果应用程序使用的是短连接,Thread Cache池的功效是最明显的。因为在短连接的数据库应用中,数据库连接的创建和销毁是非常频繁的,如果每次都须要让MySQL新建和销毁相应的连接线程,那么这个资源消耗实际上是非常大的,而当我们使用了Thread Cache时,由于连接线程大部分都处于创建好了等待取用的状态,既不须要每次都重新创建,又不须要在使用完后销毁,所以可以节省下大量的系统资源。因此在短连接的应用系统中,thread_cache_size的值应该设置得相对大一些,不应该小于应用系统对数据库的实际并发请求数。

如果使用的是长连接,Thread Cache的功效可能并没有使用短连接时那样大,但也并不是完全没有价值。因为应用程序即使是使用了长连接,也很难保证它们管理的所有连接都能处于很稳定的状态,仍然会有不少连接关闭和新建的操作出现。在有些并发量较高,应用服务器数量较大的系统中,每分钟10来次的连接创建与关闭的操作是很常见的。而且如果应用服务器的连接池管理不是太好,容易产生连接池抖动的话,所产生的连接创建和销毁操作将会更多。所以即使是在使用长连接的应用环境中,Thread Cache机制的利用仍能对性能大有帮助。只不过在长连接的环境中不需要将thread_cache_size参数设置太大,一般来说可能50到100之间就可以了。

  • thread_stack:每个连接线程被创建时,MySQL给它分配的内存大小。

当MySQL创建一个新的连接线程时,须要给它分配一定大小的内存堆栈空间,以便存放客户端的请求Query及自身的各种状态和处理信息。不过一般来说如果不是对MySQL的连接线程处理机制十分熟悉,不应该轻易调整该参数的大小,系统的默认值(192KB)基本上可以适应所有的普通应用环境。如果该值设置太小,会影响MySQL连接线程能够处理客户端请求的Query内容的大小,以及用户创建的Procedures和Functions等。

上面介绍的是怎样配置网络连接交互及连接线程的性能相关参数,下面再看看该怎样检验上面所做的设置是否合理,是否有须要调整的地方。可以通过在系统中执行如下的几个命令来获得相关的状态信息以帮助大家检验设置的合理性:

先看看连接线程相关的系统变量的设置值,如示例代码5所示:

再来看一下系统被连接的次数及当前系统中连接线程的状态值,如示例代码6所示:

通过上面的命令,可以看出,系统设置了Thread Cache池最多将缓存32个连接线程,每个连接线程创建之初,系统分配192KB的内存堆栈给它。系统启动到现在共接收到客户端的连接127次,共创建了11个连接线程,当前有7个连接线程处于和客户端连接的状态,而7个连接状态的线程中只有一个处于active状态,即只有一个正在处理客户端提交的请求。而在Thread Cache池中共缓存(cache)了4个连接线程。

通过系统设置和当前状态的分析,可以发现,thread_cache_size的设置已经足够了,甚至还远大于系统的需要。所以可以适当减少thread_cache_size的设置,比如设置为8或16。根据Connections和Threads_created这两个系统状态值,还可以计算出系统新建连接的Thread Cache命中率,也就是通过Thread Cache池中取得连接线程的次数与系统接收的总连接次数的比率,如示例代码7所示:

可以通过上述运算公式计算一下上面环境中的Thread Cache命中率:

一般来说,在系统稳定运行一段时间后,Thread Cache命中率应该保持在90%左右甚至更高才算正常。可以看出上面环境中的Thread Cache命中比率基本正常的。

Table Cache相关的优化

先来看一下MySQL打开表的相关机制。由于多线程的实现机制,为了尽可能地提高性能,在MySQL中每个线程都是独立地打开自己需要的表的文件描述符,而不是通过共享已经打开的表的文件描述符的。当然,针对不同的存储引擎可能有不同的处理方式。如MyISAM表,每一个客户端线程打开任何一个MyISAM表的数据文件都须要打开一个文件描述符,但如果是索引文件,则可以多个线程共享同一个索引文件的描述符。对于InnoDB的存储引擎,如果使用的是共享表空间来存储数据,那么须要打开的文件描述符就比较少,而如果使用的是独享表空间方式来存储数据,由于存储表数据的数据文件较多,则同样会打开很多的表文件描述符。除了数据库的实际表或索引打开时须要使用文件描述符以外,临时文件同样也须要,因此同样会占用系统中open_files_limit的设置限额。

为了解决打开表文件描述符太过频繁的问题,MySQL在系统中实现了一个Table Cache的机制,和前面介绍的Thread Cache机制有点类似,主要就是Cache打开所有表文件的描述符,当有新的请求时不须要重新打开,使用结束时也不用立即关闭。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。先看一看与Table Cache相关的系统参数及状态变量。

在MySQL中通过table_cache(从MySQL 5.1.3开始改为table_open_cache)来设置系统中缓存(Cache)地打开的表文件描述符的数量。通过MySQL官方手册中的介绍,在设置table_cache大小时应该通过max_connections参数计算得来,公式如下:

其中N代表单个Query语句中所包含的最多Table的数量。但是这样的计算其实并不太准确,分析如下:

首先,max_connections虽是系统同时可以接受的最大连接数,但这些连接并不一定都是active状态的,也就是说可能里面有不少连接都是处于Sleep状态。而处于Sleep状态的连接是不可能打开任何Table的。

其次,这个N为执行Query时,Query所包含的Table的个数也并不是太合适,因为不能忽略索引文件的打开。虽然索引文件描述符在各个连接线程之间是可以共享的,但至少每个索引文件总还是需要一个的。而且,如果Query中的每个表的访问都是先通过索引定位检索的,甚至可能还是通过多个索引,那么该Query的执行所需打开的文件描述符就更多了,可能是N的两倍甚至三倍。

最后,这个计算的公式只能计算出同一时刻须要打开的描述符的最大数量,而table_cache的设置也不一定非得根据这个极限值来设定,因为table_cache所设定的只是Cache打开的描述符的数量多少,而不是最多能够打开的量的大小。

当然,上面这些只是个人的理解,可能并不是太严谨,各位读者如果觉得有其他的理解完全可以提出来大家再探讨。

我们可以通过如示例代码8中所示的方式查看table_cache的设置和当前系统中的使用状况:

上面的结果显示系统设置的table_cache为512个,也就是说在该MySQL中,Table Cache内可以缓存512个打开文件的描述符;当前系统中打开的描述符仅有6个。

那么Table Cache池中缓存的描述符在什么情况下会被关闭呢?一般来说主要有以下几种情况:

  1. Table Cache的Cache池满了,而某个连接线程须要打开某个不在Table Cache中的表时,MySQL会通过一定的算法关闭某些没有使用的描述符;
  2. 当我们执行Flush Table等命令时,MySQL会关闭当前Table Cache中缓存的所有文件描述符;
  3. 当Table Cache中Cache量超过table_cache参数设置的值时。

Sort Buffer、Join Buffer和Read Buffer

在MySQL中,除之前介绍的多种Cache外,还有在Query执行过程中的两种Buffer会对数据库的整体性能产生影响,如示例代码9所示:

  • join_buffer_size:只有当Join是ALL、index、rang,或者index_merge时使用到Join Buffer,这种Join被称为Full Join。 Join Buffer的设置在MySQL 5.1.23版本之前最大为4GB,但是从5.1.23版本开始,在除了Windows之外64位的平台上可以超出4BG的限制。系统默认是128KB。
  • sort_buffer_size:系统中对数据进行排序时使用的Buffer。Sort Buffer同样是针对单个Thread的,所以当多个Thread同时进行排序时,系统中就会出现多个Sort Buffer。一般可以通过增大Sort Buffer来提高ORDER BY或者GROUP BY的处理性能。系统默认大小为2MB,最大限制和Join Buffer一样,在MySQL 5.1.23版本之前最大为4GB,从5.1.23版本开始,在除了Windows之外64位的平台上可以超出4GB的限制。

如果应用系统中很少有Join语句出现,则不用太在乎join_buffer_size参数的大小设置,但是如果Join语句不少的话,个人建议可以适当增大join_buffer_size,设置到1MB左右,如果内存充足甚至可以设置为2MB。对于sort_buffer_size参数来说,一般设置为2MB到4MB之间可以满足大多数应用的需求。当然,如果应用系统中的排序都比较大、内存充足且并发量不是特别大时,也可以继续增大sort_buffer_size的设置。在设置这两个Buffer时,最须要注意的就是不要忘记每个Thread都会创建自己独立的Buffer,而不是整个系统共享的Buffer,不要因为设置过大而造成系统内存不足。

参考文档