Qual è più veloce: più singoli INSERT o più INSERT a più righe?

Sto cercando di ottimizzare una parte del mio codice che inserisce i dati in MySQL. Devo inserire INSERTs per creare un enorme INSERT a più righe o più INSERT multipli più velocemente?

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Il tempo richiesto per inserire una riga è determinato dai seguenti fattori, in cui i numeri indicano le proporzioni approssimative:

  • Collegamento: (3)
  • Invio di query al server: (2)
  • Query di analisi: (2)
  • Inserimento riga: (1 × dimensione della riga)
  • Inserimento di indici: (1 × numero di indici)
  • Chiusura: (1)

Da questo dovrebbe essere ovvio, che l’invio di una dichiarazione di grandi dimensioni ti farà risparmiare un overhead di 7 per ogni istruzione di inserimento, che in ulteriore lettura del testo dice anche:

Se si inseriscono più righe dallo stesso client contemporaneamente, utilizzare le istruzioni INSERT con più elenchi VALUES per inserire più righe alla volta. Questo è considerevolmente più veloce (molte volte più veloce in alcuni casi) rispetto all’utilizzo di istruzioni INSERT a riga singola separate.

So che sto rispondendo a questa domanda quasi due anni e mezzo dopo che è stato chiesto, ma volevo solo fornire alcuni dati concreti da un progetto su cui sto lavorando proprio ora che dimostra che effettivamente fare più VALUE blocchi per inserto è MOLTO più veloce delle singole istruzioni INSERT del blocco VALUE sequenziali.

Il codice che ho scritto per questo benchmark in C # utilizza ODBC per leggere i dati in memoria da un’origine dati MSSQL (~ 19.000 righe, tutti vengono letti prima dell’inizio di qualsiasi scrittura) e il componente MySql .NET (Mysql.Data. *) Per INSERISCI i dati dalla memoria in una tabella su un server MySQL tramite istruzioni preparate. È stato scritto in modo tale da permettermi di regolare dynamicmente il numero di VALUE blocchi per INSERT preparato (cioè inserire n righe alla volta, in cui potrei regolare il valore di n prima di una corsa.) Ho anche eseguito il test più volte per ogni n.

L’esecuzione di singoli blocchi VALUE (ad es. 1 riga alla volta) richiedeva 5.7 – 5.9 secondi di esecuzione. Gli altri valori sono i seguenti:

2 file alla volta: 3,5 – 3,5 secondi
5 file alla volta: 2,2 – 2,2 secondi
10 file alla volta: 1.7 – 1.7 secondi
50 righe alla volta: 1,17 – 1,18 secondi
100 righe alla volta: 1,1 – 1,4 secondi
500 righe alla volta: 1,1 – 1,2 secondi
1000 righe alla volta: 1,17-1,17 secondi

Quindi sì, anche il solo raggruppamento di 2 o 3 scritture fornisce un notevole miglioramento della velocità (tempo di esecuzione ridotto di un fattore di n), fino ad arrivare da qualche parte tra n = 5 e n ​​= 10, a quel punto il miglioramento diminuisce notevolmente, e da qualche parte nell’intervallo da n = 10 a n = 50 il miglioramento diventa trascurabile.

Spero che aiuti le persone a decidere su (a) se usare l’idea multiparepara e (b) quanti VALUE blocchi creare per dichiarazione (assumendo che tu voglia lavorare con dati che possono essere abbastanza grandi da spingere la query oltre la dimensione massima della query per MySQL, che credo sia 16MB di default in molti posti, probabilmente più big o più piccolo a seconda del valore di max_allowed_packet impostato sul server.)

Un fattore importante sarà se si sta utilizzando un motore transazionale e se si dispone di autocommit.

L’autocommit è attivo per impostazione predefinita e probabilmente lo si vuole lasciare attivo; quindi, ogni inserto che fai esegue la sua stessa transazione. Ciò significa che se si esegue un inserimento per riga, si impegna una transazione per ogni riga.

Supponendo un singolo thread, ciò significa che il server deve sincronizzare alcuni dati sul disco per OGNI RIGA. È necessario attendere che i dati raggiungano una posizione di archiviazione persistente (si spera che la RAM sia supportata dalla batteria nel controller RAID). Questo è intrinsecamente piuttosto lento e probabilmente diventerà il fattore limitante in questi casi.

Naturalmente presumo che tu stia utilizzando un motore transazionale (solitamente innodb) E che non hai ottimizzato le impostazioni per ridurre la durata.

Suppongo anche che tu stia usando un singolo thread per fare questi inserti. L’utilizzo di più thread confonde le cose un po ‘perché alcune versioni di MySQL hanno il commit di gruppo in-innodb – questo significa che più thread che eseguono i propri commit possono condividere una singola scrittura nel log delle transazioni, il che è positivo perché significa meno sincronizzazioni per l’archiviazione persistente .

D’altra parte, il risultato è che VUOI VERAMENTE UTILIZZARE inserti multi-riga.

Esiste un limite oltre il quale diventa controproducente, ma nella maggior parte dei casi si tratta di almeno 10.000 righe. Quindi, se li metti in batch fino a 1.000 righe, probabilmente sei al sicuro.

Se usi MyISAM, c’è un altro carico di cose, ma non ti annoierò con quelle. Pace.

Invia quanti più inserti attraverso il filo contemporaneamente ansible. La velocità di inserimento effettiva dovrebbe essere la stessa, ma si noterà un miglioramento delle prestazioni dalla riduzione del sovraccarico della rete.

In generale, il minor numero di chiamate al database è il migliore (che significa più veloce, più efficiente), quindi prova a codificare gli inserimenti in modo tale da minimizzare gli accessi al database. Ricordare che, a meno che non si utilizzi un pool di connessioni, ciascun accesso al database deve creare una connessione, eseguire lo sql e quindi interrompere la connessione. Un bel po ‘di spese generali!

Potresti volere :

  • Verifica che il commit automatico sia distriggersto
  • Connessione aperta
  • Invia più lotti di inserti in una singola transazione (dimensione di circa 4000-10000 righe? Vedi)
  • Chiudere la connessione

A seconda del livello di scalabilità del server (in modo definitivo con PostgreSQl , Oracle e MSSQL ), eseguire la procedura descritta sopra con più thread e più connessioni.

MYSQL 5.5 Un’istruzione di inserimento sql richiedeva ~ 300 ~ ~ 450ms. mentre le statistiche sottostanti sono per inserzioni multiple in inserimento.

 (25492 row(s) affected) Execution Time : 00:00:03:343 Transfer Time : 00:00:00:000 Total Time : 00:00:03:343 

Direi in linea è la strada da percorrere 🙂

In generale, più inserti saranno più lenti a causa del sovraccarico della connessione. Fare più inserimenti contemporaneamente ridurrà il costo del sovraccarico per inserto.

A seconda della lingua che si sta utilizzando, è ansible creare un batch nel linguaggio di programmazione / scripting prima di passare al db e aggiungere ciascun inserto al batch. Quindi sarai in grado di eseguire un batch di grandi dimensioni utilizzando un’unica operazione di connessione. Ecco un esempio in Java.

Disabilitare i controlli dei vincoli rende gli inserti molto più veloci. Non importa il tuo tavolo ce l’ha o no. Ad esempio prova disabilitando le chiavi esterne e goditi la velocità:

 SET FOREIGN_KEY_CHECKS=0; 

È ridicolo il modo in cui Mysql e MariaDB sono pessimi quando si tratta di inserti. Ho provato mysql 5.7 e mariadb 10.3, nessuna vera differenza su quelli.

Ho provato questo su un server con dischi NVME, 70.000 IOPS, throughput seq di 1,1 GB / sec e che è ansible full duplex (lettura e scrittura).
Anche il server è un server ad alte prestazioni.
Ha dato 20 GB di RAM.
Il database è completamente vuoto.

La velocità che ricevo era di 5000 inserti al secondo quando si eseguivano inserti multi riga (provati con 1 MB fino a 10 MB di blocchi di dati)

Ora l’indizio:
Se aggiungo un altro thread e lo inserisco nelle tabelle SAME, ho improvvisamente 2×5000 / sec. Un altro thread e ho 15000 totali / sec

Considera questo: quando si eseguono inserimenti ONE ONE, è ansible scrivere sequenzialmente sul disco (con eccezioni agli indici). Quando si utilizzano i thread, si degradano effettivamente le prestazioni possibili perché ora è necessario eseguire molti più accessi casuali. Ma il controllo della realtà mostra che mysql è ottimizzato così male che i thread aiutano molto.

Le prestazioni reali possibili con un tale server sono probabilmente milioni al secondo, la CPU è intriggers, il disco è inattivo.
Il motivo è abbastanza chiaro che mariadb proprio come mysql ha ritardi interni.