Trabajar con Bases de Datos Grandes

Los jobs que hacen pump, load o dump de bases de datos de muchos terabytes pueden durar horas o días. En ese tiempo es probable que haya un reinicio del servidor, un corte de red o un bloqueo en la base de datos. Esta guía explica cómo lee y escribe un job una tabla grande, cómo sigue su progreso, cómo continúa tras un fallo sin volver a cargarlo todo y qué hay que preparar para que lo haga.

Casi todo funciona solo. Lo que tú controlas es:

• la configuración de jobs del servidor (sección jobs:),
• las opciones del job en la interfaz (Concurrencia, Opciones de carga, Determinismo, Diccionario),
• las propias tablas (claves primarias, estadísticas de la base de datos).

Lectura de una tabla grande

Un lector SQL lee una tabla de una de estas dos formas:

Bloques por clave (por defecto cuando la tabla tiene una clave utilizable)

  SELECT ... WHERE key > last_key ORDER BY key LIMIT 10000   -- bloque 1
  SELECT ... WHERE key > last_key ORDER BY key LIMIT 10000   -- bloque 2
  ...                                                         -- hasta vaciarse

Un solo cursor

  SELECT ... FROM table                                       -- una consulta larga

• Bloques por clave (keyset): la tabla se lee en el orden de su clave, 10.000 filas por consulta, y cada consulta empieza después de la última clave leída. Cada consulta es corta, así que ninguna ocupa la base de datos durante horas, y el job siempre sabe a qué posición ha llegado. Esto es lo que permite a un job reanudarse y leer una tabla en rangos paralelos.
• Un solo cursor: una consulta devuelve toda la tabla. Es igual de rápido, pero un job que se detiene a mitad no tiene una posición desde la que continuar: la tabla vuelve a empezar.

La clave es la clave primaria de la tabla, con columnas de tipo numérico o de texto. Los índices únicos no se usan como clave. Las tablas sin una clave primaria utilizable recurren a una posición física de la fila cuando la base de datos la tiene:

Tablas con un solo cursor

Aunque tenga clave, una tabla se lee con un solo cursor, y no puede reanudarse ni dividirse, cuando el job:

• la lee con una consulta personalizada o con un orden personalizado,
• la lee desde un offset, o toma los últimos registros o registros aleatorios de un límite,
• lee un datasource PostgreSQL con snapshotRead (una instantánea consistente necesita una sola consulta),
• sintetiza la entidad en lugar de leerla.

Conteo de filas y progreso

La barra de progreso de una entidad compara los registros leídos con su total. Para mostrarla sin contar antes una tabla enorme, el job usa la estimación de la base de datos:

La estimación puede no ser exacta, así que el porcentaje es aproximado. Una lectura filtrada (un where o una consulta personalizada) puede ser mucho más pequeña que la tabla, así que en ese caso el job también ejecuta un COUNT exacto cuando la tabla se estima por debajo de 5.000.000 de filas. Se ejecuta a la vez que el stream cuando el driver tiene una conexión libre, y antes del stream si no. Las tablas filtradas más grandes no se cuentan, porque contarlas podría llevar tanto como leerlas: su total sigue siendo aproximado.

Sin estadísticas no hay estimación, el total pasa a ser el tamaño del último escaneo y puede que la tabla no se divida en rangos. Ejecuta ANALYZE (PostgreSQL), recoge estadísticas (Oracle) o vuelve a escanear el modelo antes de un job grande.

Progreso, heartbeats y jobs atascados

Un job informa de su progreso a medida que lee y escribe. Los pasos largos que no mueven registros (contar, estimar, dividir una tabla, tomar una instantánea, reconstruir constraints, guardar el diccionario) envían heartbeats en su lugar, que también aparecen en el log como mucho una vez por minuto por paso, por ejemplo:

estimating orders...
splitting orders...

Dos mecanismos vigilan un job en ejecución:

