Re: [sqlite] LevelDB benchmark

[Dan Kennedy]

On 07/30/2011 12:19 PM, Dan Kennedy wrote:
> On 07/30/2011 04:45 AM, Gabor Cselle wrote:
>> Just a heads-up that we just posted an update to the benchmarks:
>> - No more superfluous index on the primary key
>> - WAL turned on with auto-checkpointing every 4096 pages
>>
>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>
>> A diff of the results is here:
>> http://code.google.com/p/leveldb/source/diff?spec=svn45=45=side=/trunk/doc/benchmark.html_path=/trunk/doc/benchmark.html=44
>>
>> A new version of db_bench_sqlite3.cc is also included in the same revision.
>>
>> As predicted by people here, SQLite shows significant performance
>> improvements across the board except for large values (which I attribute to
>> WAL).
>>
>> We're planning to put together a benchmark that uses 64-bit integers as
>> keys, rather than 128-bit/16-byte blobs (My understanding is that SQLite
>> stores 64-bit, not 32-bit integers). I'll post the results to this mailing
>> list.
>
> That's correct. 64-bit integers.
>
>> Thanks everyone for your suggestions.
>
> Another one you could do, if you're making changes, is add a
> ReadSequential() function to db_bench_sqlite3.cc. Leveldb and KC are
> both using an iterator to read the keys sequentially, but SQLite is
> doing a separate lookup of each key.
>
> How about something like this:
>
> void ReadSequential() {
>   int status;
>   sqlite3_stmt *pStmt;
>   int i;
>   std::string read_str = "SELECT * FROM test";

Oops. Should be: "SELECT * FROM test ORDER BY key".

>
>   status = sqlite3_prepare_v2(db_, read_str.c_str(), -1,, NULL);
>   ErrorCheck(status);
>   for (i=0; i<  reads_&&  SQLITE_ROW==sqlite3_step(pStmt); i++){
> bytes_ += sqlite3_column_bytes(pStmt, 1) +
> sqlite3_column_bytes(pStmt, 2);
> FinishedSingleOp();
>   }
>
>   status = sqlite3_finalize(pStmt);
>   ErrorCheck(status);
> }
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Black, Michael (IS)]

/op;
fillrand100K : 486.478 micros/op;  196.1 MB/s (1000 ops)
fillseq100K  : 486.274 micros/op;  196.2 MB/s (1000 ops)
readseq100K  :  26.951 micros/op;
readrand100K :  27.918 micros/op;





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
Sent: Saturday, July 30, 2011 2:18 AM
To: sqlite_us...@googlegroups.com; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] LevelDB benchmark

2011/7/30 Gabor Cselle <ga...@google.com>:
> Just a heads-up that we just posted an update to the benchmarks:
> - No more superfluous index on the primary key
> - WAL turned on with auto-checkpointing every 4096 pages

You may use "PRAGMA synchronous = NORMAL" instead of "PRAGMA
synchronous = FULL" in WAL mode.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

2011/7/30 Gabor Cselle :
> Just a heads-up that we just posted an update to the benchmarks:
> - No more superfluous index on the primary key
> - WAL turned on with auto-checkpointing every 4096 pages

You may use "PRAGMA synchronous = NORMAL" instead of "PRAGMA
synchronous = FULL" in WAL mode.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Dan Kennedy]

On 07/30/2011 04:45 AM, Gabor Cselle wrote:
> Just a heads-up that we just posted an update to the benchmarks:
> - No more superfluous index on the primary key
> - WAL turned on with auto-checkpointing every 4096 pages
>
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>
> A diff of the results is here:
> http://code.google.com/p/leveldb/source/diff?spec=svn45=45=side=/trunk/doc/benchmark.html_path=/trunk/doc/benchmark.html=44
>
> A new version of db_bench_sqlite3.cc is also included in the same revision.
>
> As predicted by people here, SQLite shows significant performance
> improvements across the board except for large values (which I attribute to
> WAL).
>
> We're planning to put together a benchmark that uses 64-bit integers as
> keys, rather than 128-bit/16-byte blobs (My understanding is that SQLite
> stores 64-bit, not 32-bit integers). I'll post the results to this mailing
> list.

