[이제와서 시작하는 Metabase 마스터하기 #12] 성능 최적화 - 캐싱과 쿼리 튜닝

학습 목표

이 포스트를 마치면 다음을 할 수 있습니다:

• 쿼리 성능 병목 지점 식별
• 효과적인 캐싱 전략 수립
• 데이터베이스 인덱스 활용
• 쿼리 최적화 기법 적용
• 대용량 데이터 처리 전략
• 성능 모니터링 및 개선

성능 문제 진단

성능 지표

graph TD
    A[Performance Metrics] --> B[Query Time]
    A --> C[Load Time]
    A --> D[Resource Usage]

    B --> B1[< 1s: 빠름]
    B --> B2[1-3s: 보통]
    B --> B3[> 3s: 느림]

    C --> C1[Dashboard 로딩]
    C --> C2[Question 렌더링]

    D --> D1[CPU]
    D --> D2[Memory]
    D --> D3[Network]

성능 병목 지점

1. 느린 쿼리

-- 문제: 전체 테이블 스캔
SELECT * FROM orders
WHERE YEAR(created_at) = 2025

-- Execution time: 15 seconds (10M rows)

2. 대시보드 과부하

Dashboard with 20 questions
각 Question 2초씩
Total: 40초 로딩 시간

3. 캐시 미사용

Same query 100 times today
매번 데이터베이스 조회
→ 불필요한 부하

캐싱 전략

Metabase 캐싱 시스템

graph LR
    A[User Request] --> B{Cache Exists?}
    B -->|Yes| C[Return Cached]
    B -->|No| D[Query Database]
    D --> E[Store in Cache]
    E --> F[Return Result]

    C --> G[Fast: <100ms]
    F --> H[Slow: 1-10s]

캐싱 설정

Global Settings:

Admin > Settings > Caching

Query caching:
  ☑ Enable caching
  Minimum query duration: 1 second
    (1초 이상 걸리는 쿼리만 캐싱)
  Maximum cache entry age: 24 hours
    (24시간 후 자동 삭제)

Cache invalidation:
  ○ Manual
  ● Automatic (TTL based)
  ○ Database triggers

Database-level Caching:

Admin > Databases > Your Database > Caching

Cache TTL: 1 hour (3600 seconds)

Schedule:
  - Heavy queries: 24 hours
  - Real-time data: 5 minutes
  - Static reference data: 7 days

Question-level Caching:

Question > ... menu > Caching

Cache results for:
  ○ Use database default (1 hour)
  ● Custom duration: 15 minutes
  ○ Don't cache

Use case:
  - Real-time dashboard: 1-5 minutes
  - Hourly report: 1 hour
  - Daily report: 24 hours
  - Weekly report: 7 days

사전 캐싱 (Preemptive Caching) - v0.53+:

Admin > Settings > Caching > Preemptive Caching

☑ Enable preemptive caching

작동 방식:
  1. 대시보드 사용 패턴 자동 학습
  2. 인기 파라미터 조합 상위 10개 식별
  3. 사용자가 요청하기 전에 미리 캐시 생성
  4. 백그라운드에서 자동 갱신

효과:
  - 대시보드 로딩 시간 50-80% 단축
  - 데이터베이스 부하 감소
  - 사용자 경험 대폭 개선

예시:
  Dashboard: Sales Overview
  학습된 인기 조합:
    1. date_range=last_7_days, region=all
    2. date_range=last_30_days, region=all
    3. date_range=last_7_days, region=west
    ...
  → 이 조합들은 자동으로 미리 캐시됨

캐싱 전략 패턴

패턴 1: 계층별 캐싱

Layer 1: Hot Data (자주 조회)
  - Cache TTL: 5 minutes
  - Examples: Today's revenue, Active users

Layer 2: Warm Data (정기 조회)
  - Cache TTL: 1 hour
  - Examples: Weekly trends, Department KPIs

Layer 3: Cold Data (가끔 조회)
  - Cache TTL: 24 hours
  - Examples: Historical reports, Annual stats

패턴 2: 시간대별 캐싱

Business hours (9 AM - 6 PM):
  - Short TTL: 5-15 minutes
  - 자주 업데이트되는 데이터

Off hours (6 PM - 9 AM):
  - Long TTL: 1-4 hours
  - 변경 적음, 트래픽 적음

Weekends:
  - Very long TTL: 12-24 hours
  - 거의 변경 없음

패턴 3: 데이터 타입별 캐싱

Transactional data (주문, 결제):
  - TTL: 1-5 minutes
  - 실시간성 중요

Analytical data (집계, 트렌드):
  - TTL: 15-60 minutes
  - 정확도보다 속도

Reference data (카테고리, 지역):
  - TTL: 24 hours - 7 days
  - 거의 변경 안 됨

수동 캐시 관리

캐시 초기화:

Admin > Troubleshooting > Tools

Clear cache:
  ☑ Query results cache
  ☑ Database schema cache
  ☑ Field values cache

Reason to clear:
  - 데이터 스키마 변경
  - 잘못된 결과 캐싱
  - 새 데이터 즉시 반영 필요

Question별 캐시 무효화:

Question > ... menu > Refresh cache

또는 API:
POST /api/card/:id/refresh

쿼리 최적화

1. WHERE 절 최적화

❌ 나쁜 예:

SELECT * FROM orders
WHERE YEAR(created_at) = 2025
  AND MONTH(created_at) = 3

-- 문제: 함수 사용으로 인덱스 사용 불가
-- Execution plan: Full table scan

✅ 좋은 예:

SELECT * FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at < '2025-04-01'

-- 인덱스 사용 가능
-- Execution plan: Index scan

2. JOIN 최적화

❌ 나쁜 예:

SELECT
  o.*,
  c.*,
  p.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN products p ON o.product_id = p.id

-- 문제: 불필요한 컬럼, LEFT JOIN 남용

✅ 좋은 예:

SELECT
  o.id,
  o.created_at,
  o.total,
  c.name as customer_name,
  p.name as product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2025-01-01'

-- 필요한 컬럼만, INNER JOIN 사용, 먼저 필터링

3. 집계 최적화

❌ 나쁜 예:

SELECT
  customer_id,
  (SELECT SUM(total) FROM orders o2
   WHERE o2.customer_id = o.customer_id) as total_spent
FROM orders o
GROUP BY customer_id

-- 문제: Correlated subquery (N+1 problem)

✅ 좋은 예:

SELECT
  customer_id,
  SUM(total) as total_spent
FROM orders
GROUP BY customer_id

-- 단일 패스로 집계

4. DISTINCT 최적화

❌ 나쁜 예:

SELECT DISTINCT
  o.id,
  o.created_at,
  o.total,
  c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id

-- DISTINCT는 비용이 높음

✅ 좋은 예:

SELECT
  o.id,
  o.created_at,
  o.total,
  c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id

-- 중복이 없도록 쿼리 설계

5. LIMIT 활용

-- Development/Testing
SELECT * FROM large_table
LIMIT 1000

-- Dashboard preview
SELECT * FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
LIMIT 10000

-- Top N 쿼리
SELECT *
FROM products
ORDER BY sales DESC
LIMIT 10

데이터베이스 인덱스

인덱스 필요성 판단

인덱스가 필요한 컬럼:
  ✅ WHERE 절에 자주 사용
  ✅ JOIN 조건에 사용
  ✅ ORDER BY에 사용
  ✅ GROUP BY에 사용
  ✅ Cardinality가 높음 (고유 값 많음)

인덱스가 불필요한 컬럼:
  ❌ SELECT만 하는 컬럼
  ❌ Cardinality가 낮음 (true/false 같은)
  ❌ 자주 UPDATE되는 컬럼
  ❌ 작은 테이블 (< 1000 rows)

인덱스 생성

단일 컬럼 인덱스:

-- 날짜 필터링이 많은 경우
CREATE INDEX idx_orders_created_at
  ON orders(created_at);

-- 고객별 조회가 많은 경우
CREATE INDEX idx_orders_customer_id
  ON orders(customer_id);

복합 인덱스:

-- 날짜 + 상태 필터링
CREATE INDEX idx_orders_date_status
  ON orders(created_at, status);

-- 사용 예:
SELECT * FROM orders
WHERE created_at >= '2025-01-01'
  AND status = 'completed'
-- 이 쿼리는 복합 인덱스를 활용

인덱스 순서 최적화:

-- ✅ 좋은 순서 (카디널리티 높은 것 먼저)
CREATE INDEX idx_orders_customer_date
  ON orders(customer_id, created_at);

-- customer_id: 10K unique values
-- created_at: 365 unique values

-- ❌ 나쁜 순서
CREATE INDEX idx_orders_date_customer
  ON orders(created_at, customer_id);
-- 덜 효율적

인덱스 모니터링

사용되지 않는 인덱스 찾기:

-- PostgreSQL
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

-- 사용 안 되는 인덱스는 삭제 고려

인덱스 효과 측정:

-- Before index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 12345;

-- Seq Scan on orders (cost=0.00..10000.00 rows=100)
-- Execution time: 150 ms

-- After index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 12345;

-- Index Scan using idx_orders_customer_id (cost=0.43..8.45 rows=100)
-- Execution time: 5 ms

-- 30배 빨라짐!

대용량 데이터 처리

1. 샘플링

전체 데이터 대신 샘플 사용:

-- PostgreSQL: TABLESAMPLE
SELECT * FROM large_table
TABLESAMPLE BERNOULLI (10)
-- 10% 샘플링

-- MySQL: RAND()
SELECT * FROM large_table
WHERE RAND() < 0.1
LIMIT 100000

2. 집계 테이블 (Materialized Views)

문제:

-- 매번 10M rows 집계
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at)

