Conceptos de ajuste de BD: bloqueos, caché, índices, checkpoint, memoria

Resumen — qué hace

Servicio ▸ Base de datos se conecta directamente a los recolectores de BD registrados (access=database) y muestra métricas de carga y de estructura en vivo. En lugar de limitarse a listar números, adjunta un popover educativo diseñado a cada métrica clave (espera de bloqueo, tasa de acierto de caché, uso de índices, checkpoint, páginas sucias), de modo que aprenda «por qué este valor es malo / cómo lo corrijo» justo donde lo está viendo.

Este artículo resume los conceptos que enseñan esos popovers. El léxico completo reside con una única fuente en los propios popovers en vivo (resources/lang/{ko,en}/dbmon.php, la clave edu), así que en una situación real pase el cursor por la métrica para leer su detalle específico del contexto.

La pantalla es el monitor (qué va mal ahora), el popover es la formación sobre la causa (por qué / cómo) y la instantánea real de la configuración vive en parámetros de instancia ([param] · nginx / my.cnf / sysctl). Lea los tres juntos para pasar de diagnóstico → evidencia → acción.

Cuándo usarlo

• Una tarjeta de BD muestra Degraded o Down y quiere entender el motivo (saturación de conexiones, alta concurrencia, cola de consultas, tasa creciente de consultas lentas).
• La puntuación de tasa de acierto de caché o de uso de índices es baja y quiere conocer la causa raíz y las palancas de mejora (tamaño del buffer pool, añadir índices, índices de cobertura).
• La espera de bloqueo de fila se dispara y necesita acotar qué consulta o patrón está generando la contención.
• El detalle de gestión de memoria muestra que las ondas de páginas sucias / checkpoint se acercan a la zona de riesgo y quiere una dirección de ajuste (buffer pool o log de redo más grandes).
• Incorporación de DBA / DevOps: aprender las interioridades de InnoDB (bloqueos, buffer pool, redo) sobre datos reales.

Disposición

Dónde se enganchan los popovers educativos:

El propio cuerpo de la tarjeta resume dos ejes de carga:

• Carga de concurrencia — saturación de conexiones (indicador anular) · Running (threads_running) · Backlog (waiting) · lock.
• Rendimiento — minigráfico Total QPS · Slow rate (slow_qps) · Slow (total).

El análisis de esquema inferior (plegable) muestra el tamaño total, los esquemas mayores, las tablas más grandes y la mezcla de motores (ajeno a los conceptos de ajuste, procedente de information_schema).

Cómo se usa

1. En una tarjeta de BD, localice la métrica problemática (distintivo rojo/amarillo o un valor alto).
2. Pase el cursor por la etiqueta o el distintivo de esa métrica para abrir el popover contextual: aparecen la causa, la fórmula y el cómo hacerlo.
3. Despliegue Detalles para inspeccionar la serie temporal de gestión de memoria con las ondas de sucias/checkpoint y las líneas de corte de los umbrales.
4. Aplique en la BD de destino la palanca de configuración que señale el popover (por ejemplo innodb_buffer_pool_size, añadir un índice), o contrástela con el valor actual en parámetros de instancia.

A continuación tiene una referencia resumida de cada concepto (el popover en vivo es la fuente autorizada).

Espera de bloqueo de fila — 7 causas

Tiempo medio que InnoDB esperó para adquirir un bloqueo de fila (ms). Cuanto mayor, más contención. Por frecuencia:

En esta pantalla, la causa 3 (UPDATE sin índice) es el motor oculto número 1 de los picos de espera de bloqueo. Lea la sección TRANSACTIONS de SHOW ENGINE INNODB STATUS junto con la puntuación de uso de índices.

Tasa de acierto de caché (buffer pool de InnoDB)

Alta cuando la mayoría de las lecturas se sirven desde memoria (el buffer pool) y no desde disco.

hit ratio = 1 − Δinnodb_buffer_pool_reads / Δinnodb_buffer_pool_read_requests

Cómo elevarla (resumen): aumente innodb_buffer_pool_size (regla empírica: 50–75% de la RAM) · reduzca los escaneos completos para no contaminar el buffer pool · reduzca el conjunto de trabajo (evite columnas no usadas, separe BLOB grandes, archive) · use innodb_buffer_pool_instances para recortar la contención y vuelque/recargue el buffer pool al reiniciar.

Uso de índices

Proporción de lecturas que encontraron filas por índice frente a lecturas que escanearon hasta la fila siguiente.

index usage = Δhandler_read_key / (Δhandler_read_key + Δhandler_read_rnd_next)

Cómo elevarlo (resumen): use EXPLAIN para encontrar escaneos completos (type=ALL) y ordenaciones en fichero, y luego indexe las columnas de WHERE/JOIN/ORDER BY · construya índices compuestos empezando por la columna más selectiva (regla del prefijo por la izquierda) · no anule los índices con funciones o conversiones sobre la columna (WHERE DATE(col)=… → use un rango) · use índices de cobertura · elimine índices duplicados o sin uso.

