Что нужно делать когда нагрузка на бд становится слишком большая?

Что делать когда нагрузка на БД становится слишком большая?

Это одна из ключевых задач Data Engineer. Когда БД перегружена, нужна система-матическая диагностика и применение скейлирующих решений в правильном порядке.

Шаг 1: Диагностика (измерение проблемы)

Сначала понимаешь, ЧТО перегружено и ПОЧЕМУ.

1.1. Какой ресурс исчерпан?

-- PostgreSQL: посмотреть основные метрики
SELECT 
  datname as database,
  numbackends as active_connections,
  tup_inserted + tup_updated + tup_deleted as mutations_per_sec,
  tup_fetched as reads_per_sec
FROM pg_stat_database
WHERE datname = 'production'
ORDER BY datname;

-- MySQL: текущие процессы
SHOW PROCESSLIST;

-- Redis: память
INFO memory
-- Результат: used_memory, maxmemory, evicted_keys (если много evictions)

1.2. Какие запросы медленные?

-- PostgreSQL: slow query log
SELECT 
  query,
  calls,
  total_time / calls as avg_time_ms,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Результат: обычно видишь 1-2 query которые занимают 80% времени

1.3. Метрики мониторинга

Установи мониторинг ДО кризиса:

# Prometheus metrics для отслеживания
from prometheus_client import Gauge, Counter

db_connections = Gauge('db_connections_active', 'Active DB connections')
db_query_time = Gauge('db_query_time_seconds', 'Query execution time')
db_errors = Counter('db_errors_total', 'Total DB errors')
db_cache_hits = Counter('db_cache_hits_total', 'Cache hits')

# Установка алертов
# Alert if connections > 80% of max_connections
# Alert if query_time > 5 seconds (P99)
# Alert if cache_hit_ratio < 90%

Шаг 2: Быстрые исправления (первая помощь)

Это делаешь СРАЗУ, пока диагностируешь:

2.1. Останови bad queries

-- Найти долгоживущий запрос
SELECT 
  pid,
  query,
  state,
  EXTRACT(EPOCH FROM (now() - query_start)) as duration_seconds
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration_seconds DESC;

-- Если query зависла на 1 час — убей его
SELECT pg_terminate_backend(12345);  -- pid из результата выше

2.2. Увеличь connection pool

# Если проблема в недостаточном количестве connections
# Временно увеличь:

# SQLAlchemy
engine = create_engine(
    'postgresql://...',
    poolsize=50,  # было 20
    max_overflow=20,  # было 10
)

# Django
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'CONN_MAX_AGE': 600,  # держи connection дольше
        'OPTIONS': {'connect_timeout': 10}
    }
}

2.3. Очисти кэш/буферы

-- PostgreSQL: очисти statement cache
DISCART PLANS;  -- забудь compiled queries

-- MySQL
FLUSH QUERY CACHE;
FLUSH TABLES;

Шаг 3: Оптимизация запросов (среднесрочно)

Это то, что реально помогает.

3.1. Добавь индексы

Проверь, какие индексы отсутствуют:

-- PostgreSQL: посмотри seq scans (плохо!)
SELECT 
  relname as table_name,
  seq_scan,
  idx_scan,
  seq_scan - idx_scan as extra_seq_scans
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY extra_seq_scans DESC
LIMIT 10;

-- Результат: если table_name: orders, seq_scan: 10M, idx_scan: 100
-- Значит запросы не используют индексы

-- Добавь индекс на часто используемые колонки
CREATE INDEX CONCURRENTLY idx_orders_user_date 
ON orders(user_id, created_at DESC);

-- CONCURRENTLY = не блокирует таблицу при создании индекса

3.2. Переписи медленные запросы

-- Плохо: LEFT JOIN + группировка создаёт temporary table
SELECT 
  u.user_id,
  COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN payments p ON o.id = p.order_id
LEFT JOIN items i ON o.id = i.order_id
GROUP BY u.user_id;
-- Это: 1M users × 100M orders = медленно

-- Хорошо: подзапрос с aggregation перед JOIN
SELECT 
  u.user_id,
  order_stats.order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
) order_stats ON u.id = order_stats.user_id;
-- Это: 1M users × 1M unique user_ids в subquery = быстрее

3.3. Партиционирование

-- Если таблица очень большая (500M+ rows)
-- Партиципируй по дате

CREATE TABLE orders_partitioned (
  id BIGSERIAL,
  user_id INT,
  created_at TIMESTAMP,
  amount DECIMAL
) PARTITION BY RANGE (created_at);

-- Автоматически создаёт партиции
CREATE TABLE orders_2024_q1 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- Преимущество: запросы читают только нужные партиции
-- SELECT WHERE created_at > '2024-03-01' читает только Q1 2024

Шаг 4: Масштабирование БД (medium term)

Когда оптимизация не помогает.

4.1. Вертикальное масштабирование (bigger machine)

❌ Минусы:
- CPU 8 cores → 16 cores = 2x cost
- RAM 32GB → 128GB = 4x cost
- Даже не гарантирует 2x производительность (из-за contention)
- Есть потолок (не можешь бесконечно масштабировать single server)

✅ Плюсы:
- Просто: просто купи мощнее сервер
- Быстро: часов 2-3 на миграцию

