Tema 20: Monitoreo, métricas y diagnóstico — el tlamatini del rendimiento

#PF-020 | XP: 300 | Insignia: Vigía del Tlamatini | Tier: Senior | Tiempo: 4 h

Objetivo

Configurarás el monitoreo continuo de tu base de datos: métricas de rendimiento (CPU, memoria, I/O, conexiones), logs de queries lentas, diagnóstico de bloqueos, y alertas automáticas. También aprenderás a usar `EXPLAIN` para entender por qué una query es lenta y cómo optimizarla.

Mapa del tema

Las 4 métricas que todo DBA debe vigilar Slow query log y pg_stat_statements EXPLAIN y EXPLAIN ANALYZE Diagnóstico de bloqueos y deadlocks Alertas y dashboards (Prometheus + Grafana, o lo simple) LAB: detectar y resolver una query lenta

Visión mexica — Por Citlalli

El tlamatini (sabio mexica) era el astrónomo del altépetl. Cada noche observaba las estrellas y los movimientos de Venus para anticipar las lluvias, las cosechas y los eclipses. No actuaba sobre eventos: los preveía. Cuando Venus se ocultaba, sabía que la sequía vendría; cuando reaparecía, era tiempo de sembrar.

El DBA moderno es un tlamatini de su base. Las métricas son sus estrellas: conexiones activas, latencia de queries, uso de disco, cache hit ratio. Cuando una métrica cambia de patrón, sabe que algo viene. Cuando el cache hit ratio baja del 95%, la tormenta se acerca. Cuando las conexiones llegan al 80% del máximo, hay que actuar antes del colapso.

Bloque 1 — Las 4 métricas que todo DBA debe vigilar

Métrica 1: Conexiones activas vs máximas

MySQL:

SHOW STATUS LIKE 'Threads_connected';
-- Actual: 12
SHOW VARIABLES LIKE 'max_connections';
-- Máximo: 151

Ratio: `12/151 = 7.9%`. Verde. Si supera 80% (`120/151`), amarillo. Si llega a 95%, rojo: te vas a quedar sin conexiones.

PostgreSQL:

