MySQL查询缓存配置不合理致服务器高内存占用求助

Hey there, let's work through your server memory and database query cache issue together!

一、问题核心分析 #

Your 120GB server only having 16GB free memory, paired with a query cache free ratio of just 11.9% (vs. the ideal ~80%), tells us two key things:

• The current query cache size is way too small for your workload—this leads to constant cache evictions (old results getting kicked out to make space for new ones), which wastes CPU/memory on cache maintenance instead of speeding up queries.
• It’s also worth checking if other database memory components are hogging resources, since query cache alone might not be the only culprit.

二、Step-by-Step Adjustment Guide #

1. First, map out your current query cache setup #

Log into your database and run these commands to get a clear picture of your current config:

SHOW VARIABLES LIKE '%query_cache%';

Focus on these critical parameters:

• query_cache_size: Total size of your current query cache
• query_cache_type: Whether the cache is enabled (should be ON or DEMAND for most use cases)
• query_cache_min_res_unit: The smallest block size the cache uses—too large can cause memory fragmentation

2. Resize the query cache incrementally #

Since your cache is nearly full, we need to give it more breathing room, but don’t overdo it (we don’t want to starve other database components):

• Start by doubling or tripling your current query_cache_size (e.g., if it’s 1GB, bump it to 2.5GB first)
• After adjusting, wait 1-2 hours then check the free ratio again with:

SHOW STATUS LIKE '%Qcache%';

Aim to keep Qcache_free_memory / Qcache_total_memory between 70%-85%—this balances cache utilization and avoids unnecessary evictions.

3. Fix memory fragmentation (if needed) #

If you see a high Qcache_free_blocks value from the above status check, it means your cache has lots of unused small blocks. Tune the minimum unit size:

SET GLOBAL query_cache_min_res_unit = 2048;

Adjust the value (1024-4096 bytes is a safe range) based on the average size of your query results.

4. Check other database memory hogs #

Query cache isn’t the only memory eater. For InnoDB (the most common engine), check the buffer pool size:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

For a 120GB server, the InnoDB buffer pool should usually take 50%-70% of total memory (60-84GB), but make sure to leave at least 10-20GB free for the OS and other processes.

三、Monitor & Validate After Changes #

• Keep an eye on server memory with free -h or top to ensure you don’t over-allocate to the database.
• Check query cache stats regularly to confirm the free ratio stays in the target range, and track cache hit rate (Qcache_hits / (Qcache_hits + Qcache_inserts)—aim for 90%+).
• Watch query response times to see if performance improves as the cache works more efficiently.

四、Critical Note #

If you’re using MySQL 8.0 or newer, the query cache has been completely removed. In that case, shift your focus to optimizing the InnoDB buffer pool and adding application-level caching (like Redis) instead.

内容的提问来源于stack exchange，提问作者R3ason