That's correct. 64-bit integers.

> Thanks everyone for your suggestions.

Another one you could do, if you're making changes, is add a
ReadSequential() function to db_bench_sqlite3.cc. Leveldb and KC are
both using an iterator to read the keys sequentially, but SQLite is
doing a separate lookup of each key.

How about something like this:

   void ReadSequential() {
 int status;
 sqlite3_stmt *pStmt;
 int i;
 std::string read_str = "SELECT * FROM test";

 status = sqlite3_prepare_v2(db_, read_str.c_str(), -1, , NULL);
 ErrorCheck(status);
 for (i=0; i < reads_ && SQLITE_ROW==sqlite3_step(pStmt); i++){
   bytes_ += sqlite3_column_bytes(pStmt, 1) + 
sqlite3_column_bytes(pStmt, 2);
   FinishedSingleOp();
 }

 status = sqlite3_finalize(pStmt);
 ErrorCheck(status);
   }

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/29/2011 02:45 PM, Gabor Cselle wrote:
> (My understanding is that SQLite stores 64-bit, not 32-bit integers). 

SQLite actually stores integers using a variable length encoding and is
documented here:

  http://www.sqlite.org/fileformat.html#varint_format

The largest integer value is signed 64 bit.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4zRX0ACgkQmOOfHg372QT+aACgw+Uk7Dm+PCzx+GpyS3NRDT4s
KI8AnjWXdKYeGkioFivSylA5wcLtV8HK
=ywnM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Gabor Cselle]

Just a heads-up that we just posted an update to the benchmarks:
- No more superfluous index on the primary key
- WAL turned on with auto-checkpointing every 4096 pages

http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

A diff of the results is here:
http://code.google.com/p/leveldb/source/diff?spec=svn45=45=side=/trunk/doc/benchmark.html_path=/trunk/doc/benchmark.html=44

A new version of db_bench_sqlite3.cc is also included in the same revision.

As predicted by people here, SQLite shows significant performance 
improvements across the board except for large values (which I attribute to 
WAL).

We're planning to put together a benchmark that uses 64-bit integers as 
keys, rather than 128-bit/16-byte blobs (My understanding is that SQLite 
stores 64-bit, not 32-bit integers). I'll post the results to this mailing 
list.

Thanks everyone for your suggestions.

Gabor
Product Manager, Google


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Gabor Cselle]

Dear Dr. Hipp,

I'm a Product Manager here at Google and one of the authors of the 
benchmarks discussed on this thread.

Our intention with the benchmarks was to compare LevelDB with popular 
storage engines for the specific use case for which it was built, mapping 
string keys to string values. SQLite has a much wider range of applications 
than key-value mapping, but it's sometimes used by developers as a key-value 
store. We wanted to show how LevelDB compares in this specific use case.

You raise an excellent point about the superfluous index, and we'll remove 
the index and update the benchmark results.

We chose to keep WAL disabled since that is the default in SQLite, but we 
will retake the measurements with WAL on.

For the results to be comparable, we had to use the same key/value sizes for 
all of the tests, that's why we keyed on 128-bit blobs rather than 32-bit 
integers. We'll create a variant of the Kyoto TreeDB and LevelDB benchmarks 
that key on 32-bit integers, and we'll post those results to this list.

We are big fans of SQLite here at Google and did not intend to cast it in a 
bad light. 

Thank you for your suggestions, I will follow up with our results soon.

Gabor Cselle
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

2011/7/29 Black, Michael (IS) :
> 2X-20X is hardly "small...overhead" in my world.
>
> Even 2X is the difference between 30 days and 15 days.  One 16-computer blade 
> rack vs two racks ($200,000 vs $400,000).
>
> That's why google did this.  Works for what they need and is lots cheaper.

