MySQL为什么要关闭Query Cache?

当你的数据库打开了Query Cache(简称QC)功能后,数据库在执行SELECT语句时,会将其结果放到QC中,当下一次处理同样的SELECT请求时,数据库就会从QC取得结果,而不需要去数据表中查询。

MySQL为什么要关闭Query Cache?
MySQL为什么要关闭Query Cache?

在这个”Cache为王”的时代,我们总是通过不同的方式去缓存我们的结果从而提高响应效率,但一个缓存机制是否有效,效果如何,却是一个需要好好思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中,如果缓存命中率非常高的话,有测试表明在极端情况下可以提高效率238%。但实际情况如何?

Query Cache有如下规则,如果数据表被更改,那么和这个数据表相关的全部Cache全部都会无效,并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。举个例子,如果数据表posts访问频繁,那么意味着它的很多数据会被QC缓存起来,但是每一次posts数据表的更新,无论更新是不是影响到了cache的数据,都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话,那么Query Cache将会成为系统的负担。有实验表明,糟糕时,QC会降低系统13%的处理能力。

如果你的应用对数据库的更新很少,那么QC将会作用显著。比较典型的如博客系统,一般博客更新相对较慢,数据表相对稳定不变,这时候QC的作用会比较明显。如果数据库一共往QC中写入了约800W次缓存,但是实际命中的只有约500W次。也就是说,每一个缓存的使用率约为0.66次。很难说,该缓存的作用是否大于QC系统所带来的开销。但是有一点是很肯定的,QC缓存的作用是很微小的,如果应用层能够实现缓存,将可以忽略QC的效果。

————-下面是关于Query Cache相关参数—————–

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

query_cache_size:设置Query Cache所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL会自动调整降低最小量以达到1024的倍数。

query_cache_type:控制Query Cache功能的开关,可以设置为0(OFF),1(ON)和2(DEMAND)三种:0表示关闭Query Cache功能,任何情况下都不会使用Query Cache;1表示开启Query Cache功能,但是当SELECT语句中使用的SQL_NO_CACHE提示后,将不使用Query Cache;2(DEMAND)表示开启Query Cache功能,但是只有当SELECT语句中使用了SQL_CACHE提示后,才使用Query Cache。

query_cache_limit:允许Cache的单条Query结果集的最大容量,默认是1MB,超过此参数设置的Query结果集将不会被Cache。

query_cache_min_res_unit:设置Query Cache中每次分配内存的最小空间大小,也就是每个Query的Cache最小占用的内存空间大小。

query_alloc_block_size:缓存的块大小,默认为8192字节。

query_cache_wlock_invalidate:控制当有写锁加在表上的时候,是否先让该表相关的Query Cache失效,1(TRUE),在写锁定的同时将使该表相关的所有Query Cache 失效。0(FALSE),在锁定时刻仍然允许读取该表相关的Query Cache。

Qcache_lowmem_prunes:这是一个状态变量(show status),当缓存空间不够需要释放旧的缓存时,该值会自增。

如何确认一个系统的 Query Cache 的运行是否健康,命中率如何,设置量是否足够?

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 128998  |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)
 
mysql> show global status like '%Com_select%';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select    | 129157 |
+---------------+--------+

Qcache_free_blocks:目前还处于空闲状态的Query Cache中内存Block数目。

Qcache_free_memory:目前还处于空闲状态的Query Cache内存总量。

Qcache_hits:Query Cache命中次数。

Qcache_inserts:向Query Cache中插入新的Query Cache的次数,也就是没有命中的次数。

Qcache_lowmem_prunes:当Query Cache内存容量不够,需要从中删除老的Query Cache以给新的Cache对象使用的次数。

Qcache_not_cached:没有被Cache的SQL数,包括无法被Cache的SQL以及由于query_cache_type设置的不会被Cache 的 SQL。

Qcache_queries_in_cache:目前在Query Cache中的SQL数量。

Qcache_total_blocks:Query Cache中总的Block数量。

可以根据这几个状态计算出Cache命中率,计算出Query Cache大小设置是否足够。

写在前面:MySQL的Query Cache大部分情况下其实只是鸡肋而已,建议全面禁用,默认关闭。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。不过,可能有的人人为只需要把query_cache_size大小调整为0就可以了,可以忽略query_cache_type参数的值,反正它也是可以在线调整的。事实果真如此吗?让我们来实际模拟测试下就知道了。

我们模拟了以下几种场景:

1、初始化时,同时设置query_cache_size和query_cache_type的值为0;

2、初始化时,设置query_cache_size = 0,但设置query_cache_type = 1;

3、初始化时,设置query_cache_size = 0,query_cache_type = 1,但是启动后立刻修改query_cache_type = 0 ;

4、初始化时,设置query_cache_size = 0,query_cache_type = 0,但是启动后立刻修改query_cache_type = 1 ;

5、初始化时,设置query_cache_size = xMB,query_cache_type = 1,但是启动后立刻修改query_cache_type = 0 ;

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

