为什么Mysql的Qcache_free_memory还很大,但是Qcache_lowmem_prunes却不为0且一直在增加? 最近接手管理几台Mysql服务器,原先query_cache_size为4G,被我改成了512M,后来就出现了如题描述的问题 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 从来没见过开这么大的query_cache,这个东西最好别开贴你的语句显示 (root:MYSQLNUM05:)[(none)]> show status like 'Qcache_%';+-------------------------+-----------+| Variable_name | Value |+-------------------------+-----------+| Qcache_free_blocks | 67495 | | Qcache_free_memory | 346697536 | | Qcache_hits | 27205076 | | Qcache_inserts | 8413944 | | Qcache_lowmem_prunes | 723463 | | Qcache_not_cached | 2838399 | | Qcache_queries_in_cache | 109282 | | Qcache_total_blocks | 288054 | +-------------------------+-----------+8 rows in set (0.00 sec)(root:MYSQLNUM05:)[(none)]> show variables like 'query_cache_%';+------------------------------+-----------+| Variable_name | Value |+------------------------------+-----------+| query_cache_limit | 134217728 | | query_cache_min_res_unit | 2048 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+5 rows in set (0.00 sec) 是不是有取很大的结果集 直接占用了很大的缓存,然后就缓存占光但是缓存的数据涉及的表又频繁变更,导致这个占用大空间的sql的结果集cache频繁过期所有就会看到虽然留有很大空闲空间,但是被换出结果集很多 query_cache_size设置太大导致缓存把内存吃光了。 mysql> select 134217728/1024/1024;+---------------------+| 134217728/1024/1024 |+---------------------+| 128.00000000 |+---------------------+1 row in set (0.00 sec)超过128M的结果集的SQL,就不会被CACHE到。可用内存330M也没用 +----------------------+| 346697536 /1024/1024 |+----------------------+| 330.63653564 |+----------------------+1 row in set (0.02 sec) mysql> select 134217728/1024/1024;+---------------------+| 134217728/1024/1024 |+---------------------+| 128.00000000 |+---------------------+1 row in set (0.00 sec)超过128M的结果集的SQL,就不会被CACHE到。可用内存330M也没用 +----------------------+| 346697536 /1024/1024 |+----------------------+| 330.63653564 |+----------------------+1 row in set (0.02 sec) 我觉得你说的这种情况仅限于可能性,我还是怀疑是由于我修改query_cache_size后没有重启mysql导致的,但不能确定。你的回复还是最给力的,谢谢! 请问一个编码的问题 关于count聚合函数用法的困惑 linux 如何编译 mysql 的C程序 ,求高手解答 Mysql 大数据量查询如何优化(当前数据仅有80万已很慢) datetimepicker显示问题 求条件查询排序优化方法 为什么我的SQL用不了DECLARE,一用就报错 mysql查询数据记录40万,就出现下面的错误 这个存储过程怎么调用 查询并返回表的全部字段,并更新某一字段 Mysql查询缓存设置的可能会出现什么问题? MySql提示 could not fetch schema table status错误
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 67495 |
| Qcache_free_memory | 346697536 |
| Qcache_hits | 27205076 |
| Qcache_inserts | 8413944 |
| Qcache_lowmem_prunes | 723463 |
| Qcache_not_cached | 2838399 |
| Qcache_queries_in_cache | 109282 |
| Qcache_total_blocks | 288054 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
(root:MYSQLNUM05:)[(none)]> show variables like 'query_cache_%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 134217728 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
5 rows in set (0.00 sec)
+---------------------+
| 134217728/1024/1024 |
+---------------------+
| 128.00000000 |
+---------------------+
1 row in set (0.00 sec)超过128M的结果集的SQL,就不会被CACHE到。可用内存330M也没用
+----------------------+
| 346697536 /1024/1024 |
+----------------------+
| 330.63653564 |
+----------------------+
1 row in set (0.02 sec)
+---------------------+
| 134217728/1024/1024 |
+---------------------+
| 128.00000000 |
+---------------------+
1 row in set (0.00 sec)超过128M的结果集的SQL,就不会被CACHE到。可用内存330M也没用
+----------------------+
| 346697536 /1024/1024 |
+----------------------+
| 330.63653564 |
+----------------------+
1 row in set (0.02 sec)
你的回复还是最给力的,谢谢!