And single-thread without correct synchronous. Plus full in-memory
copy of LevelDB log.
And tests is adopted for specific scenarious (IMHO test of fixed value
size is incorrect).
I'm sure key-value database may be faster.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Eduardo Morras]

At 14:19 29/07/2011, Black, Michael (IS) wrote:
>What they don't say explicitly is that if all you need is key/value 
>capability then an SQL database is overkill and only slows you down 
>(bit of a duh factor there though not obvious to 
>neophytes).  Generally speaking that's one thing they don't teach in 
>college is optimization.  I can't count the # of projects I've 
>worked on where what was implemented was so slow as to be useless by 
>the time it was done only to have to redesign the thing for 
>speed.  The one I'm working now I've sped up by 100X.

A  data storage can work with O(1)  algorithms while 
B-Tree indexes are O(nlogn). It can't do everything you can do with a 
B-Tree but it does what you need.

>The bad part is you'd better really know that all you need is 
>key/value otherwise you have to re-do things and kill your 
>performance which then could mean rearchitecting your solution.
>
>I know my boss is always changing requirements on our IR programs...:-)

Jejejeje, a boss is always O(n^2) or worse in time implementation :) 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Black, Michael (IS)]

2X-20X is hardly "small...overhead" in my world.

Even 2X is the difference between 30 days and 15 days.  One 16-computer blade 
rack vs two racks ($200,000 vs $400,000).

That's why google did this.  Works for what they need and is lots cheaper.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
Sent: Friday, July 29, 2011 7:44 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] LevelDB benchmark

2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>:
> What they don't say explicitly is that if all you need is key/value 
> capability then an SQL database is overkill and only slows you down (bit of a 
> duh factor there though not obvious to neophytes).

The overhead by SQL layer is small. And are used prepared statements in test.
As I see LevelDB use data integrity equal to SQLite WAL mode with
disabled fsync. And LevelDB is limited only single-thread access. With
the patched test SQLite is about
2x - 20x slower and it's absolutly normal I think. With dirrefent
page_size we can make
some tests faster.

P.S. There is constant database created by DJB. And exists patch to
drop "constant"
limitation. IMHO it's functionally equal and better solution than LevelDB...

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Richard Hipp]

On Fri, Jul 29, 2011 at 12:30 AM, Alexey Pechnikov
wrote:

> With integer->blob mapping patch I get these results:
>
>
Good information, Alexey.  Thanks for running this.  It would seem that the
SQLite team has some serious work ahead of us.


>
> $ ./db_bench_sqlite3
> SQLite: version 3.7.7.1
> Date:   Fri Jul 29 05:32:05 2011
> CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
> CPUCache:   512 KB
> Keys:   16 bytes each
> Values: 100 bytes each
> Entries:100
> RawSize:110.6 MB (estimated)
> 
> fillseq  :  77.394 micros/op;1.3 MB/s
> fillseqsync  : 133.326 micros/op;0.7 MB/s (1 ops)
> fillseqbatch :  31.511 micros/op;3.1 MB/s
> fillrandom   : 518.605 micros/op;0.2 MB/s
> fillrandsync : 227.374 micros/op;0.4 MB/s (1 ops)
> fillrandbatch : 411.859 micros/op;0.2 MB/s
> overwrite: 793.869 micros/op;0.1 MB/s
> overwritebatch : 743.661 micros/op;0.1 MB/s
> readrandom   :  31.236 micros/op;
> readseq  :  20.331 micros/op;
> fillrand100K :4872.027 micros/op;   19.6 MB/s (1000 ops)
> fillseq100K  :7249.182 micros/op;   13.2 MB/s (1000 ops)
> readseq100K  : 634.887 micros/op;
> readrand100K : 606.026 micros/op;
>
>
> $ ./db_bench
> LevelDB:version 1.2
> Date:   Fri Jul 29 11:20:59 2011
> CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
> CPUCache:   512 KB
> Keys:   16 bytes each
> Values: 100 bytes each (50 bytes after compression)
> Entries:100
> RawSize:110.6 MB (estimated)
> FileSize:   62.9 MB (estimated)
> WARNING: Snappy compression is not enabled
> 
> fillseq  :  10.107 micros/op;   10.9 MB/s
> fillsync : 276.920 micros/op;0.4 MB/s (1000 ops)
> fillrandom   :  21.275 micros/op;5.2 MB/s
> overwrite:  30.717 micros/op;3.6 MB/s
> readrandom   :  48.781 micros/op;
> readrandom   :  39.841 micros/op;
> readseq  :   2.227 micros/op;   49.7 MB/s
> readreverse  :   3.549 micros/op;   31.2 MB/s
> compact  : 5274551.868 micros/op;
> readrandom   :  35.392 micros/op;
> readseq  :   1.743 micros/op;   63.5 MB/s
> readreverse  :   2.927 micros/op;   37.8 MB/s
> fill100K :6631.138 micros/op;   14.4 MB/s (1000 ops)
> crc32c   :  11.447 micros/op;  341.2 MB/s (4K per op)
> snappycomp   :   8.106 micros/op; (snappy failure)
> snappyuncomp :  26.941 micros/op; (snappy failure)
> acquireload  :   1.407 micros/op; (each op is 1000 loads)
>
>
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

2011/7/29 Black, Michael (IS) :
> What they don't say explicitly is that if all you need is key/value 
> capability then an SQL database is overkill and only slows you down (bit of a 
> duh factor there though not obvious to neophytes).

The overhead by SQL layer is small. And are used prepared statements in test.
As I see LevelDB use data integrity equal to SQLite WAL mode with
disabled fsync. And LevelDB is limited only single-thread access. With
the patched test SQLite is about
2x - 20x slower and it's absolutly normal I think. With dirrefent
page_size we can make
some tests faster.

P.S. There is constant database created by DJB. And exists patch to
drop "constant"
limitation. IMHO it's functionally equal and better solution than LevelDB...

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Black, Michael (IS)]

What they don't say explicitly is that if all you need is key/value capability 
then an SQL database is overkill and only slows you down (bit of a duh factor 
there though not obvious to neophytes).  Generally speaking that's one thing 
they don't teach in college is optimization.  I can't count the # of projects 
I've worked on where what was implemented was so slow as to be useless by the 
time it was done only to have to redesign the thing for speed.  The one I'm 
working now I've sped up by 100X.



The bad part is you'd better really know that all you need is key/value 
otherwise you have to re-do things and kill your performance which then could 
mean rearchitecting your solution.



I know my boss is always changing requirements on our IR programs...:-)





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Eduardo Morras [nec...@retena.com]
Sent: Friday, July 29, 2011 2:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] LevelDB benchmark

At 02:53 29/07/2011, Richard Hipp wrote:
>On Thu, Jul 28, 2011 at 12:27 AM, <res...@googlemail.com> wrote:
>
>I suspect that I will come up with other suggestions once I have a chance to
>dig a little deeper into the benchmarks.  If you have suggestions, please
>publish them here.

They are comparing orange with apples. They compare a <key,value>
data store with a Sql relational database. From their home page(1):
* This is not a SQL database. It does not have a relational data
model, it does not support SQL queries, and it has no support for indexes.
So, they support simple queries ( = simbol) and not complex queries
(<=, >=, <, >, !=). Why don't compare it with Hadoop, Pnuts/Sherpa,
or similar No-Sql data stores?

>Thanks for your help and support!


(1) http://code.google.com/p/leveldb/


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Eduardo Morras]

At 02:53 29/07/2011, Richard Hipp wrote:
>On Thu, Jul 28, 2011 at 12:27 AM,  wrote:
>
>I suspect that I will come up with other suggestions once I have a chance to
>dig a little deeper into the benchmarks.  If you have suggestions, please
>publish them here.