Antigüedad del checkpoint (presión del log de redo)

Cuánto ha avanzado el log de redo más allá del último checkpoint (bytes) = los cambios aún no volcados a los ficheros de datos y el trabajo que InnoDB reproduciría tras una caída. El % of redo de la cabecera es la distancia hasta el precipicio de la tormenta de volcado.

Cómo leerlo: sube = escrituras > volcado (el redo se llena) · baja = el volcado se ha puesto al día (sano) · diente de sierra = acumular ↔ volcar, un ritmo normal. Cerca del 75%, considere un buffer pool más grande o un innodb_log_file_size mayor.

Volcado de páginas sucias (gestión de memoria)

Páginas del buffer pool modificadas pero aún no escritas a disco. El valor apropiado son los propios umbrales de volcado del servidor (innodb_max_dirty_pages_pct = MAX, innodb_max_dirty_pages_pct_lwm = LWM). Los expertos: las páginas sucias deberían oscilar por debajo del LWM.

La serie temporal de gestión de memoria superpone las líneas de corte LWM/MAX sobre las páginas sucias y marca los episodios de expulsión y volcado. Las métricas de la derecha muestran además Pending writes y Row-lock avg.

Fuentes de datos

Los contadores y el almacenamiento reales a los que remiten los conceptos educativos:

• La serie temporal se almacena en infr_db_metric (migración 000681). App\Services\Database\DbCollectorService se conecta directamente a la BD registrada, lee SHOW GLOBAL STATUS + @@max_connections + processlist y calcula las métricas derivadas como delta respecto a la muestra anterior.
• La recolección se ejecuta mediante el comando infr:collect-db (planificado cada minuto por defecto, con salida en un solo disparo). Al entrar en la página, si la última muestra está caducada, ensureFresh() toma de inmediato una muestra puntual.
• Intervalo de sondeo de la pantalla: 30 s. Para evitar fugas de conexiones, el recolector usa DB::purge en finally más un tiempo de espera de PDO.

Umbrales de estado de la tarjeta (degraded) (valores por defecto de OpsPolicy::dbThresholds(); ajústelos en Política de operaciones):

Acceso

• Lectura (ver pantalla y popovers): demo (nivel 1) en adelante. Los popovers son contenido puramente educativo: sin restricciones adicionales ni auditoría.
• Ajustar los umbrales de estado de BD: se gestionan en Política de operaciones — administrador (nivel 5/10 · infr.level:N).
• Registrar o editar recolectores de BD: Recolectores, administrador (nivel 5/10). Las contraseñas se guardan cifradas (Crypt) en el recolector, o se reutiliza un juego de credenciales de .env mediante cred_ref = env:PREFIX (nunca codificadas en el código).

Resolución de problemas y preguntas frecuentes

P. Backlog muestra «own connections only». Contar las consultas en cola en todo el servidor requiere el privilegio PROCESS en la cuenta de BD. Sin él solo se cuentan sus propias conexiones. Consulte sql/infr_db_monitor_grant.sql (se recomienda una cuenta de monitorización dedicada).

P. Las puntuaciones de caché o índices aparecen vacías (N/A). Las métricas derivadas se calculan como el delta entre dos muestras. Si el recolector acaba de arrancar o los contadores se han reiniciado (un reinicio del servidor), la primera muestra no produce puntuación: espere al siguiente ciclo.

P. Una tarjeta aparece como stale (sin muestra reciente). En modo economy la recolección se omite cuando no hay ningún usuario en la consola. Al entrar, ensureFresh(45s) toma una muestra inmediata, pero si el planificador va retrasado la tarjeta puede degradarse brevemente a pending. Para recolección permanente, ponga el modo de recolección de BD en always en Política de operaciones.

P. ¿Cómo traduzco o edito el texto de los popovers? El léxico educativo reside en un único lugar: la clave edu de resources/lang/{ko,en}/dbmon.php (fuente única). El resto de idiomas recurre a en.

P. ¿Cómo acoto de verdad la causa de una espera de bloqueo? En la BD de destino, inspeccione SHOW ENGINE INNODB STATUS (TRANSACTIONS · LATEST DETECTED DEADLOCK) e information_schema.INNODB_TRX / data_locks, y contrástelo con la tabla de 7 causas de arriba.

Relacionado

• Servicio ▸ Base de datos — la pantalla en vivo de carga y estructura donde aparecen estos conceptos
• Conceptos de ajuste de Redis — los popovers educativos equivalentes para Sesiones (Redis): capacidad de memoria, tasa de acierto, fragmentación
• Recolectores de BD — la canalización de recolección por conexión directa a las BD registradas
• Política de operaciones — ajuste los umbrales de estado de BD y el modo de recolección
• Parámetros de instancia — la instantánea real de valores de ajuste (my.cnf, etc.), la evidencia para el ajuste con IA