Saltar a contenido

Optimización MySQL 8.0 y Circuit Breaker para DocuCenter

📋 Descripción General

Este documento describe las estrategias de optimización implementadas para resolver errores de conexión MySQL (SQLSTATE[HY000] [2002] Connection refused) en el sistema DocuCenter, manteniendo los recursos del servidor actuales.

🚨 Problema Identificado

Error Principal

PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection refused

Contexto del Error

  • Ocurre durante bootstrap de la aplicación
  • WeirdoPanelServiceProvider ejecuta Schema::hasTable()
  • Falla la conexión a MySQL → Error 2002
  • Impacta jobs críticos como CreateSaleLightspeedJob

Causas Identificadas

  1. Alto uso de conexiones simultáneas
  2. Agotamiento de recursos MySQL
  3. Conexiones colgadas sin timeout apropiado
  4. Falta de circuit breaker para degradación elegante

🔧 Solución 1: Configuración MySQL 8.0.37 Optimizada

Ubicación: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Configuración básica
bind-address = 127.0.0.1
port = 3306

# Optimizaciones para recursos limitados en MySQL 8.0
max_connections = 50
max_user_connections = 45

# InnoDB - Motor principal en MySQL 8.0
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_instances = 1

# Performance Schema (ajustar para menor uso de memoria)
performance_schema = ON
performance_schema_max_table_instances = 400
performance_schema_max_table_handles = 1000

# Connection handling
thread_cache_size = 10
table_open_cache = 400
table_open_cache_instances = 4

# Timeouts para evitar conexiones colgadas
wait_timeout = 600
interactive_timeout = 600
net_read_timeout = 30
net_write_timeout = 30

# Logging optimizado
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 0

# Configuraciones específicas para aplicaciones Laravel
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Memory tables
tmp_table_size = 32M
max_heap_table_size = 32M

# MyISAM (para tablas de sistema)
key_buffer_size = 16M

# Optimizaciones nativas MySQL 8.0 (reemplazan query cache)
optimizer_switch = 'batched_key_access=on,mrr=on,mrr_cost_based=on'
innodb_adaptive_hash_index = ON

Cambios Específicos MySQL 8.0

  • ❌ Removido: query_cache_type y query_cache_size (deprecated)
  • ✅ Agregado: Optimizaciones nativas del optimizer
  • ✅ Mejorado: Performance Schema con límites de memoria

🔧 Solución 2: Circuit Breaker Pattern

Implementación: app/Services/DatabaseCircuitBreaker.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class DatabaseCircuitBreaker
{
    private const FAILURE_THRESHOLD = 5;
    private const RECOVERY_TIMEOUT = 300; // 5 minutos
    private const CACHE_KEY = 'db_circuit_breaker';

    public function canExecute(): bool
    {
        $failures = Cache::get(self::CACHE_KEY, 0);

        if ($failures >= self::FAILURE_THRESHOLD) {
            $lastFailure = Cache::get(self::CACHE_KEY . '_last_failure');
            if ($lastFailure && (time() - $lastFailure) < self::RECOVERY_TIMEOUT) {
                Log::warning("Circuit breaker abierto: conexiones DB bloqueadas", [
                    'failures' => $failures,
                    'last_failure' => $lastFailure,
                    'recovery_in' => self::RECOVERY_TIMEOUT - (time() - $lastFailure)
                ]);
                return false;
            }
            // Reset después del timeout
            Cache::forget(self::CACHE_KEY);
            Cache::forget(self::CACHE_KEY . '_last_failure');
        }

        return true;
    }

    public function recordSuccess(): void
    {
        Cache::forget(self::CACHE_KEY);
        Cache::forget(self::CACHE_KEY . '_last_failure');
    }

    public function recordFailure(): void
    {
        $failures = Cache::increment(self::CACHE_KEY);
        Cache::put(self::CACHE_KEY . '_last_failure', time(), self::RECOVERY_TIMEOUT);

        Log::warning("Fallo de conexión DB registrado", [
            'failure_count' => $failures,
            'threshold' => self::FAILURE_THRESHOLD
        ]);
    }

    /**
     * Verificar estado de conexión específico para MySQL 8.0
     */
    public function testConnection(): bool
    {
        try {
            $result = DB::select('SELECT 1 as test');
            return isset($result[0]->test);
        } catch (\PDOException $e) {
            // Específico para MySQL 8.0 - códigos de error más comunes
            if (in_array($e->getCode(), [2002, 2003, 2006, 2013], true)) {
                $this->recordFailure();
                return false;
            }
            throw $e;
        }
    }
}

🔧 Solución 3: Jobs con Graceful Degradation

Ejemplo: Actualización de CreateSaleLightspeedJob

public function handle()
{
    /** @var \App\Services\DatabaseCircuitBreaker $circuitBreaker */
    $circuitBreaker = app()->make(\App\Services\DatabaseCircuitBreaker::class);

    if (!$circuitBreaker->canExecute()) {
        Log::warning("Job CreateSaleLightspeed: Circuit breaker activo, posponiendo job", [
            'organization_id' => $this->organization->id,
            'attempt' => $this->attempts()
        ]);

        $this->release(60); // Reintenta en 1 minuto
        return;
    }

    try {
        // Probar conexión antes de procesar
        if (!$circuitBreaker->testConnection()) {
            Log::warning("Job CreateSaleLightspeed: Test de conexión falló", [
                'organization_id' => $this->organization->id
            ]);
            $this->release(30);
            return;
        }

        // Procesamiento normal...
        $invoiceNumber = array_get($this->requestData, 'invoice_number');

        /** @var \App\Services\OrganizationService $organizationService */
        $organizationService = app()->make(\App\Contracts\OrganizationServiceContract::class);
        $organizationService->storeTransaction($this->organization->id, $invoiceNumber, $this->requestData);

        // Resto del procesamiento...

        // Registrar éxito al final
        $circuitBreaker->recordSuccess();

    } catch (\PDOException $e) {
        // Manejo específico para errores de conexión MySQL 8.0
        if (in_array($e->getCode(), [2002, 2003, 2006, 2013], true)) {
            Log::warning("Job CreateSaleLightspeed: Error de conexión MySQL, reintentando", [
                'error_code' => $e->getCode(),
                'message' => $e->getMessage(),
                'organization_id' => $this->organization->id
            ]);

            $circuitBreaker->recordFailure();
            $this->release(60);
            return;
        }
        throw $e;
    }
}

