5 ms·
Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a b
by chjj 10y ago
Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node.
In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for something like 30,000 utxos out of a set of a couple million. What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over 30 seconds in SQlite.
Now, you can tell me I did the benchmarks wrong, and "oh, if you just did this it would be faster!", but 30+ seconds is slower to an absolutely insane level. Needless to say, I went the key-value store route, but I was still astounded at how slow sqlite was once it got a few million records in the database.
I actually like sqlite, but when you know you're going to be dealing with 70gb of data and over 10 million records, preemptive optimization is the key. If I were the author, I would consider switching to postgres if there are over 500k-1m records to be expected. That being said, if they're partial to sqlite, SQLightning (https://github.com/LMDB/sqlightning https://github.com/LMDB/sqlightning) looks pretty interesting (SQLite with an LMDB backend).
edit: To clarify, these weren't particularly scientific benchmarks. This was me timing a very specific query to get an idea of the level of data management I was up against. Don't take my word for it.
- ww520 10y agoI'm curious to know how come Sqlite performed so bad on couple millions records. Can you share what the table schema is? What the queries are? And what indices built for the Postgres and Sqlite tables?
- panic 10y agoWhat operations were you doing in your benchmark? What was SQLite actually doing (CPU?, disk I/O?) while taking 30 seconds to finish? This would be a lot more useful and less FUDdy with more detail.
- chjj 10y agoI'll clarify here: these weren't very scientific benchmarks, as I alluded to. So, don't take my word for it. I didn't measure ops per second, I didn't take into account system load or cpu usage, etc. This is all anecdotal evidence. I'm not saying this an announcement that "you shouldn't use sqlite". I measured the time of one query. It was a benchmark I ran just to personally give me a general idea of what I was up against in terms of data management. This is primarily why I didn't post them in the first place. This was several months ago. I'll try to find the code that ran them and put them up on github if I can, but I doubt it will be that useful without the actual data, which isn't easy to upload. So yes, to clarify, don't take my word for it. This is just my experience.
- vonklaus 10y agoI would be interested in a repo or post about your setup for the actual project; downloading, storing and using blockchain data. Sounds super interesting so if it(article, repo, site) isn't private & exists, would be keen. Cheers.
- coleifer 10y agoNot the poster, but I did some benchmarks with embedded databases and Python if you're interested in completely useless, unscientific data: http://charlesleifer.com/blog/completely-un-scientific-benchmarks-of-some-embedded-databases-with-python/ http://charlesleifer.com/blog/completely-un-scientific-bench...
- chjj 10y agoThe project itself an alternative bitcoin full node, one of the few (along with btcd, bitcoinj, etc), and the only one that runs in the browser: http://bcoin.io/browser.html http://bcoin.io/browser.html. I've posted it here before but no one seemed to be interested. It's probably not good if you want to index anything more than addresses, but you could easily modify it to index other things if you wanted to. The actual blockchain database resides here: https://github.com/bcoin-org/bcoin/blob/coinviews5/lib/bcoin/chaindb.js https://github.com/bcoin-org/bcoin/blob/coinviews5/lib/bcoin... ^ That's the current branch I'm working on which, as it happens, optimizes a lot of the storage of utxos. Before, it was storing them the messy bitcoinj way. Now it uses coin viewpoints similar to bitcoind and btcd.
- mappu 10y agoI've found sqlite's performance on bulk insertions to be massively (100x) improved by wrapping the bulk insertion in a transaction. fsync()ing a couple hundred thousand individual INSERTs isn't fast.
- coleifer 10y agoBatching inserts, wrapping the batches in transactions, waiting til after to add indexes: best way to go.
- Sami_Lehtinen 10y agoJournaling mode and sync modes etc, also make a big difference: http://www.sami-lehtinen.net/blog/sqlite3-performance-testing http://www.sami-lehtinen.net/blog/sqlite3-performance-testin...
- lomnakkus 10y agoThose optimizations also apply to PostgreSQL.
- Programmatic 10y agoIt would still allow you to use a simpler solution and not paint yourself in a corner if you optimized on sqlite; no preemptive optimization necessary wrt choice of dbms if you can perform the work on the simpler solution.
- lomnakkus 10y agoOptimizing in SQLite is just as much of a sunk cost as optimizing in PostgreSQL if you switch away to another database.
- orf 10y ago> What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over 30 seconds in SQlite. I'm sorry to say but you're almost certainly doing something wrong then.
- chjj 10y agoIt's possible, but it was a very simple database layout. Pretty hard to screw up. Then again, I screw up simple things all the time. But whatever I screwed up would likely have been duplicated on the postgres side since the sqlite and postgres setups were nearly identical. Postgres performed fine compared to sqlite. It was a bit behind leveldb, but I figure the overhead of flushing the data to a socket has an added cost. Leveldb has the advantage of being in the same process. If I remember right: Querying for leveldb was iteration from [20-byte-hash][lo-utxo-id] to [20-byte-hash][hi-utxo-id] and subsequent lookups on the keys containing the actual data. The sql table setup was `id` as a char primary key and `address` as a char with an index. The query was a simple `select data from utxos where address = ?`. The actual `data` blob was on average maybe 60 bytes in size. Maybe there was something I could have done better there. I'm not sure. This was just my experience. This is all beside the point anyway. My point was whatever database was better, it was worth testing each one, and I don't consider it to be premature optimization.
- pilif 10y ago`select * from utxos where address =?` should not take 300ms no matter the size or type of database (assuming there is an index on address).
- agnivade 10y agoNot really. If your table does not fit into memory, there is a good chance that a disk seek is required. Now all bets are off.
- invaliduser 10y ago
- ams6110 10y agoWith relational databases, you absolutely can see subsecond to 30+ second ranges for the same query on the same data, if you don't have proper indexing or stats on your tables.
- voiper1 10y agoIndeed! An index can be the difference between a 2-5minute full table scan and a subsecond query.
- Sami_Lehtinen 10y agoDepends from your disk system and data set size ... It can also mean 2-5 hours or days versus subsecond query. Try having 6 TB database with integer column. Then select just one row from it. Either it's indexed or not. You don't need to explain that, if query ends up taking forever it wasn't indexed.