Pourquoi MySQL InnoDB est-il si lent?

J’utilise de grands nombres aléatoires comme clés (provenant d’un autre système). Les insertions et les mises à jour sur des tables relativement petites (comme dans quelques millions de lignes) prennent beaucoup plus de temps que ce que je pense raisonnable.

J’ai distillé un test très simple pour illustrer. Dans le tableau de test, j’ai essayé de le rendre aussi simple que possible; mon vrai code n’a pas une disposition aussi simple et a des relations et des indices supplémentaires, etc. Cependant, une configuration plus simple montre des performances équivalentes.

Voici les résultats:

creating the MyISAM table took 0.000 seconds creating 1024000 rows of test data took 1.243 seconds inserting the test data took 6.335 seconds selecting 1023742 rows of test data took 1.435 seconds fetching 1023742 batches of test data took 0.037 seconds dropping the table took 0.089 seconds creating the InnoDB table took 0.276 seconds creating 1024000 rows of test data took 1.165 seconds inserting the test data took 3433.268 seconds selecting 1023748 rows of test data took 4.220 seconds fetching 1023748 batches of test data took 0.037 seconds dropping the table took 0.288 seconds

L’insertion de lignes 1M dans MyISAM prend 6 secondes; dans InnoDB prend 3433 secondes !

Qu’est-ce que je fais mal? Qu’est-ce qui est mal configuré? (MySQL est une installation Ubuntu normale avec les valeurs par défaut)

Voici le code de test:

 import sys, time, random import MySQLdb as db # usage: python script db_username db_password database_name db = db.connect(host="127.0.0.1",port=3306,user=sys.argv[1],passwd=sys.argv[2],db=sys.argv[3]).cursor() def test(engine): start = time.time() # fine for this purpose db.execute(""" CREATE TEMPORARY TABLE Testing123 ( k INTEGER PRIMARY KEY NOT NULL, v VARCHAR(255) NOT NULL ) ENGINE=%s;"""%engine) duration = time.time()-start print "creating the %s table took %0.3f seconds"%(engine,duration) start = time.time() # 1 million rows in 100 chunks of 10K data = [[(str(random.getrandbits(48)) if a&1 else int(random.getrandbits(31))) for a in xrange(10*1024*2)] for b in xrange(100)] duration = time.time()-start print "creating %d rows of test data took %0.3f seconds"%(sum(len(rows)/2 for rows in data),duration) sql = "REPLACE INTO Testing123 (k,v) VALUES %s;"%("(%s,%s),"*(10*1024))[:-1] start = time.time() for rows in data: db.execute(sql,rows) duration = time.time()-start print "inserting the test data took %0.3f seconds"%duration # execute the query start = time.time() query = db.execute("SELECT k,v FROM Testing123;") duration = time.time()-start print "selecting %d rows of test data took %0.3f seconds"%(query,duration) # get the rows in chunks of 10K rows = 0 start = time.time() while query: batch = min(query,10*1024) query -= batch rows += len(db.fetchmany(batch)) duration = time.time()-start print "fetching %d batches of test data took %0.3f seconds"%(rows,duration) # drop the table start = time.time() db.execute("DROP TABLE Testing123;") duration = time.time()-start print "dropping the table took %0.3f seconds"%duration test("MyISAM") test("InnoDB")

InnoDB ne se comporte pas bien avec les clés primaires «aléatoires». Essayez une clé séquentielle ou auto-incrémentée, et je pense que vous verrez de meilleures performances. Votre champ de clé “réel” peut toujours être indexé, mais pour une insertion en bloc, il est préférable de supprimer et de recréer cet index en un coup après l’insertion. Serait intéressé de voir vos repères pour cela!

Quelques questions connexes

Ralentissez INSERT dans la table InnoDB avec la valeur aléatoire de la colonne PRIMARY KEY
Pourquoi les insertions / mises à jour MySQL InnoDB sur les grandes tables sont-elles très lentes quand il y a quelques index?
InnoDB insère très lentement et ralentit

InnoDB prend en charge les transactions, vous n’utilisez pas de transactions explicites, donc innoDB doit effectuer une validation après chaque instruction ( “effectue un vidage du journal sur le disque pour chaque insertion” ).

Exécutez cette commande avant votre boucle:

 START TRANSACTION

et ceci après que vous bouclez

 COMMIT

J’ai eu besoin de tester simultanément une application d’insertion lourde dans MyISAM et InnoDB. Un seul paramètre résolvait les problèmes de vitesse que je rencontrais. Essayez de définir les éléments suivants:

 innodb_flush_log_at_trx_commit = 2

Assurez-vous de bien comprendre les risques en lisant le paramètre ici .

Voir aussi https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2/12612 et https: //dba.stackexchange. com / a / 29974/9405

J’ai des résultats très différents sur mon système, mais cela n’utilise pas les valeurs par défaut. Vous êtes probablement goulot sur innodb-log-file-size, qui est 5M par défaut. A innodb-log-file-size = 100M, j’obtiens des résultats comme ceci (tous les nombres sont en secondes):

  MyISAM InnoDB create table 0.001 0.276 create 1024000 rows 2.441 2.228 insert test data 13.717 21.577 select 1023751 rows 2.958 2.394 fetch 1023751 batches 0.043 0.038 drop table 0.132 0.305

L’augmentation de la innodb-log-file-size accélérera cette opération de quelques secondes. La suppression des garanties de durabilité en définissant innodb-flush-log-at-trx-commit=2 ou 0 améliorera également les numéros d’insertion.

La valeur par défaut d’InnoDB est plutôt mauvaise. InnoDB est très dépendant de la RAM, vous pourriez trouver de meilleurs résultats si vous modifiez les parameters. Voici un guide que j’ai utilisé InnoDB optimisation de base

Quelle est la taille de votre pool de tampons innodb? Assurez-vous que vous avez défini 75% de votre RAM. Habituellement, les insertions sont meilleures en ordre de clé primaire pour InnoDB. Mais avec une grande taille de piscine, vous devriez voir de bonnes vitesses.

C’est un sujet ancien mais fréquemment recherché. Tant que vous êtes au courant des risques (comme indiqué par @philip Koshy ci-dessus) de perdre des transactions engagées au cours de la dernière seconde, avant les mises à jour massives, vous pouvez définir ces parameters globaux.

 innodb_flush_log_at_trx_commit=0 sync_binlog=0

puis rallumez-le (si vous le souhaitez) une fois la mise à jour terminée.

 innodb_flush_log_at_trx_commit=1 sync_binlog=1

pour la conformité totale ACID.

Il y a une énorme différence dans les performances d’écriture / mise à jour lorsque ces deux options sont activées et désactivées. D’après mon expérience, d’autres éléments discutés ci-dessus font une différence, mais seulement marginaux.

L’indexation en texte intégral est une autre chose qui influe grandement sur update/insert . Dans un cas, une table avec deux champs de texte ayant un index de texte intégral, en insérant des lignes de 2 millilitres, prenait 6 heures et la même chose ne prenait que 10 minutes après la suppression de l’index de texte intégral. Plus d’index, plus de temps. Ainsi, les index de recherche autres que les clés uniques et primaires peuvent être supprimés avant les insertions / mises à jour massives.

des choses qui accélèrent les insertions:

j’avais enlevé toutes les clés d’une table avant l’insertion grande dans la table vide
puis trouvé que j’avais un problème que l’index ne correspondait pas à la mémoire.
J’ai également trouvé que sync_binlog = 0 (devrait être 1) même si binlog n’est pas utilisé.
a également trouvé que je n’ai pas défini innodb_buffer_pool_instances