• El temporizador de atasco: un job sin progreso, sin heartbeat y sin línea de log durante jobs.stallTimeoutSec segundos (15 minutos por defecto) se detiene y termina failed con job stalled: no progress for N seconds. Detecta una consulta bloqueada por un lock o una conexión muerta.
• El lease: el worker que ejecuta el job lo renueva cada jobs.heartbeatSec segundos. Cuando el worker muere, los demás workers terminan el job, o lo reanudan con jobs.autoResume. Consulta Jobs interrumpidos y Leases y heartbeats.

Un job cuenta como completado solo cuando todas sus entidades se han completado: una entidad fallida, un atasco o un error fatal lo terminan failed, aunque Continuar reportando después de fallar haya dejado terminar a las demás entidades.

Reanudar un job fallido o interrumpido

Reiniciar sobre un job de pump, load o dump fallido lo ejecuta de nuevo y continúa donde se detuvo (consulta Reiniciar jobs fallidos). Con jobs.autoResume: true ocurre lo mismo automáticamente cuando muere el worker del job o se detiene el servidor.

Checkpoints

Cada vez que se confirma un lote en el sink, el job guarda en MongoDB un checkpoint de la entidad: la última clave (o posición) leída y el número de registros confirmados. Antes de cada checkpoint se guardan también los valores nuevos del diccionario, de modo que una entidad reanudada enmascara los registros posteriores al checkpoint con los mismos valores que antes.

orders   [========== confirmados ==========|-- pendiente --|          ]
                                           ^
                             checkpoint: clave 48.210.000, 48,2M confirmados

Reinicio: el sink tiene 48,2M filas -> leer desde la clave 48.210.001, sin truncar

Al reiniciar:

1. Las entidades completadas se saltan.
2. Para cada una de las demás, el job cuenta las filas del sink y las compara con el checkpoint. Un lote que se estaba escribiendo cuando el job se detuvo puede haber entrado o no: el job cuenta el rango de claves de ese lote para saberlo.
3. Si coinciden, la entidad continúa tras el checkpoint, sin truncar ni eliminar la tabla. El log dice resuming orders after record 48210001 (48210000 committed).
4. Si no, la entidad vuelve a empezar: se vacía y se lee desde el principio. El log dice cannot resume orders, it starts over: ... con el motivo.

Qué vuelve a empezar

Una entidad vuelve a empezar en lugar de reanudarse cuando:

Un dump a un dataset puede saltar las entidades que terminó, pero una entidad que estaba escribiendo vuelve a empezar, porque un dataset no puede contar sus filas.

Qué no se reinicia

• El diccionario del job se conserva, no se sobrescribe: los valores enmascarados en la primera ejecución mantienen su valor enmascarado. Sin la caché, el job guarda su diccionario en un archivo del directorio temporal del host (gig-dict-<jobId>.db) mientras todavía pueda reiniciarse, y lo borra cuando el job termina bien o empieza de cero.
• La cuenta de reanudaciones automáticas (jobs.maxAutoResumes) se mantiene entre reinicios. Volver a ejecutar crea un job nuevo desde cero y la pone a cero.

Leer una tabla grande en rangos paralelos

Con una Concurrencia mayor que 1, un job procesa varias entidades a la vez. Una tabla lo bastante grande se divide además en rangos de su clave, que leen y escriben en paralelo pipelines distintos:

orders (12M filas, Concurrencia 4, rangeRows 1.000.000)

  clave 1 ........ 3,1M ......... 6,0M ......... 9,2M ......... 12M
       [ rango 1   ][   rango 2   ][   rango 3   ][   rango 4    ]
         pipeline     pipeline       pipeline       pipeline

• El número de rangos es la Concurrencia del job (o como mucho rangeParts), pero no más de un rango por cada 1.000.000 de filas estimadas (rangeRows). Una tabla necesita al menos 2.000.000 de filas estimadas para dividirse. Con workers auxiliares, una tabla se divide en hasta Concurrencia × (1 + jobs.shardWorkers) rangos.
• Los límites salen de una muestra de la clave (consulta la tabla de Lectura de una tabla grande), así que los rangos tienen más o menos el mismo número de filas. El log dice reading orders in 4 ranges.
• Los rangos comparten los huecos de Concurrencia con las demás entidades del job.
• El primer rango que escribe abre y vacía la tabla del sink antes de que escriban los demás.
• Cada rango guarda sus propios checkpoints. Un reinicio reanuda cada rango desde su checkpoint, así que todos los rangos deben tener uno.
• Con un diccionario, los rangos que encuentran a la vez el mismo valor original se quedan con el primer valor enmascarado guardado, de modo que el mismo valor se enmascara igual en todos los rangos.

