Faire un vidage et une restauration de la manière décrite signifie que MySQL doit complètement reconstruire les index au fur et à mesure que les données sont importées. Il doit également analyser les données à chaque fois.
Ce serait beaucoup plus efficace si vous pouviez copier les fichiers de données dans un format que MySQL comprend déjà. Une bonne façon de faire est d'utiliser innobackupex de Percona
(Open Source et distribué dans le cadre de XtraBackup disponible en téléchargement ici).
Cela prendra un instantané des tables MyISAM, et pour les tables InnoDB, il copiera les fichiers sous-jacents, puis rejouera le journal des transactions par rapport à eux pour assurer un état cohérent. Il peut le faire à partir d'un serveur en direct sans temps d'arrêt (je ne sais pas si c'est une exigence de votre part ?)
Je vous suggère de lire la documentation, mais pour effectuer une sauvegarde dans sa forme la plus simple, utilisez :
$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/
Si les données sont sur la même machine, alors innobackupex a même une simple commande de restauration :
$ innobackupex --copy-back /path/to/BACKUP-DIR
Il existe de nombreuses autres options et différentes manières de faire la sauvegarde, donc je vous encourage vraiment à lire attentivement la documentation avant de commencer.
Pour référence à la vitesse, notre serveur de test lent, qui fait environ 600 IOPS, peut restaurer une sauvegarde de 500 Go en environ 4 heures en utilisant cette méthode.
Enfin :vous avez mentionné ce qui pourrait être fait pour accélérer l'importation. Cela dépendra principalement de la nature du goulot d'étranglement. En règle générale, les opérations d'importation sont liées aux E/S (vous pouvez tester cela en vérifiant les attentes io) et la façon d'accélérer cela est avec un débit de disque plus rapide - soit des disques plus rapides eux-mêmes, soit plusieurs d'entre eux à l'unisson.
Assurez-vous d'augmenter votre "max_allowed_packet " variable à une taille suffisamment grande. Cela vous aidera vraiment si vous avez beaucoup de données texte. L'utilisation d'un matériel haute performance améliorera sûrement la vitesse d'importation des données.
mysql --max_allowed_packet=256M -u root -p < "database-file.sql"
Une chose que vous pouvez faire est
SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0
Et vous pouvez aussi jouer avec les valeurs
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method
en my.cnf pour vous aider à démarrer, mais en général, vous devriez également consulter le reste des paramètres d'innodb pour voir ce qui vous convient le mieux.
C'est un problème que j'ai eu dans le passé et que je n'ai pas l'impression d'avoir complètement résolu, mais j'espère m'être orienté dans cette direction dès le départ. Cela m'aurait fait gagner pas mal de temps.
Il manque beaucoup de paramètres pour bien comprendre la raison du problème. tels que :
- Version MySQL
- Type de disque et vitesse
- Libérez de la mémoire sur le serveur avant de démarrer le serveur MySQL
- sortie iostat avant et au moment du mysqldump.
- Quels sont les paramètres que vous utilisez pour créer le fichier de vidage en premier lieu.
et bien d'autres.
Je vais donc essayer de deviner que votre problème est dans les disques car j'ai 150 instances de MySQL que je gère avec 3 To de données sur l'une d'elles, et généralement le disque est le problème
Passons maintenant à la solution :
Tout d'abord, votre MySQL n'est pas configuré pour de meilleures performances.
Vous pouvez en savoir plus sur les paramètres les plus importants à configurer sur le blog Percona :http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
Vérifiez surtout les paramètres :
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
innodb_flush_method
Si votre problème est le disque - la lecture du fichier à partir du même lecteur - aggrave le problème.
Et si votre serveur MySQL commence à échanger parce qu'il n'a pas assez de RAM disponible, votre problème devient encore plus important.
Vous devez exécuter des diagnostics sur votre machine avant et au moment de la procédure de restauration pour le comprendre.
De plus, je peux vous suggérer d'utiliser une autre technique pour effectuer la tâche de reconstruction, qui fonctionne plus rapidement que mysqldump.
C'est Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/
Vous devrez créer la sauvegarde avec et restaurer à partir de celle-ci, ou reconstruire directement à partir du serveur en cours d'exécution avec l'option de streaming.
De plus, la version de MySQL à partir de 5.5 - InnoDB fonctionne plus rapidement que MyISAM. Envisagez de modifier toutes vos tables.