[python] SQLite Performance Benchmark - perché è: memoria: così lento ... solo 1.5X più veloce del disco?


Answers

La mia domanda per te è, cosa stai cercando di punto di riferimento?

Come già accennato, SQLite: memory: DB è lo stesso di quello basato su disco, cioè paginato, e l'unica differenza è che le pagine non vengono mai scritte su disco. Quindi l'unica differenza tra i due sono le scritture del disco: memoria: non ha bisogno di fare (non ha nemmeno bisogno di leggere alcun disco, quando una pagina del disco deve essere scaricata dalla cache).

Ma leggere / scrivere dalla cache può rappresentare solo una frazione del tempo di elaborazione della query, a seconda della query. La tua query ha una clausola where con due grandi insiemi di id di cui devono essere membri le righe selezionate, il che è costoso.

Come Cary Millsap dimostra nel suo blog sull'ottimizzazione di Oracle (ecco un post di rappresentanza: http://carymillsap.blogspot.com/2009/06/profiling-with-my-boy.html ), è necessario capire quali parti della query l'elaborazione richiede tempo. Supponendo che i test dell'appartenenza agli insiemi rappresentino il 90% del tempo di interrogazione e l'IO 10% basato su disco, va a: memoria: salva solo il 10%. Questo è un esempio estremo improbabile che sia rappresentativo, ma spero che ciò dimostri che la tua particolare domanda sta inclinando i risultati. Utilizzare una query più semplice e le parti IO dell'elaborazione della query aumenteranno, e quindi il vantaggio di: memoria :.

Come nota finale, abbiamo sperimentato con le tabelle virtuali di SQLite, dove sei responsabile dello storage effettivo, e utilizzando i contenitori C ++, che sono tipizzati a differenza del modo in cui SQLite memorizza i valori delle celle, potremmo vedere un'impronta significativa nel tempo di elaborazione over: memory :, ma sta ottenendo un po 'di argomento;) --DD

PS: Non ho abbastanza Karma per commentare il post più popolare di questo thread, quindi sto commentando qui :) per dire che la versione SQLite recente non usa le pagine 1KB di default su Windows: http://www.sqlite.org/changes.html#version_3_6_12

Question

Perché è: memoria: in sqlite così lento?

Sto provando a vedere se ci sono miglioramenti delle prestazioni ottenuti usando la sqlite in-memory rispetto alla sqlite basata su disco. Fondamentalmente vorrei scambiare tempo di avvio e memoria per ottenere query estremamente rapide che non colpiscono il disco durante il corso dell'applicazione.

Tuttavia, il seguente benchmark mi dà solo un fattore di 1.5X in termini di velocità migliorata. Qui, sto generando 1M di file di dati casuali e caricandoli sia in una versione basata su disco che in memoria della stessa tabella. Quindi eseguo query casuali su entrambi i db, restituendo insiemi di dimensioni circa 300k. Mi aspettavo che la versione basata su memoria fosse considerevolmente più veloce, ma come accennato ricevo solo aumenti di velocità di 1,5 volte.

Ho sperimentato diverse altre dimensioni di dbs e set di query; il vantaggio di: memoria: sembra aumentare man mano che aumenta il numero di righe nel db. Non sono sicuro del perché il vantaggio sia così piccolo, anche se ho avuto alcune ipotesi:

  • la tabella utilizzata non è abbastanza grande (in righe) da fare: memoria: un enorme vincitore
  • più joins / tables renderebbero: la memoria: vantaggio più evidente
  • c'è un tipo di memorizzazione nella cache che si verifica alla connessione o al livello del sistema operativo in modo tale che i risultati precedenti siano accessibili in qualche modo, corrompendo il benchmark
  • c'è una sorta di accesso al disco nascosto in corso che non vedo (non ho ancora provato lsof, ma ho disattivato i PRAGMA per l'inserimento nel journal)

Sto facendo qualcosa di sbagliato qui? Qualche idea sul perché: memoria: non produce quasi istantanee ricerche? Ecco il punto di riferimento:

==> sqlite_memory_vs_disk_benchmark.py <==

#!/usr/bin/env python
"""Attempt to see whether :memory: offers significant performance benefits.

"""
import os
import time
import sqlite3
import numpy as np

def load_mat(conn,mat):
    c = conn.cursor()

    #Try to avoid hitting disk, trading safety for speed.
    #http://.com/questions/304393
    c.execute('PRAGMA temp_store=MEMORY;')
    c.execute('PRAGMA journal_mode=MEMORY;')

    # Make a demo table
    c.execute('create table if not exists demo (id1 int, id2 int, val real);')
    c.execute('create index id1_index on demo (id1);')
    c.execute('create index id2_index on demo (id2);')
    for row in mat:
        c.execute('insert into demo values(?,?,?);', (row[0],row[1],row[2]))
    conn.commit()

def querytime(conn,query):
    start = time.time()
    foo = conn.execute(query).fetchall()
    diff = time.time() - start
    return diff

#1) Build some fake data with 3 columns: int, int, float
nn   = 1000000 #numrows
cmax = 700    #num uniques in 1st col
gmax = 5000   #num uniques in 2nd col