4.2. Горизонтальное масштабирование (read replicas)

Проблема: основной сервер перегружен чтением

Решение: read replicas

Структура:
  Primary (writes) ← чтение + запись
  ├─ Replica 1 (read-only) ← только чтение
  ├─ Replica 2 (read-only) ← только чтение
  └─ Replica 3 (read-only) ← только чтение

# PostgreSQL: создать replica
# На primary:
PG_HBA_CONF: host replication replica 192.168.1.10/32 trust

# На replica:
RESTORE_COMMAND = 'cp /archive/%f "%p"'
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.1 port=5432 user=replication'

# В приложении:
from sqlalchemy import create_engine

write_db = create_engine('postgresql://primary...')
read_db = create_engine('postgresql://replica1...')  # для SELECT

def get_orders(user_id):
    # читаешь с replica
    return read_db.execute(f"SELECT * FROM orders WHERE user_id = {user_id}")

4.3. Шардирование (split data)

Когда БД растёт и горизонтальное не помогает.

Проблема: 100M users в одной таблице, индексы не помогают

Решение: шардирование (разбить на несколько БД)

Логика:
shard_id = hash(user_id) % num_shards

В приложении:
shards = [
  db1 (users 0-25M),
  db2 (users 25M-50M),
  db3 (users 50M-75M),
  db4 (users 75M-100M),
]

user_id = 12345678
shard = 12345678 % 4  # shard 2
get_user(12345678) → connects to db2

Шаг 5: Caching Layer (быстрый результат)

Не все запросы нужно выполнять в БД.

5.1. Query result caching

import redis
import json
from functools import wraps

redis_client = redis.Redis(host='localhost', port=6379)

def cache_query(ttl_seconds=3600):
    """Кэшируй результаты запроса в Redis"""
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # Генерируем ключ кэша
            cache_key = f"{func.__name__}:{json.dumps(args)}:{json.dumps(kwargs)}"
            
            # Проверяем кэш
            cached = redis_client.get(cache_key)
            if cached:
                return json.loads(cached)
            
            # Если не в кэше, выполняем query
            result = func(*args, **kwargs)
            
            # Сохраняем результат в кэш
            redis_client.setex(cache_key, ttl_seconds, json.dumps(result))
            return result
        return wrapper
    return decorator

# Использование
@cache_query(ttl_seconds=3600)
def get_user_stats(user_id):
    query = "SELECT COUNT(*) as order_count FROM orders WHERE user_id = %s"
    return db.execute(query, [user_id])

# Первый вызов: читает из БД
stats1 = get_user_stats(123)

# Следующие вызовы (в течение часа): из Redis
stats2 = get_user_stats(123)  # быстро!

5.2. Materialized views

-- Создай материализованное представление (snapshot таблицы)
CREATE MATERIALIZED VIEW user_stats_mv AS
SELECT 
  user_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent,
  MAX(created_at) as last_order_date
FROM orders
GROUP BY user_id;

-- Пересчитывай раз в час
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats_mv;

-- В запросах используй view вместо table
SELECT * FROM user_stats_mv WHERE user_id = 123;
-- Это instant (не нужно GROUP BY на 100M rows)

Шаг 6: Offload нетрадиционных нагрузок

Не всё должно быть в OLTP БД.

6.1. Отправь analytics в Data Warehouse

# Плохо: каждый аналитический запрос нагружает production БД
db.execute("SELECT user_id, COUNT(*) FROM orders GROUP BY user_id")

# Хорошо: синхронизируй данные в Data Warehouse
# nightly sync
- Extract from production PostgreSQL
- Load to BigQuery/Snowflake
- Аналитики работают с BigQuery

# Production БД не страдает

6.2. Используй NoSQL для specific usecases

# PostgreSQL: для ACID транзакций
book_order()  # ACID, consistency важна

# MongoDB: для гибкой схемы
log_event({"user_id": 123, "event": "click", ...})  # NoSQL OK

# ElasticSearch: для full-text search
search_products("laptop computer")  # ES, не PostgreSQL

# Redis: для real-time data
increment_view_count(product_id)  # Redis, очень быстро

Checklist: Действия при перегрузке БД

Немедленно (минуты):

• Определить узкие места (slow query log)
• Убить зависшие запросы
• Увеличить connection pool
• Перезагрузить приложение (если есть утечка соединений)

Короткосрочно (часы):

• Добавить индексы на часто используемые колонки
• Переписать 1-2 критичных slow query
• Включить query caching (Redis)
• Создать materialized view для популярных агрегаций

Среднесрочно (дни):

• Реплицировать БД для чтения
• Микс-грейд оборудования (вертикальное масштабирование)
• Поднять separate Data Warehouse для analytics

Долгосрочно (недели/месяцы):

• Архитектурные изменения (микросервисы, CQRS)
• Шардирование по потребностям
• Полная переиндексация и опимизация

Вывод

Когда нагрузка на БД становится большой:

1. Диагностика — понять что сломалось
2. Быстрые фиксы — kill bad queries, add connections
3. Оптимизация — индексы, переписать запросы
4. Масштабирование — replicas, sharding, bigger machine
5. Кэширование — Redis, materialized views
6. Offload — analytics в DW, NoSQL для specific cases

Порядок важен: сначала оптимизация (дешево), потом масштабирование (дорого).