Materialized View의 Block Size 제어

❌ 비효율적인 시나리오:
100만 건 이벤트 → 1건씩 INSERT → 100만 번의 네트워크 요청
각 요청당 평균 5ms 지연 → 총 5,000초 (약 83분!)

✅ 효율적인 시나리오:
100만 건 이벤트 → 10,000건씩 batch INSERT → 100번의 네트워크 요청
각 요청당 평균 50ms 지연 → 총 5초

-- 이런 패턴이 수천~수만 번 반복되면?
START TRANSACTION;
INSERT INTO table VALUES (1, 'data');
COMMIT;

START TRANSACTION;
INSERT INTO table VALUES (2, 'data');
COMMIT;

Block Size가 작을 때 (1,000):
읽기 요청 1,000번 → 각각 오버헤드 발생 → 느림

Block Size가 적절할 때 (65,536):
읽기 요청 15번 → 오버헤드 최소화 → 빠름

SELECT * FROM large_table
SETTINGS max_block_size = 10000;

-- 일반 SELECT 쿼리
SELECT * FROM large_table
SETTINGS max_block_size = 10000;

-- 그렇다면 Materialized View에서는?
CREATE MATERIALIZED VIEW my_mv
TO target_table
AS
SELECT ... FROM source_table
SETTINGS
    max_block_size = ???,
    min_insert_block_size_rows = ???;  -- 이게 가능한가?

서비스:
  - Kafka 7.5.0 (메시지 브로커)
  - ClickHouse 25.11.2 (데이터 처리)
  - MySQL 8.0 (타겟 DB)

-- TO와 ENGINE을 동시에 사용하면 에러!
CREATE MATERIALIZED VIEW my_mv
TO target_table
ENGINE = MergeTree()  -- ❌ Error: Can't declare both TO and ENGINE
SETTINGS ...

DB::Exception: Syntax error: Both 'TO' and 'ENGINE' cannot be specified

SELECT
    name,
    create_table_query
FROM system.tables
WHERE engine = 'MaterializedView'
  AND name = 'buffer_to_mysql_mv'
FORMAT Vertical;

SELECT
    event_time,
    query_kind,
    Settings['max_block_size'] AS max_block_size,
    Settings['min_insert_block_size_rows'] AS min_insert_block_size_rows
FROM system.query_log
WHERE query LIKE '%CREATE MATERIALIZED VIEW%buffer_to_mysql_mv%'
  AND type = 'QueryFinish'
FORMAT Vertical;

event_time:                    2025-12-13 10:30:45
query_kind:                    Create
max_block_size:                1000          ✅
min_insert_block_size_rows:    5000          ✅

# Kafka에 50,000개 이벤트 전송
python3 kafka_producer.py --num-events 50000

# 30초 대기 후 확인

SELECT count() FROM events_buffer;
-- 결과: 50,000 rows

SELECT * FROM mysql_aggregated_events;

-- ❌ 이렇게 하면 에러!
CREATE TABLE kafka_events (
    event_time DateTime DEFAULT now(),  -- DEFAULT 불가!
    query_kind String,
    query_duration_ms UInt32
) ENGINE = Kafka(...);

DB::Exception: KafkaEngine doesn't support DEFAULT/MATERIALIZED/EPHEMERAL
expressions for columns.

-- ✅ DEFAULT 제거
CREATE TABLE kafka_events (
    event_time DateTime,  -- DEFAULT 없이
    query_kind String,
    query_duration_ms UInt32
) ENGINE = Kafka(...);

CREATE MATERIALIZED VIEW s3_export_mv
TO s3_table
AS
SELECT * FROM source_table
SETTINGS
    max_block_size = 1000000,  -- 큰 블록으로 파일 크기 증가
    min_insert_block_size_rows = 1000000;

SETTINGS
    max_block_size = 10000,               -- 작은 블록으로 빠른 처리
    min_insert_block_size_rows = 5000,    -- 작은 batch
    min_insert_block_size_bytes = 1048576,  -- 1MB
    max_insert_threads = 4;               -- 병렬 처리

SETTINGS
    max_block_size = 100000,              -- 큰 블록으로 I/O 최소화
    min_insert_block_size_rows = 500000,  -- 큰 batch
    min_insert_block_size_bytes = 268435456,  -- 256MB
    max_insert_threads = 1;               -- 순차 처리로 부하 분산

SETTINGS
    max_block_size = 1000000,             -- 매우 큰 블록
    min_insert_block_size_rows = 10000000,  -- 1천만 행씩
    min_insert_block_size_bytes = 1073741824;  -- 1GB

-- 설정: min_insert_block_size_rows = 100000
-- 소스 데이터: 1,000,000 rows

SELECT date, country, count(*)
FROM events
GROUP BY date, country  -- 결과가 50개만 나옴

-- MySQL 테이블
CREATE TABLE aggregated_events (
    event_date DATE,
    query_kind VARCHAR(50),
    query_count BIGINT,
    PRIMARY KEY (event_date, query_kind)
);

-- ClickHouse에서 직접 지원하지 않으므로
-- MySQL 테이블을 다음과 같이 수정
CREATE TABLE aggregated_events (
    ...
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                 ON UPDATE CURRENT_TIMESTAMP
);

-- MySQL Table Engine 대신 ClickHouse 테이블 사용
CREATE TABLE aggregated_events (
    event_date Date,
    query_kind String,
    query_count UInt64,
    total_duration_ms UInt64
) ENGINE = ReplacingMergeTree()
ORDER BY (event_date, query_kind);

-- 주기적으로 MySQL에 UPSERT

-- ❌ 위험!
SETTINGS min_insert_block_size_bytes = 10737418240;  -- 10GB

SELECT
    name,
    database,
    engine,
    substring(create_table_query,
              position(create_table_query, 'SETTINGS'),
              200) AS settings_part
FROM system.tables
WHERE engine = 'MaterializedView'
  AND name LIKE '%_mv'
FORMAT Vertical;

SELECT
    view,
    status,
    last_refresh_time,
    last_refresh_result,
    exception
FROM system.view_refreshes
WHERE database = 'default'
ORDER BY last_refresh_time DESC
LIMIT 10;

SELECT
    event_time,
    query_duration_ms,
    read_rows,
    written_rows,
    Settings['max_block_size'] AS max_block_size,
    Settings['min_insert_block_size_rows'] AS min_insert_block_size_rows
FROM system.query_log
WHERE query LIKE '%INSERT INTO%mysql_aggregated_events%'
  AND type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

SELECT
    host,
    port,
    database,
    user,
    default_kind,
    pool_size
FROM system.mysql_connections
FORMAT Vertical;

CREATE MATERIALIZED VIEW my_mv
TO target_table
AS
SELECT ... FROM source_table
GROUP BY ...
SETTINGS
    max_block_size = 65536,              -- 기본값 유지
    min_insert_block_size_rows = 100000,  -- 10만 행
    min_insert_block_size_bytes = 10485760;  -- 10MB