jobs.concurrent fija una Concurrencia mínima para todos los jobs del servidor, así que puedes activar los rangos sin editar las opciones de cada regla.

Cuándo se lee una tabla entera

Una tabla la lee un solo pipeline, aunque sea grande, cuando:

• se lee con un solo cursor, o por posición del registro (datasets),
• el sink escribe con un único escritor: datasets, almacenamiento de archivos, DB2 for i por SSH,
• el sink es DB2 (conexiones ibm_db o js400) y el modo de carga es merge o update, que pasan por una única tabla temporal,
• reanuda una ejecución anterior que la leyó en un solo rango,
• tiene un límite, una consulta personalizada o un offset,
• el job usa el modo Determinista o la entidad una semilla,
• se sintetiza o se aprende,
• una transformación depende del orden de los registros: números secuenciales, valores elegidos de una lista, un Shuffle, valores únicos.

Si la base de datos no puede muestrear la clave, el log dice orders is read in one range, its key could not be split y el job continúa con un solo pipeline.

Workers auxiliares para pumps

Un job worker es un proceso Node.js y ejecuta un job a la vez. En un pump cuyo cuello de botella es ese proceso (el enmascaramiento consume CPU), otros job workers del mismo host pueden procesar rangos del mismo job. Configura jobs.shardWorkers con el número de auxiliares por pump y arranca suficientes job workers con gig start -j=N (consulta start).

job de pump (worker 1)     cola de unidades en MongoDB    auxiliares (pump-shard@host)
  planifica los rangos --> [orders 1][orders 2][orders 3]  <--- worker 2
  procesa unidades         [orders 4][customers][...]      <--- worker 3
  suma el progreso

• Los auxiliares solo se ejecutan en el host del pump, porque comparten el archivo de diccionario del job (SQLite en modo WAL).
• Escriben en el log del pump, con el prefijo [shard worker N], y no aparecen en las listas de jobs.
• Si un auxiliar muere, sus unidades fallan y el pump termina failed: reinícialo para reanudarlas.
• Los auxiliares solo se usan en pumps hacia datasources PostgreSQL, MySQL, Oracle o MongoDB, y no con los modos de carga merge o update.

Consulta Workers auxiliares de shards.

Escribir en PostgreSQL con COPY

Los sinks PostgreSQL cargan cada lote con COPY FROM STDIN en lugar de sentencias INSERT (jobs.copy, activado por defecto). Los modos de carga merge y update copian a una tabla temporal de staging y la aplican en la misma transacción. Un lote que COPY no puede cargar se inserta con INSERT; tras 3 lotes así, la tabla continúa con INSERT. Las tablas con columnas de tipo compuesto siempre usan INSERT. Consulta Escrituras COPY en Postgres.

Constraints y triggers

Cargar una tabla grande con sus claves foráneas y triggers activos es lento y depende del orden de las tablas. Cada driver los desactiva solo para las tablas del job y los vuelve a activar al final:

• PostgreSQL: elimina las constraints de las tablas del job y las claves foráneas de otras tablas que apuntan a ellas, y guarda sus definiciones en gigantics_meta.dropped_constraints en el sink. Si el job muere antes de restaurarlas, el siguiente job sobre ese sink las restaura. Las tablas se vacían con TRUNCATE. Consulta PostgreSQL.
• SQL Server: desactiva las constraints y los triggers de las tablas del job (NOCHECK CONSTRAINT, DISABLE TRIGGER) y deja sin comprobar las claves foráneas de otras tablas que apuntan a ellas, y después las vuelve a activar todas. La opción del datasource trustConstraints (desactivada por defecto) vuelve a validar las filas al activarlas, para que SQL Server vuelva a confiar en ellas, a costa de una comprobación completa. Consulta SQL Server.
• MySQL: vacía las tablas con TRUNCATE con FOREIGN_KEY_CHECKS=0. Consulta MySQL.