They are comparing orange with apples. They compare a  
data store with a Sql relational database. From their home page(1):
* This is not a SQL database. It does not have a relational data 
model, it does not support SQL queries, and it has no support for indexes.
So, they support simple queries ( = simbol) and not complex queries 
(<=, >=, <, >, !=). Why don't compare it with Hadoop, Pnuts/Sherpa, 
or similar No-Sql data stores?

>Thanks for your help and support!


(1) http://code.google.com/p/leveldb/ 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

With integer->blob mapping patch I get these results:


$ ./db_bench_sqlite3
SQLite: version 3.7.7.1
Date:   Fri Jul 29 05:32:05 2011
CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
CPUCache:   512 KB
Keys:   16 bytes each
Values: 100 bytes each
Entries:100
RawSize:110.6 MB (estimated)

fillseq  :  77.394 micros/op;1.3 MB/s
fillseqsync  : 133.326 micros/op;0.7 MB/s (1 ops)
fillseqbatch :  31.511 micros/op;3.1 MB/s
fillrandom   : 518.605 micros/op;0.2 MB/s
fillrandsync : 227.374 micros/op;0.4 MB/s (1 ops)
fillrandbatch : 411.859 micros/op;0.2 MB/s
overwrite: 793.869 micros/op;0.1 MB/s
overwritebatch : 743.661 micros/op;0.1 MB/s
readrandom   :  31.236 micros/op;
readseq  :  20.331 micros/op;
fillrand100K :4872.027 micros/op;   19.6 MB/s (1000 ops)
fillseq100K  :7249.182 micros/op;   13.2 MB/s (1000 ops)
readseq100K  : 634.887 micros/op;
readrand100K : 606.026 micros/op;


$ ./db_bench
LevelDB:version 1.2
Date:   Fri Jul 29 11:20:59 2011
CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
CPUCache:   512 KB
Keys:   16 bytes each
Values: 100 bytes each (50 bytes after compression)
Entries:100
RawSize:110.6 MB (estimated)
FileSize:   62.9 MB (estimated)
WARNING: Snappy compression is not enabled

fillseq  :  10.107 micros/op;   10.9 MB/s
fillsync : 276.920 micros/op;0.4 MB/s (1000 ops)
fillrandom   :  21.275 micros/op;5.2 MB/s
overwrite:  30.717 micros/op;3.6 MB/s
readrandom   :  48.781 micros/op;
readrandom   :  39.841 micros/op;
readseq  :   2.227 micros/op;   49.7 MB/s
readreverse  :   3.549 micros/op;   31.2 MB/s
compact  : 5274551.868 micros/op;
readrandom   :  35.392 micros/op;
readseq  :   1.743 micros/op;   63.5 MB/s
readreverse  :   2.927 micros/op;   37.8 MB/s
fill100K :6631.138 micros/op;   14.4 MB/s (1000 ops)
crc32c   :  11.447 micros/op;  341.2 MB/s (4K per op)
snappycomp   :   8.106 micros/op; (snappy failure)
snappyuncomp :  26.941 micros/op; (snappy failure)
acquireload  :   1.407 micros/op; (each op is 1000 loads)



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/28/2011 07:39 PM, Simon Slavin wrote:
> Actually I don't see how BLOBs can be used in an index anyway, since 
> technically blobs have no ordering.

memcmp provides an ordering just as it can for strings without a collation.
 (That is what SQLite implements - you can decide how that complies with "SQL".)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4yHnIACgkQmOOfHg372QQ2lACgighSjQE9YWG4PyV0ZJh3niCj
oHgAn2pn23N0ZQtJ8f2pZS84lg2PS8fN
=csl7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Simon Slavin]

On 29 Jul 2011, at 3:34am, Roger Binns wrote:

> On 07/28/2011 06:57 PM, Simon Slavin wrote:
>> Would it improve the SQLite time if it was changed to strings instead of 
>> BLOBs ?
> 
> Note that internally SQLite treats strings and blobs virtually identically.
> Usually the same data structure and functions are used for them.  At the
> end of the day they are both a bag of bytes.
> 
> The major difference is that strings also have an encoding which needs to be
> taken into account should the bag of bytes need to be passed to a user
> defined function, collation, return code etc.