1、想要彻底关闭query cache,务必在一开始就设置query_cache_type = 0,即便是启动后将query_cache_type从1改成0,也不行;

2、即便query_cache_size = 0,但query_cache_type非0的话,在实际环境中,可能会频繁发生Waiting for query cache lock;

3、一开始就设置query_cache_type = 0的话,没有办法在运行过程中再次动态启用,反过来则可以。也就是说,一开始是启用query cache的, 在运行过程中将其关闭,但事实上仍然会发生Waiting for query cache lock,并没有真正的关闭;

详细测试过程:

一、测试方法

采用sysbench模拟并发OLTP请求:

$ sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

二、具体几种测试模式

1、一直关闭QC(query cache的简写,下同),即 query_cache_size = 0, query_cache_type = 0

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34。

2、启用QC,但QC size 设置为 0,即:query_cache_size = 0,query_cache_type = 1

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52。

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改query_cache_type = 0

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54。

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改query_cache_type = 1

这时,会提示报错信息:失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it。也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

5、启用QC,并设置QC size为256M,即query_cache_size = 256M,query_cache_type = 1

这种情况下,在测试过程中一直有Waiting for query cache lock状态出现,并且结果tps也很差,只有1395.39(几个案例中最差的一种)。

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即query_cache_size = 256M,query_cache_type = 1,启动后立刻修改query_cache_type = 0

这种情况下,在测试过程中也一直有Waiting for query cache lock状态出现,结果tps:2295.79(在这个模式下,如果设置query_cache_type = 2,效果也不佳)。

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

 

MySQL配置的工作原理

MySQL从哪儿获得配置信息:命令行参数和配置文件。类Unix系统中,配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在启动时,可以通过命令行参数指定配置文件的位置,当然命令行中也可以指定其它参数,服务器会读取配置文件的内容,删除所有注释和换行,然后和命令行选项一起处理。

任何打算长期使用的配置项都应该写入配置文件,而不是在命令行中指定。一定要清楚的知道MySQL使用的配置文件位置,在修改时不能想当然,比如,修改了/etc/my.cnf的配置项,但MySQL实际并未使用这个配置文件。如果你不知道当前使用的配置文件路径,可以尝试:

root@msc3:~# which mysqld
/usr/sbin/mysqld
root@msc3:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

一个典型的配置文件包含多个部分,每个部分的开头是一个方括号括起来的分段名称。MySQL程序通常读取跟它同名的分段部分,比如,许多客户端程序读取client部分。服务器通常读取mysqld这一段,一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的。

MySQL每一个配置项均使用小写,单词之间用下划线或者横线隔开,虽然我们常用的分隔符是下划线,但如果在命令行或者配置文件中见到如下配置,你要知道,它们其实是等价的:

# 配置文件
max_connections=5000
max-connections=5000
# 命令行
/usr/sbin/mysqld --max_connections=5000
/usr/sbin/mysqld --max-connections=5000

配置项可以有多个作用域:全局作用域、会话作用域(每个连接作用不同)、对象作用域。很多会话级配置项跟全局配置相等,可以认为是默认值,如果改变会话级配置项,它只影响改动的当前连接,当连接关闭时,所有的参数变更都会失效。下面有几个示例配置项:

  • query-cache-size 全局配置项
  • sort-buffer-size 默认全局相同,但每个线程里也可以设置
  • join-buffer-size 默认全局,且每个线程也可以设置。但若一个查询中关联多张表,可以为每个关联分配一个关联缓存(join-buffer),所以一个查询可能有多个关联缓冲。

配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改,MySQL把这些归为动态配置变量:

# 设置全局变量,GLOBAL和@@global作用是一样的
set   GLOBAL   sort-buffer-size  = <value>
set   @@global.sort-buffer-size := <value>

# 设置会话级变量,下面6种方式作用是一样的
# 即:没有修饰符、SESSION、LOCAL等修饰符作用是一致的
set  SESSION   sort-buffer-size  = <value>
set  @@session.sort-buffer-size := <value>
set          @@sort-buffer-size  = <value>
set  LOCAL     sort-buffer-size  = <value>
set     @@ocal.sort-buffer-size := <value>
set            sort-buffer-size  = <value>

# set命令可以同时设置多个变量,但其中只要有一个变量设置失败,所有的变量都未生效
SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;
SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

动态的设置变量,MySQL关闭时这些变量都会失效。如果在服务器运行时修改了变量的全局值,这个值对当前会话和其他任何已经存在的会话都不起效果,这是因为会话的变量值是在连接创建时从全局值初始化而来的。注意,在配置修改后,需要确认是否修改成功。

你可能注意到,上面的示例中,有些使用“=”,有些使用“:=”。对于set命令本身来说,两种赋值运算符没有任何区别,在命令行中使用任一运算符符,均可以生效。而在其他语句中,赋值运算符必须是“:=”,因为在非set语句中“=”被视为比较运算符。具体可以参考如下示例:
详细示例可以参考:stackoverflow

