Perché SQLite è così lento (~ 2 q / s) su una macchina specifica?

Su uno dei miei server (i7 Ivy Core, 32 GB di RAM, Debian 6 @ 64 bit, PHP 5.4.10) ho esperienza di inserimenti estremamente lenti con SQLite. Il seguente programma di test riport solo 2,2 inserti / secondo (14 secondi per l'inserimento di 30 righe).

unlink("test.db"); $db = new PDO('sqlite:test.db'); $db->exec("CREATE TABLE test (dummy INT)"); $count = 30; $t = microtime(true); for ($i=0; $i<$count; $i++) { $db->exec("INSERT INTO test VALUES ($i)") or die("SQLite error: ".$db->errorInfo()[2]."\n"); } $elapsed = microtime(true)-$t; echo sprintf("%d inserts in %.3f secs (%.1f q/s)\n", $count, $elapsed, $count/$elapsed); 

Produzione:

 $ time php test.php 30 inserts in 13.911 secs (2.2 q/s) real 0m14.634s user 0m0.004s sys 0m0.016s 

So che posso accelerare questo avvolgendo BEGIN / END attorno alle istruzioni di inserimento (che mi dà 200000 q / s) ma anche senza una transazione questo dovrebbe essere molto più veloce. Su altre macchine (più vecchie) (stessa versione di PHP) raggiungo più di 300 query / sec senza transactions esplicite.

Quale potrebbe essere la causa di questo? Devo sintonizzare Sqlite o l'O / S?

Ho fatto un test simile su una macchina Linux a 64 bit usando strace -C -tt per avere un'idea di where SQLite3 sta prendendo tempo.

 % time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 99.03 0.004000 32 124 fsync 0.64 0.000026 0 222 mprotect 0.32 0.000013 0 216 munmap 

Il ritardo evidente è nella function fsync , che è:

  • configurabile
  • dipende iotop / O generale del disco (controlla iotop , iostat )
  • dipende molto da IOSS (quindi, il file system e l'allocazione del disco – potresti get un valore su ext3, uno diverso su xfs e un terzo su btrfs)
  • dipende ovviamente, indirettamente, dall'hardware sottostante e dalle sue stranezze o accordature.

Distriggersndo la sincronizzazione, le performance di SQLite3 aumentano di un fattore di circa tremila:

 $db = new PDO('sqlite:test.db'); $db->exec('pragma synchronous = off;'); 

Anch'io ho due valori diversi su due macchine molto simili (una ha ext4, l'altra XFS, ma non sono sicuro questo è il motivo principale – anche i loro profili di carico sono diversi).

A proposito, l'uso di istruzioni preparate equivale quasi a raddoublere la velocità di esecuzione al livello più veloce (da 45k a 110k INSERT, in lotti di 3000 poiché a tale velocità 30 INSERT sono obbligati a fornire tempi spuri) e aumenta la velocità più bassa da circa 6 a circa 150.

Quindi questo (usando le istruzioni preparate) potrebbe essere una buona soluzione per migliorare le operazioni ripetute senza toccare la sincronizzazione dei file, ad esempio, pur essendo sempre sicuro che il livello di rischio dei dati rimane lo stesso. Dopo di ciò proverei le transactions o fsync (forse anche il journaling della memory) a seconda del rischio e del valore di un'interruzione dei dati.

Quando si progetta un sistema da zero, sono sicuramente consigliabili alcuni test su diverse FS.

Test su diversi file system (stessa macchina)

 ext4 (acl,user_xattr,data=order) 5.5 queries/s using transactions 170 queries/s disabling fsync 16000 queries/s using transactions and disabling fsync 47200 queries/s 

Su un file system temporaneo , fsync è economico, quindi l'operazione di spegnimento non offre molti vantaggi. La maggior parte del tempo è dedicata alla sorveglianza, quindi le transactions sono fondamentali.

 tmpfs 13700 queries/s disabling fsync 15350 queries/s enabling transactions 47900 queries/s using transactions and disabling fsync 48200 queries/s 

Ovviamente, è necessario tenere conto della corretta organizzazione e indexing dei dati e, per i grandi insiemi di dati, potrebbe rivelarsi più importnte.


AGGIORNAMENTO : per spremere un po 'più di performance, si può anche mettere il diario SQLite in memory con pragma journal_mode=MEMORY;