Pourquoi MySQL InnoDB insert est si lent?

j'utilise de grands nombres aléatoires comme clés (venant d'un autre système). Les encarts et les mises à jour sur des tableaux relativement petits (comme dans quelques millions de rangées) prennent beaucoup plus de temps que je ne pense est raisonnable.

j'ai distillé un test très simple pour illustrer. Dans la table de test, j'ai essayé de le rendre aussi simple que possible; mon code n'a pas une mise en page simple et a des relations et des indices supplémentaires. Cependant, une configuration plus simple montre l'équivalent performance.

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

insérer des lignes de 1m dans MyISAM prend 6 secondes; dans InnoDB prend 3433 secondes !

Qu'est-ce que je fais de mal? Ce qui est mal configuré? (MySQL est une réaction normale de Ubuntu l'installation avec les paramètres par défaut)

voici le code du 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")
48
demandé sur Paul Dixon 2012-03-22 13:16:24

8 réponses

InnoDB ne supporte pas bien les clés primaires "aléatoires". Essayez une clé séquentielle ou auto-incrément, et je pense que vous verrez une meilleure performance. Votre' vrai ' champ de clé pourrait encore être indexé, mais pour un insert en vrac vous pourriez être mieux de laisser tomber et de recréer cet index en un seul clic après l'insert dans son intégralité. Serais intéressé de voir tes repères!

des questions connexes

35
répondu Paul Dixon 2017-05-23 11:54:58

InnoDB a le support de transaction, vous n'utilisez pas les transactions explicites donc innoDB doit faire un commit après chaque énoncé ( " effectue un log flush vers le disque pour chaque insert " ).

exécutez cette commande avant votre boucle: 

START TRANSACTION

et celui-ci après vous boucle 

COMMIT
58
répondu flo 2015-04-09 11:51:33

j'ai eu besoin de faire des tests d'une application insert-heavy dans MyISAM et InnoDB simultanément. Il y avait un seul réglage qui a résolu les problèmes de vitesse que j'avais. Essayez de définir ce qui suit: 

innodb_flush_log_at_trx_commit = 2

assurez-vous de bien comprendre les risques en lisant à propos du réglage ici .

voir Aussi /q/designing-a-calendar-system-like-google-calendar-48817/"https://dba.stackexchange.com/a/29974/9405">https://dba.stackexchange.com/a/29974/9405

20
répondu Philip Koshy 2017-04-13 12:42:38

, j'obtiens des résultats très différents sur mon système, mais ce n'est pas en utilisant les valeurs par défaut. Vous êtes probablement goulot d'étranglement sur innodb-log-file-size, qui est de 5M par défaut. À innodb-log-file-size=100M je reçois 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

augmente le innodb-log-file-size va accélérer de quelques secondes. L'abandon des garanties de durabilité par le réglage innodb-flush-log-at-trx-commit=2 ou 0 améliorera quelque peu les nombres d'insertion aussi bien.

6
répondu Andrew 2014-12-02 05:46:22

la valeur par défaut pour InnoDB est en fait assez mauvaise. InnoDB est très dépendant de la RAM, vous pourriez trouver un meilleur résultat si vous modifiez les paramètres. Voici un guide que j'ai utilisé InnoDB optimisation de base

5
répondu Dikei 2012-03-22 11:28:34

Quelle est votre taille innodb de piscine tampon? Assurez-vous que vous avez mis à 75% de votre RAM. Habituellement les inserts sont meilleurs quand dans l'ordre de la clé primaire pour InnoDB. Mais avec une grande piscine, vous devriez voir de bonnes vitesses.

2
répondu Ajay 2013-03-13 00:14:18

c'est un sujet ancien mais fréquemment recherché. Tant que vous êtes conscient des risques (comme indiqué par @philip Koshy ci-dessus) de perdre des transactions engagées dans la dernière seconde ou plus, avant les mises à jour massives, vous pouvez définir ces paramètres globaux 

innodb_flush_log_at_trx_commit=0
sync_binlog=0

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

innodb_flush_log_at_trx_commit=1
sync_binlog=1

pour une conformité totale à L'acide.

il y a une énorme différence en écriture/mise à jour performance lorsque les deux sont éteints et allumés. D'après mon expérience, d'autres éléments discutés ci-dessus font une certaine différence, mais seulement marginale.

une autre chose qui affecte update/insert est grandement l'index du texte intégral. Dans un cas, un tableau comportant deux champs de texte avec l'index du texte intégral, l'insertion de lignes de 2mil a pris 6 heures et la même chose n'a pris que 10 min après la suppression de l'index du texte intégral. Plus d'indices, plus de temps. Ainsi, les index de recherche autres que la clé unique et primaire peuvent être supprimé avant les inserts massifs / mises à jour.

2
répondu Allen King 2018-07-29 03:22:18

choses qui accélèrent les inserts:

  • j'avais retiré toutes les clés d'une table avant grand insérer dans la table vide
  • j'ai alors constaté que j'avais un problème que l'index ne correspondait pas dans la mémoire.
  • aussi découvert que j'avais sync_binlog=0 (1) même si binlog n'est pas utilisé.
  • aussi trouvé que je n'ai pas mis innodb_buffer_pool_instances
1
répondu Shimon Doodkin 2016-03-08 19:37:57