Back to Details

altinity-expert-clickhouse-caches

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Cache Analysis and Tuning

缓存分析与调优

Analyze ClickHouse cache systems: mark cache, uncompressed cache, query cache, and compiled expression cache.
分析ClickHouse缓存系统：标记缓存、未压缩缓存、查询缓存和编译表达式缓存。

Diagnostics

诊断

Run all queries from the file checks.sql and analyze the results.
运行checks.sql文件中的所有查询并分析结果。

Cache Sizing Recommendations

缓存大小建议

CacheTypical SizeNotes
Mark Cache5-10% of RAMHigher if random access patterns
Uncompressed0 (disabled) or 5-10%Enable only for specific workloads
Query Cache1-5GBFor repeated identical queries
Compiled Expression128MB-1GBHigher for complex expressions
缓存类型典型大小说明
标记缓存内存的5-10%如果是随机访问模式可设置更高
未压缩缓存0（禁用）或内存的5-10%仅针对特定工作负载启用
查询缓存1-5GB适用于重复的完全相同的查询
编译表达式缓存128MB-1GB复杂表达式场景可设置更高

Problem Investigation

问题排查

Poor Mark Cache Hit Ratio

标记缓存命中率低

Possible causes:
  1. Cache too small for working set
  2. Queries scan many different tables
  3. Many small queries to cold data
可能原因：
  1. 缓存对于工作数据集来说太小
  2. 查询扫描多个不同的表
  3. 大量针对冷数据的小查询

Cache Too Large

缓存过大

If mark cache > 15% RAM:
Solutions:
  • Reduce 
    index_granularity
    for tables with excessive marks
  • Drop unused tables
  • Reduce 
    mark_cache_size
    setting
如果标记缓存占用内存超过15%：
解决方案：
  • 针对标记过多的表减小
    index_granularity
  • 删除未使用的表
  • 减小
    mark_cache_size
    设置

Cross-Module Triggers

跨模块触发条件

FindingLoad ModuleReason
Cache using too much RAM
altinity-expert-clickhouse-memory
Overall memory analysis
Poor hit ratio + high disk IO
altinity-expert-clickhouse-storage
Disk bottleneck
Many marks per table
altinity-expert-clickhouse-schema
Consider index_granularity tuning
Query cache misses
altinity-expert-clickhouse-reporting
Query pattern analysis
发现的问题加载模块原因
缓存占用过多内存
altinity-expert-clickhouse-memory
整体内存分析
命中率低 + 磁盘IO高
altinity-expert-clickhouse-storage
磁盘瓶颈
每张表标记过多
altinity-expert-clickhouse-schema
考虑调优index_granularity
查询缓存未命中
altinity-expert-clickhouse-reporting
查询模式分析

Settings Reference

设置参考

SettingScopeNotes
mark_cache_size
ServerGlobal mark cache limit
uncompressed_cache_size
ServerSet to 0 to disable
use_uncompressed_cache
QueryEnable per-query
query_cache_max_size
ServerQuery result cache
use_query_cache
QueryEnable per-query
设置项作用范围说明
mark_cache_size
服务器全局标记缓存限制
uncompressed_cache_size
服务器设置为0以禁用
use_uncompressed_cache
查询按查询启用
query_cache_max_size
服务器查询结果缓存
use_query_cache
查询按查询启用