InnoDB prend plus d'une heure pour importer fichier de 600 mo, MyISAM en quelques minutes

Je suis actuellement en train de travailler sur la création d'un environnement de tester les performances d'une application; je suis en essais avec MySQL et InnoDB pour savoir ce qui peut servir le meilleur de nous. Au sein de cet environnement, nous allons préparer automatiquement la base de données (charge existant décharges) et de l'instrument de nos outils de test.

Je suis en train de préparer de tester la même vidage des données avec MySQL et InnoDB, mais je suis déjà à défaut d'apporter de l'importation initiale à une utilisable de la vitesse pour le InnoDB partie. La première dump a pris plus de temps, mais cela n'a pas concerné moi encore:

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done

real    0m38.152s
user    0m8.381s
sys     0m2.612s

real    1m16.665s
user    0m6.600s
sys     0m2.552s

Toutefois, l'importation de fois ont été très différentes:

$ for i in  testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done

real    2m52.821s
user    0m10.505s
sys     0m1.252s

real    87m36.586s
user    0m10.637s
sys     0m1.208s

Après recherche je suis arrivé sur Tables à langer à partir de MyISAM pour InnoDB faire ralentir le système et ensuite utilisé set global innodb_flush_log_at_trx_commit=2:

$ time mysql testdb_innodb < testdb_innodb.sql

real    64m8.348s
user    0m10.533s
sys     0m1.152s

À mon humble avis encore terriblement lente. J'ai aussi désactivé log_bin pour ces tests et voici une liste de toutes les variables mysql.

Faire j'ai accepter cette longue InnoDB fois ou peuvent-ils être améliorés? J'ai le plein contrôle sur ce serveur MySQL que c'est purement pour cet environnement de test.

Que je peux appliquer des configurations spéciales uniquement pour l'importation initiale et de les modifier pour les applications de tests afin qu'ils correspondent mieux les environnements de production.

Mise à jour:

Donné les commentaires, j'ai désactivé autocommit et les différents contrôles:

$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \
; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date

real    47m59.019s
user    0m10.665s
sys     0m2.896s

La vitesse améliorée, mais pas tant que ça. Est mon test imparfait?

Mise à jour 2:

J'ai été en mesure d'accéder à une autre machine ont été importations a seulement pris environ 8 minutes. J'ai comparé les configurations et appliqué les paramètres suivants pour mon installation de MySQL:

innodb_additional_mem_pool_size = 20971520
innodb_buffer_pool_size = 536870912
innodb_file_per_table
innodb_log_buffer_size = 8388608
join_buffer_size = 67104768
max_allowed_packet = 5241856
max_binlog_size = 1073741824
max_heap_table_size = 41943040
query_cache_limit = 10485760
query_cache_size = 157286400
read_buffer_size = 20967424
sort_buffer_size = 67108856
table_cache = 256
thread_cache_size = 128
thread_stack = 327680
tmp_table_size = 41943040

Avec ces réglages, je suis maintenant à environ 25 minutes. Encore loin de la à quelques minutes MyISAM prend, mais il devient de plus en plus utilisable pour moi.

Vous savez quoi? Nous devrions vraiment nous avons déplacé cette à serverfault. A voté pour le faire. Bonne chance.
Semble raisonnable. Est-il rien de ma part je peux/dois faire?

InformationsquelleAutor mark | 2010-01-30

126

Avez-vous essayé le en Vrac Chargement des Données des Conseils de la InnoDB De Réglage Des Performances De Conseils (surtout le premier):
- Lors de l'importation de données dans InnoDB, assurez-vous que MySQL n'a pas de
  mode de validation automatique activé parce que c'
  nécessite un journal de chasse de disque pour chaque
  insérer. Pour désactiver la validation automatique lors de
  votre opération d'importation, l'entourent
  avec SET autocommit et COMMIT
  déclarations:
  
  SET autocommit=0; ... SQL import statements ... COMMIT;
  
  Si vous utilisez la commande mysqldump option --opt, vous obtenez les fichiers de vidage sont
  rapide à l'importation dans un InnoDB table,
  même sans les envelopper avec de la
  SET autocommit et COMMIT
  des déclarations.
