Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

beets.io

Post reply on HN
Print view View original

101–110 of 149 posts

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#101

Post by Sami_Lehtinen »

SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

I've done also some tests, and so far in most of cases SQLite3 has been fastest option and it's also trivial to manage compared to other options. SQLite3 is absolutely awesome. Until you'll need concurrent writes. It's also important to notice that configuration options will make a big performance difference. http://www.sami-lehtinen.net/blog/sqlite3-performance-python... http://www.sami-lehtinen.net/blog/database-performance-tests... http://www.sami-lehtinen.net/blog/sqlite3-performance-testin...

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#102

Post by Sami_Lehtinen »

mappu wrote: post #57
chjj wrote: post #41

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 so…

I'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.

Journaling mode and sync modes etc, also make a big difference: http://www.sami-lehtinen.net/blog/sqlite3-performance-testin...

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#103

Post by Sami_Lehtinen »

voiper1 wrote: post #88
ams6110 wrote: post #61

Earlier quoted context omitted.

With 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.

Indeed! An index can be the difference between a 2-5minute full table scan and a subsecond query.

Depends 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.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#104

Post by ThisIs_MyName »

Sami_Lehtinen wrote: post #101
SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

I've done also some tests, and so far in most of cases SQLite3 has been fastest option and it's also trivial to manage compared to other options. SQLite3 is absolutely awesome. Until you'll need concurrent writes. It's also important to notice that configuration options will make a big performance difference. http://www.sami-lehtinen.net/blog/sqlite3-performance-python... http://www.sami-lehtinen.net/blog/database-pe…

Yup, I did some benchmarking (for a webapp which runs a separate process for each customer) and SQLite had the highest TPS and lowest memory usage by a large margin.

Probably because it runs in the same address space as each server process and there is no message passing (TCP/unix socket) overhead.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#105

Post by agnivade »

pilif wrote: post #96
chjj wrote: post #81

Earlier quoted context omitted.

It'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 c…

`select * from utxos where address =?` should not take 300ms no matter the size or type of database (assuming there is an index on address).

Not 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.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#106

Post by Annatar »

Wow, the article is such a fresh breath of air, primarily because the author demonstrates common sense.

He (they?) picked SQLite for all the correct reasons:

- best tool for the job for their situation;

- write-light and read-heavy;

- zero configuration;

- easy to embed;

- understanding that optimizing queries by far gives the best performance in the shortest amount of time.

As an aside, I'm currently using SQLite for Bacula and Postfix, and it's a joy to use; the only drawback I found so far is lack of REGEXP REPLACE in the SQL dialect which the database supports (must be loaded with .load /path/to/lib/libpcre.so, but it is not part of the language). I used the Oracle RDBMS for my PowerDNS deployments, but in retrospect, the way PowerDNS works, SQLite would have been an even better match. All in all, it is great to read that someone picked it for all the correct reasons, rather than some fashion trend, as is often the case in computer industry.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#107

Post by kyriakos »

SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

Kodi media player uses mysql and runs on most platforms. As an end user mysql is completely invisible

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#108

Post by Annatar »

Sami_Lehtinen wrote: post #101
SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

I've done also some tests, and so far in most of cases SQLite3 has been fastest option and it's also trivial to manage compared to other options. SQLite3 is absolutely awesome. Until you'll need concurrent writes. It's also important to notice that configuration options will make a big performance difference. http://www.sami-lehtinen.net/blog/sqlite3-performance-python... http://www.sami-lehtinen.net/blog/database-pe…

SQLite is a practical option if concurrent writes are not required. It is not meant for that, and that is also one of the reasons why it is both fast and simple, since without concurrency, a whole set of really complex problems goes away. SQLite is great when the communication and arbitration between two services takes place at the application level, as is the case with DNS AXFR requests, for example, and each of those services may have its own private data store. In such a situation, an immediate benefit is realized by having a domain specific, uniform data manipulation tool (SQL) without having to write custom data manipulation code. This is especially well suited to scenarios where configuration management is performed via OS packaging.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#109

Post by INTPenis »

SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

Not faster perhaps but with sqlite3 I had locking issues and after I switched to mysql I don't. It's really that simple. The app was used by a small office of 40 people at first, then started being used by close to a hundred people and that's when the locking errors began.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#110

Post by leo_santagada »

INTPenis wrote: post #109
SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

Not faster perhaps but with sqlite3 I had locking issues and after I switched to mysql I don't. It's really that simple. The app was used by a small office of 40 people at first, then started being used by close to a hundred people and that's when the locking errors began.

Were you using wal mode with sqlite? It helps a lot with locking.
Post reply on HN