So that's a "no".  Actually I don't see how BLOBs can be used in an index 
anyway, since technically blobs have no ordering.  But the nature of SQL is 
that if you can't sort on it you can't index it, and that would mean you 
couldn't search for a BLOB.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/28/2011 06:57 PM, Simon Slavin wrote:
> Would it improve the SQLite time if it was changed to strings instead of 
> BLOBs ?

Note that internally SQLite treats strings and blobs virtually identically.
 Usually the same data structure and functions are used for them.  At the
end of the day they are both a bag of bytes.

The major difference is that strings also have an encoding which needs to be
taken into account should the bag of bytes need to be passed to a user
defined function, collation, return code etc.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4yHCYACgkQmOOfHg372QQ7ugCgm+ZBxtQJdpeLc/+ibVX8yVD9
8cEAn0GtwGRnH9hQfc8JuQZ580vP3Ia7
=oPtH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Afriza N. Arief]

On Fri, Jul 29, 2011 at 8:53 AM, Richard Hipp  wrote:

> On Thu, Jul 28, 2011 at 12:27 AM,  wrote:
>
> > they used
> >
> > CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
> > CREATE INDEX keyindex ON test (key)
> >
>
> Notice the inefficiencies inherent in this schema.
>
> (1) A primary key on a BLOB?  Really?
> (2) They create an redundant index on the primary key.  They would double
> the write performance with no impact on read performance simply be omitting
> the index.
>
> I propose a new set of benchmarks, SQLite vs. LevelDB, with the following
> schema:
>
>   CREATE TABLE test(key INTEGER PRIMARY KEY, value BLOB);
>
> I'm thinking SQLite will do much better in that case, and may well exceed
> the performance of LevelDB in most cases.  (This is a guess - I have not
> actually tried it.)
>
> Of course, if you really do need a blob-to-blob mapping, then I suppose
> LevelDB might be a better choice.  But not many applications do that kind
> of
> thing.  What SQL database (other than SQLite) even allows an index or
> primary key on a blob???


Actually as per their blog-post (
http://google-opensource.blogspot.com/2011/07/leveldb-fast-persistent-key-value-store.html
) .
They probably want to simulate "an ordered mapping from string keys to
string values" and to test "batch updates that modify many keys scattered
across a large key space".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

There are the LevelDB sources and tests
svn checkout http://leveldb.googlecode.com/svn/trunk/ leveldb-read-only

Build SQLite test as
make db_bench_sqlite3
And LevelDB test as
make db_bench

My patch for leveldb-read-only/doc/bench/db_bench_sqlite3.cc to disable
redudant index and enable WAL is here:
http://pastebin.com/dM2iqdvj

And patch as above plus integer keys instead of blobs
http://pastebin.com/CnBeChWg

P.S. For blob-to-blob mapping we may use table with index on hashed key.
Virtual table can simplify this.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Richard Hipp]

On Thu, Jul 28, 2011 at 12:27 AM,  wrote:

> they used
>
> CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
> CREATE INDEX keyindex ON test (key)
>

Notice the inefficiencies inherent in this schema.

(1) A primary key on a BLOB?  Really?
(2) They create an redundant index on the primary key.  They would double
the write performance with no impact on read performance simply be omitting
the index.

I propose a new set of benchmarks, SQLite vs. LevelDB, with the following
schema:

   CREATE TABLE test(key INTEGER PRIMARY KEY, value BLOB);

I'm thinking SQLite will do much better in that case, and may well exceed
the performance of LevelDB in most cases.  (This is a guess - I have not
actually tried it.)

Of course, if you really do need a blob-to-blob mapping, then I suppose
LevelDB might be a better choice.  But not many applications do that kind of
thing.  What SQL database (other than SQLite) even allows an index or
primary key on a blob???

