Show HN: LevelDB outperforms others on a cheap phone (Microsoft ESE and SQLite) | Hacker News Reader

Show HN: LevelDB outperforms others on a cheap phone (Microsoft ESE and SQLite)

github.com

57 pointsmaxpert47 comments

The SQLite numbers are not going to be realistic here given that "no SQLite transactions" are used and the write-ahead-log is probably not used. Assuming defaults are used, that means "PRAGMA synchronous=FULL" and "PRAGMA journal_mode=DELETE" (so no Write-Ahead-Log). This means every mutating statement is potentially going to result in multiple fsync() operations. See https://www.sqlite.org/pragma.html for context.

I didn't use any of the sophisticated stuff at all. As article states, I did it like what a typical programmer might have done if he wanted to do some basic key-value storage.

Please put a note in your article explaining this. SQLite is literally 10x faster using the WAL vs standard mode. And while you're at it, please consider an index on the SQL server and SQLite tables, so the reads are faster as well.

I would love to see the article updated using SQLite's PRAGMA journal_mode = WAL and PRAGMA synchronous = NORMAL. Then it would be a much more fair comparison.

Please don't give programmers a license to be lazy and not learn about their tools!!! If this article is trying to inform, or give benchmarks, it should not come to invalid conclusions without explaining the tradeoffs.

Yep have tried it with WAL and it still doesn't beat LevelDB. Thanks for the lazy suggestion I added disclaimer on top of the page right away!

But what is the point of your page? If it's to make recommendations then go a step further and include changing a setting or two for better recommendations.

Considering your background and endorsement of nosql databases, it comes off as more of a puff piece if you don't actually try to make the other db's run fast. It especially seems that way when you tout it as "blazing fast compared to any other storage solutions", and then proceed to test it in one narrow setup.

That noted, you said that you used the default settings, and I'd be curious if there are settings for LevelDB that could be changed to make it faster.

I'd be interested if you did more tests explore more than just one particular setup.

Edit: I realized that this may have come off a little negative. Overall I think it's nice, but seems less applicable than you make it seem.

So--this is really more of a statement of how the typical programmer doesn't RTFM than a comparison of the capabilities of the technologies.

SQLite is NOT a key/value store, it's a full featured SQLDB, and hence the default parameters are set to serve this purpose.

Comparing it to levelDB is like comparing redis to postgres with default value.

You can use postgres as redis. You can actually use the key/value store engine and tweak the settings to get very nice performance out of it. But it's not the default behavior.

This is one example of the pitfalls of making decisions "based on data", as is now the fad. Turns out that your data probably sucks or doesn't measure what you think it does. It's better to not know than to know the wrong thing. A posteriori data analysis can be great, but nothing can beat a priori understanding. Ideally, you should be using your data to get to that understanding, not replace it.

Great point. The phrase "garbage in, garbage out" seems relevant.

I always like reading comments on these types of analyses, since they often use the author's data to help me understand what might have been overlooked (which I often would have overlooked also!).

It would also be interesting to see a LevelDB vs SQLite comparison on Android, but there is some room to improve SQLite on ext4 (Journaling the Journal):

https://www.usenix.org/conference/atc13/technical-sessions/p...

SQLite 4 is supposed to have a simplified K/V storage engine. That would probably be a more fair comparison.

