配置clickhouse冷热数据分离

在 config.d 中加入如下配置

• 配置存储目录

<disks>
    <fast_disk> <!-- disk name -->
        <path>/mnt/fast_ssd/clickhouse</path>  #ssd 数据目录
    </fast_disk>
    <disk1>
        <path>/mnt/hdd1/clickhouse</path> # 普通盘数据目录
        <keep_free_space_bytes>10485760</keep_free_space_bytes> # 磁盘预留空间
    </disk1>
    <disk2>
        <path>/mnt/hdd2/clickhouse</path> # 普通盘数据目录
        <keep_free_space_bytes>10485760</keep_free_space_bytes> # 磁盘预留空间
    </disk2>
</disks>
 

• 配置存储策略

<policies>
       <ssd_to_hdd>
        <volumes>
            <hot>
                <disk>fast_disk</disk>
                <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>   # 数据块大小
            </hot>
            <cold>
                <disk>disk1</disk>
                <disk>disk2</disk>
            </cold>
            <move_factor>0.2</move_factor>  # 当SSD数据盘低于20%存储时会将历史数据存储到hdd
        </volumes>
    </ssd_to_hdd>
</policies>
 

• 在创建表时引用数据存储策略

CREATE TABLE table_with_non_default_policy (
    EventDate Date,
    OrderID UInt64,
    BannerID UInt64,
    SearchPhrase String
) ENGINE = MergeTree
ORDER BY (OrderID, BannerID)
PARTITION BY toYYYYMM(EventDate)
SETTINGS storage_policy = 'ssd_to_hdd'