Warum ist :memory: in Sqlite so langsam?
Ich habe versucht zu sehen, ob es irgendwelche Leistungsverbesserungen durch die Verwendung von In-Memory-Sqlite im Vergleich zu disk-basierten Sqlite gewonnen sind. Grundsätzlich würde ich gerne Startzeit und Speicher tauschen, um extrem schnelle Abfragen zu erhalten, die no im Laufe der Anwendung auf die Festplatte.
Der folgende Benchmark zeigt jedoch nur eine 1,5-fache Verbesserung der Geschwindigkeit. Hier generiere ich 1 Mio. Zeilen Zufallsdaten und lade sie sowohl in eine platten- als auch in eine speicherbasierte Version derselben Tabelle. Dann führe ich auf beiden Datenbanken Zufallsabfragen durch, die Sätze mit einer Größe von etwa 300k zurückgeben. Ich hatte erwartet, dass die speicherbasierte Version erheblich schneller sein würde, aber wie bereits erwähnt, erreiche ich nur eine 1,5-fache Beschleunigung.
Ich habe mit verschiedenen anderen Größen von Datenbanken und Abfragesätzen experimentiert; der Vorteil von :memory: する scheinen zu steigen, wenn die Anzahl der Zeilen in der Datenbank zunimmt. Ich bin mir nicht sicher, warum der Vorteil so gering ist, obwohl ich ein paar Hypothesen hatte:
- die verwendete Tabelle ist nicht groß genug (in Zeilen), um :memory: zu einem großen Gewinner zu machen
- mehr Joins/Tabellen würden den :memory: Vorteil deutlicher machen
- es gibt eine Art Zwischenspeicherung auf Verbindungs- oder Betriebssystemebene, so dass die vorherigen Ergebnisse irgendwie zugänglich sind und den Benchmark verfälschen
- eine Art versteckter Festplattenzugriff stattfindet, den ich nicht sehe (ich habe lsof noch nicht ausprobiert, aber ich habe die PRAGMAs für das Journaling deaktiviert)
Mache ich hier etwas falsch? Haben Sie eine Idee, warum :memory: nicht fast sofortige Suchergebnisse liefert? Hier ist der Benchmark:
==> 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://stackoverflow.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])Hier sind die Ergebnisse. Beachten Sie, dass die Festplatte für eine ziemlich breite Palette von Abfragegrößen etwa 1,5 Mal so viel Zeit benötigt wie der Speicher.
[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 | 11345Sollte der Arbeitsspeicher im Vergleich zur Festplatte nicht fast sofort verfügbar sein? Was läuft hier schief?
bearbeiten
Hier sind einige gute Vorschläge.
Ich denke, die wichtigste Erkenntnis für mich ist, dass es wahrscheinlich keine Möglichkeit gibt, :memory: deutlich schneller aber es gibt eine Möglichkeit, den Plattenzugriff relativ langsamer. **
Mit anderen Worten, der Benchmark misst die realistische Leistung des Speichers angemessen, aber nicht die realistische Leistung der Festplatte (z. B. weil die cache_size pragma zu groß ist oder weil ich keine Schreibvorgänge durchführe). Ich werde mit diesen Parametern herumspielen und meine Ergebnisse veröffentlichen, sobald ich die Gelegenheit dazu habe.
Das heißt, wenn es jemanden gibt, der denkt, ich kann etwas mehr Geschwindigkeit aus der In-Memory-DB quetschen (andere als durch Anheben der cache_size und default_cache_size, die ich tun werde), ich bin ganz Ohr...
