Consideraciones sobre la importación de datos para MariaDB

En el siguiente contenido se encuentra información técnica relacionada con la carga de datos en MariaDB. Este contenido está dirigido a usuarios familiarizados con la arquitectura del servidor de MariaDB.

Registro binario

La habilitación del registro binario reduce el rendimiento de la carga de datos y requiere hasta cuatro veces más espacio en disco en comparación con el registro desactivado. El tamaño de la transacción utilizado para cargar los datos afecta directamente al rendimiento del sistema y a las necesidades de espacio en disco, las transacciones más grandes requieren más recursos.

Tamaño de la transacción

El tamaño de la transacción influye en los siguientes aspectos de las cargas de datos de MariaDB:

• Consumo de recursos

• Utilización del espacio en disco

• Reanudación del proceso

• Tiempo de recuperación

• Formato de entrada (archivos sin formato o SQL)

En esta sección se describe el modo en que el tamaño de transacción afecta al registro binario y se argumentan los beneficios de desactivar el registro binario durante la carga de grandes volúmenes de datos. Puede habilitar y desactivar el registro binario estableciendo el periodo de retención de copia de seguridad automatizado de Amazon RDS. Un valor distinto de cero activa el registro binario y el valor cero lo desactiva. Para obtener más información, consulte Backup retention period (Periodo de retención de copia de seguridad).

En esta sección también se describe el impacto de las transacciones de gran tamaño en InnoDB y por qué es importante que los tamaños de transacción sean pequeños.

Transacciones pequeñas

En las transacciones pequeñas, el registro binario duplica el número de escrituras en disco requeridas para cargar los datos. Este efecto puede degradar el desempeño notablemente en otras sesiones de base de datos y aumentar el tiempo requerido para cargar los datos. La degradación experimentada depende en parte de los siguientes factores:

• Tasa de carga

• Otra actividad de la base de datos que tenga lugar durante la carga

• Capacidad de la instancia de base de datos de Amazon RDS

Los registros binarios también consumen un espacio en disco aproximadamente igual al volumen de datos cargado hasta que se hace una copia de seguridad de los registros y se eliminan. Amazon RDS minimiza este impacto creando copias de seguridad de los registros binarios y retirándolos con frecuencia.

Transacciones grandes

En el caso de transacciones grandes, el registro binario triplica IOPS y el uso del disco por los siguientes motivos:

• La caché del registro binario almacena temporalmente los datos de las transacciones en el disco.

• Esta caché crece con el tamaño de la transacción, lo que consume espacio en disco.

• Cuando se completa la transacción (confirmación o reversión), el sistema copia la caché en el registro binario.

Este proceso crea tres copias de los datos:

• Los datos originales

• La caché en el disco

• La entrada del registro binario final

Cada operación de escritura implica una E/S adicional, lo que repercute aún más en el rendimiento.

Por este motivo, el registro binario requiere el triple de espacio en disco en comparación con el registro desactivado. Por ejemplo, si se cargan 10 GiB de datos en una sola transacción, se crean tres copias:

• 10 GiB para los datos de la tabla

• 10 GiB para la caché del registro binario

• 10 GiB para el archivo del registro binario

El espacio total en disco temporal necesario es 30 GiB.

Consideraciones importantes sobre el espacio en disco:

• El archivo de caché persiste hasta que finaliza la sesión o hasta que una nueva transacción crea otra caché.

• El registro binario permanece hasta que se realiza una copia de seguridad, lo que podría retener 20 GiB (caché y registro) durante un periodo prolongado.

Si se usa LOAD DATA LOCAL INFILE para cargar los datos, la recuperación de datos crea una cuarta copia en caso de que la base de datos deba recuperarse desde una copia de seguridad realizada antes de la carga. Durante la recuperación, MariaDB extrae los datos desde el registro binario en un archivo sin formato. Luego, MariaDB ejecuta LOAD DATA LOCAL INFILE. En función del ejemplo anterior, esta recuperación requiere un espacio total en disco temporal de 40 GiB o 10 GiB cada uno para la tabla, la caché, el registro y el archivo local. Sin al menos 40 GiB de espacio libre en disco, la recuperación produce un error.

