Eseguire un dump e ripristinare nel modo descritto significa che MySQL deve ricostruire completamente gli indici man mano che i dati vengono importati. Deve anche analizzare i dati ogni volta.
Sarebbe molto più efficiente se potessi copiare i file di dati in un formato che MySQL già comprende. Un buon modo per farlo è usare innobackupex di Percona
(Open Source e distribuito come parte di XtraBackup disponibile per il download da qui).
Questo creerà un'istantanea delle tabelle MyISAM e per le tabelle InnoDB copierà i file sottostanti, quindi riprodurrà il registro delle transazioni su di essi per garantire uno stato coerente. Può farlo da un server live senza tempi di inattività (non ho idea se questo è un tuo requisito?)
Ti suggerisco di leggere la documentazione, ma per fare un backup nella sua forma più semplice usa:
$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/
Se i dati si trovano sulla stessa macchina, allora innobackupex ha anche un semplice comando di ripristino:
$ innobackupex --copy-back /path/to/BACKUP-DIR
Ci sono molte più opzioni e diversi modi per eseguire effettivamente il backup, quindi ti incoraggio davvero a leggere bene la documentazione prima di iniziare.
Per quanto riguarda la velocità, il nostro server di test lento, che esegue circa 600 IOPS, può ripristinare un backup di 500 GB in circa 4 ore utilizzando questo metodo.
Infine:hai menzionato cosa si potrebbe fare per accelerare l'importazione. Dipenderà principalmente da quale sia il collo di bottiglia. In genere, le operazioni di importazione sono legate all'I/O (puoi verificarlo controllando le attese io) e il modo per velocizzarle è con un throughput del disco più veloce:dischi stessi più veloci o più dischi all'unisono.
Assicurati di aumentare il tuo "max_allowed_packet " variabile a una dimensione sufficientemente grande. Questo sarà davvero d'aiuto se hai molti dati di testo. L'utilizzo di hardware ad alte prestazioni migliorerà sicuramente la velocità di importazione dei dati.
mysql --max_allowed_packet=256M -u root -p < "database-file.sql"
Una cosa che puoi fare è
SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0
E puoi anche giocare con i valori
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method
in my.cnf per iniziare, ma in generale dovresti dare un'occhiata anche al resto dei parametri innodb per vedere cosa ti si addice meglio.
Questo è un problema che ho avuto in passato che non sento di aver affrontato completamente, ma spero di essermi indirizzato in questa direzione sin dall'inizio. Mi sarei risparmiato un bel po' di tempo.
Ci sono molti parametri che mancano, per comprendere appieno il motivo del problema. come:
- Versione di MySQL
- Tipo e velocità del disco
- Libera memoria sul server prima di avviare il server MySQL
- output di iostat prima e al momento del mysqldump.
- Quali sono i parametri che utilizzi per creare il file dump in primo luogo.
e molti altri.
Quindi cercherò di indovinare che il tuo problema è nei dischi perché ho 150 istanze di MySQL che gestisco con 3 TB di dati su uno di essi, e di solito il problema è il disco
Ora alla soluzione:
Prima di tutto, il tuo MySQL non è configurato per le migliori prestazioni.
Puoi leggere le impostazioni più importanti da configurare nel post del blog di Percona:http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
Controlla soprattutto i parametri:
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
innodb_flush_method
Se il tuo problema è il disco, la lettura del file dalla stessa unità sta peggiorando il problema.
E se il tuo server MySQL inizia a scambiarsi perché non ha abbastanza RAM disponibile, il tuo problema diventa ancora più grande.
Devi eseguire la diagnostica sulla tua macchina prima e al momento della procedura di ripristino per capirlo.
Inoltre, posso suggerirti di utilizzare un'altra tecnica per eseguire l'attività di ricostruzione, che funziona più velocemente di mysqldump.
È Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/
Dovrai creare il backup con esso e ripristinare da esso o ricostruire direttamente dal server in esecuzione con l'opzione di streaming.
Inoltre, la versione di MySQL a partire dalla 5.5 - InnoDB è più veloce di MyISAM. Prendi in considerazione l'idea di cambiare tutte le tue tabelle con esso.