Considerazioni sull'importazione di dati per MariadB

Il seguente contenuto contiene informazioni tecniche relative al caricamento dei dati in MariaDB. Questo contenuto è rivolto agli utenti che hanno familiarità con l'architettura del server MariaDB.

Registrazione binaria

L'attivazione della registrazione binaria riduce le prestazioni di caricamento dei dati e richiede fino a quattro volte più spazio su disco rispetto alla registrazione disattivata. Le dimensioni delle transazioni utilizzate per caricare i dati influiscono direttamente sulle prestazioni del sistema e sulle esigenze di spazio su disco: transazioni di grandi dimensioni richiedono più risorse.

Dimensioni delle transazioni

La dimensione della transazione influenza i seguenti aspetti dei caricamenti di dati MariadB:

• Consumo di risorse

• Utilizzo dello spazio su disco

• Riprendi il processo

• È ora di recuperare

• Formato di input (file flat o SQL)

Questa sezione descrive in che modo le dimensioni della transazione incidono sul log binario e spiega perché sia conveniente disattivare il logo binario durante il caricamento di grandi quantità di dati. Puoi abilitare e disabilitare la registrazione binaria impostando il periodo di conservazione dei backup automatizzati di Amazon RDS. Un valore pari a zero disattiva il log binario, mentre qualsiasi altro valore lo attiva. Per ulteriori informazioni, consulta Backup retention period (Periodo di retention dei backup).

Questa sezione descrive anche l'impatto delle transazioni di grandi dimensioni su InnoDB e perché è importante mantenere piccole le dimensioni delle transazioni.

Transazioni di piccole dimensioni

Nel caso delle transazioni di piccole dimensioni, i log binari raddoppiano il numero di scritture su disco richieste per il caricamento dei dati. Tale effetto può incidere molto negativamente sulle prestazioni di altre sessioni di database e allungare i tempi richiesti per il caricamento dei dati. Il degrado subito dipende in parte dai seguenti fattori:

• Velocità di caricamento

• Altre attività del database che si svolgono durante il caricamento

• Capacità dell'istanza database Amazon RDS

Inoltre, i log binari occupano uno spazio su disco all'incirca pari alla quantità di dati caricati fino al backup e alla rimozione dei log. Amazon RDS riduce al minimo questo problema eseguendo spesso il backup e la rimozione dei log binari.

Transazioni di grandi dimensioni

Per transazioni di grandi dimensioni, la registrazione binaria triplica gli IOPS e l'utilizzo del disco per i seguenti motivi:

• La cache del registro binario archivia temporaneamente i dati delle transazioni su disco.

• Questa cache cresce con la dimensione della transazione, che consuma spazio su disco.

• Al termine della transazione (commit o rollback), il sistema copia la cache nel registro binario.

Questo processo crea tre copie dei dati:

• I dati originali

• La cache su disco

• L'ultima voce del log binario

Ogni operazione di scrittura comporta un I/O aggiuntivo, con un ulteriore impatto sulle prestazioni.

Per questo motivo, la registrazione binaria richiede il triplo dello spazio su disco rispetto alla registrazione disabilitata. Ad esempio, il caricamento di 10 GiB di dati come singola transazione crea tre copie:

• 10 GiB per i dati della tabella

• 10 GiB per la cache dei log binari

• 10 GiB per il file di registro binario

Lo spazio temporaneo totale richiesto su disco è di 30 GiB.

Considerazioni importanti sullo spazio su disco:

• Il file di cache persiste fino al termine della sessione o fino alla creazione di un'altra cache da parte di una nuova transazione.

• Il log binario rimane attivo fino a quando non viene eseguito il backup, e può contenere 20 GiB (cache e log) per un periodo prolungato.

Se si utilizza LOAD DATA LOCAL INFILE per caricare i dati, Data Recovery crea una quarta copia nel caso in cui il database debba essere ripristinato da un backup eseguito prima del caricamento. Durante il ripristino, MariadB estrae i dati dal registro binario in un file flat. MariaDB viene quindi eseguito. LOAD DATA LOCAL INFILE Basandosi sull'esempio precedente, questo ripristino richiede uno spazio su disco temporaneo totale di 40 GiB o 10 GiB ciascuno per tabella, cache, registro e file locale. Senza almeno 40 GiB di spazio libero su disco, il ripristino non riesce.

