LNMP下针对 WordPress 的MySQL优化小技巧

今天无意中看到了一个LNMP下针对 WordPress 的MySQL优化小技巧,感觉很靠谱,于是就分享出来了,我已经启用了!其实很简单那就是启用MySQL Query Cache,说白了就是MySQL的查询缓存。

mysql-backup.png

MySQL 设置 MySQL Query Cache,可以保存保存查询结果,同样的查询不再从数据库检索,对速度有很大的提升。

登陆服务器:

Mysql -uroot -p

SET GLOBAL query_cache_size = 16777216;

SHOW VARIABLES LIKE 'query_cache_size';

输出:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

vim /etc/my.cnf

添加如下代码,然后重启mysql

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

经过以上设置之后,使用querycachelimit变量,每个查询结果的最大值可以为1048576,单位是KB。

当然也有人认为MySQL Query Cache开启后的意义不大,甚至还会影响一定的性能,这个只能说是仁者见仁智者见智了,具体原因和分析如下:

2016121203340532.png

在这个”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的效果。

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参数的值,反正它也是可以在线调整的。

本文摘录自【MySQL为什么要关闭Query Cache?

最后修改:2017 年 08 月 05 日 03 : 03 PM
如果觉得我的文章对你有用,请随意赞赏

11 条评论

  1. boke112导航

    没想到博主已经研究到数据库缓存了,最近好像总是在折腾缓存

    1. 明月登楼
      @boke112导航

      呵呵,根据需求瞎折腾而已!

    2. 明月登楼
      @boke112导航

      其实吧,这个缓存的开启是要根据自身实际情况决定的!比如,网站查询“读”的次数多的时候比较适合,“写”的次数多的话就成了累赘了!

  2. 狂放

    dalao,你的博客我这打不开啊2333

    1. 明月登楼
      @狂放

      哦,估计又是又拍云节点的问题吧!我屏蔽了非中国IP地址,如果是用代理上网的话会有可能访问不了的!

  3. 橘子书

    感觉以后要折腾的话,可以过来取经!

    1. 明月登楼
      @橘子书

      多交流,相互学习了!

      1. 橘子书
        @明月登楼

        发现你这个站点,比明月登楼优化的好。

        1. 明月登楼
          @橘子书

          不是优化的好,主要这个站点 Typecho ,本身就短小精悍,出了名的“快”,其次是明月登楼的博客目前用的是虚拟主机,性能上受限了,并且使用的360的安全卫士,主要是安全性,浏览性能上就差了!等有空了再折腾吧!

  4. 在线看小说

    好深奥的样子

    1. 明月登楼
      @在线看小说

      呵呵,多谢支持,欢迎常来!

发表评论