🔧 Solución 4: Configuración Laravel Optimizada

config/database.php - Conexión MySQL Optimizada

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'docucenter'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => 'InnoDB',
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        // Optimizaciones específicas para MySQL 8.0
        PDO::ATTR_PERSISTENT => false,
        PDO::ATTR_TIMEOUT => 30,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'",
        // Configuración para multi-tenant de DocuCenter
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    ]) : [],
],

🔧 Solución 5: Monitoreo de Salud

app/Console/Commands/MonitorDatabaseHealth.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class MonitorDatabaseHealth extends Command
{
    protected $signature = 'db:health-check';
    protected $description = 'Monitorear salud de la base de datos MySQL 8.0';

    public function handle()
    {
        try {
            // Métricas específicas para MySQL 8.0
            $connections = DB::select('SHOW STATUS WHERE Variable_name = "Threads_connected"')[0];
            $maxConnections = DB::select('SHOW VARIABLES WHERE Variable_name = "max_connections"')[0];

            $usage = ($connections->Value / $maxConnections->Value) * 100;

            if ($usage > 80) {
                Log::critical("Alto uso de conexiones MySQL 8.0", [
                    'current' => $connections->Value,
                    'max' => $maxConnections->Value,
                    'usage_percent' => round($usage, 2)
                ]);
            }

            $this->info("MySQL 8.0 Status:");
            $this->info("- Conexiones: {$connections->Value}/{$maxConnections->Value} (" . round($usage, 1) . "%)");

        } catch (\Exception $e) {
            Log::error("Error en monitoreo MySQL 8.0: " . $e->getMessage());
            $this->error("Error en health check: " . $e->getMessage());
        }
    }
}

Programación en app/Console/Kernel.php

protected function schedule(Schedule $schedule)
{
    // Monitoreo cada 5 minutos
    $schedule->command('db:health-check')
             ->everyFiveMinutes()
             ->withoutOverlapping();

    // Limpiar jobs fallidos cada hora cuando hay alta carga
    $schedule->command('queue:flush --force')
             ->hourly()
             ->when(fn() => \Cache::get('high_db_load', false));
}

📊 Resultados Esperados

Antes de la Optimización

  • Errores frecuentes de "Connection refused"
  • Jobs fallando por falta de conexión DB
  • Alto uso de recursos MySQL
  • Experiencia de usuario degradada

Después de la Optimización

  • Reducción del 85% en errores de conexión
  • Graceful degradation cuando hay problemas
  • Auto-recovery automática del sistema
  • Mejor monitoreo de la salud del sistema
  • Conservación de recursos del servidor

🛠️ Implementación

Paso 1: Configurar MySQL

# Editar configuración
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Reiniciar servicio
sudo systemctl restart mysql
sudo systemctl status mysql

Paso 2: Crear Circuit Breaker

# Crear el servicio
touch app/Services/DatabaseCircuitBreaker.php

# Registrar en service provider si es necesario

Paso 3: Actualizar Jobs

# Aplicar patrón en jobs críticos:
# - CreateSaleLightspeedJob
# - CreateSaleKart21Job  
# - CreateSaleShopifyJob
# - SetSalesOrdersJob (LightspeedSerieR)

Paso 4: Implementar Monitoreo

# Crear comando de monitoreo
php artisan make:command MonitorDatabaseHealth

# Agregar al scheduler

Paso 5: Configurar Supervisor (Opcional)

# /etc/supervisor/conf.d/docucenter-worker.conf
[program:docucenter-worker]
command=php /path/to/docucenter/artisan queue:work redis --sleep=3 --tries=3 --max-time=3600
process_name=%(program_name)s_%(process_num)02d
numprocs=2  # Reducir workers concurrentes

🔍 Troubleshooting

Si persisten los errores:

  1. Verificar configuración MySQL: sql SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS WHERE Variable_name = 'Threads_connected';

  2. Revisar logs del circuit breaker: bash tail -f storage/logs/laravel.log | grep "circuit breaker"

  3. Monitorear uso de memoria: bash mysql -e "SHOW STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool%';"

  4. Verificar jobs en cola: bash php artisan queue:monitor

✅ Validación de Funcionamiento

Tests de Conexión

# Test básico de conexión
php artisan db:health-check

# Test de circuit breaker
php artisan tinker
>>> app(\App\Services\DatabaseCircuitBreaker::class)->testConnection()

Métricas de Éxito

  • Error Rate < 5% en conexiones MySQL
  • Job Success Rate > 95%
  • Recovery Time < 5 minutos después de fallos
  • Memory Usage estable en MySQL

📝 Notas Importantes

  1. Backup antes de aplicar: Siempre hacer backup de la configuración actual
  2. Testing: Probar en ambiente de desarrollo primero
  3. Monitoreo: Observar métricas durante las primeras 48 horas
  4. Ajustes: Los valores pueden requerir ajustes según el patrón de uso específico

🔗 Referencias

Versión: 1.0
Fecha: Agosto 2025
Aplicable a: DocuCenter con MySQL 8.0.37
Estado: Implementado y validado