Ottimizzazione di carichi di dati di grandi dimensioni

Per carichi di dati di grandi dimensioni, disabilita la registrazione binaria per ridurre il sovraccarico e i requisiti di spazio su disco. È possibile disabilitare la registrazione binaria impostando il periodo di conservazione del backup su 0. Al termine del caricamento, ripristina il periodo di conservazione del backup sul valore diverso da zero appropriato. Per ulteriori informazioni, vedere Modifica di un'istanza Amazon RDS DB Periodo di conservazione del Backup nella tabella delle impostazioni.

Nota

Se l'istanza DB è un'istanza DB di origine per le repliche di lettura, non è possibile impostare il periodo di conservazione del backup su 0.

Prima di caricare i dati, ti consigliamo di creare uno snapshot del DB. Per ulteriori informazioni, consulta Gestione dei backup manuali.

InnoDB

Le seguenti informazioni sulle opzioni di annullamento della registrazione e ripristino supportano il mantenimento di transazioni InnoDB di dimensioni ridotte per ottimizzare le prestazioni del database.

Comprendere l'undo logging di InnoDB

Undo è un meccanismo di registrazione che consente il rollback delle transazioni e supporta il controllo simultaneo multiversione (MVCC).

Per MariaDB 10.11 e versioni precedenti, i log di annullamento vengono archiviati nel tablespace del sistema InnoDB (di solito ibdata1) e vengono conservati fino a quando il thread di eliminazione non li rimuove. Di conseguenza, le transazioni di caricamento di dati di grandi dimensioni possono far sì che il tablespace di sistema diventi piuttosto grande e consuma spazio su disco che non è possibile recuperare a meno che non si ricrea il database.

Per tutte le versioni di MariaDB, il thread di eliminazione deve attendere la rimozione di eventuali registri di annullamento fino al completamento o al ripristino della transazione attiva più vecchia. Se il database sta elaborando altre transazioni durante il caricamento, anche i relativi registri di annullamento si accumulano e non possono essere rimossi, anche se le transazioni vengono salvate e nessun'altra transazione richiede i log di annullamento per MVCC. In questa situazione, tutte le transazioni, incluse quelle di sola lettura, rallentano. Questo rallentamento si verifica perché tutte le transazioni accedono a tutte le righe modificate da qualsiasi transazione, non solo dalla transazione di caricamento. In effetti, le transazioni devono scansionare i log di annullamento che impediscono che le transazioni di carico a lunga durata venissero eliminate durante una pulizia del registro di annullamento. Ciò influisce sulle prestazioni di qualsiasi operazione di accesso alle righe modificate.

Opzioni di ripristino delle transazioni InnoDB

Sebbene InnoDB ottimizzi le operazioni di commit, i rollback di transazioni di grandi dimensioni sono lenti. Per un ripristino più rapido, esegui un point-in-time ripristino o ripristina un'istantanea del DB. Per ulteriori informazioni, consultare Point-in-time ripristino e Ripristino su un'istanza DB.

Formati di importazione dei dati

MariaDB supporta due formati di importazione dei dati: file flat e SQL. Consulta le informazioni su ciascun formato per determinare l'opzione migliore per le tue esigenze.

File flat

Per transazioni di piccole dimensioni, carica file flat conLOAD DATA LOCAL INFILE. Questo formato di importazione dei dati può offrire i seguenti vantaggi rispetto all'utilizzo di SQL:

• Meno traffico di rete

• Riduzione dei costi di trasmissione dei dati

• Riduzione del sovraccarico di elaborazione del database

• Elaborazione più rapida

LOAD DATA LOCAL INFILEcarica l'intero file flat come un'unica transazione. Mantieni piccole le dimensioni dei singoli file per ottenere i seguenti vantaggi:

• Funzionalità di ripristino: è possibile tenere traccia dei file caricati. Se si verifica un problema durante il caricamento, puoi riprendere da dove avevi interrotto. Potrebbe essere necessario ritrasmettere alcuni dati ad Amazon RDS, ma con file di piccole dimensioni, la quantità ritrasmessa è minima.

• Caricamento parallelo dei dati: se disponi di IOPS e larghezza di banda di rete sufficienti per il caricamento di un singolo file, il caricamento in parallelo potrebbe far risparmiare tempo.