// @exp 表示用户变量,上面的示例均是系统变量
// 错误
set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;

// 正确
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

有一些配置使用了不同的单位,比如table-cache变量指定表可以被缓存的数量,而不是表可以被缓存的字节数。而key-buffer-size则是以字节为单位。

还有一些配置可以指定后缀单位,比如1M=1024*1024字节,但需要注意的是,这只能在配置文件或者作为命令行参数时有效。当使用SQL的SET命令时,必须使用数字值1048576或者1024*1024这样的表达式,但在配置文件中不能使用表达式。

小心翼翼的配置MySQL

们常常动态的修改配置,但请务必小心,因为它们可能导致数据库做大量耗时的工作,从而影响数据库的整体性能。比如从缓存中刷新脏块,不同的刷新方式对I/O的影响差别很大(后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去,就比如:

好习惯1:不要通过配置项的名称来推断一个变量的作用

不要通过配置项的名称来推断一个变量的作用,因为它可能跟你想象的完全不一样。比如:

  • read-buffer-size:当MySQL需要顺序读取数据时,如无法使用索引,其将进行全表扫描或者全索引扫描。这时,MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在缓存中,当缓存空间被写满或者全部数据读取结束后,再将缓存中的数据返回给上层调用者,以提高效率。
  • read-rnd-buffer-size:和顺序读取相对应,当MySQL进行非顺序读取(随机读取)数据块的时候,会利用这个缓冲区暂存读取的数据。比如:根据索引信息读取表数据、根据排序后的结果集与表进行Join等等。总的来说,就是当数据块的读取需要满足一定的顺序的情况下,MySQL 就需要产生随机读取,进而使用到read-rnd-buffer-size参数所设置的内存缓冲区。

这两个配置都是在扫描MyISAM表时有效,且MySQL会为每个线程分配内存。对于前者,MySQL只会在查询需要使用时才会为该缓存分配内存,并且一次性分配该参数指定大小的全部内存,而后者同样是需要时才分配内存,但只分配需要的内存大小而不是参数指定的数值,max-read-rnd-buffer-size(实际上没有这个配置项)这个名字更能表达这个变量的实际含义。

好习惯2:不要轻易在全局修改会话级别的配置

对于某些会话级别的设置,不要轻易的在全局增加它们的值,除非你确认这样做是对的。比如:sort-buffer-size,该参数控制排序操作的缓存大小,MySQL只会在查询需要做排序操作时才会为该缓冲分配内存,一旦需要排序,就会一次性分配指定大小的内存,即使是非常小的排序操作。因此在配置文件中应该配置的小一些,然后在某些查询需要排序时,再在连接中把它调大。比如:

SET @@seession.sort-buffer-size := <value>
-- 执行查询的sql
SET @@seession.sort-buffer-size := DEFAULT #恢复默认值
# 可以将类似的代码封装在函数中方便使用。

好习惯3:配置变量时,并不是值越大越好

配置变量时,并不是值越大越好,而且如果设置的值太高,可能更容易导致内存问题。在修改完成后,应该通过监控来确认变量的修改对服务器整体性能的影响。

好习惯4:规范注释,版本控制

在配置文件中写好注释,可能会节省自己和同事大量的工作,一个更好的习惯是把配置文件置于版本控制之下。

说完了好习惯,再来说说不好的习惯。

坏习惯1:根据一些“比率”来调优

一个经典的按“比率”调优的经验法则是,缓存的命中率应该高于某个百分比,如果命中率过低,则应该增加缓存的大小。这是非常错误的意见,大家可以仔细思考一下:缓存的命中率跟缓存大小有必然联系吗?(分母变大,值就变大了?)除非确实是缓存太小了。关于MyISAM键缓冲命中率,下文会详细说明。

坏习惯2:随便使用调优脚本

尽量不要使用调优脚本!不同的业务场景、不同的硬件环境对MySQL的性能要求是不一样的。比如有些业务对数据的完整性要求较高,那么就一定要保证数据不丢失,出现故障后可恢复数据,而有些业务却对数据的完整性要求没那么高,但对性能要求更高。因此,即使是同一个变量,在这两个不同场景下,其配置的值也应该是不同的。那你还能放心的使用网上找到的脚本吗 ?

本小节示例的几个配置项,仅用于举例说明,并不代表它们有多么重要,请根据实际应用场景配置它们。就比如sort-buffer-size,你真的需要100M内存来缓存10行数据?

给你一个基本的MySQL配置

前面已经说到,MySQL可配置性太强,看起来需要花很多时间在配置上,但其实大多数配置的默认值已经是最佳的,最好不要轻易改动太多的配置,你甚至不需要知道某些配置的存在。这里有一个最小的示例配置文件,可以作为服务器配置文件的一个起点,其中有一些配置项是必须的。本节将为你详细剖析每个配置有何作用?为什么要配置它?怎么确定合适的值?

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
port                           = 3306
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G

 

本文:MySQL为什么要关闭Query Cache?

发表评论