La velocità di inserimento di SQLite rallenta all’aumentare del numero di record a causa di un indice

Domanda originale

sfondo

È risaputo che SQLite deve essere regolato per ottenere velocità di inserimento dell’ordine di 50k inserti / s. Ci sono molte domande qui riguardo velocità di inserimento lente e una ricchezza di consigli e punti di riferimento.

Ci sono anche affermazioni che SQLite può gestire grandi quantità di dati , con report di oltre 50 GB che non causano problemi con le impostazioni corrette.

Ho seguito il consiglio qui e altrove per raggiungere queste velocità e sono felice con gli inserti / i 35k-45k. Il problema che ho è che tutti i benchmark dimostrano solo velocità di inserimento veloci con record <1m. Quello che sto vedendo è che la velocità di inserimento sembra essere inversamente proporzionale alla dimensione del tavolo .

Problema

Il mio caso d’uso richiede la memorizzazione di tuple da 500 a 1 b ( [x_id, y_id, z_id] ) su alcuni anni (1m di file / giorno) in una tabella di collegamenti. I valori sono tutti gli ID interi compresi tra 1 e 2.000.000. C’è un singolo indice su z_id .

Le prestazioni sono ottime per le prime 10 m righe, ~ 35k inserzioni / s, ma quando la tabella ha ~ 20 milioni di righe, le prestazioni cominciano a risentirne. Ora sto vedendo circa 100 inserti / s.

La dimensione del tavolo non è particolarmente grande. Con 20 m di file, la dimensione sul disco è di circa 500 MB.

Il progetto è scritto in Perl.

Domanda

È questa la realtà delle tabelle di grandi dimensioni in SQLite o ci sono dei segreti per mantenere alti tassi di inserimento per le tabelle con> 10 milioni di righe?

Soluzioni alternative note che vorrei evitare, se ansible

  • Rilasciare l’indice, aggiungere i record e reindicizzare : va bene come soluzione temporanea, ma non funziona quando il DB deve ancora essere utilizzabile durante gli aggiornamenti. Non funzionerà per rendere il database completamente inaccessibile per x minuti / giorno
  • Rompere la tabella in sottotabella / file più piccoli : funzionerà a breve termine e l’ho già sperimentato. Il problema è che ho bisogno di essere in grado di recuperare i dati dall’intera cronologia durante le interrogazioni, il che significa che alla fine avrò raggiunto il limite di 62 allegati. Albind, raccogliere risultati in una tabella temporanea e staccare centinaia di volte per richiesta sembra essere un sacco di lavoro e sovraccarico, ma ci proverò se non ci sono altre alternative.
  • Imposta SQLITE_FCNTL_CHUNK_SIZE : non conosco C (?!), Quindi preferirei non impararlo solo per ottenere questo risultato. Non riesco a vedere alcun modo per impostare questo parametro usando Perl.

AGGIORNARE

Seguendo il suggerimento di Tim che un indice stava causando tempi di inserimento sempre più lenti nonostante le affermazioni di SQLite che è in grado di gestire ampi set di dati, ho eseguito un confronto tra benchmark con le seguenti impostazioni:

  • righe inserite: 14 milioni
  • commit dimensione batch: 50.000 record
  • cache_size pragma: 10.000
  • page_size pragma: 4.096
  • temp_store pragma: memoria
  • journal_mode pragma: delete
  • pragma synchronous : spento

Nel mio progetto, come nei risultati di benchmark riportati di seguito, viene creata una tabella temporanea basata su file e viene utilizzato il supporto integrato di SQLite per l’importazione di dati CSV. La tabella temporanea viene quindi allegata al database ricevente e set di 50.000 righe vengono inseriti con un’istruzione insert-select . Pertanto, i tempi di inserimento non riflettono il tempo di inserimento del file nel database , ma piuttosto la velocità di inserimento da tabella a tabella . Considerare il tempo di importazione CSV ridurrebbe le velocità del 25-50% (una stima molto approssimativa, non ci vuole molto tempo per importare i dati CSV).

Chiaramente avere un indice provoca il rallentamento della velocità di inserimento con l’aumentare delle dimensioni della tabella.

Grafico di SQLite per inserire velocità e dimensioni della tabella

È abbastanza chiaro dai dati sopra riportati che la risposta corretta può essere assegnata alla risposta di Tim piuttosto che le asserzioni che SQLite non è in grado di gestirlo. Chiaramente può gestire dataset di grandi dimensioni se l’ indicizzazione del set di dati non fa parte del tuo caso d’uso. Ho usato SQLite proprio per questo, come backend per un sistema di logging, per un po ‘ora che non ha bisogno di essere indicizzato, quindi sono rimasto piuttosto sorpreso dal rallentamento che ho vissuto.

Conclusione

Se qualcuno si trova a voler archiviare una grande quantità di dati usando SQLite e farlo indicizzare, l’ uso di frammenti potrebbe essere la risposta. Alla fine ho deciso di utilizzare i primi tre caratteri di un hash MD5 una colonna univoca in z per determinare l’assegnazione a uno dei 4.096 database. Poiché il mio caso d’uso è principalmente di natura archivistica, lo schema non cambierà e le query non richiederanno mai la marcia a frammenti. Esiste un limite alle dimensioni del database poiché i dati estremamente vecchi verranno ridotti e alla fine eliminati, quindi questa combinazione di sharding, impostazioni di pragma e anche un po ‘ di normalizzazione mi dà un buon bilanciamento che, basato sul benchmarking precedente, manterrà una velocità di inserimento di almeno 10 k di inserti / secondo.

Se il tuo requisito è trovare un determinato z_id e gli x_ids e gli y_ids collegati (distinto dalla selezione rapida di un intervallo di z_ids) potresti guardare in un db nested-relazionale non indicizzato della tabella hash che ti permetterebbe di trovare immediatamente la tua strada verso un determinato z_id per ottenere i suoi y_ids e x_ids – senza il sovraccarico di indicizzazione e la concomitante prestazione degradata durante gli inserti man mano che l’indice aumenta. Al fine di evitare collisioni con secchi o collisioni, scegli un algoritmo di hashing chiave che attribuisca il maggior peso alle cifre di z_id con maggiore variazione (ponderata a destra).

PS Un database che usa un b-tree può apparire a prima vista più veloce di un db che usa l’hashing lineare, per esempio, ma le prestazioni dell’inserto rimarranno all’altezza dell’hash lineare, poiché le prestazioni sull’albero b iniziano a deteriorarsi.

PPS Rispondere alla domanda di kawing-chiu: la caratteristica principale rilevante qui è che tale database si basa sulle cosiddette tabelle “sparse” in cui la posizione fisica di un record è determinata da un algoritmo di hashing che prende come input la chiave del record. Questo approccio consente di ricercare direttamente la posizione del record nella tabella senza l’intermediazione di un indice . Poiché non è necessario attraversare gli indici o ribilanciare gli indici, i tempi di inserimento rimangono costanti man mano che la tabella diventa più densamente popolata. Con un albero b, al contrario, i tempi di inserimento diminuiscono man mano che l’albero dell’indice cresce. Le applicazioni OLTP con un numero elevato di inserimenti simultanei possono trarre vantaggio da un approccio così spoglio. I documenti sono sparsi per tutto il tavolo. Lo svantaggio dei record che vengono sparpagliati nella “tundra” della tabella sparsa è che la raccolta di grandi serie di record che hanno un valore in comune, come un codice postale, può essere più lenta. L’approccio a tabelle sparse con hash è ottimizzato per inserire e recuperare singoli record e per recuperare le reti di record correlati, non grandi insiemi di record che hanno in comune alcuni valori di campo.

Un database relazionale annidato è uno che consente le tuple all’interno di una colonna di una riga.

Grande domanda e follow-up molto interessante!

Vorrei solo fare una breve osservazione: hai detto che rompere il tavolo in sottotabella / file più piccoli e collegarli in seguito non è un’opzione perché raggiungerai rapidamente il limite rigido di 62 database collegati. Anche se questo è completamente vero, non penso che tu abbia considerato un’opzione intermedia: suddividere i dati in più tabelle, ma continuare a utilizzare lo stesso singolo database (file).


Ho fatto un benchmark molto crudo solo per assicurarmi che il mio suggerimento abbia davvero un impatto sulle prestazioni.

Schema:

 CREATE TABLE IF NOT EXISTS "test_$i" ( "i" integer NOT NULL, "md5" text(32) NOT NULL ); 

Dati: 2 milioni di righe:

  • i = 1. 2.000.000
  • md5 = md5 hex digest di i

Ogni transazione = 50.000 INSERT s.


Database: 1; Tabelle: 1; Indici: 0

 0..50000 records inserted in 1.87 seconds 50000..100000 records inserted in 1.92 seconds 100000..150000 records inserted in 1.97 seconds 150000..200000 records inserted in 1.99 seconds 200000..250000 records inserted in 2.19 seconds 250000..300000 records inserted in 1.94 seconds 300000..350000 records inserted in 1.94 seconds 350000..400000 records inserted in 1.94 seconds 400000..450000 records inserted in 1.94 seconds 450000..500000 records inserted in 2.50 seconds 500000..550000 records inserted in 1.94 seconds 550000..600000 records inserted in 1.94 seconds 600000..650000 records inserted in 1.93 seconds 650000..700000 records inserted in 1.94 seconds 700000..750000 records inserted in 1.94 seconds 750000..800000 records inserted in 1.94 seconds 800000..850000 records inserted in 1.93 seconds 850000..900000 records inserted in 1.95 seconds 900000..950000 records inserted in 1.94 seconds 950000..1000000 records inserted in 1.94 seconds 1000000..1050000 records inserted in 1.95 seconds 1050000..1100000 records inserted in 1.95 seconds 1100000..1150000 records inserted in 1.95 seconds 1150000..1200000 records inserted in 1.95 seconds 1200000..1250000 records inserted in 1.96 seconds 1250000..1300000 records inserted in 1.98 seconds 1300000..1350000 records inserted in 1.95 seconds 1350000..1400000 records inserted in 1.95 seconds 1400000..1450000 records inserted in 1.95 seconds 1450000..1500000 records inserted in 1.95 seconds 1500000..1550000 records inserted in 1.95 seconds 1550000..1600000 records inserted in 1.95 seconds 1600000..1650000 records inserted in 1.95 seconds 1650000..1700000 records inserted in 1.96 seconds 1700000..1750000 records inserted in 1.95 seconds 1750000..1800000 records inserted in 1.95 seconds 1800000..1850000 records inserted in 1.94 seconds 1850000..1900000 records inserted in 1.95 seconds 1900000..1950000 records inserted in 1.95 seconds 1950000..2000000 records inserted in 1.95 seconds 

Dimensione del file di database: 89,2 MiB.


Database: 1; Tabelle: 1; Indici: 1 ( md5 )

 0..50000 records inserted in 2.90 seconds 50000..100000 records inserted in 11.64 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.62 seconds 200000..250000 records inserted in 11.28 seconds 250000..300000 records inserted in 12.09 seconds 300000..350000 records inserted in 10.60 seconds 350000..400000 records inserted in 12.25 seconds 400000..450000 records inserted in 13.83 seconds 450000..500000 records inserted in 14.48 seconds 500000..550000 records inserted in 11.08 seconds 550000..600000 records inserted in 10.72 seconds 600000..650000 records inserted in 14.99 seconds 650000..700000 records inserted in 10.85 seconds 700000..750000 records inserted in 11.25 seconds 750000..800000 records inserted in 17.68 seconds 800000..850000 records inserted in 14.44 seconds 850000..900000 records inserted in 19.46 seconds 900000..950000 records inserted in 16.41 seconds 950000..1000000 records inserted in 22.41 seconds 1000000..1050000 records inserted in 24.68 seconds 1050000..1100000 records inserted in 28.12 seconds 1100000..1150000 records inserted in 26.85 seconds 1150000..1200000 records inserted in 28.57 seconds 1200000..1250000 records inserted in 29.17 seconds 1250000..1300000 records inserted in 36.99 seconds 1300000..1350000 records inserted in 30.66 seconds 1350000..1400000 records inserted in 32.06 seconds 1400000..1450000 records inserted in 33.14 seconds 1450000..1500000 records inserted in 47.74 seconds 1500000..1550000 records inserted in 34.51 seconds 1550000..1600000 records inserted in 39.16 seconds 1600000..1650000 records inserted in 37.69 seconds 1650000..1700000 records inserted in 37.82 seconds 1700000..1750000 records inserted in 41.43 seconds 1750000..1800000 records inserted in 49.58 seconds 1800000..1850000 records inserted in 44.08 seconds 1850000..1900000 records inserted in 57.17 seconds 1900000..1950000 records inserted in 50.04 seconds 1950000..2000000 records inserted in 42.15 seconds 

Dimensione del file di database: 181,1 MiB.


Database: 1; Tabelle: 20 (una per 100.000 record); Indici: 1 ( md5 )

 0..50000 records inserted in 2.91 seconds 50000..100000 records inserted in 10.30 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.45 seconds 200000..250000 records inserted in 10.11 seconds 250000..300000 records inserted in 11.04 seconds 300000..350000 records inserted in 10.25 seconds 350000..400000 records inserted in 10.36 seconds 400000..450000 records inserted in 11.48 seconds 450000..500000 records inserted in 10.97 seconds 500000..550000 records inserted in 10.86 seconds 550000..600000 records inserted in 10.35 seconds 600000..650000 records inserted in 10.77 seconds 650000..700000 records inserted in 10.62 seconds 700000..750000 records inserted in 10.57 seconds 750000..800000 records inserted in 11.13 seconds 800000..850000 records inserted in 10.44 seconds 850000..900000 records inserted in 10.40 seconds 900000..950000 records inserted in 10.70 seconds 950000..1000000 records inserted in 10.53 seconds 1000000..1050000 records inserted in 10.98 seconds 1050000..1100000 records inserted in 11.56 seconds 1100000..1150000 records inserted in 10.66 seconds 1150000..1200000 records inserted in 10.38 seconds 1200000..1250000 records inserted in 10.24 seconds 1250000..1300000 records inserted in 10.80 seconds 1300000..1350000 records inserted in 10.85 seconds 1350000..1400000 records inserted in 10.46 seconds 1400000..1450000 records inserted in 10.25 seconds 1450000..1500000 records inserted in 10.98 seconds 1500000..1550000 records inserted in 10.15 seconds 1550000..1600000 records inserted in 11.81 seconds 1600000..1650000 records inserted in 10.80 seconds 1650000..1700000 records inserted in 11.06 seconds 1700000..1750000 records inserted in 10.24 seconds 1750000..1800000 records inserted in 10.57 seconds 1800000..1850000 records inserted in 11.54 seconds 1850000..1900000 records inserted in 10.80 seconds 1900000..1950000 records inserted in 11.07 seconds 1950000..2000000 records inserted in 13.27 seconds 

Dimensione del file di database: 180,1 MiB.


Come puoi vedere, la velocità di inserimento rimane praticamente costante se dividi i dati in più tabelle.

Sfortunatamente direi che questa è una limitazione delle tabelle di grandi dimensioni in SQLite. Non è progettato per funzionare su set di dati di grandi dimensioni o di grandi volumi. Pur comprendendo che potrebbe aumentare drasticamente la complessità del progetto, probabilmente stai meglio alla ricerca di soluzioni di database più sofisticate appropriate alle tue esigenze.

Da tutto ciò che hai collegato, sembra che la dimensione della tabella per accedere alla velocità sia un compromesso diretto. Non posso avere entrambi.

Nel mio progetto, non ho potuto dividere il database, poiché è indicizzato su colonne diverse. Per velocizzare gli inserti, ho messo il database durante la creazione su / dev / shm (= linux ramdisk) e quindi copiato sul disco locale. Questo ovviamente funziona bene solo per un database write-once, read-many.

Sospetto che la collisione del valore hash dell’indice causi una lentezza della velocità di inserimento.

Quando abbiamo molte molte righe in una tabella, e quindi la collisione del valore hash della colonna indicizzata avverrà più frequentemente. Significa che il motore Sqlite deve calcolare il valore hash due o tre volte, o forse anche quattro volte, per ottenere un valore hash diverso.

Quindi immagino che questa sia la causa principale della lentezza dell’inserimento di SQLite quando la tabella ha molte righe.

Questo punto potrebbe spiegare perché l’uso di frammenti potrebbe evitare questo problema. Chi è un vero esperto nel dominio SQLite per confermare o negare il mio punto qui?