Pre-Caching ClickHouse Metadata to the Memory

Scenario

If there are a large number of service tables holding a large amount of data, loading metadata during rolling restart is time-consuming. You can use RocksDB to pre-cache the metadata to the memory to accelerate metadata loading.

Enabling Metadata Pre-Caching

You can set use_metadata_cache to 1 or true to cache metadata to the memory through RocksDB.

1. Use the ClickHouse client to connect to the ClickHouse server by referring to Using ClickHouse from Scratch.
2. Configure metadata pre-caching.
   • Enable metadata pre-caching for historical tables.

     ALTER TABLE <table name> MODIFY SETTING use_metadata_cache=1;

     Or

     ALTER TABLE <table name> MODIFY SETTING use_metadata_cache=true;

   • Enable metadata pre-caching when you create a table.

     CREATE TABLE <table name>

     (

     `x` UInt32,

     `y` UInt32,

     `z` UInt32,

     `t` UInt32

     )

     ENGINE = MergeTree

     PARTITION BY x % 10

     ORDER BY (x, y)

     SETTINGS index_granularity = 8192, use_metadata_cache = 1

     or

     CREATE TABLE <table name>

     (

     `x` UInt32,

     `y` UInt32,

     `z` UInt32,

     `t` UInt32

     )

     ENGINE = MergeTree

     PARTITION BY x % 10

     ORDER BY (x, y)

     SETTINGS index_granularity = 8192, use_metadata_cache = true

Parameter Tuning

Metadata pre-caching can be optimized as follows:

Log in to FusionInsight Manager, choose Cluster > Services > ClickHouse, click Configurations and then All Configurations, and modify the following parameters: