Perché l’inserimento InnoDB di MySQL è così lento?

Sto usando grandi numeri casuali come chiavi (provenienti da un altro sistema). Inserti e aggiornamenti su tabelle abbastanza piccole (come in alcuni milioni di righe) impiegano molto più tempo di quanto ritenga ragionevole.

Ho distillato un test molto semplice per illustrare. Nella tabella dei test ho cercato di renderlo il più semplice ansible; il mio vero codice non ha un layout così semplice e ha relazioni e indici aggiuntivi e così via. Tuttavia, una configurazione più semplice mostra prestazioni equivalenti.

Ecco i risultati:

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’inserimento di 1 milione di righe in MyISAM richiede 6 secondi; in InnoDB impiega 3433 secondi !

Che cosa sto facendo di sbagliato? Cosa non è configurato correttamente? (MySQL è una normale installazione di Ubuntu con valori predefiniti)

Ecco il codice di prova:

 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 non gestisce bene le chiavi primarie “casuali”. Prova una chiave sequenziale o un incremento automatico, e credo che vedrai prestazioni migliori. Il tuo campo chiave “reale” potrebbe ancora essere indicizzato, ma per un inserto di massa potresti stare meglio scartando e ricreando quell’indice in un colpo dopo aver completato l’inserimento. Sarebbe interessato a vedere i tuoi punti di riferimento per questo!

Alcune domande correlate

  • Lento INSERISCI nella tabella InnoDB con il valore casuale della colonna PRIMARY KEY
  • Perché gli inserimenti / aggiornamenti di MySQL InnoDB su tabelle di grandi dimensioni diventano molto lenti quando ci sono alcuni indici?
  • InnoDB si inserisce molto lentamente e lentamente

InnoDB ha il supporto per le transazioni, non stai utilizzando transazioni esplicite, quindi innoDB deve eseguire un commit dopo ogni istruzione ( “esegue un log flush su disco per ogni inserto” ).

Esegui questo comando prima del tuo ciclo:

 START TRANSACTION 

e questo dopo il tuo ciclo

 COMMIT 

Avevo bisogno di eseguire test di un’applicazione con inserti pesanti sia in MyISAM che InnoDB contemporaneamente. C’era una singola impostazione che risolveva i problemi di velocità che stavo avendo. Prova a impostare il seguente:

 innodb_flush_log_at_trx_commit = 2 

Assicurati di comprendere i rischi leggendo le impostazioni qui .

Vedi anche https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2/12612 e https: //dba.stackexchange. COM / a / 29974/9405

Ottengo risultati molto diversi sul mio sistema, ma questo non sta usando i valori predefiniti. È probabile che vi sia un collo di bottiglia su innodb-log-file-size, che è 5M per impostazione predefinita. A innodb-log-file-size = 100M ottengo risultati come questo (tutti i numeri sono espressi in secondi):

  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 

Aumentando la innodb-log-file-size aumenterà la velocità di alcuni secondi. Eliminare le garanzie di durata impostando innodb-flush-log-at-trx-commit=2 o 0 migliorerà anche i numeri degli inserti.

Il valore predefinito per InnoDB è in realtà piuttosto brutto. InnoDB è molto dipendente dalla RAM, potresti trovare risultati migliori se modifichi le impostazioni. Ecco una guida che ho utilizzato l’ ottimizzazione di InnoDB di base

Qual è la tua dimensione del pool di buffer innodb? Assicurati di averlo impostato sul 75% della tua RAM. Di solito gli inserti sono migliori quando in ordine di chiave primaria per InnoDB. Ma con una grande piscina, dovresti vedere buone velocità.

Questo è un vecchio argomento ma frequentemente cercato. Fintanto che sei consapevole dei rischi (come affermato sopra da @philip Koshy) di perdere le transazioni impegnate nell’ultimo secondo circa, prima di enormi aggiornamenti, puoi impostare questi parametri globali

 innodb_flush_log_at_trx_commit=0 sync_binlog=0 

quindi riaccenderlo (se lo si desidera) una volta completato l’aggiornamento.

 innodb_flush_log_at_trx_commit=1 sync_binlog=1 

per la piena conformità ACID.

Esiste un’enorme differenza nelle prestazioni di scrittura / aggiornamento quando entrambi vengono distriggersti ​​e triggersti. Nella mia esperienza, altre cose discusse sopra fanno qualche differenza ma solo marginale.

Un’altra cosa che influisce notevolmente update/insert è l’indice full text. In un caso, una tabella con due campi di testo con indice di testo completo, l’inserimento di 2mil righe ha richiesto 6 ore e lo stesso ha richiesto solo 10 minuti dopo la rimozione dell’indice completo. Più indici, più tempo. Quindi gli indici di ricerca diversi dalla chiave univoca e primaria possono essere rimossi prima di inserimenti / aggiornamenti massicci.

cose che velocizzano gli inserti:

  • avevo rimosso tutte le chiavi da una tabella prima di inserire di grandi dimensioni nella tabella vuota
  • poi ho trovato un problema che l’indice non si adattava alla memoria.
  • ho anche trovato che sync_binlog = 0 (dovrebbe essere 1) anche se binlog non è usato.
  • ho anche scoperto che non ho impostato innodb_buffer_pool_instances