SQLite性能基准测试–为什么：memory：这么慢……只有磁盘速度的1.5倍？

Question

为什么在sqlite中：memory：这么慢？

我一直在尝试查看通过使用内存中的sqlite与基于磁盘的sqlite是否可以获得任何性能改进。基本上，我想以启动时间和内存为代价来获得非常快速的查询，这些查询在应用程序运行期间
不会 对磁盘造成影响。

但是，以下基准测试只能使我提高速度1.5倍。在这里，我正在生成一百万行随机数据，并将其加载到同一表的基于磁盘和内存的版本中。然后，我在两个数据库上运行随机查询，返回大小约为300k的集合。我期望基于内存的版本会更快，但是如上所述，我的速度只有1.5倍。

我尝试了其他几种大小的数据库和查询集。内存：优势 也 似乎上升为行的分贝数增加了。我不确定为什么优势如此之小，尽管我有一些假设：

• 所用的表不够大（成行），无法形成：memory：一个巨大的赢家
• 更多的联接/表将使：memory：优势更加明显
• 在连接或操作系统级别进行某种缓存，以便以某种方式可以访问以前的结果，从而破坏基准
• 我没有看到某种隐藏的磁盘访问（我还没有尝试过lsof，但是我确实关闭了PRAGMA的日志功能）

我在这里做错什么了吗？为什么：memory：没有产生几乎即时的查询？这是基准：

==> 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是否应该相对于磁盘而言几乎是即时的？这是怎么了

编辑

这里有一些好的建议。

我想对我来说，主要的收获是可能没有办法使：memory： 绝对更快 ，但是有一种方法可以使磁盘访问 相对较慢。

换句话说，基准测试足以衡量内存的实际性能，但不能衡量磁盘的实际性能（例如，因为cache_size编译指示太大或因为我没有写数据）。我会弄乱那些参数，并在有机会时发布我的发现。

就是说，如果有人认为我可以从内存数据库中挤出更多的速度（除了提高我将要做的cache_size和default_cache_size的能力），我全都…

Answer 1

它与SQLite具有页面缓存的事实有关。根据文档，默认页面缓存为2000个1K页面或大约2Mb。由于这大约占您数据的75％到90％，因此这两个数字非常相似也就不足为奇了。我的猜测是，除了SQLite页面缓存外，其余数据仍在OS磁盘缓存中。如果让SQLite刷新页面缓存（和磁盘缓存），您将看到一些真正的显着差异。