Considerazioni sull’importazione di dati per MySQL

Il contenuto seguente include informazioni tecniche relative al caricamento dei dati in MySQL. Il contenuto è rivolto a utenti con una buona conoscenza dell’architettura server MySQL.

Registrazione di log binari

L’abilitazione della registrazione di log binari riduce le prestazioni di caricamento dei dati e richiede uno spazio su disco quattro volte superiore rispetto alla registrazione di log disabilitata. 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 un numero maggiore di risorse.

Dimensioni delle transazioni

Le dimensioni delle transazioni influenzano i seguenti aspetti dei caricamenti di dati MySQL:

• Consumo di risorse

• Utilizzo dello spazio su disco

• Processo di ripresa

• Tempo di ripristino

• 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 log binario durante il caricamento di grandi quantità di dati. Per abilitare e disabilitare la registrazione di log binari, impostare il periodo di conservazione dei backup automatici 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 in InnoDB nonché i motivi per cui è importante contenere 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. La riduzione delle prestazioni dipende in parte dai seguenti fattori:

• Velocità di caricamento

• Altre attività del database in esecuzione durante il caricamento

• Capacità dell’istanza database Amazon RDS

I log binari consumano una quantità di spazio su disco approssimativamente pari alla quantità di dati caricati, fino a quando non ne viene effettuato il backup e i dati non vengono rimossi. Amazon RDS attenua il problema grazie a backup frequenti e alla rimozione dei log binari.

Transazioni di grandi dimensioni

Per transazioni di grandi dimensioni, la registrazione di log binari triplica il numero di IOPS e l’utilizzo del disco per i seguenti motivi:

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

• Le dimensioni della cache aumentano in base a quelle della transazione, consumando spazio su disco.

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

Il processo crea tre copie dei dati:

• Dati originali

• Cache su disco

• 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 di log binari richiede uno spazio su disco tre volte maggiore rispetto al caso in cui sia 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 del log binario

• 10 GiB per il file di log binario

Lo spazio su disco totale richiesto temporaneamente è 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 ne viene eseguito il backup e può contenere 20 GiB di dati (cache e log) per un periodo prolungato.

Se si utilizza LOAD DATA LOCAL INFILE per caricare i dati, durante il ripristino viene creata una quarta copia dei dati qualora il database debba essere ripristinato da un backup eseguito prima del caricamento. Durante il recupero, MySQL estrae i dati dal log binario in un file flat. MySQL esegue quindi LOAD DATA LOCAL INFILE. In base all’esempio precedente, questo ripristino richiede uno spazio su disco temporaneo totale di 40 GiB o di 10 GiB ciascuno per tabella, cache, log e file locale. Se non sono disponibili almeno 40 GiB di spazio su disco, il ripristino non può essere eseguito.

Ottimizzazione di carichi di dati di grandi dimensioni

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

Nota

Non è possibile impostare il periodo di conservazione dei backup su zero se l’istanza database è un’origine per le repliche di lettura.

Prima di caricare i dati, è consigliabile creare uno snapshot di database. Per ulteriori informazioni, consulta Gestione dei backup manuali.

InnoDB

Le seguenti informazioni sulla registrazione di log di undo e sulle opzioni di ripristino prevedono di mantenere ridotte le dimensioni delle transazioni InnoDB per ottimizzare le prestazioni del database.

Informazioni sulla registrazione di log di undo InnoDB

L’annullamento (undo) è un meccanismo della registrazione di log che consente il rollback delle transazioni e supporta il controllo della concorrenza multiversione (MVCC).

In MySQL 5.7 e versioni precedenti, i log di undo vengono archiviati nel tablespace del sistema InnoDB (in genere ibdata1) e mantenuti fino a quando il thread di eliminazione non li rimuove. Di conseguenza, le transazioni di caricamento di dati di grandi dimensioni possono causare un aumento significativo delle dimensioni del tablespace del sistema, con un consumo di spazio su disco che non si può recuperare senza ricreare il database da zero.

Per tutte le versioni di MySQL, il thread di eliminazione deve attendere la rimozione dei log di undo fino al commit o al rollback della transazione attiva meno recente. Se il database elabora altre transazioni durante il caricamento, anche i relativi log di undo si accumulano e non possono essere rimossi, anche nel caso di commit delle transazioni e in quello in cui nessun’altra transazione richieda log di undo per MVCC. In questa situazione, tutte le transazioni, incluse quelle di sola lettura, rallentano. Il rallentamento si verifica perché tutte le transazioni accedono a tutte le righe modificate da qualsiasi transazione, non solo da quella di caricamento. In effetti, le transazioni devono eseguire la scansione dei log di undo che le transazioni di caricamento di lunga durata hanno impedito di eliminare durante una pulizia dei log di undo stessi. Ciò influisce sulle prestazioni di tutte le operazioni di accesso alle righe modificate.

Opzioni di ripristino delle transazioni InnoDB

Sebbene InnoDB ottimizzi le operazioni di commit, i rollback delle transazioni di grandi dimensioni sono lenti. Per maggiore rapidità, eseguire un recupero point-in-time o ripristinare uno snapshot di database. Per ulteriori informazioni, consultare Point-in-time recupero e Ripristino in un’istanza database.

Formati di importazione dei dati

MySQL supporta due formati di importazione di dati, ovvero file flat e SQL. Consultare le informazioni su ciascun formato per determinare l’opzione migliore per le proprie esigenze.

