Configuración y Persistencia de Parámetros

Los parámetros del sistema en MySQL se clasifican según su alcance: global (afecta a todas las sesiones nuevas) y de sesión (afecta solo a la conexión actual). Es fundamental comprender que modificar un parámetro global no altera las sesiones ya establecidas.

-- Configuración de parámetros a nivel de sesión y global
SET SESSION wait_timeout = 3600;
SET GLOBAL wait_timeout = 3600;

Para evitar que los cambios dinámicos se pierdan tras un reinicio, MySQL 8 introdujo la persistencia de parámetros. Esto guarda la configuración en un archivo JSON (mysqld-auto.cnf) dentro del directorio de datos.

-- Aplica el cambio en memoria y lo persiste en disco
SET PERSIST max_connections = 500;

-- Solo guarda el cambio para el próximo reinicio, sin afectar la instancia actual
SET PERSIST_ONLY innodb_buffer_pool_size = 2147483648;

Cálculo de Memoria Máxima Estimada

Para prevenir que el sistema operativo agote la memoria y termine el proceso de MySQL (OOM Killer), es crucial estimar el consumo máximo de RAM. Aunque en la práctica los hilos no consumen simultáneamente sus asignaciones máximas, esta fórmula proporciona un límite superior seguro:

SELECT (
    @@key_buffer_size + 
    @@innodb_buffer_pool_size + 
    @@innodb_log_buffer_size + 
    (@@max_connections * (
        @@read_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@thread_stack
    ))
) / (1024 * 1024 * 1024) AS max_memory_estimate_gb;

Dimensionamiento del Buffer Pool de InnoDB

El parámetro innodb_buffer_pool_size es crítico para el rendimiento. En servidores dedicados, se recomienda asignar entre el 70% y el 80% de la RAM total. Sin embargo, no tiene sentido configurarlo por encima del tamaño total de los datos activos de InnoDB.

SELECT 
    COUNT(*) AS total_tables,
    CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), 'G') AS data_size,
    CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), 'G') AS index_size,
    CONCAT(ROUND(SUM(data_length + index_length)/(1024*1024*1024), 2), 'G') AS total_footprint
FROM information_schema.TABLES 
WHERE engine = 'InnoDB';

Para instancias que se ejecutan en entornos virtuales o contenedores donde la memoria puede escalar dinámicamente, el parámetro innodb_dedicated_server=ON ajusta automáticamente el buffer pool, los archivos de log y el método de flush basándose en la RAM detectada al iniciar.

Gestión de Logs y I/O

Los registros de rehacer (redo logs) de InnoDB garantizan la durabilidad de las transacciones. El tamaño de estos archivos debe ser suficiente para albergar la tasa de generación de logs durante las horas pico, evitando que se activen puntos de control (checkpoints) demasiado frecuentes, lo que degrada el rendimiento de I/O.

-- Cálculo del tamaño activo del log basado en el Log Sequence Number (LSN)
SELECT ROUND((458921044 - 412335891) / 1024 / 1024) AS active_log_size_mb;

Parámetros de Sincronización de Disco

• innodb_flush_log_at_trx_commit: Controla el comportamiento de escritura del redo log. El valor 1 (por defecto) garantiza cero pérdida de datos pero es más lento. El valor 2 escribe en el caché del sistema operativo en cada commit y sincroniza a disco una vez por segundo, ofreciendo un excelente equilibrio entre rendimiento y seguridad.
• sync_binlog: Controla la sincronización del binary log. Configurar este valor a 1 junto con innodb_flush_log_at_trx_commit=1 es mandatory para replicación síncrona y recuperación ante desastres, pero requiere almacenamiento con caché respaldada por batería (write-back).
• innodb_flush_method: En Linux, O_DIRECT es generalmente la mejor opción para evitar la doble memoria caché (InnoDB y Sistema Operativo), optimizando el uso de la RAM.

Grupos de Recursos (Resource Groups)

Introducidos en MySQL 8, los grupos de recursos permiten asignar hilos de ejecución a núcleos de CPU específicos y ajustar su prioridad. Esto es ideal para aislar cargas de trabajo de reporting o ETL de las transacciones críticas del negocio.

-- Crear un grupo de recursos para procesos por lotes
CREATE RESOURCE GROUP batch_processing TYPE = USER VCPU = 4-5 THREAD_PRIORITY = 15;

-- Ajustar dinámicamente durante horas de alta concurrencia
ALTER RESOURCE GROUP batch_processing VCPU = 5 THREAD_PRIORITY = 19;

-- Asignar la sesión actual a este grupo
SET RESOURCE GROUP batch_processing;

Nota: En Linux, el servicio de MySQL debe tener la capacidad CAP_SYS_NICE para poder modificar la prioridad de los hilos.

Identificación de Consultas Problemáticas (Top SQL)

El esquema sys y el performance_schema son herramientas indispensables para identificar cuellos de botella. En lugar de depender únicamente del log de consultas lentas, las vistas de rendimiento ofrecen métricas aggregadas en tiempo real.

