AgentSkillsCN

altinity-expert-clickhouse-caches

深入分析 ClickHouse 的各类缓存系统，包括标记缓存、未压缩缓存以及查询缓存。适用于解决缓存命中率问题，并进行缓存调优。

SKILL.md
--- frontmatter
name: altinity-expert-clickhouse-caches
description: Analyze ClickHouse cache systems including mark cache, uncompressed cache, and query cache. Use for cache hit ratio issues and cache tuning.

Cache Analysis and Tuning

Analyze ClickHouse cache systems: mark cache, uncompressed cache, query cache, and compiled expression cache.

Diagnostics

Run all queries from the file checks.sql and analyze the results.

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

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

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

Cross-Module Triggers

FindingLoad ModuleReason
Cache using too much RAMaltinity-expert-clickhouse-memoryOverall memory analysis
Poor hit ratio + high disk IOaltinity-expert-clickhouse-storageDisk bottleneck
Many marks per tablealtinity-expert-clickhouse-schemaConsider index_granularity tuning
Query cache missesaltinity-expert-clickhouse-reportingQuery pattern analysis

Settings Reference

SettingScopeNotes
mark_cache_sizeServerGlobal mark cache limit
uncompressed_cache_sizeServerSet to 0 to disable
use_uncompressed_cacheQueryEnable per-query
query_cache_max_sizeServerQuery result cache
use_query_cacheQueryEnable per-query