File flat

Per transazioni di piccole dimensioni, caricare file flat con LOAD DATA LOCAL INFILE. Rispetto all’utilizzo di SQL, questo formato di importazione dati può comportare i seguenti vantaggi:

• Minor traffico di rete

• Riduzione dei costi di trasmissione dei dati

• Riduzione del sovraccarico di elaborazione del database

• Elaborazione più rapida

LOAD DATA LOCAL INFILE carica l’intero file flat come un’unica transazione. Mantenere ridotte le dimensioni dei singoli file per ottenere i seguenti vantaggi:

• Capacità di ripristino: si può tenere facilmente traccia dei file caricati. In caso di problemi durante il caricamento, si può riprendere l’operazione dal punto in cui era stata interrotta. Potrebbe essere necessario trasmettere nuovamente alcuni file ad Amazon RDS, ma nel caso di file di piccole dimensioni la quantità di dati ritrasmessa è minima.

• Caricamento di dati in parallelo: se si dispone di IOPS e di larghezza di banda della rete sufficienti per caricare un file singolo, il caricamento in parallelo potrebbe consentire di risparmiare tempo.

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

Le transazioni di grandi dimensioni riducono i vantaggi dell’utilizzo di LOAD DATA LOCAL INFILE per importare i dati. Se non si riesce a suddividere una grande quantità di dati in file di dimensioni minori, prendere in considerazione l’utilizzo di SQL.

SQL

Rispetto ai file flat, SQL presenta un grande vantaggio perché consente di mantenere ridotte le dimensioni delle transazioni, sebbene i tempi di caricamento siano significativamente più lunghi. Dopo un errore, inoltre, può essere difficile determinare il punto da cui riprendere perché non è possibile riavviare i file mysqldump. In caso di errore durante il caricamento di un file mysqldump, quest’ultimo dovrà essere modificato o sostituito prima che sia possibile riprendere il caricamento. In alternativa, dopo aver corretto la causa dell’errore, è possibile tornare al punto temporale precedente al caricamento e inviare nuovamente il file. Per ulteriori informazioni, consulta Point-in-time recupero.

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

Se si caricano dati per lunghi periodi, ad esempio ore o giorni, senza registrazione di log binari, utilizzare gli snapshot di database per fornire checkpoint periodici per la sicurezza dei dati. Ogni snapshot di database crea una copia coerente dell’istanza database che funge da punto di ripristino in caso di errori di sistema o di eventi di danneggiamento dei dati. Poiché gli snapshot di database sono veloci, i checkpoint frequenti hanno un impatto minimo sulle prestazioni del caricamento. È possibile eliminare gli snapshot di database precedenti senza influire sulla durabilità o sulle funzionalità di ripristino del database. Per ulteriori informazioni sugli snapshot di database, consulta Gestione dei backup manuali.

Riduzione dei tempi di caricamento del database

Di seguito sono indicati alcuni suggerimenti per ridurre i tempi di caricamento:

• Creare tutti gli indici secondari prima di caricare i dati nei database MySQL. A differenza di altri sistemi di database, quando si aggiungono o si modificano gli indici secondari MySQL ricostruisce l’intera tabella. Questo processo consente di creare una nuova tabella con modifiche agli indici, di copiare tutti i dati e di eliminare la tabella originale.

• Caricare i dati in base all’ordine della chiave primaria. Per le tabelle InnoDB, questa operazione può ridurre i tempi di caricamento del 75%-80% e le dimensioni dei file di dati del 50%.

• Disabilitare i vincoli di chiave esterna impostando foreign_key_checks su 0. Questa operazione è spesso necessaria per i file flat caricati con LOAD DATA LOCAL INFILE. Per qualsiasi carico, la disabilitazione dei controlli di chiave esterna accelera il caricamento dei dati. Dopo aver completato il caricamento, riabilitare i vincoli impostando foreign_key_checks su 1 e verificare i dati.

• Caricare i dati in parallelo a meno che non si stia raggiungendo il limite di risorse. Per consentire il caricamento simultaneo su più segmenti di tabella, se necessario utilizzare tabelle partizionate.

• Per ridurre il sovraccarico di esecuzione SQL, combinare più istruzioni INSERT in singole operazioni INSERT con più valori. mysqldump implementa questa ottimizzazione in modo automatico.

• Ridurre le operazioni di I/O dei log InnoDB impostando innodb_flush_log_at_trx_commit su 0. Al termine del caricamento, ripristinare il valore di innodb_flush_log_at_trx_commit su 1.

  avvertimento

  Se si imposta innodb_flush_log_at_trx_commit su 0, InnoDB cancella i log ogni secondo anziché a ogni commit. Questa impostazione aumenta le prestazioni, ma può comportare il rischio di perdita delle transazioni durante i guasti del sistema.

• Se si caricano i dati in un’istanza database che non include repliche di lettura, impostare sync_binlog su 0. Al termine del caricamento, ripristinare il valore di sync_binlog parameter su 1.

• Caricare i dati in un’istanza database Single-AZ prima di convertire l’istanza database in un’implementazione Multi-AZ. Se l’istanza database utilizza già un’implementazione Multi-AZ, non è consigliabile passare a un’implementazione Single-AZ per il caricamento dei dati. In tal modo si otterrebbero solo miglioramenti marginali.