我一直在尝试使用内存中的sqlite与基于磁盘的sqlite相比,是否有任何性能改进。基本上,我想用启动时间和内存来获得非常快速的查询,这些查询在应用程序过程中不会命中磁盘。
但是,下面的基准测试只给了我1.5倍的速度提升系数。在这里,我生成了1M行随机数据,并将其加载到同一个表的基于磁盘和内存的版本中。然后,我在两个数据库上运行随机查询,返回大约300k的大小集。我希望基于内存的版本要快得多,但如前所述,我只得到1.5倍的加速。
我尝试了其他几种大小的dbs和查询集;其优点是:内存:似乎会随着db中行数的增加而增加。我不知道为什么优势这么小,尽管我有几个假设:
我在这里做错什么了吗?关于原因的任何想法:记忆:不产生几乎即时的查找吗?以下是基准:
==> 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])
这是结果。注意,对于相当大范围的查询大小,磁盘占用的内存大约是内存的1.5倍。
[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
RAM相对于磁盘不应该几乎是即时的吗?这里出什么事了?
这里有一些好的建议。
我想我的主要观点是**可能没有办法:内存:绝对更快,但是有一种方法可以使磁盘访问相对较慢。**
换言之,基准测试充分衡量了内存的实际性能,而不是磁盘的实际性能(例如,由于缓存大小pragma太大或因为我没有执行写操作)。当我有机会的时候,我会处理这些参数并发布我的发现。
也就是说,如果有人认为我可以从内存数据库中挤出更多的速度(而不是通过提高缓存大小和默认缓存大小,我会这么做),我会全神贯注。。。
我问你的问题是,你想基准什么?
如前所述,SQLite的:memory:DB与基于磁盘的相同,即paged,唯一的区别是页面从未写入磁盘。因此,两者之间的唯一区别是磁盘写入:内存:不需要执行(当必须从缓存中卸载磁盘页时,也不需要执行任何磁盘读取)。
但是,根据查询的不同,缓存中的读/写可能只表示查询处理时间的一小部分。您的查询有一个where子句,其中包含两个选定行必须是其成员的大id集,这很昂贵。
正如Cary Millsap在其关于优化Oracle的博客(这里有一篇有代表性的文章:http://carymillsap.blogspot.com/2009/06/profiling-with-my-boy.html)中演示的那样,您需要了解查询处理的哪些部分需要时间。假设集合成员资格测试表示90%的查询时间,而基于磁盘的IO为10%,则转到:memory:saves only that 10%。这是一个不太可能具有代表性的极端示例,但我希望它能说明您的特定查询会使结果倾斜。使用一个更简单的查询,查询处理的IO部分将增加,因此:memory:。
作为最后一个注意事项,我们已经对SQLite的虚拟表进行了实验,在那里你负责实际存储,并且通过使用不同于SQLite存储单元值的方式的C++容器,我们可以看到处理时间上的显著改进:内存:但是这是话题的一部分;
附言:我没有足够的业力来评论这篇文章中最受欢迎的帖子, 所以我在这里评论:)说最新的SQLite版本不使用1KB 默认情况下,Windows上的页:http://www.sqlite.org/changes.html#version_3_6_12
你在做选择,你在用内存缓存。尝试交叉选择更新。
这与SQLite有页面缓存有关。根据Documentation,默认的页缓存是20001k页或大约2Mb。因为这大约是你数据的75%到90%,所以这两个数字非常相似并不奇怪。我的猜测是,除了SQLite页面缓存之外,其余的数据仍在OS磁盘缓存中。如果让SQLite刷新页面缓存(和磁盘缓存),您将看到一些非常显著的差异。
相关问题 更多 >
编程相关推荐