Volver a crear las constraints de tablas de cientos de millones de filas puede llevar horas. Un job fallido o cancelado lo espera como mucho jobs.finishTimeoutSec segundos. Consulta también Oracle y MongoDB.

Logs de los jobs y diccionario

Los jobs grandes escriben mucho, en dos sitios:

• Los logs de los jobs se guardan en MongoDB. Con el nivel de traza por defecto 1, no se guardan el SQL de cada consulta ni las líneas de cada lote. Por defecto se borran a los 90 días (jobs.logRetentionDays o jobLogRetentionDays). La vista del log carga las 2.000 líneas más recientes y más bajo demanda.
• El diccionario de un job (cuando enmascara con uno) se guarda en un archivo SQLite del host mientras el job se ejecuta, y sus valores nuevos se guardan en MongoDB cada 100.000 valores o 60 segundos, y antes de cada checkpoint, en lugar de todos al final, de modo que un job reanudado encuentra los valores enmascarados de los lotes que confirmó. Asegúrate de que el directorio temporal del host tiene espacio para el diccionario de tu job más grande. Consulta Planificación del espacio en disco.

Dónde se configuran estas opciones

Opciones de lectura avanzadas

Las reglas de los jobs de Gigantics usan los valores por defecto de estas opciones. Solo pueden cambiarse en la sección rules: de un archivo debaser.yml usado directamente con el motor debaser (gig debaser):

rules:
  big-pump:
    concurrent: 8
    # auto: contar lecturas filtradas de menos de 5M filas; always; never
    exactCount: never
    # auto: bloques por clave si la tabla tiene clave; cursor: una consulta
    readMode: auto
    # filas por consulta de bloque (por defecto 10000)
    readChunkRows: 50000
    # un rango por cada tantas filas estimadas (por defecto 1000000, 0: nunca dividir)
    rangeRows: 500000
    # máximo de rangos por tabla (por defecto: concurrent)
    rangeParts: 8

readMode: cursor fuerza una sola consulta por tabla: esas tablas no pueden reanudarse ni dividirse.

Lista de comprobación

Antes de un pump, load o dump grande:

1. Claves primarias: asegúrate de que las tablas grandes tienen una clave primaria con columnas numéricas o de texto. Las tablas sin ella se leen con un solo cursor (salvo en PostgreSQL 14 o posterior, SQLite y MongoDB) y vuelven a empezar tras un fallo.
2. Estadísticas: ejecuta ANALYZE (PostgreSQL) o recoge estadísticas (Oracle) para que las estimaciones, el progreso y los rangos sean correctos. En PostgreSQL 14 o posterior, vuelve a escanear el modelo si el log lo pide.
3. Concurrencia: pon la Concurrencia a 2 o más (o jobs.concurrent) para que las tablas grandes se lean en rangos, dentro de las conexiones que aceptan tus bases de datos.
4. Modo aleatorio: en tablas enormes evita el modo Determinista, los Shuffle, los límites y las consultas personalizadas, que desactivan la reanudación, los rangos o ambos.
5. Modo de carga: prefiere Truncar. Una entidad en Añadir que no puede reanudarse falla en lugar de volver a empezar.
6. Reanudación automática: configura jobs.autoResume: true para que los reinicios y despliegues no terminen los jobs largos.
7. Workers auxiliares: para pumps limitados por CPU, configura jobs.shardWorkers y arranca suficientes job workers (gig start -j=N).
8. Disco y logs: deja espacio en el directorio temporal para el archivo del diccionario y configura jobs.logRetentionDays.
9. Timeouts: sube jobs.stallTimeoutSec si las consultas del origen tardan mucho en dar sus primeras filas, y jobs.finishTimeoutSec si reconstruir las constraints puede llevar más de un día.

Cuando un job falla, abre su log para ver por qué, corrige la causa y usa Reiniciar (no Volver a ejecutar) para continuar desde los checkpoints. Revisa las líneas resuming ... y cannot resume ... del principio del log para ver qué entidades continúan y cuáles vuelven a empezar.

Ver También

• Configuración de Jobs
• Jobs
• Shuffle
• Planificación del espacio en disco