-- 10초 소요

해결: Materialized View:

-- 매일 1회 집계 (cron job)
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue,
  COUNT(*) as order_count
FROM orders
GROUP BY DATE(created_at);

CREATE INDEX idx_daily_revenue_date ON daily_revenue(date);

-- 사용
SELECT * FROM daily_revenue
WHERE date >= '2025-01-01';

-- 0.1초 소요 (100배 빠름!)

자동 갱신:

-- PostgreSQL: Manual refresh
REFRESH MATERIALIZED VIEW daily_revenue;

-- Cron job (매일 2 AM)
0 2 * * * psql -c "REFRESH MATERIALIZED VIEW daily_revenue"

3. 파티셔닝

시간 기반 파티셔닝:

-- PostgreSQL: Declarative partitioning
CREATE TABLE orders (
  id BIGINT,
  created_at TIMESTAMP,
  total DECIMAL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Query only scans relevant partition
SELECT * FROM orders
WHERE created_at >= '2025-01-01';
-- Only scans orders_2025 partition

4. 점진적 로딩

Infinite Scroll 대신 Pagination:

-- Page 1
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 100;

-- Page 2 (cursor-based)
SELECT * FROM orders
WHERE id < 
ORDER BY created_at DESC
LIMIT 100;

Metabase 설정 최적화

1. Connection Pooling

Admin > Databases > Your Database

Connection pool settings:
  Min pool size: 2
  Max pool size: 10
  Connection timeout: 30 seconds
  Idle timeout: 10 minutes

Explanation:
  - Min: 항상 유지할 연결 수
  - Max: 최대 동시 연결 수
  - Connection timeout: 연결 대기 시간
  - Idle timeout: 미사용 연결 종료 시간

2. Query Timeouts

Admin > Settings > General

Query timeout: 60 seconds

Individual question override:
  Question > ... menu > Edit > Advanced
  Custom timeout: 120 seconds (for heavy queries)

3. Sync Frequency

Admin > Databases > Your Database > Scheduling

Database syncing:
  - Scan for new tables: Daily
  - Scan for field values: Hourly

Recommendation:
  - Production DB: Daily (새 테이블 드물게 추가)
  - Development DB: Hourly (자주 변경됨)

4. Resource Limits

Admin > Settings > General

Max results:
  - GUI builder: 2000 rows
  - Downloaded results: 1 million rows

Memory limits:
  - JVM heap size: 4 GB (Metabase 설정)
  - Query result buffer: 1 GB

성능 모니터링

1. 슬로우 쿼리 로그

PostgreSQL:

-- postgresql.conf
log_min_duration_statement = 1000  # 1초 이상

-- 로그 분석
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
WHERE mean_time > 1000
ORDER BY mean_time DESC
LIMIT 20;

MySQL:

-- my.cnf
slow_query_log = 1
long_query_time = 1

-- 슬로우 쿼리 확인
SELECT * FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;

2. Metabase 내부 메트릭

Admin > Troubleshooting > Logs

Query execution log:
  - Question ID
  - Execution time
  - Row count
  - Cache hit/miss
  - Error messages

Export to CSV for analysis

3. 커스텀 모니터링 대시보드

-- Metabase database의 query_execution 테이블
SELECT
  DATE(started_at) as date,
  AVG(running_time) as avg_execution_time,
  MAX(running_time) as max_execution_time,
  COUNT(*) as query_count,
  SUM(CASE WHEN error IS NOT NULL THEN 1 ELSE 0 END) as error_count
FROM query_execution
WHERE started_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(started_at)
ORDER BY date DESC;

4. Alerts 설정

Alert: Slow Query Detected

Question:
  SELECT COUNT(*) FROM query_execution
  WHERE running_time > 10000  -- 10초 이상
    AND started_at >= NOW() - INTERVAL '1 hour'

Condition: > 5 (1시간에 5건 이상)

Recipients: DBA team

Action:
  - 해당 쿼리 식별
  - EXPLAIN ANALYZE 실행
  - 인덱스 또는 쿼리 최적화

성능 최적화 체크리스트

쿼리 레벨

• WHERE 절에 함수 사용 제거
• 필요한 컬럼만 SELECT
• INNER JOIN 우선 사용
• Subquery 대신 JOIN 사용
• LIMIT으로 결과 제한
• DISTINCT 대신 정확한 JOIN

데이터베이스 레벨

• 자주 사용하는 컬럼에 인덱스
• 복합 인덱스 순서 최적화
• 미사용 인덱스 제거
• 통계 정보 업데이트
• VACUUM (PostgreSQL)
• ANALYZE (주기적 실행)

Metabase 레벨

• 적절한 캐싱 TTL 설정
• Connection pool 튜닝
• Query timeout 설정
• Sync frequency 조정
• 불필요한 Question/Dashboard 제거

아키텍처 레벨

• Materialized views for 집계
• Partitioning for 대용량 테이블
• Read replicas for 분석
• CDN for static assets
• Load balancer for 트래픽 분산

실전 연습 문제

연습 1: 느린 쿼리 최적화 (초급)

문제:

-- 현재 쿼리 (15초 소요)
SELECT * FROM orders
WHERE MONTH(created_at) = 3
  AND YEAR(created_at) = 2025

해답 보기

-- 최적화된 쿼리 (0.5초)
SELECT * FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at < '2025-04-01'

-- 인덱스 생성
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 성능 개선:
-- Before: Full table scan (15초)
-- After: Index range scan (0.5초)
-- 30배 빨라짐

연습 2: 집계 테이블 구축 (중급)

과제: 일일 매출 리포트가 매번 10초 걸림. 최적화하기.

해답 보기

-- 1. Materialized View 생성
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  DATE(created_at) as sale_date,
  COUNT(*) as order_count,
  SUM(total) as total_revenue,
  AVG(total) as avg_order_value,
  COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

-- 2. 인덱스 생성
CREATE INDEX idx_daily_sales_date ON daily_sales_summary(sale_date);

-- 3. 자동 갱신 (Cron job: 매일 2 AM)
#!/bin/bash
# refresh_daily_sales.sh
psql -U metabase -d production <<EOF
REFRESH MATERIALIZED VIEW daily_sales_summary;
EOF

-- 4. Metabase Model 생성
-- Model: "Daily Sales Summary"
-- Data source: daily_sales_summary view
-- Update: Daily at 2 AM

-- 5. 캐싱 설정
-- Cache TTL: 24 hours
-- 이유: 하루 1번만 갱신되므로

-- 성능 결과:
-- Before: 10초 (1000만 rows 집계)
-- After: 0.1초 (365 rows 조회)
-- 100배 빨라짐!

연습 3: 대시보드 최적화 (고급)

과제: 20개 Question이 있는 대시보드가 40초 걸림. 최적화하기.

해답 보기

문제 분석:
  - 20 Questions × 2초 = 40초
  - 많은 Question이 같은 데이터 조회
  - 캐싱 미사용
  - 불필요한 Question 포함

최적화 전략:

1. Base Model 생성:

-- Model: "Orders Last 30 Days"
SELECT
  id,
  created_at,
  total,
  customer_id,
  product_id,
  status
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'completed'

-- 캐싱: 1 hour
-- 이유: 여러 Question이 공유

2. Question 재구성:

Before:
  - Q1: SELECT ... FROM orders WHERE date ...
  - Q2: SELECT ... FROM orders WHERE date ...
  - Q3: SELECT ... FROM orders WHERE date ...
  (각각 별도 쿼리, 캐시 공유 안 됨)

After:
  - Q1: SELECT ... FROM "Orders Last 30 Days"
  - Q2: SELECT ... FROM "Orders Last 30 Days"
  - Q3: SELECT ... FROM "Orders Last 30 Days"
  (모두 같은 Model 사용, 캐시 공유)

3. Question 통합:

Before:
  - Total Revenue (Question 1)
  - Order Count (Question 2)
  - Avg Order Value (Question 3)

After:
  - KPI Summary (하나의 Question에 3 metrics)

4. Lazy Loading:

<!-- 보이는 Question만 먼저 로드 -->
<script>
  const observer = new IntersectionObserver((entries) => {
    entries.forEach(entry => {
      if (entry.isIntersecting) {
        loadQuestion(entry.target.dataset.questionId);
      }
    });
  });
</script>

5. 불필요한 Question 제거:

Before: 20 Questions
Analysis:
  - 5 Questions: 한 달간 조회 0회 → 삭제
  - 3 Questions: 중복 정보 → 통합
  - 2 Questions: 너무 느림 → 최적화 또는 제거

After: 10 Questions

6. 캐싱 계층화:

Hot Questions (자주 조회):
  - Cache TTL: 5 minutes
  - Examples: Today's revenue, Active orders

Warm Questions:
  - Cache TTL: 1 hour
  - Examples: Weekly trends

Cold Questions:
  - Cache TTL: 24 hours
  - Examples: Monthly comparisons

최종 결과:
  - Before: 40초 (20 questions × 2초)
  - After: 3초
    • Base Model 조회: 1초 (캐시 사용)
    • 10 Questions 렌더링: 2초
  - 13배 빨라짐!

추가 개선:
  - Paginate long tables
  - Limit chart data points (max 100)
  - Use PNG instead of SVG for static charts
  - Enable dashboard filters (사용자가 조정 가능)

다음 단계

성능 최적화를 마스터했습니다. 다음 포스트에서는:

• 멀티 데이터소스: 여러 데이터베이스 통합
• Cross-database joins: 다른 DB 간 조인
• Data federation: 통합 분석 환경

요약

성능 최적화 우선순위

1. Quick Wins (즉시 효과):
   - 캐싱 활성화
   - LIMIT 추가
   - 불필요한 컬럼 제거

2. Medium Effort (중간 노력):
   - 인덱스 생성
   - 쿼리 리팩토링
   - Question 통합

3. Long-term (장기 투자):
   - Materialized views
   - Partitioning
   - Read replicas

성능 목표

메트릭	목표	우수	최적
Query time	< 3s	< 1s	< 0.5s
Dashboard load	< 10s	< 5s	< 3s
Cache hit rate	> 50%	> 70%	> 90%
Error rate	< 1%	< 0.1%	< 0.01%

다음 포스트에서는 여러 데이터베이스를 통합하여 분석하는 방법을 배웁니다!

📚 시리즈 전체 목차

🚀 기초편 (1-5화)

1. Metabase 소개와 핵심 개념
2. 설치와 초기 설정
3. 샘플 데이터 둘러보기
4. 첫 차트 만들기 - 실습 완전정복
5. 대시보드 만들기 - 한 화면에 모으기

💪 활용편 (6-10화)

1. 필터와 파라미터
2. SQL 네이티브 쿼리
3. 데이터 모델링
4. 자동화와 알림
5. 권한과 보안

🎯 고급편 (11-16화)

1. 임베딩과 공유
2. [성능 최적화] (현재 글)
3. 멀티 데이터소스
4. 커스터마이징
5. 운영과 모니터링
6. 실전 프로젝트