altinity-expert-clickhouse-dictionaries

仓库: altinity/skills
安装量: 44
排名: #16689

安装

npx skills add https://github.com/altinity/skills --skill altinity-expert-clickhouse-dictionaries

Dictionary Diagnostics

Analyze external dictionaries: configuration, memory usage, reload status, and performance.

Quick Diagnostics 1. Dictionary Overview select database, name, status, origin, type, formatReadableSize(bytes_allocated) as memory, element_count as elements, loading_duration, last_successful_update_time, last_exception from system.dictionaries order by bytes_allocated desc

  1. Dictionary Health Check select database, name, status, multiIf( status = 'FAILED', 'Critical', status = 'LOADING', 'Moderate', last_exception != '', 'Major', dateDiff('hour', last_successful_update_time, now()) > 24, 'Moderate', 'OK' ) as severity, last_exception, last_successful_update_time from system.dictionaries order by multiIf(severity = 'Critical', 1, severity = 'Major', 2, severity = 'Moderate', 3, 4), bytes_allocated desc

  2. Memory Usage Audit with sum(bytes_allocated) as dict_memory, (select value from system.asynchronous_metrics where metric = 'OSMemoryTotal') as total_ram select formatReadableSize(dict_memory) as total_dictionary_memory, formatReadableSize(total_ram) as total_ram, round(100.0 * dict_memory / total_ram, 2) as pct_of_ram, multiIf(pct_of_ram > 20, 'Critical', pct_of_ram > 15, 'Major', pct_of_ram > 10, 'Moderate', 'OK') as severity from system.dictionaries

Dictionary Details Top Dictionaries by Memory select database, name, type, formatReadableSize(bytes_allocated) as memory, element_count as elements, round(bytes_allocated / nullIf(element_count, 0), 2) as bytes_per_element, loading_duration, lifetime_min, lifetime_max from system.dictionaries order by bytes_allocated desc limit 20

Dictionary Configuration select database, name, key, attribute.names as attributes, attribute.types as types, source, lifetime_min, lifetime_max, loading_start_time, loading_duration from system.dictionaries order by name

Dictionary Staleness Check select database, name, last_successful_update_time, dateDiff('minute', last_successful_update_time, now()) as minutes_since_update, lifetime_max, multiIf( minutes_since_update > lifetime_max * 2, 'Critical - very stale', minutes_since_update > lifetime_max, 'Major - past lifetime', minutes_since_update > lifetime_max * 0.9, 'Moderate - approaching lifetime', 'OK' ) as freshness from system.dictionaries where lifetime_max > 0 order by minutes_since_update desc

Failed Dictionaries Current Failures select database, name, status, last_exception, loading_start_time, last_successful_update_time from system.dictionaries where status = 'FAILED' or last_exception != ''

Dictionary Load Errors in Logs select event_time, level, logger_name, substring(message, 1, 300) as message from system.text_log where logger_name like '%Dictionary%' and level in ('Error', 'Warning') and event_time > now() - interval 1 hour order by event_time desc limit 30

Dictionary Performance Lookup Performance (via query_log) select normalized_query_hash, count() as executions, round(avg(query_duration_ms)) as avg_ms, any(substring(query, 1, 100)) as query_sample from system.query_log where type = 'QueryFinish' and query ilike '%dictGet%' and event_date = today() group by normalized_query_hash order by count() desc limit 20

Dictionary Hit/Miss Ratio select 'DictCacheHits' as metric, (select value from system.events where event = 'DictCacheHits') as value union all select 'DictCacheMisses' as metric, (select value from system.events where event = 'DictCacheMisses') as value settings system_events_show_zero_values = 1

Dictionary Types Cache Dictionary Analysis

For cache dictionaries, check hit rate and size:

select database, name, type, element_count, formatReadableSize(bytes_allocated) as memory, loading_duration from system.dictionaries where type like '%cache%'

Flat/Hashed Dictionary Size Check select database, name, type, element_count, formatReadableSize(bytes_allocated) as memory, round(bytes_allocated / nullIf(element_count, 0)) as bytes_per_key, if(bytes_per_key > 1000, 'High memory per key', 'OK') as note from system.dictionaries where type in ('Flat', 'Hashed', 'ComplexKeyHashed') order by bytes_allocated desc

Dictionary Sources Identify Source Types select source, count() as dictionaries, formatReadableSize(sum(bytes_allocated)) as total_memory from system.dictionaries group by source order by sum(bytes_allocated) desc

Check Source Connectivity

For ClickHouse source dictionaries:

-- Verify source table exists select d.name as dictionary_name, d.source, t.database as source_database, t.name as source_table from system.dictionaries d left join system.tables t on d.source like concat('%', t.database, '.', t.name, '%') where d.source like '%clickhouse%'

Dictionary Reload Operations Force Reload (diagnostic query - shows syntax) -- SYSTEM RELOAD DICTIONARY {database}.{name} -- SYSTEM RELOAD DICTIONARIES

-- Check reload result select name, status, loading_start_time, loading_duration, last_exception from system.dictionaries where name = '{dictionary_name}'

Scheduled Reload Check select database, name, lifetime_min, lifetime_max, last_successful_update_time, dateDiff('second', last_successful_update_time, now()) as seconds_since_update, if(seconds_since_update > lifetime_max, 'Should have reloaded', 'OK') as reload_status from system.dictionaries where lifetime_max > 0 order by seconds_since_update desc

Best Practices Dictionary Sizing Guidelines Elements Recommended Type < 100K Flat (if sequential keys) 100K - 10M Hashed

10M Consider partitioning or cache Complex keys ComplexKeyHashed Sparse access Cache with SSD Common Issues Symptom Cause Solution High memory Too many elements Use cache type, filter data Slow reload Large source table Add filters, use delta updates Stale data Source unreachable Check connectivity, add retry Failed status Source query fails Check source table/query Ad-Hoc Query Guidelines Required Safeguards -- For text_log where event_time > now() - interval 1 hour limit 100

Key Tables system.dictionaries - Dictionary state system.text_log (logger_name like '%Dictionary%') - Load logs system.events (DictCache*) - Cache statistics Cross-Module Triggers Finding Load Module Reason High memory usage altinity-expert-clickhouse-memory Overall memory analysis Load failures altinity-expert-clickhouse-overview Error summary + routing Source connectivity altinity-expert-clickhouse-logs Log investigation Slow lookups altinity-expert-clickhouse-reporting Query optimization Settings Reference Setting Notes dictionaries_lazy_load Load on first access vs startup dictionary_load_wait_timeout_ms Wait time for lazy load max_dictionary_num_to_warn Warning threshold

← 返回排行榜