(Hm, looking at LevelDB I don't see references to Android any more, maybe they dropped support?)

SQLite 4 is also designed, in part, to beat LevelDB (i.e. they explicitly benchmark the default storage engine against LevelDB)[1].

SQLite 4 looks pretty darn cool, is it still actively being worked on? Commits seem quite scarce[2].

1. https://sqlite.org/src4/doc/trunk/www/design.wiki

2. https://sqlite.org/src4/timeline

Checkout https://github.com/maxpert/lsm-windows for port of the core LSM engine of SQLite4. I might be doing one in one more weekend.

I already did a port of SQLite4's LSM engine on Universal Windows (https://github.com/maxpert/lsm-windows) would have to do some more work before I can do a benchmark. Would love if someone can contribute :)

Doesn't leveldb , kinda suck? Meaning every ~serious db doesn't use it or has created a fork of it ? or see the symas-lmdb benchmarks for many different usecases of different kv stores http://symas.com/mdb/#bench

Well LMDB is hands down the fastest of them all. All the benchmarks that I saw with LMDB were mostly on Linux and ext file system. It would be really interesting to see how the memory mapped files perform on a phone though. But doing so requires me to port complete LMDB on phone :P

LMDB already runs on iOS and Android. In my experience you will have issues on some Android devices though. For instance, I recently found out that on some Android devices writes to a mmap-ed file through the usual POSIX interfaces are not immediatly reflected on reads through the mmap, which means that on those devices you MUST use MDB_WRITEMAP.

For those who don't know, I/O on Android is a nightmare. On your typical phone, the filesystem you have access to is a FUSE that mirrors to an EXT4 filesystem mounted with crazy device-specific options which include noauto_da_alloc.

I don't know any DB which works fast and consistently across all Android phones on the market. SQLite is probably the safest bet for now.

> LMDB already runs on iOS and Android. In my experience you will have issues on some Android devices though. For instance, I recently found out that on some Android devices writes to a mmap-ed file through the usual POSIX interfaces are not immediatly reflected on reads through the mmap, which means that on those devices you MUST use MDB_WRITEMAP.

How is that possible if they are using Linux and UBC (Unified Buffer Cache). It's unlikely they anybody would go undo that from the VM/FS layer.

The only think I can think off this behavior happening in a Linux app is if you're creating a private mapping... Private mappings are COW and a write to the global would cause a copy of the old data for you app. They'll be no sycing till you msync()

To be honest I am not entirely sure of my interpretation of the issue yet. What I know is that on those devices you sometimes can't read your writes with LMDB if it's not using MDB_WRITEMAP.

While trying to debug I ended up on the only question ever asked by Howard Chu (LMDB author) on Stack Overflow: http://stackoverflow.com/questions/7061910/when-does-an-o-sy...

I am still trying to figure out what exactly is happening here but it's not very easy given that I do not even have access to a rooted phone that exhibits the issue.

It really depends on where you're storing your DB files. If you're using the /data partition it should Just Work. If you're using external storage, yes, android does a bunch of stupid tricks to implement its secure storage, and that interferes with mmap.

Here's the code that resulted from that Stack Overflow question: https://github.com/LMDB/lmdb/blob/mdb.master/libraries/liblm...

After 36 years of writing code, I have more answers than questions.

This whole post is kinda laughable, so a mediocre bug-ridden key/value store is somewhat faster than an SQL database. Big deal, they're all slow.

LMDB has been tested on quite a lot of filesystems, not just ext.

http://symas.com/mdb/microbench/july/#sec11

And actually, LMDB was working fine back in the Froyo/Gingerbread days. I integrated it into XDAndroid way back then. http://forum.xda-developers.com/showthread.php?t=1171052 (By way of SQLightning.)

Never tested it on things like squashfs or whatnot though.

It sucks allright.

The performance numbers are actually mediocre at best in "real world" conditions. When I tried using it in a project where both high performance and concurrency were needed, I also discovered that it doesn't scale past 1 CPU core - probably uses mutexes or inefficient locks. I gave up on it rather than trying to fix it.

That said, it has a simple API and sometimes neither concurrency nor ACID are important.

I believe the core writer runs on a single thread, so this makes sense.

Also, I'd argue leveldb shines in a distributed environment where the simple interface is easy to leverage at scale--after all, it is basically the tablet part of big table.

It sucks a lot.

https://github.com/bitcoin/bitcoin/issues/2770

http://hackingdistributed.com/2013/11/27/bitcoin-leveldb/

That appears to be a bug in munmap() on OS X that just happened to bite LevelDB.

Nice demo. If one just needs a KV store, it's good that the engine that only does KV outperforms the more general engines ;) On top of all the engineering that goes into making LevelDB fast.

LevelDB also made the Bitcoin client super unstable and prone to dataloss. Use sqlite, because it's bullet-resistent and has competent maintainers.

500 inserts is way too small for a database benchmark. At that size point you're stressing the in-memory format of the database and not the actual IO performance. I'm guessing LevelDB just wins because it's considerably simpler and doesn't have to go through a schema layer - it's literally just a LSM tree.

I tried it with 5K to 10K entries and it still out performs SQLite or any other engines. For SQLite the default and most probabilistic way a programmer will implement a key value store. You can try the sample on your own machine as well.

Well, you're also doing a random insert. That workload will always perform better on an LSM tree when compared to a B/B+ Tree (used by the non-LevelDB engines). Can you post sequential read/write benchmarks in addition?

I the article I have already covered that LevelDB is known to have better sequential performance anywayz http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

Do you mean 'likely'? Because 'probabilistic' is something else.

I would say likely

Realm would also be a great comparison. Albeit the core is not open yet so maybe that's why it was left out? For mobile databases though it really screams for me, when compared to SQLite.

Good suggestion will add it to benchmarks

That's because leveldb by default writes asynchronously. And probably at the time of reading they were still in RAM/cached.

Let me quote LevelDB docs here:

"By default, each write to leveldb is asynchronous: it returns after pushing the write from the process into the operating system. The transfer from operating system memory to the underlying persistent storage happens asynchronously. The sync flag can be turned on for a particular write to make the write operation not return until the data being written has been pushed all the way to persistent storage. (On Posix systems, this is implemented by calling either fsync(...) or fdatasync(...) or msync(..., MS_SYNC) before the write operation returns.)"

and if you see my benchmarks I am flushing each entry to the disk.

Something is still missing. It's hard to imagine such drastic performance difference in btree implementation.

btw I've had better experience using kyoto db than levelDB.

How many applications are really doing intensive read/writes on mobile?

On Android most well-architected applications will persist almost everything to disk before showing it to the user. Network directly to UI is too unstable.

It's not about intensive reads/writes (dozens of MB) it's about making each individual read or write very fast so you don't drop frames. On Android you have 16ms to draw each frame (60fps). If you have a DB request take 5ms then all of a sudden you're very likely to have jank in your app.

Don't do I/O on the UI thread. The time it takes to do a write should not affect jank.

Yeah, a well architected application doesn't touch disk on the UI thread.

Just write a chat or email application that persists your conversation on disk. It goes crazy at times.

Define intensive.

But any well architected app that wants to work well without Internet will write many things to disk.

500 writes a second is very realistic when downloading some bulk content that will be used to power different states of the app. Now this might not be a continuous burst of 500 operations a second sustained, but many it has a handful of these bursts over several minutes. Given that, I would say that lots of mobile apps can have brief periods of tons of writes.

The last time I look at LevelDB it didn't support multiprocess access to the same file (so ended up using SQLite). Ha this change?

Still same :(

I don't think there are many storage systems that have higher throughout with two concurrent writers than one, so a global write lock is not unreasonable.

Updated the documentation with assumptions.

Reply on news.ycombinator.com