I hereby call on all you loyal readers out there to help me come up with a
more balanced comparison between SQLite and LevelDB.  The published
benchmark from Google strikes me more as a hit piece than a reasonable
comparison between the databases.

I'm on a business trip and am unable to help a great deal until early next
week.  So your cooperation will be greatly appreciated.  May I suggest the
following comparisons as a start:

(1) Rerun the Google benchmarks with (a) WAL enabled and (b) the redundant
index removed.

(2) Modify the Google benchmarks to test an INTEGER->BLOB mapping using an
INTEGER PRIMARY KEY on SQLite, instead of the BLOB->BLOB mapping.

I suspect that I will come up with other suggestions once I have a chance to
dig a little deeper into the benchmarks.  If you have suggestions, please
publish them here.

Thanks for your help and support!



>
>
> on random replaces it doubles the write operations.
>
>
>
> J Decker schrieb:
> > On Wed, Jul 27, 2011 at 6:22 PM, Stephan Wehner 
> wrote:
> >> There are some benchmark's at
> >> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> >>
> >> I don't have anything to point to, but I thought sqlite3 does better
> >> than stated there.
> >>
> >> In particular, 26,900 sequential writes per second and 420 random writes
> >> per second from section "1. Baseline Performance" look suspicious.
> >>
> >
> > Wow, that's a bad mark for sqlite; I dunno it's somewhat misleading,
> > because I do know that if I use sqlite as a logging database, and
> > stream data to it it's kinda slow, and works better if I bunch up
> > inserts with multiple value sets.  But, enabling transactions, and
> > doing the same thing, write speed goes way up.  And now with WAL
> > journal, it might affect that speed test also in auto transact mode
> > especially
> >
> >> What you say?
> >>
> >> Stephan
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

Hm, I test I find index on PK field:

CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
CREATE INDEX keyindex ON test (key)

Epic fail, I think :D


Default test on Intel(R) Atom(TM) CPU N450   @ 1.66GHz
fillseq  : 442.937 micros/op;0.2 MB/s
fillseqsync  :1678.168 micros/op;0.1 MB/s (1 ops)
fillseqbatch :  73.016 micros/op;1.5 MB/s
...

And with enabled WAL and synchronous=NORMAL and wal_autocheckpoint=4096
(LevelDB log size is 4Mb by default) and without index on PK field (!):
fillseq  : 139.190 micros/op;0.8 MB/s
fillseqsync  : 228.869 micros/op;0.5 MB/s (1 ops)
fillseqbatch :  56.131 micros/op;2.0 MB/s
...

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Alexey Pechnikov]

LevelDB use append log but SQLite is tested without WAL :)
I check and some tests 2.5x faster with WAL.


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[reseok]

they used

CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
CREATE INDEX keyindex ON test (key)


on random replaces it doubles the write operations.



J Decker schrieb:
> On Wed, Jul 27, 2011 at 6:22 PM, Stephan Wehner  
> wrote:
>> There are some benchmark's at
>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>
>> I don't have anything to point to, but I thought sqlite3 does better
>> than stated there.
>>
>> In particular, 26,900 sequential writes per second and 420 random writes
>> per second from section "1. Baseline Performance" look suspicious.
>>
> 
> Wow, that's a bad mark for sqlite; I dunno it's somewhat misleading,
> because I do know that if I use sqlite as a logging database, and
> stream data to it it's kinda slow, and works better if I bunch up
> inserts with multiple value sets.  But, enabling transactions, and
> doing the same thing, write speed goes way up.  And now with WAL
> journal, it might affect that speed test also in auto transact mode
> especially
> 
>> What you say?
>>
>> Stephan
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Stephan Wehner]

On Wed, Jul 27, 2011 at 7:00 PM, Simon Slavin  wrote:
>
> On 28 Jul 2011, at 2:53am, Stephan Wehner wrote:
>
>> On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>>>
>>> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>>>
 There are some benchmark's at
 http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

 I don't have anything to point to, but I thought sqlite3 does better
 than stated there.
