クリックハウス

<max_server_memory_usage>80000000000</max_server_memory_usage>  <!-- 80GB -->
<max_memory_usage>40000000000</max_memory_usage>  <!-- 40GB per query -->
<max_bytes_before_external_group_by>20000000000</max_bytes_before_external_group_by>
<max_threads>8</max_threads>  <!-- Parallel query threads -->

<storage_configuration>
    <disks>
        <default>
            <path>/var/lib/clickhouse/</path>
        </default>
        <fast_ssd>
            <path>/mnt/fast_ssd/clickhouse/</path>
        </fast_ssd>
        <slow_hdd>
            <path>/mnt/slow_hdd/clickhouse/</path>
        </slow_hdd>
    </disks>
    <policies>
        <hot_and_cold>
            <volumes>
                <hot>
                    <disk>fast_ssd</disk>
                </hot>
                <cold>
                    <disk>slow_hdd</disk>
                </cold>
            </volumes>
            <move_factor>0.2</move_factor>
        </hot_and_cold>
    </policies>
</storage_configuration>

-- MergeTree (basic)
CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_type String,
    url String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS index_granularity = 8192;

-- ReplicatedMergeTree (with replication)
CREATE TABLE events_replicated (
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_type String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- Distributed table (cluster)
CREATE TABLE events_distributed AS events_replicated
ENGINE = Distributed(cluster_name, database_name, events_replicated, rand());

CREATE TABLE metrics (
    timestamp DateTime CODEC(Delta, ZSTD),
    metric_name LowCardinality(String),
    value Float64 CODEC(Gorilla, ZSTD),
    tags Array(String),
    metadata Map(String, String)
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp);

-- Use PREWHERE for filtering (faster than WHERE)
SELECT * FROM events
PREWHERE event_date >= today() - 7
WHERE user_id IN (SELECT id FROM users WHERE premium = 1);

-- Materialized views for pre-aggregation
CREATE MATERIALIZED VIEW events_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_hour, event_type)
AS SELECT
    toDate(event_time) AS event_date,
    toHour(event_time) AS event_hour,
    event_type,
    count() AS event_count
FROM events
GROUP BY event_date, event_hour, event_type;

-- Automatic data expiration
ALTER TABLE events
MODIFY TTL event_date + INTERVAL 90 DAY;

-- Move old data to cold storage
ALTER TABLE events
MODIFY TTL 
    event_date + INTERVAL 7 DAY TO VOLUME 'hot',
    event_date + INTERVAL 30 DAY TO VOLUME 'cold',
    event_date + INTERVAL 90 DAY DELETE;

-- System metrics
SELECT * FROM system.metrics;

-- Current queries
SELECT query_id, user, query, elapsed FROM system.processes;

-- Query log analysis
SELECT 
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Table sizes
SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) AS size,
    sum(rows) AS rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;