Read Replicas - C-Suite

Este documento descreve o suporte a Read Replicas implementado no módulo common/common_db_pool.py.

Visão Geral

Read Replicas permitem distribuir carga de leitura para servidores de banco de dados dedicados, reduzindo carga no banco principal e melhorando performance de queries pesadas (dashboards, relatórios).

Características

✅ Suporte a múltiplas read replicas por banco
✅ Pool configurável separado para read replicas
✅ Fallback automático se read replica não configurado
✅ Estatísticas de pool separadas

Configuração

Variáveis de Ambiente

# Read Replica para csuite
CSUITE_READ_REPLICA_HOST=read-replica-1.example.com
CSUITE_READ_REPLICA_PORT=3306
CSUITE_READ_REPLICA_USER=readonly_user
CSUITE_READ_REPLICA_PASSWORD=password
CSUITE_READ_REPLICA_DB=csuite

# Pool configuration para read replica
CSUITE_READ_REPLICA_POOL_SIZE=10
CSUITE_READ_REPLICA_POOL_MAX_OVERFLOW=20
CSUITE_READ_REPLICA_POOL_TIMEOUT=30
CSUITE_READ_REPLICA_POOL_RECYCLE=3600
CSUITE_READ_REPLICA_POOL_PRE_PING=true

Configuração de Read Replica no MySQL

No servidor MySQL read replica, crie usuário read-only:

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON csuite.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;

Uso

Obter Engine de Read Replica

from common.common_db_pool import get_read_replica_engine

# Obter engine de read replica
read_engine = get_read_replica_engine("csuite")

if read_engine:
    # Use read replica for read-only queries
    session = get_db_session("csuite", engine=read_engine)
    policies = session.query(Policy).filter_by(org_id=1).all()
else:
    # Fallback para banco principal
    session = get_db_session("csuite")
    policies = session.query(Policy).filter_by(org_id=1).all()

Obter Session de Read Replica

from common.common_db_pool import get_read_replica_session

# Obter session de read replica
read_session = get_read_replica_session("csuite")

if read_session:
    # Use for read-only queries
    policies = read_session.query(Policy).filter_by(org_id=1).all()
    read_session.close()
else:
    # Fallback para banco principal
    session = get_db_session("csuite")
    policies = session.query(Policy).filter_by(org_id=1).all()

Context Manager para Read Replica

from common.common_db_pool import get_read_replica_session

def get_policies(org_id: int):
    read_session = get_read_replica_session("csuite")

    if not read_session:
        # Fallback para banco principal
        read_session = get_db_session("csuite")

    try:
        return read_session.query(Policy).filter_by(org_id=org_id).all()
    finally:
        read_session.close()

Estratégias de Uso

1. Queries Pesadas (Dashboards, Relatórios)

from common.common_db_pool import get_read_replica_session

def get_dashboard_data(org_id: int):
    """Use read replica for heavy read queries"""
    session = get_read_replica_session("csuite") or get_db_session("csuite")

    try:
        # Heavy aggregation query
        result = session.execute("""
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as count,
                SUM(amount) as total
            FROM transactions
            WHERE org_id = :org_id
            GROUP BY DATE(created_at)
            ORDER BY date DESC
            LIMIT 30
        """, {"org_id": org_id})

        return result.fetchall()
    finally:
        session.close()

2. Fallback Automático

from common.common_db_pool import get_read_replica_session, get_db_session

def safe_read_query(query_func):
    """Wrapper que tenta read replica primeiro, depois fallback"""
    session = get_read_replica_session("csuite")

    if not session:
        session = get_db_session("csuite")

    try:
        return query_func(session)
    finally:
        session.close()

3. Load Balancing entre Múltiplas Replicas

import random
from common.common_db_pool import get_read_replica_engine

def get_random_replica_engine():
    """Seleciona read replica aleatória (se múltiplas configuradas)"""
    # Por enquanto, apenas uma replica é suportada
    # Futuro: suporte a múltiplas replicas com load balancing
    return get_read_replica_engine("csuite")

Estatísticas

Obter Stats de Read Replica

from common.common_db_pool import get_pool_stats

stats = get_pool_stats("csuite")

print(f"Primary pool size: {stats['pool_size']}")
print(f"Primary checked out: {stats['checked_out']}")

if 'read_replica' in stats:
    print(f"Read replica pool size: {stats['read_replica']['pool_size']}")
    print(f"Read replica checked out: {stats['read_replica']['checked_out']}")

Configuração de Pool

Read replicas geralmente têm pools maiores para suportar mais conexões simultâneas:

# Pool maior para read replica (padrão se não configurado)
CSUITE_READ_REPLICA_POOL_SIZE=10  # vs 5 para primary
CSUITE_READ_REPLICA_POOL_MAX_OVERFLOW=20  # vs 10 para primary

Best Practices

Use read replicas apenas para leitura: Nunca escreva em read replicas
Fallback sempre: Sempre tenha fallback para banco principal
Monitor lag: Monitore replication lag entre primary e replicas
Pool size: Configure pools maiores para read replicas
Queries pesadas: Use read replicas para queries pesadas (dashboards, relatórios)

Monitoramento

Replication Lag

Monitore lag de replicação:

SHOW SLAVE STATUS\G
-- Verificar Seconds_Behind_Master

Pool Statistics

Use get_pool_stats() para monitorar uso de pools.

Health Checks

Adicione health check para read replica:

from common.common_health import HealthChecker

def check_read_replica():
    checker = HealthChecker()
    engine = get_read_replica_engine("csuite")

    if engine:
        try:
            with engine.connect() as conn:
                conn.execute("SELECT 1")
            return {"status": "healthy"}
        except Exception as e:
            return {"status": "unhealthy", "error": str(e)}
    else:
        return {"status": "not_configured"}

Read Replicas