altinity-expert-clickhouse-dictionaries

name: altinity-expert-clickhouse-dictionaries description: Analyze ClickHouse external dictionaries including configuration, memory usage, reload status, and performance. Use for dictionary issues and load failures.

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

2. 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

3. 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

Common Issues

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

Settings Reference