We’re pretty happy with SQLite and not urgently interested in a fancier DBMS • Hacker News Board

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

#111

Post by SixSigma » Mon, Jun 20, 2016, 7:20 AM UTC

mianos wrote: post #87

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…

So many times I see people handing out a root password to edit /etc/password. Sure, it may be crazy for performance but, even a trivial number of users and aliases are more easily managed with a remote mysql client. Everyone that has used postfix in a production environment knows the pain otherwise, the rest feel free to vote down.

The next step up is userdb though, not MySql

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

#112

Post by SixSigma » Mon, Jun 20, 2016, 7:22 AM UTC

chjj wrote: post #81

orf wrote: post #58

Earlier quoted context omitted.

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

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…

Until there are millions of records grep will win

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

#113

Post by INTPenis » Mon, Jun 20, 2016, 7:32 AM UTC

leo_santagada wrote: post #110

INTPenis wrote: post #109

Earlier quoted context omitted.

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.

I don't even know what it is so I wish I had looked it up first but now it's too late. Also mysql will aide in later replicating the application between different locations to ensure availability for each office.

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

#114

Post by takeda » Mon, Jun 20, 2016, 7:53 AM UTC

tedmiston wrote: post #18

I have used SQLite for similar use cases, but occasionally it's led to a corrupted db. I had a cron task writing to it once a day, but an issue with the scheduler led to 2 tasks one day with the latter one finishing before the former. Of course I can add locking or something in my code, but I'd prefer to handle at a lower level — for example, have SQLite take the latest write without corrupting. I'm hoping someone ha…

Haven't used SQLite in a while, but this is how I did it.

You should enable WAL (pragma journal_mode=wal) I believe you need to do this on every connection.

Now, whenever you are accessing the data do it within transactions (https://www.sqlite.org/lang_transaction.html) generally SQLite will acquire proper locks when needed (it will minimize amount of locked time by deferring locks until they are needed, but you can use immediate or exclusive modifiers. If you for example put exclusive modifier only one program will be able to read/write at the time, you generally will want to use defer (default) behavior since it still provides the safety but multiple applications still can access the database at the same time)

Also note that locking might not work if you keep the database on network file system.

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

#115

Post by invaliduser » Mon, Jun 20, 2016, 8:20 AM UTC

agnivade wrote: post #105

pilif wrote: post #96

Earlier quoted context omitted.

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

By table you mean index, right ? What matters most is the size of the index, and most index data structures (see btrees) work so that very few disk seeks are needed, even if the index is large.

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

#116

Post by creshal » Mon, Jun 20, 2016, 8:22 AM UTC

spudlyo wrote: post #52

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…

Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL! It's really not that hard to imagine. There is an embedded library version of MySQL called libmysqld which is made for this very purpose. Of course as you point out it's quite overkill for something that would work perfectly well with Berkeley DBs.

I've never seen it used like that, but Microsoft's equivalent SQL Server Express is a nightmare in deployment and support, because you need to administer a full-blown SQL Server on every customer PC (backups, migration debugging, …). I've no idea why people don't use SQLite instead.

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

#117

Post by creshal » Mon, Jun 20, 2016, 8:27 AM UTC

mianos wrote: post #87

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…

So many times I see people handing out a root password to edit /etc/password. Sure, it may be crazy for performance but, even a trivial number of users and aliases are more easily managed with a remote mysql client. Everyone that has used postfix in a production environment knows the pain otherwise, the rest feel free to vote down.

Why MySQL and not LDAP?

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

#118

Post by SwellJoe » Mon, Jun 20, 2016, 8:28 AM UTC

mianos wrote: post #87

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…

So many times I see people handing out a root password to edit /etc/password. Sure, it may be crazy for performance but, even a trivial number of users and aliases are more easily managed with a remote mysql client. Everyone that has used postfix in a production environment knows the pain otherwise, the rest feel free to vote down.

There are practically countless ways to delegate management of mailboxes and users without granting root. That's not even a hard problem to solve, and certainly not one that justifies introducing a hugely complex additional variable to the equation.

Anyway, I'm not saying "never use MySQL for mail users" (though, I think the percentage of deployments where it makes sense is closer to none than it is to one), I'm just trying to make the point that MySQL is, in some folks minds, a magical solution to performance problems. Often, it not only introduces needless complexity, it won't even improve performance. It's a classic example of "when all you have is a hammer, everything starts to look like a nail". MySQL is a very fine hammer. It just isn't the right tool for every job.

The article we're talking about is another case where a little knowledge is a dangerous thing. A desktop app serving one user with a tiny data set (as I understand it, we're talking about the metadata for a person's music collection) is exactly the right workload for SQLite. I'd be shocked if a naive port to MySQL were faster (though they acknowledge that there's room for query optimization), and not at all surprised if it were slower. And, I know it'll require more memory and disk space for the same working set.

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

#119

Post by SwellJoe » Mon, Jun 20, 2016, 8:30 AM UTC

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.

That's a pretty big difference in scale. The app in the original article seems to be a desktop application, not a client/server system with many users. There's plenty of use cases where MySQL (or PostgreSQL) is a great choice. Yours is probably one of them.

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

#120

Post by creshal » Mon, Jun 20, 2016, 8:31 AM UTC

int_19h wrote: post #95

While we're speaking of SQLite; one thing that has little exposure that could probably use more is that it now ships with Windows as a system DLL: https://engineering.microsoft.com/2015/10/29/sqlite-in-windo... Between that, and packages readily available on most Linux and BSD distros out there (and, in most cases, installed by default), it's well on its way to become a de facto standard system API for relational sto…

It's amusing how Microsoft's different departments keep trying to kill off each other. They spent so much time trying to shove SQL Server Express down everyone's throat, and now everyone gets SQLite included instead.