-- Identificar las consultas con mayor tiempo de espera promedio
SELECT digest_text, count_star, 
       ROUND(avg_timer_wait/1000000000, 2) AS avg_latency_ms,
       sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name NOT IN ('performance_schema', 'sys')
ORDER BY avg_timer_wait DESC
LIMIT 5;

Planes de Ejecución y el Optimizador

El optimizador de MySQL evalúa el costo de diferentes estrategias de acceso utilizando estadísticas de tablas e índices. A partir de MySQL 8.0.18, el formato de árbol (TREE) y EXPLAIN ANALYZE proporcionan una visión mucho más detallada del plan real ejecutado.

-- Plan de ejecución en formato árbol
EXPLAIN FORMAT=TREE 
SELECT employee_id, department 
FROM employees 
WHERE hire_date > '2020-01-01';

-- Ejecución real con métricas de tiempo y filas por nodo
EXPLAIN ANALYZE 
SELECT employee_id, department 
FROM employees 
WHERE hire_date > '2020-01-01';

Rastreo del Optimizador (Optimizer Trace)

Cuando el optimizador toma decisiones contraintuitivas (como ignorar un índice en favor de un escaneo completo), el Optimizer Trace revela el cálculo de costos interno.

SET optimizer_trace = "enabled=on";
SELECT * FROM invoices WHERE client_id < 200;
SELECT trace INTO OUTFILE '/tmp/trace_invoices.json' FROM information_schema.optimizer_trace;
SET optimizer_trace = "enabled=off";

Optimización de Índices y Estadísticas

Un índice mal diseñado o estadísticas desactualizadas pueden arruinar el rendimiento. MySQL 8 permite marcar índices como "invisibles" para probar su eliminación sin borrarlos físicamente, lo cual es una operación costosa en tablas grandes.

-- Hacer un índice invisible para el optimizador
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- Forzar al optimizador a usar índices invisibles en una consulta específica
SELECT * /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ 
FROM users WHERE email = 'test@example.com';

Índices de Prefijo y Selectividad

Para columnas de texto largas, indexar la cadena completa consume demasiada memoria. Calcular la selectividad de diferentes longitudes de prefijo permite crear índices compactos y eficinetes.

SELECT 
    COUNT(DISTINCT LEFT(url, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(url, 15)) / COUNT(*) AS sel_15,
    COUNT(DISTINCT url) / COUNT(*) AS full_selectivity
FROM web_links;

Histogramas para Distribuciones Asimétricas

Las estadísticas tradicionales asumen una distribución unfiorme de los datos. Los histogramas informan al optimizador sobre la distribución real de los valores, mejorando drásticamente las estimaciones de filtrado en columnas no indexadas o con datos sesgados.

ANALYZE TABLE financial_records UPDATE HISTOGRAM ON transaction_amount WITH 128 BUCKETS;

SELECT schema_name, table_name, column_name,
    histogram->>'$."histogram-type"' AS hist_type,
    json_length(histogram->'$.buckets') AS bucket_count
FROM information_schema.column_statistics;

Joins, Ordenamiento y Fragmentación

Optimización de Joins

El orden de las tablas en un JOIN dicta el rendimiento. Si el optimizador elige un orden subóptimo, se pueden utilizar hints para forzar una ruta específica. Además, aumentar el join_buffer_size a nivel de sesión puede acelerar joins que no utilizan índices.

SELECT /*+ JOIN_ORDER(departments, employees) */ 
    d.dept_name, e.first_name 
FROM departments d 
JOIN employees e ON d.dept_id = e.dept_id;

Filesort vs Ordenamiento por Índice

El ordenamiento en memoria (filesort) es costoso. Si el sort_buffer_size es insuficiente, MySQL realiza múltiples pasadas de fusión en disco (Sort_merge_passes). Siempre que sea posible, se debe diseñar un índice que cubra tanto el filtrado (WHERE) como el ordenamiento (ORDER BY).

Desfragmentación de Tablespaces

Las operaciones masivas de DELETE y UPDATE dejan espacios vacíos en los archivos de datos. Para reclamar este espacio y desfragmentar la tabla, se debe reconstruir.

-- Reconstrucción de tabla para liberar espacio fragmentado
ALTER TABLE historical_orders FORCE;

Expresiones de Tabla Comunes (CTE)

Las CTE (WITH) mejoran la legibilidad y el mantenimiento de consultas complejas, permitiendo definir conjuntos de resultados temporales que pueden ser referenciados múltiples veces dentro de la misma instrucción, eliminando la necesidad de subconsultas anidadas profundas o tablas temporales manuales.

WITH RegionalRevenue (region, total_revenue) AS (
    SELECT region_code, SUM(revenue) 
    FROM sales_transactions
    WHERE transaction_year = 2023
    GROUP BY region_code
)
SELECT region, total_revenue
FROM RegionalRevenue
WHERE total_revenue > 1000000
ORDER BY total_revenue DESC;