SELECT count() AS conexiones, (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS maximo
FROM pg_stat_activity;

Métrica 2: Cache hit ratio (InnoDB Buffer Pool / PostgreSQL shared_buffers)

MySQL (InnoDB):

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
-- Lecturas del buffer (rápido)
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- Lecturas de disco (lento)
-- Cache hit ratio = (read_requests - reads) / read_requests  100

Si está por debajo del 99%, el buffer pool es pequeño. Considera aumentar:

[mysqld]
innodb_buffer_pool_size = 1G  # Para 4 GB RAM, dedicado a MySQL

PostgreSQL:

SELECT
  sum(blks_hit) AS hits,
  sum(blks_read) AS reads,
  round(100.0  sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2) AS cache_hit_ratio
FROM pg_stat_database;
-- Esperado: > 99%

Métrica 3: Queries lentas (slow queries)

MySQL: habilita el slow query log y cuenta las queries que tardan más de N segundos.

[mysqld]
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = ON

SHOW STATUS LIKE 'Slow_queries';
-- Acumulado desde el último reinicio

PostgreSQL: usa `pg_stat_statements` (extensión estándar).

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

CREATE EXTENSION pg_stat_statements;
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Métrica 4: Replicación (si tienes replica)

MySQL:

SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0 (sincronizado)
-- Si > 60 segundos, la replica está atrasada

PostgreSQL:

SELECT
  client_addr,
  state,
  sent_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Bloque 2 — Slow query log y pg_stat_statements en acción

Caso: tienda_tlalli con 110 pedidos y 50 clientes

Ejecutas 1000 veces la misma query y notas latencia:

SELECT c.nombre, c.email, COUNT(p.pedido_id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
GROUP BY c.cliente_id
ORDER BY total_pedidos DESC;

Si tarda 800 ms con 50 clientes pero 30 segundos con 50,000 clientes, el problema no es la query, es la escala. Aquí entra `EXPLAIN`.

Habilitar pg_stat_statements

-- En la base que quieres monitorear
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Resetear estadísticas
SELECT pg_stat_statements_reset();
-- Esperar un rato (1 hora o 1 día según tráfico)
-- Ver las queries más lentas
SELECT
  substring(query for 100) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric / 1000, 2) AS total_sec,
  rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'  -- Excluir esta misma query
ORDER BY mean_exec_time DESC
LIMIT 20;

Interpretar el resultado

Una fila típica:

| query_snippet | calls | mean_ms | total_sec | rows | |---|---|---|---|---| | `SELECT FROM pedidos WHERE cliente_id = $1` | 8421 | 12.3 | 103.6 | 4.2 | | `SELECT nombre FROM clientes WHERE email = $1` | 2310 | 0.8 | 1.8 | 1.0 | | `SELECT producto_id FROM productos WHERE categoria_id = $1 ORDER BY precio DESC` | 4521 | 85.4 | 386.2 | 23.1 |

La tercera query es la candidata a optimizar: 85 ms promedio, 386 segundos totales en 4521 llamadas. Si añadimos un índice, podemos reducirla a 5 ms.

Bloque 3 — EXPLAIN y EXPLAIN ANALYZE

MySQL

EXPLAIN SELECT c.nombre, COUNT(p.pedido_id) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
GROUP BY c.cliente_id;

Salida:

id  select_type  table  type   possible_keys  key       rows  Extra
1   SIMPLE       c      ALL    NULL           NULL      50    Using temporary
1   SIMPLE       p      ref    cliente_id     cliente_id 2    NULL

Interpretación:

• `c` (clientes): `type=ALL` y `rows=50` → escaneo completo de tabla. 50 clientes no es problema, pero con 50,000 sí.
• `p` (pedidos): `type=ref` y `key=cliente_id` → usa el índice, eficiente.
• `Extra: Using temporary` → MySQL está creando una tabla temporal para el GROUP BY. Aceptable para 50 filas.

PostgreSQL

EXPLAIN ANALYZE
SELECT c.nombre, COUNT(p.pedido_id) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
GROUP BY c.cliente_id;

Salida:

HashAggregate  (cost=12.5..14.0 rows=50 width=40) (actual time=0.5..0.6 rows=50.0 loops=1)
  Group Key: c.cliente_id
  ->  Hash Right Join  (cost=4.0..10.0 rows=110 width=40) (actual time=0.1..0.3 rows=110.0 loops=1)
        Hash Cond: (p.cliente_id = c.cliente_id)
        ->  Seq Scan on pedidos p  (cost=0.0..2.1 rows=110 width=8) (actual time=0.01..0.05 rows=110.0 loops=1)
        ->  Hash  (cost=3.5..3.5 rows=50 width=36) (actual time=0.05..0.05 rows=50.0 loops=1)
              ->  Seq Scan on clientes c  (cost=0.0..3.5 rows=50 width=36) (actual time=0.02..0.04 rows=50.0 loops=1)
Planning Time: 0.2 ms
Execution Time: 0.8 ms

Interpretación:

• `Seq Scan on clientes` y `Seq Scan on pedidos` → escaneos completos. Esperable para tablas pequeñas.
• `Execution Time: 0.8 ms` → excelente. Si la tabla creciera, veríamos `Index Scan` recomendado.

Patrones problemáticos en EXPLAIN

| Patrón | Problema | Solución | |---|---|---| | `Seq Scan` en tabla grande | Escaneo completo (lento) | Crear índice en la columna del WHERE | | `Using filesort` (MySQL) | Ordenamiento en disco | Índice en la columna del ORDER BY | | `Using temporary` (MySQL) | Tabla temporal | Índice compuesto que cubra el GROUP BY | | `Nested Loop` con `rows` muy grande | Join ineficiente | Considerar `Hash Join` o reescribir | | `actual time=... rows=... loops=N` con N alto | Subquery ejecutada N veces | Reescribir como JOIN |

Bloque 4 — Diagnóstico de bloqueos y deadlocks

MySQL: detectar bloqueos

-- Ver transacciones activas
SELECT  FROM information_schema.INNODB_TRX;
-- Ver locks
SELECT  FROM performance_schema.data_locks;

O más visual:

SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_query,
  trx_rows_locked
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

MySQL: deadlock detectado

-- Ver el último deadlock
SHOW ENGINE INNODB STATUS\G

En la sección `LATEST DETECTED DEADLOCK`, MySQL te dice:

• Qué transacciones estaban involucradas.
• Qué queries ejecutaban.
• Qué lock se liberó (la víctima del rollback).

PostgreSQL: bloqueos

-- Ver queries bloqueadas y quién las bloquea
SELECT
  blocked.pid          AS pid_bloqueado,
  blocked.usename      AS usuario_bloqueado,
  blocking.pid         AS pid_bloqueador,
  blocking.usename     AS usuario_bloqueador,
  blocked.query        AS query_bloqueada,
  blocking.query       AS query_bloqueadora
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND kl.pid != bl.pid
  AND kl.granted = true
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

Solución a bloqueos

Identificar la transacción bloqueadora (query de arriba). Evaluar si es legítima (un `UPDATE` largo en horas de baja actividad puede ser OK). Si es legítima: esperar a que termine. Si es ilegítima o demasiado larga: terminarla manualmente:

-- PostgreSQL
SELECT pg_cancel_backend(pid_bloqueador);
-- O más agresivo:
SELECT pg_terminate_backend(pid_bloqueador);

-- MySQL
-- Encontrar el thread_id y matarlo
SELECT id, query, time FROM information_schema.processlist WHERE db = 'tienda_tlalli';
KILL 12345;

Bloque 5 — Alertas y dashboards

La pila simple: cron + script + email

Un script bash que verifica métricas y envía alerta:

#!/usr/bin/env bash
/opt/scripts/check_db.sh
THRESHOLD=80
MAX_CONN=$(mysql -u monitor -p"${MONITOR_PWD}" -N -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}')
CURR_CONN=$(mysql -u monitor -p"${MONITOR_PWD}" -N -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
RATIO=$((CURR_CONN  100 / MAX_CONN))
if [ "$RATIO" -gt "$THRESHOLD" ]; then
  echo "ALERTA: Conexiones MySQL al ${RATIO}% (${CURR_CONN}/${MAX_CONN})" |
    mail -s "[DB ALERTA] Conexiones altas" admin@ciberecus.mx
fi

/5     /opt/scripts/check_db.sh

La pila profesional: Prometheus + Grafana

Para bases de producción reales:

MySQL exporter (`mysqld_exporter` de Prometheus): expone métricas en `:9104/metrics`. PostgreSQL exporter (`postgres_exporter`): expone métricas en `:9187/metrics`. Prometheus: scrapea cada 15s, almacena series temporales. Grafana: visualiza con dashboards pre-hechos (hay dashboards de la comunidad para MySQL y PostgreSQL). Alertmanager: reglas de alerta (ratio de cache < 95%, conexiones > 80%, replicación lag > 60s).

• MySQL: https://grafana.com/grafana/dashboards/7362
• PostgreSQL: https://grafana.com/grafana/dashboards/9628

AI Mission

Pídele a tu IA que analice un `EXPLAIN ANALYZE`. Prompt sugerido:

Tengo esta query que tarda 8 segundos en MySQL 8 con 1M filas:
SELECT c.nombre, c.email, COUNT(p.pedido_id) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE p.fecha_pedido > '2026-01-01'
GROUP BY c.cliente_id
ORDER BY total DESC
LIMIT 20;
[pega aquí el EXPLAIN ANALYZE]
Dame:
Cuál es el principal cuello de botella.
Qué índice añadirías y por qué.
Si reescribirías la query, cómo.
Cuánto esperas mejorar (estimación).

Errores típicos

`EXPLAIN` sin `ANALYZE` en PostgreSQL → ves el plan estimado, no el real. Los dos pueden diferir. Crear índices innecesarios → cada índice añade overhead en INSERT/UPDATE. Un índice no usado es peso muerto. Monitorear solo CPU → un瓶颈 de disco no se ve en CPU. Alertar por todo → el operador se cansa y silencia las alertas. Solo alerta por lo accionable. No hacer baseline → si no sabes qué era "normal" antes, no sabes qué es "anormal" ahora. Confiar en `pg_stat_statements` sin normalizar queries → queries con literales distintos aparecen como entradas separadas. Usar `pg_stat_statements.track = top` y normalizar.

LAB práctico — Detectar y resolver una query lenta

Objetivo: introducir una query lenta intencionalmente, diagnosticarla con `EXPLAIN`, optimizarla con un índice, y medir la mejora.

Paso 1 — Generar volumen

-- Insertar 10,000 pedidos y 5,000 clientes adicionales
INSERT INTO clientes (nombre, email, telefono, fecha_registro)
SELECT
  'Cliente ' || g,
  'cliente' || g || '@test.com',
  '+52 55 ' || lpad(g::text, 8, '0'),
  current_date - (random()  365)::int
FROM generate_series(1, 5000) g;
INSERT INTO pedidos (cliente_id, fecha_pedido, total, estado)
SELECT
  (random()  5000 + 1)::int,
  current_date - (random()  180)::int,
  (random()  1000 + 50)::numeric(10,2),
  (ARRAY['pendiente','pagado','enviado','entregado','cancelado'])[(random()  5)::int + 1]
FROM generate_series(1, 10000) g;

Paso 2 — Query lenta

SELECT c.nombre, c.email, COUNT(p.pedido_id) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE p.fecha_pedido >= '2026-06-01'
GROUP BY c.cliente_id
ORDER BY total DESC
LIMIT 20;

Medir tiempo:

\timing on
-- Correr la query
-- Esperado: 2-5 segundos con 10K pedidos

Paso 3 — Diagnóstico

EXPLAIN ANALYZE
SELECT c.nombre, c.email, COUNT(p.pedido_id) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE p.fecha_pedido >= '2026-06-01'
GROUP BY c.cliente_id
ORDER BY total DESC
LIMIT 20;

Buscar: `Seq Scan on pedidos`, `rows=10000`, tiempo alto.

Paso 4 — Optimizar

-- Crear índice compuesto
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos(cliente_id, fecha_pedido);
-- O simplemente sobre fecha_pedido
CREATE INDEX idx_pedidos_fecha ON pedidos(fecha_pedido);

ANALYZE pedidos;  -- Actualizar estadísticas para el planner

Paso 5 — Re-medir

EXPLAIN ANALYZE
-- Misma query
-- Esperado: < 100 ms

Comparar antes/después. Anotar en el cuaderno.

Paso 6 — Verificar con pg_stat_statements

SELECT
  substring(query for 80) AS q,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE query LIKE '%pedidos%'
ORDER BY mean_exec_time DESC;

Entregables del LAB

• [ ] Captura de la query lenta antes (con `\timing`).
• [ ] EXPLAIN ANALYZE antes con cuello de botella identificado.
• [ ] Índice creado.
• [ ] EXPLAIN ANALYZE después, confirmando uso del índice.
• [ ] Medición del speedup.
• [ ] Entrada en `pg_stat_statements` mostrando la mejora.

Tu base de datos necesita su propio xiuhpohualli: un registro histórico de métricas. No basta con alertar en tiempo real. Necesitas poder decir "hace 6 meses, cuando hicimos el deploy del 15 de febrero, el cache hit ratio bajó del 99% al 92% y se mantuvo así 3 semanas. La causa fue el nuevo índice sobre `pedidos` que no anticipamos"*. Esa memoria operativa es la diferencia entre un DBA que reacciona y uno que anticipa.

Reflexión mexica: el tlamatini no era adivino, era observador disciplinado. La predicción viene del registro, no de la inspiración.

Cierre del cuaderno

Las 4 métricas que vigilo: conexiones, cache hit, slow queries, replicación. Comando para ver queries más lentas en mi base actual. Última vez que añadí un índice: ¿medí antes/después? ____________. ¿Tengo alertas configuradas? Si no, mi primer paso mañana: ____________. ¿Dónde guardo mi "xiuhpohualli" (histórico de métricas)? ____________.