Small (un-representative) benchmark on sqlite with blobs

Mon Jun 25 00:40:33 UTC 2007

Hi,

David, Kris and myself had a (short) discussion about how to persist
duchain data. Especially with multi-projects in mind we might get quite
some data.

As David said he had issues with bdb in KDevelop3.4 we pondered wether
using a sql db with blobs might be a good idea (with a really simple
table layout). We can use the Qt db classes here to not have to fiddle
with the low-level API of the dbs.

So I created a short testcase for reading a 140 Meg file from disk,
storing it into the db and retrieving it again. All this done on sqlite,
because mysql and postgresql need more setup to be done (with sqlite all
one needs to provide is a database name).

I don't have the exact numbers for that anymore, but it mainly is like
this:
1x140Meg testfile
Fetching from db with cold cache: about 1 minutes
Writing to db: about 40 seconds
fetching from db with cache: 10 seconds
Reading of file from disk with QFile and cold caches: 10 seconds

Now I had another 30 minutes and extended that to a more suitable test,
using 20 35 meg files of binary data (not sure wether thats the duchain
size later on, but well...). Storing these 20 files looks like this:

,----
| andreas at morpheus:~/temp/testsqlite>./testsqlite -create
| true
| Drop table result: QSqlError(1, "Unable to execute statement", "no such table: f1")
| create table result: QSqlError(-1, "", "")
| "Reading Start(1xaa):" QDateTime("Mo Jun 25 02:23:57 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:01 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:01 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:16 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(2xaa):" QDateTime("Mo Jun 25 02:24:16 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:19 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:19 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:22 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(3xaa):" QDateTime("Mo Jun 25 02:24:22 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:24 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:24 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:29 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(4xaa):" QDateTime("Mo Jun 25 02:24:29 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:31 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:31 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:34 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(5xaa):" QDateTime("Mo Jun 25 02:24:34 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:38 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:38 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:41 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(1xab):" QDateTime("Mo Jun 25 02:24:41 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:44 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:44 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:24:49 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(2xab):" QDateTime("Mo Jun 25 02:24:49 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:24:55 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:24:55 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:01 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(3xab):" QDateTime("Mo Jun 25 02:25:01 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:06 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:06 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:09 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(4xab):" QDateTime("Mo Jun 25 02:25:09 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:14 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:14 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:18 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(5xab):" QDateTime("Mo Jun 25 02:25:18 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:22 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:22 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:26 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(1xac):" QDateTime("Mo Jun 25 02:25:26 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:33 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:33 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:36 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(2xac):" QDateTime("Mo Jun 25 02:25:36 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:39 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:39 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:42 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(3xac):" QDateTime("Mo Jun 25 02:25:42 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:45 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:45 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:50 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(4xac):" QDateTime("Mo Jun 25 02:25:50 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:25:53 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:25:53 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:25:58 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(5xac):" QDateTime("Mo Jun 25 02:25:58 2007")
| Opening file: true
| read bytes: 36700160
| Reading Done: QDateTime("Mo Jun 25 02:26:01 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:01 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:05 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(1xad):" QDateTime("Mo Jun 25 02:26:05 2007")
| Opening file: true
| read bytes: 34254415
| Reading Done: QDateTime("Mo Jun 25 02:26:08 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:08 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:12 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(2xad):" QDateTime("Mo Jun 25 02:26:12 2007")
| Opening file: true
| read bytes: 34254415
| Reading Done: QDateTime("Mo Jun 25 02:26:15 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:15 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:17 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(3xad):" QDateTime("Mo Jun 25 02:26:17 2007")
| Opening file: true
| read bytes: 34254415
| Reading Done: QDateTime("Mo Jun 25 02:26:20 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:20 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:23 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(4xad):" QDateTime("Mo Jun 25 02:26:23 2007")
| Opening file: true
| read bytes: 34254415
| Reading Done: QDateTime("Mo Jun 25 02:26:26 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:26 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:30 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
| "Reading Start(5xad):" QDateTime("Mo Jun 25 02:26:30 2007")
| Opening file: true
| read bytes: 34254415
| Reading Done: QDateTime("Mo Jun 25 02:26:33 2007")
| Executing: "insert into f1(num,data) values(:num,:blob);"
| Inserting Start: QDateTime("Mo Jun 25 02:26:33 2007")
| true
| Inserting End: QDateTime("Mo Jun 25 02:26:36 2007")
| Inserting result: QSqlError(-1, "", "")
| lines inserted: 1
`----

Sorry, for the long output. Now as one can see storing such files is
not taking that much time with hot-caches (the first, cold-cache case
does take a bit longer).

Now reading the whole data from the database using 1 single select looks
like this (with somewhat hot caches, I'll do a cold-cache-case
tomorrow):

,----
| andreas at morpheus:~/temp/testsqlite>./testsqlite
| true
| Fetching Start: QDateTime("Mo Jun 25 02:34:40 2007")
| lines fetched: 0
| Fetching Row number 0 : QDateTime("Mo Jun 25 02:34:43 2007")
| Fetched size: 36700160
| Fetching Row number 0 done: QDateTime("Mo Jun 25 02:34:43 2007")
| Fetching Row number 1 : QDateTime("Mo Jun 25 02:34:45 2007")
| Fetched size: 36700160
| Fetching Row number 1 done: QDateTime("Mo Jun 25 02:34:45 2007")
| Fetching Row number 2 : QDateTime("Mo Jun 25 02:34:50 2007")
| Fetched size: 36700160
| Fetching Row number 2 done: QDateTime("Mo Jun 25 02:34:50 2007")
| Fetching Row number 3 : QDateTime("Mo Jun 25 02:34:52 2007")
| Fetched size: 36700160
| Fetching Row number 3 done: QDateTime("Mo Jun 25 02:34:52 2007")
| Fetching Row number 4 : QDateTime("Mo Jun 25 02:34:55 2007")
| Fetched size: 36700160
| Fetching Row number 4 done: QDateTime("Mo Jun 25 02:34:55 2007")
| Fetching Row number 5 : QDateTime("Mo Jun 25 02:34:57 2007")
| Fetched size: 36700160
| Fetching Row number 5 done: QDateTime("Mo Jun 25 02:34:57 2007")
| Fetching Row number 6 : QDateTime("Mo Jun 25 02:35:00 2007")
| Fetched size: 36700160
| Fetching Row number 6 done: QDateTime("Mo Jun 25 02:35:00 2007")
| Fetching Row number 7 : QDateTime("Mo Jun 25 02:35:02 2007")
| Fetched size: 36700160
| Fetching Row number 7 done: QDateTime("Mo Jun 25 02:35:02 2007")
| Fetching Row number 8 : QDateTime("Mo Jun 25 02:35:03 2007")
| Fetched size: 36700160
| Fetching Row number 8 done: QDateTime("Mo Jun 25 02:35:03 2007")
| Fetching Row number 9 : QDateTime("Mo Jun 25 02:35:05 2007")
| Fetched size: 36700160
| Fetching Row number 9 done: QDateTime("Mo Jun 25 02:35:05 2007")
| Fetching Row number 10 : QDateTime("Mo Jun 25 02:35:17 2007")
| Fetched size: 36700160
| Fetching Row number 10 done: QDateTime("Mo Jun 25 02:35:17 2007")
| Fetching Row number 11 : QDateTime("Mo Jun 25 02:35:46 2007")
| Fetched size: 36700160
| Fetching Row number 11 done: QDateTime("Mo Jun 25 02:35:46 2007")
| Fetching Row number 12 : QDateTime("Mo Jun 25 02:36:36 2007")
| Fetched size: 36700160
| Fetching Row number 12 done: QDateTime("Mo Jun 25 02:36:36 2007")
| Fetching Row number 13 : QDateTime("Mo Jun 25 02:36:54 2007")
| Fetched size: 36700160
| Fetching Row number 13 done: QDateTime("Mo Jun 25 02:36:54 2007")
| Fetching Row number 14 : QDateTime("Mo Jun 25 02:37:18 2007")
| Fetched size: 36700160
| Fetching Row number 14 done: QDateTime("Mo Jun 25 02:37:18 2007")
| Fetching Row number 15 : QDateTime("Mo Jun 25 02:37:26 2007")
| Fetched size: 34254415
| Fetching Row number 15 done: QDateTime("Mo Jun 25 02:37:26 2007")
| Fetching Row number 16 : QDateTime("Mo Jun 25 02:37:36 2007")
| Fetched size: 34254415
| Fetching Row number 16 done: QDateTime("Mo Jun 25 02:37:36 2007")
| Fetching Row number 17 : QDateTime("Mo Jun 25 02:37:47 2007")
| Fetched size: 34254415
| Fetching Row number 17 done: QDateTime("Mo Jun 25 02:37:47 2007")
| Fetching Row number 18 : QDateTime("Mo Jun 25 02:37:55 2007")
| Fetched size: 34254415
| Fetching Row number 18 done: QDateTime("Mo Jun 25 02:37:55 2007")
| Fetching Row number 19 : QDateTime("Mo Jun 25 02:38:12 2007")
| Fetched size: 34254415
| Fetching Row number 19 done: QDateTime("Mo Jun 25 02:38:12 2007")
| Fetching End: QDateTime("Mo Jun 25 02:38:12 2007")
`----

The first 10 entries can be fetched quite good, but then it gets really
slow. I don't know why this is the case, but I guess the problem is that
the whole 630 meg database file doesn't fit into my 1G memory
alltogether. so its delivering the other entries from disk by doing seek
or some such...

So now everybody can think wether all this is fast eough or not. Test on
your own disk, the main.cpp and .pro files are attached (it reads
<num>xa<a-d> and uses foobar as db name).

Andreas

-- 
You will be Told about it Tomorrow.  Go Home and Prepare Thyself.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: main.cpp
Type: text/x-c++src
Size: 2114 bytes
Desc: not available
URL: <http://mail.kde.org/pipermail/kdevelop-devel/attachments/20070625/2c1e71d7/attachment.cpp>
-------------- next part --------------
######################################################################
# Automatically generated by qmake (2.01a) So Jun 24 22:59:36 2007
######################################################################

TEMPLATE = app
TARGET = 
DEPENDPATH += .
INCLUDEPATH += .
QT += sql
# Input
SOURCES += main.cpp