Optimización de grandes cargas de datos

Para grandes cargas de datos, desactive el registro binario para reducir la sobrecarga y los requisitos de espacio en disco. Puede desactivar el registro binario estableciendo el periodo de retención de copias de seguridad en 0. Una vez completada la carga, restaure el periodo de retención de copias de seguridad al valor apropiado distinto de cero. Para obtener más información, consulte Modificación de una instancia de base de datos de Amazon RDS y Periodo de retención de copia de seguridad en la tabla de configuración.

nota

Si la instancia de base de datos es una instancia de base de datos de origen para réplicas de lectura, no puede establecer el periodo de retención de copia de seguridad en 0.

Antes de cargar los datos, recomendamos que cree una instantánea de base de datos. Para obtener más información, consulte Administración de copias de seguridad manuales.

InnoDB

La siguiente información sobre las opciones de registro y recuperación para deshacer ayuda a mantener pequeñas las transacciones de InnoDB para optimizar el rendimiento de la base de datos.

Descripción del registro para deshacer InnoDB

Deshacer es un mecanismo de registro que permite la reversión de transacciones y admite el control de concurrencia multiversión (MVCC).

Para MariaDB 10.11 y versiones anteriores, los registros para deshacer se almacenan en el espacio de tablas del sistema InnoDB (normalmente ibdata1) y se retienen hasta que el subproceso de purga los elimina. De este modo, las transacciones de carga de datos de gran tamaño pueden provocar que el espacio de tablas del sistema sea muy grande y consumir espacio en disco que no puede recuperar si no recrea la base de datos.

Para todas las versiones de MariaDB, el subproceso de purga debe esperar para eliminar cualquier registro para deshacer hasta que la transacción activa más antigua se confirme o se revierta. Si la base de datos procesa otras transacciones durante la carga, los registros para deshacer también se acumulan y no pueden eliminarse, incluso si se confirman las transacciones y ninguna otra transacción necesita registros para deshacer MVCC. En esta situación, todas las transacciones, incluidas las de solo lectura, se ralentizan. Esta ralentización se produce porque todas las transacciones acceden a todas las filas que cualquier transacción (no solo la transacción de carga) cambia. En efecto, las transacciones deben examinar los registros para deshacer que las transacciones de carga de larga duración impidieron purgar durante una limpieza del registro para deshacer. Esto afecta al rendimiento de cualquier operación que acceda a las filas modificadas.

Opciones de recuperación de transacciones de InnoDB

Aunque InnoDB optimiza las operaciones de confirmación, las reversiones de transacciones grandes son lentas. Para una recuperación más rápida, realice una recuperación en un momento dado o restaure una instantánea de base de datos. Para obtener más información, consulte Recuperación a un momento dado y Restauración a una instancia de base de datos.

Formatos de importación de datos

MariaDB admite dos formatos de importación de datos: archivos sin formato y SQL. Revise la información sobre cada formato para determinar cuál es la mejor opción para sus necesidades.

Archivos sin formato

Para transacciones pequeñas, cargue archivos sin formato con LOAD DATA LOCAL INFILE. Este formato de importación de datos puede proporcionar los siguientes beneficios en comparación con el uso de SQL:

• Menos tráfico de red

• Costos de transmisión de datos más bajos

• Menor sobrecarga de procesamiento de bases de datos

• Procesamiento más rápido

LOAD DATA LOCAL INFILE carga todo el archivo sin formato en una sola transacción. Mantenga pequeño el tamaño de los archivos individuales para obtener las siguientes ventajas:

• Posibilidad de reanudación: puede hacer un seguimiento de los archivos que se han cargado. Si surge un problema durante la carga, puede continuar donde se detuvo. Es posible que sea necesario volver a transmitir algunos datos a Amazon RDS, pero con los archivos pequeños, el volumen es mínimo.

• Carga de datos en paralelo: si tiene IOPS y ancho de banda de la red suficiente para la carga de un solo archivo, la carga en paralelo podría ahorrar tiempo.

• Control de la velocidad de carga: si la carga de datos tiene un impacto negativo en otros procesos, puede controlar la velocidad de carga aumentando el intervalo entre los archivos.