- Si vous avez UNIQUE contraintes sur les touches, vous pouvez accélérer le tableau
  les importations de désactiver temporairement le
  l'unicité des contrôles lors de l'importation
  session:
  
  SET unique_checks=0; ... SQL import statements ... SET unique_checks=1;
  
  Pour les grandes tables, cela permet d'économiser beaucoup d'I/O disque parce que InnoDB pouvez utiliser
  ses insérer un tampon d'écriture secondaire
  index des enregistrements dans un lot. Être certain
  que les données ne contient pas de doublons
  les touches.
- Si vous avez FOREIGN KEY contraintes dans vos tables, vous pouvez
  la vitesse de la table des importations en tournant le
  clé étrangère vérifie pour l'
  la durée de la session d'importation:
  
  SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1;
  
  Pour les grandes tables, cela permet d'économiser beaucoup d'e/s de disque
De l'OMI, l'ensemble du chapitre vaut la peine de le lire.
- Énorme +1. Réponse parfaite.
- Merci pour le pointeur! Je suis passé par le chapitre, vous avez cité les parties les plus pertinentes déjà de toute façon, appliqué et eux courut de nouveau le test. Plus vite, mais toujours de façon plus lente. Ne peux pas le croire. Semble que je suis en train de faire à de vrais débutants erreur ici.
- Vous êtes les bienvenus. Mais en effet, sans autocommit, sans unicité des contrôles, sans clé étrangère chèques, vous devriez obtenir une vitesse similaire. Avez-vous de l'index sur la table? Peut-être de les déposer et de les recréer après l'importation en vrac.
- Oui, les tables ont indicies, en particulier les plus grands. Je vais voir comment je peux le faire.
- J'ai jeté avec --opt option. Vidage de la taille de 700 mo. Maintenant Importer sa prise depuis longtemps. 20 minutes de jeu. Encore son importation!!!
- Apparemment --opt est par défaut, au moins dans 10.13 (à partir de mysql 5.5.31).
- Essayé toutes ces, mais l'importation d'un fichier de 4 mo tout de même pris 10 minutes. Off.
- Est-il un moyen d'ajouter de l'emballage des lignes de ~1G sql fichiers de vidage? J'ai beaucoup de ces gros fichiers.
- J'ai créé des fichiers before et after et enveloppé avec: cat before <(pv my_large_dump.gz | gunzip) after | mysql -uroot -p dbname j'espère que ça aide quelqu'un
- slick truc @snapfractalpop
InformationsquelleAutor Pascal Thivent
5

Avez-vous essayé le démarrage d'une opération dès le départ et de s'engager à la fin? À partir de la question vous avez accédé: "Modifier les Données d'Insertion étape pour démarrer une transaction au début et un à la commettre à la fin. Vous obtiendrez une amélioration, je vous le garantir."

Rappelez-vous que InnoDB est transactionnelle, MyISAM ne l'est pas. Transactionnelle moteurs de traiter chaque énoncé comme une transaction en particulier si vous n'avez pas explicitement le contrôle de la transaction. Cela peut être coûteux.
- +1 aussi parce que vous l'avez souligné la bonne chose
- J'ai maintenant mis à jour à la question de la validation d'être explicitement désactivé. Il courait plus vite, mais prend trop de temps (à mon humble avis).
InformationsquelleAutor T.J. Crowder
2

J'ai trouvé le disque dur pour être le goulot d'étranglement - old-fashioned disques sont sans espoir, les disques SSD sont d'accord-ish, mais encore loin d'être parfait. L'importation de tmpfs et copier les données est plus rapide, détails: https://dba.stackexchange.com/a/89367/56667

InformationsquelleAutor egmont
1

J'ai eu des problèmes à faire beaucoup de volume de l'importation et de recommander la accepté de répondre. J'ai trouvé, vous pourrez également accélérer les choses de manière significative par:
1. Abandon de tous les indices (autres que la clé primaire), le chargement des données puis de rajouter des indices
2. La vérification de votre innodb_log_file_size * innodb_log_files_in_group est suffisante pour éviter d'écrire sur le disque dans le sous-deuxième fréquence
Sujet #2 les valeurs par défaut de 5 M * 2 ne sera pas assez sur un système moderne. Pour plus de détails voir innodb_log_file_size et innodb_log_files_in_group

InformationsquelleAutor KCD

Vous devez vous connecter pour publier un commentaire.