• Controllo della velocità di caricamento: se il caricamento dei dati ha un impatto negativo su altri processi, è possibile controllare la velocità di caricamento aumentando l'intervallo tra i file.

Le transazioni di grandi dimensioni riducono i vantaggi dell'utilizzo LOAD DATA LOCAL INFILE per l'importazione dei dati. Quando non riesci a suddividere una grande quantità di dati in file più piccoli, prendi in considerazione l'utilizzo di SQL.

SQL

SQL ha un vantaggio principale rispetto ai file flat: puoi facilmente mantenere piccole le dimensioni delle transazioni. Tuttavia, il caricamento di SQL può richiedere molto più tempo rispetto ai file flat. Inoltre, dopo un errore, può essere difficile determinare da dove riprendere: non è possibile riavviare i file mariadb-dump. Se si verifica un errore durante il caricamento del file mariadb-dump, è necessario modificare o sostituire il file prima che il caricamento possa riprendere. Oppure, in alternativa, dopo aver corretto la causa dell'errore, è possibile ripristinare la situazione precedente al caricamento e inviare nuovamente il file. Per ulteriori informazioni, consulta Point-in-time ripristino.

Utilizzo degli snapshot DB di Amazon RDS per i checkpoint del database

Se carichi dati per lunghi periodi, ad esempio ore o giorni, senza registrazione binaria, utilizza le istantanee DB per fornire checkpoint periodici per la sicurezza dei dati. Ogni snapshot DB crea una copia coerente dell'istanza del database che funge da punto di ripristino in caso di errori di sistema o eventi di danneggiamento dei dati. Poiché le istantanee del DB sono veloci, i checkpoint frequenti hanno un impatto minimo sulle prestazioni di carico. È possibile eliminare le istantanee DB precedenti senza influire sulla durabilità o sulle funzionalità di ripristino del database. Per ulteriori informazioni sulle istantanee DB, vedere. Gestione dei backup manuali

Riduzione dei tempi di caricamento del database

I seguenti elementi sono suggerimenti aggiuntivi per ridurre i tempi di caricamento:

• Crea tutti gli indici secondari prima di caricare i dati nei database MariadB. A differenza di altri sistemi di database, MariaDB ricostruisce l'intera tabella quando aggiunge o modifica indici secondari. Questo processo crea una nuova tabella con modifiche all'indice, copia tutti i dati ed elimina la tabella originale.

• Carica i dati nell'ordine delle chiavi primarie. Per le tabelle InnoDB, ciò può ridurre i tempi di caricamento del 75% - 80% e ridurre le dimensioni dei file di dati del 50%.

• Disattiva i vincoli di chiave esterna impostando su. foreign_key_checks 0 Questo è spesso necessario per i file flat caricati con. LOAD DATA LOCAL INFILE Per qualsiasi carico, la disabilitazione dei controlli con chiave esterna accelera il caricamento dei dati. Al termine del caricamento, riattiva i vincoli impostando foreign_key_checks e verificando i dati. 1

• Carica i dati in parallelo a meno che non ti avvicini a un limite di risorse. Per consentire il caricamento simultaneo su più segmenti di tabella, utilizzate tabelle partizionate se necessario.

• Per ridurre il sovraccarico di esecuzione SQL, combina più INSERT istruzioni in singole operazioni multivalore. INSERT mariadb-dumpimplementa questa ottimizzazione automaticamente.

• Riduci le operazioni di IO del registro InnoDB innodb_flush_log_at_trx_commit impostando su. 0 Al termine del caricamento, ripristina innodb_flush_log_at_trx_commit su. 1

  avvertimento

  L'impostazione innodb_flush_log_at_trx_commit su 0 fa sì che InnoDB svuoti i suoi log ogni secondo anziché a ogni commit. Questa impostazione aumenta le prestazioni ma può rischiare la perdita delle transazioni durante i guasti del sistema.

• Se stai caricando dati in un'istanza DB che non dispone di repliche di lettura, imposta susync_binlog. 0 Al termine del caricamento, ripristina sync_binlog parameter su. 1

• Carica i dati in un'istanza DB Single-AZ prima di convertire l'istanza DB in una distribuzione Multi-AZ. Se l'istanza DB utilizza già un'implementazione Multi-AZ, non è consigliabile passare a una distribuzione Single-AZ per il caricamento dei dati. In questo modo si ottengono solo miglioramenti marginali