Las transacciones de gran tamaño reducen los beneficios de usar LOAD DATA LOCAL INFILE para importar datos. Cuando no pueda dividir una gran cantidad de datos en archivos más pequeños, considere la posibilidad de utilizar SQL.

SQL

SQL tiene una ventaja fundamental con respecto a los archivos sin formato: puede mantener pequeños los tamaños de transacción. Sin embargo, SQL puede tardar mucho más en cargar que los archivos sin formato. Además, después de un error, puede ser difícil determinar dónde reanudar, ya que no se pueden reiniciar los archivos mariadb-dump. Si se produce un error durante la carga de un archivo mariadb-dump, debe modificarlo o sustituirlo antes de poder reanudar la carga. Otra opción, después de corregir la causa del error, puede restaurar a un momento anterior a la carga antes de cargar y volver a enviar el archivo. Para obtener más información, consulte Recuperación a un momento dado.

Uso de instantáneas de base de datos de Amazon RDS para puntos de comprobación de la base de datos

Si carga datos durante periodos prolongados, como horas o días, sin registro binario, utilice las instantáneas de base de datos para proporcionar puntos de comprobación periódicos para garantizar la seguridad de los datos. Cada instantánea de base de datos crea una copia coherente de la instancia de base de datos que sirve como punto de recuperación durante errores del sistema o eventos de corrupción de datos. Como las instantáneas de base de datos son rápidas, los puntos de comprobación frecuentes tienen un impacto mínimo en el rendimiento de la carga. Puede eliminar las instantáneas de base de datos anteriores sin que ello afecte a la durabilidad de la base de datos ni a las capacidades de recuperación. Para obtener más información acerca de las instantáneas de base de datos, consulte Administración de copias de seguridad manuales.

Reducción de los tiempos de carga de bases de datos

Los siguientes elementos son consejos adicionales para reducir los tiempos de carga:

• Cree todos los índices secundarios antes de cargar los datos en las bases de datos MariaDB. A diferencia de otros sistemas de bases de datos, MariaDB reconstruye toda la tabla al agregar o modificar índices secundarios. Este proceso crea una nueva tabla con los cambios de índice, copia todos los datos y elimina la tabla original.

• Cargue los datos en el orden de la clave principal. Para las tablas de InnoDB, esto puede reducir los tiempos de carga entre un 75 % y un 80 % y reducir el tamaño del archivo de datos en un 50 %.

• Desactive las restricciones de clave externa estableciendo foreign_key_checks en 0. Esto es obligatorio en muchos casos para archivos sin formato cargados con LOAD DATA LOCAL INFILE. Para cualquier carga, desactivar las comprobaciones de claves externas acelera la carga de datos. Una vez completada la carga, vuelva a habilitar las restricciones configurando foreign_key_checks en 1 y verifique los datos.

• Cargue datos en paralelo a no ser que se aproxime al límite de los recursos. Para habilitar la carga simultánea en varios segmentos de la tabla, use tablas particionadas cuando sea apropiado.

• Para reducir la sobrecarga de ejecución de SQL, combine varias instrucciones INSERT en operaciones INSERT únicas con varios valores. mariadb-dump implementa esta optimización automáticamente.

• Reduzca las operaciones de E/S de registro de InnoDB estableciendo innodb_flush_log_at_trx_commit en 0. Una vez completada la carga, restaure innodb_flush_log_at_trx_commit a 1.

  aviso

  Si se establece innodb_flush_log_at_trx_commit en 0, InnoDB vaciará sus registros cada segundo en lugar de hacerlo con cada confirmación. Esta configuración aumenta el rendimiento, pero puede conllevar el riesgo de perder transacciones si se produce un error en el sistema.

• Si va a cargar datos en una instancia de base de datos que no tiene réplicas de lectura, establezca sync_binlog en 0. Una vez completada la carga, restaure sync_binlog parameter a 1.

• Cargue los datos en una instancia de base de datos de Single-AZ antes de convertir la instancia de base de datos en una implementación Multi-AZ. Si la instancia de base de datos ya usa una implementación Multi-AZ, no se recomienda cambiar a una implementación Single-AZ para la carga de datos. Hacerlo solo proporciona mejoras marginales