ClickHouse DB::Exception: Cache dictionary update failed

The "DB::Exception: Cache dictionary update failed" error in ClickHouse indicates that a dictionary using the CACHE or SSD_CACHE layout was unable to fetch data from its configured source during a lookup or background refresh. Unlike HASHED or FLAT dictionaries that load all data upfront, cache dictionaries fetch data on demand and cache it -- so this error surfaces during query execution rather than at load time. The error code is CACHE_DICTIONARY_UPDATE_FAIL.

Impact

Queries that request keys not currently in the cache will fail, since the dictionary cannot reach its source to look them up. Keys already cached may still be served if allow_read_expired_keys is enabled. In high-throughput environments, this can cause cascading failures if many queries depend on dictionary lookups.

Common Causes

1. Source database or service is down -- the MySQL, PostgreSQL, ClickHouse, or HTTP source used by the dictionary is unreachable.
2. Network connectivity issues -- firewall changes, DNS resolution failures, or network partitions between the ClickHouse server and the dictionary source.
3. Source query timeout -- the query used to fetch data from the source takes too long and times out.
4. Authentication or permission changes -- credentials configured in the dictionary source are no longer valid.
5. Source table was dropped or renamed -- the table or view referenced in the dictionary source no longer exists.
6. Too many concurrent cache update requests -- under heavy load, the dictionary may exceed its maximum allowed concurrent source queries.

Troubleshooting and Resolution Steps

1. Check the dictionary status and last exception:

   SELECT name, status, last_exception, element_count, hit_rate
   FROM system.dictionaries
   WHERE name = 'my_cache_dict';
   

2. Test source connectivity directly from ClickHouse:

   -- For a MySQL source
   SELECT count() FROM mysql('host:port', 'db', 'table', 'user', 'password');
   
   -- For a ClickHouse source
   SELECT count() FROM remote('source-host', 'db', 'table');
   

3. Review the dictionary source configuration:

   SHOW CREATE DICTIONARY my_cache_dict;
   

   Verify hostname, port, credentials, database, and table are all correct and current.

4. Increase source query timeout if the source is slow:

   <!-- In dictionary XML config -->
   <source>
       <mysql>
           <host>mysql-host</host>
           <port>3306</port>
           <connect_timeout>10</connect_timeout>
           <read_write_timeout>10</read_write_timeout>
       </mysql>
   </source>
   

5. Enable expired key reads to prevent query failures during outages:

   -- In dictionary definition
   LAYOUT(CACHE(SIZE_IN_CELLS 1000000 ALLOW_READ_EXPIRED_KEYS 1))
   

   This allows queries to return stale cached values instead of failing when the source is unavailable.

6. Check server logs for detailed error context:

   grep -i 'cache_dictionary\|CACHE_DICTIONARY' /var/log/clickhouse-server/clickhouse-server.log | tail -30
   

Best Practices

• Enable allow_read_expired_keys for cache dictionaries in production to provide graceful degradation during source outages.
• Monitor dictionary hit rates via system.dictionaries. A very low hit rate means many lookups go to the source, increasing the risk of this error.
• Size the cache appropriately -- a cache that is too small will evict entries frequently, increasing source load and failure risk.
• Set reasonable timeouts for source connections to avoid queries hanging indefinitely.
• Consider switching to a HASHED layout if the full dataset fits in memory, since it eliminates runtime source dependency.
• Use SSD_CACHE for large datasets that do not fit in RAM but still need cache-style on-demand loading.

Frequently Asked Questions

Q: What is the difference between CACHE and HASHED dictionary layouts?
A: HASHED loads all data from the source into memory at startup and refreshes periodically. It has no runtime source dependency. CACHE loads data on demand as keys are requested and caches them, which means it depends on the source being available during query execution.

Q: Will allow_read_expired_keys always prevent this error?
A: Only for keys that are already in the cache (even if expired). If a completely new key is requested and the source is unavailable, the query will still fail because there is no cached value to return.

Q: How do I know if my cache dictionary is properly sized?
A: Check hit_rate in system.dictionaries. A hit rate below 90% suggests the cache is too small for the working set. Also compare element_count against SIZE_IN_CELLS -- if they are close, the cache is full and evicting entries.

Q: Can I manually populate a cache dictionary?
A: No, cache dictionaries are populated on demand through queries. You cannot pre-load them. If you need all data available immediately, use HASHED or FLAT layouts instead.