mat = np.zeros((nn,3),dtype='object')
mat[:,0] = np.random.randint(0,cmax,nn)
mat[:,1] = np.random.randint(0,gmax,nn)
mat[:,2] = np.random.uniform(0,1,nn)

#2) Load it into both dbs & build indices
try: os.unlink('foo.sqlite')
except OSError: pass

conn_mem = sqlite3.connect(":memory:")
conn_disk = sqlite3.connect('foo.sqlite')
load_mat(conn_mem,mat)
load_mat(conn_disk,mat)
del mat

#3) Execute a series of random queries and see how long it takes each of these
numqs = 10
numqrows = 300000 #max number of ids of each kind
results = np.zeros((numqs,3))
for qq in range(numqs):
    qsize = np.random.randint(1,numqrows,1)
    id1a = np.sort(np.random.permutation(np.arange(cmax))[0:qsize]) #ensure uniqueness of ids queried
    id2a = np.sort(np.random.permutation(np.arange(gmax))[0:qsize])
    id1s = ','.join([str(xx) for xx in id1a])
    id2s = ','.join([str(xx) for xx in id2a])
    query = 'select * from demo where id1 in (%s) AND id2 in (%s);' % (id1s,id2s)

    results[qq,0] = round(querytime(conn_disk,query),4)
    results[qq,1] = round(querytime(conn_mem,query),4)
    results[qq,2] = int(qsize)

#4) Now look at the results
print "  disk | memory | qsize"
print "-----------------------"
for row in results:
    print "%.4f | %.4f | %d" % (row[0],row[1],row[2])

Ecco i risultati. Nota che il disco impiega circa 1,5 volte la memoria per una gamma piuttosto ampia di dimensioni di query.

[ramanujan:~]$python -OO sqlite_memory_vs_disk_clean.py
  disk | memory | qsize
-----------------------
9.0332 | 6.8100 | 12630
9.0905 | 6.6953 | 5894
9.0078 | 6.8384 | 17798
9.1179 | 6.7673 | 60850
9.0629 | 6.8355 | 94854
8.9688 | 6.8093 | 17940
9.0785 | 6.6993 | 58003
9.0309 | 6.8257 | 85663
9.1423 | 6.7411 | 66047
9.1814 | 6.9794 | 11345

La RAM non dovrebbe essere quasi istantanea rispetto al disco? Cosa c'è che non va qui?

modificare

Alcuni buoni suggerimenti qui.

Immagino che il punto principale di takehome per me sia che ** probabilmente non c'è modo di fare: memoria: assolutamente più veloce , ma c'è un modo per rendere l'accesso al disco relativamente più lento. **

In altre parole, il benchmark misura adeguatamente le prestazioni realistiche della memoria, ma non le prestazioni realistiche del disco (ad esempio perché il pragma cache_size è troppo grande o perché non sto facendo scritture). Conoscerò questi parametri e posterò le mie scoperte quando ne avrò la possibilità.

Detto questo, se c'è qualcuno che pensa che io possa spremere un po 'più di velocità dal db in memoria (a parte il fatto di dover caricare cache_size e default_cache_size, cosa che farò), sono tutto orecchie ...




Il database di memoria in SQLite è in realtà una cache di pagine che non tocca mai il disco. Quindi è necessario dimenticare l'uso della memoria db in SQLite per ottimizzare le prestazioni

È possibile disattivare il journal, disattivare la modalità di sincronizzazione, impostare una cache delle pagine di grandi dimensioni e si avranno quasi le stesse prestazioni nella maggior parte delle operazioni, ma la durata verrà persa.

Dal tuo codice è assolutamente chiaro che DEVI RIPETERE il comando e SOLO i parametri BIND, perché questo ha portato via oltre il 90% delle prestazioni del test.




Potrebbe essere che sqlite3 non stia realmente scrivendo i tuoi dati su disco dalla cache? che potrebbe spiegare perché i numeri sono simili.

Potrebbe anche essere possibile che il sistema operativo stia pagando a causa della scarsa memoria disponibile?




gli array numpy sono più lenti di dict e tuple e di altre sequenze di oggetti finché non si hanno a che fare con 5 milioni o più oggetti in una sequenza. È possibile migliorare notevolmente la velocità di elaborazione di enormi quantità di dati eseguendo il iterazione su di esso e utilizzando i generatori per evitare di creare e ricreare oggetti di grandi dimensioni temporanei.

numpy è diventato il tuo fattore limitante in quanto è progettato per offrire prestazioni lineari. Non è una stella con piccole o grandi quantità di dati. Ma le prestazioni di Numpy non si trasformano in una curva man mano che il set di dati cresce. Resta una linea retta.

Inoltre, SQLite è solo un database molto veloce. Più veloce della maggior parte dei database del server. Si pone la domanda sul perché qualcuno userebbe i database NOSQL quando un database ultrarapido e ultrarapido che usa SQL è stato in circolazione e messo alla prova in tutto, dai browser ai telefoni cellulari per anni.




Links