>>>
>>> i looked through their source code, trying to see if they defined 
>>> transactions. But I couldn't even find an INSERT command.
>>
>> Well, LevelDB is much simpler than sqlite3: it's a key-value store.
>
> Okay, but if they include their source code for testing SQLite I should be 
> able to find the word 'INSERT' somewhere in it, right ?
>

Sorry, I misunderstood -- S

> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Martin Gadbois]

On Wed, Jul 27, 2011 at 9:22 PM, Stephan Wehner wrote:

> There are some benchmark's at
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>
> I don't have anything to point to, but I thought sqlite3 does better
> than stated there.
>
> In particular, 26,900 sequential writes per second and 420 random writes
> per second from section "1. Baseline Performance" look suspicious.
>
> What you say?
>
>
I wish they compared  BerkerlyDB too 

-- 
Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Simon Slavin]

On 28 Jul 2011, at 3:01am, David Garfield wrote:

> They used REPLACE.  See
> http://code.google.com/p/leveldb/source/browse/trunk/doc/bench/db_bench_sqlite3.cc#492
> 
> They used explicit transactions, and tested with both single REPLACE
> transactions and 1000 REPLACE transactions.  Section 1A would be the
> single REPLACE transactions, while 2B is the batches.

Ah.  Thanks.  That explains it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[David Garfield]

They used REPLACE.  See
http://code.google.com/p/leveldb/source/browse/trunk/doc/bench/db_bench_sqlite3.cc#492

They used explicit transactions, and tested with both single REPLACE
transactions and 1000 REPLACE transactions.  Section 1A would be the
single REPLACE transactions, while 2B is the batches.

--David Garfield

Simon Slavin writes:
> 
> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
> 
> > There are some benchmark's at 
> > http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> > 
> > I don't have anything to point to, but I thought sqlite3 does better 
> > than stated there.
> 
> i looked through their source code, trying to see if they defined 
> transactions.  But I couldn't even find an INSERT command.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Simon Slavin]

On 28 Jul 2011, at 2:53am, Stephan Wehner wrote:

> On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>> 
>> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>> 
>>> There are some benchmark's at
>>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>> 
>>> I don't have anything to point to, but I thought sqlite3 does better
>>> than stated there.
>> 
>> i looked through their source code, trying to see if they defined 
>> transactions. But I couldn't even find an INSERT command.
> 
> Well, LevelDB is much simpler than sqlite3: it's a key-value store.

Okay, but if they include their source code for testing SQLite I should be able 
to find the word 'INSERT' somewhere in it, right ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Stephan Wehner]

On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>
> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>
>> There are some benchmark's at
>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>
>> I don't have anything to point to, but I thought sqlite3 does better
>> than stated there.
>
> i looked through their source code, trying to see if they defined 
> transactions.  But I couldn't even find an INSERT command.
>

Well, LevelDB is much simpler than sqlite3: it's a key-value store.

Stephan


> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[J Decker]

On Wed, Jul 27, 2011 at 6:22 PM, Stephan Wehner  wrote:
> There are some benchmark's at
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>
> I don't have anything to point to, but I thought sqlite3 does better
> than stated there.
>
> In particular, 26,900 sequential writes per second and 420 random writes
> per second from section "1. Baseline Performance" look suspicious.
>

Wow, that's a bad mark for sqlite; I dunno it's somewhat misleading,
because I do know that if I use sqlite as a logging database, and
stream data to it it's kinda slow, and works better if I bunch up
inserts with multiple value sets.  But, enabling transactions, and
doing the same thing, write speed goes way up.  And now with WAL
journal, it might affect that speed test also in auto transact mode
especially

> What you say?
>
> Stephan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

[Simon Slavin]

On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:

> There are some benchmark's at 
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> 
> I don't have anything to point to, but I thought sqlite3 does better 
> than stated there.

i looked through their source code, trying to see if they defined transactions. 
 But I couldn't even find an INSERT command.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users