Comment by quietbritishjim | Hacker News Reader

Comment by quietbritishjim | Hacker News Reader

Parent Full thread

quietbritishjim

Like everyone else, I'm going to offer tuning tips even thought that very much wasn't your point :-)

What you are describing sounds almost exactly like PRAMGA synchronous = FULL [1] (which is the default). That pragma controls when fsync occurs. Depending on your application, you might have got away with NORMAL or even OFF. Again depending on your application, you could have set journal_mode = OFF or increased the mmap_size (both also discussed on that page). Yes those are fairly magical hacks, but synchronous and journal_mode at least are things are always worth considering for a new SQLite database (and before building it with debugging symbols!).

Even without those tweaks, the really key thing is to use fairly large transactions. I'm surprised that alone wouldn't have got you decent performance.

One option you didn't have at the time but might help today is write-ahead mode [2] with journal_mode = WAL (but still presumably not as fast as journal_mode = OFF!). I believe the only reason it isn't enabled by default is for backwards compatibility. According to that article, it was introduced in 2010-07-21, and improved to better handle large transactions (>100MB) in 2016-02-15.

[1] https://www.sqlite.org/pragma.html#pragma_synchronous

[2] https://sqlite.org/wal.html

I've been there. It doesn't work. I've been applying all those tips and tricks, but it didn't improve performance that much, but I have lost data.

Sqlite is full of locks, and any writes are single-threaded stop-the-world, and there's no way around it. It's Sqlite's philosophy. Write-heavy databases just need something like MVCC, and Sqlite won't have that.

I think you make a point which is very true and valid but not relevant to this particular comment thread.

You are talking about multiple different processes/threads heavily and concurrently writing to a database. In that case, you're absolutely right, the point has come to switch to a client/server database like PostgreSQL or MySQL.

But the parent comment was not about that (or at least they didn't explicitly mention concurrency, and their mention of "a default setting that made spill-to-disk very aggressive" rather than locks suggested that concurrency wasn't the problem). They seemed to be talking about a single writer inserting at a high rate, which is something I'd expect to cope with very well with the right tricks (mostly batching multiple inserts in transactions - I acknowledge their comment that the defaults are unfortunate though). Yes even with a single process there are locks, but if a lock is uncontended then it is not normally a problem.

> the point has come to switch to a client/server database

Windows has an embedded NoSQL DB engine which is fine with concurrent writes and multi-versioning: https://en.wikipedia.org/wiki/Extensible_Storage_Engine

There’re disadvantages too. It does not implement SQL, the queries need to be done manually on top of various indices in these tables. The DB has much more than a single file. The API is way more complicated than sqlight. The databases are portable from older to newer versions of Windows with automatic upgrades, but not the other way.

The JET database continually corrupts itself. It’s terrible.

The Windows search functionality uses this database and the only way Microsoft was able to make this feature somewhat reliable is by having it run very thorough checks at startup and tossing the database and reindexing at the first hint of trouble.

I don’t know why it is so terribly unreliable but I know it is.

Interesting, I have opposite experience. Once I had lots of experience with AD and Exchange. Later I shipped desktop software based on ESENT, and supported it for a few years. Was generally happy with the technology.

Are you sure these issues are caused by the DB engine? Other possibilities include your PC (like interference with crappy AV software), or Microsoft’s code of these search indexing services.

This happens on large amounts of PCs and I don’t think the way Microsoft uses its own database engine should be an excuse for it corrupting its data.

I think the difference is that Windows Search is always running, including when the computer crashes, is turned off unexpectedly or doesn’t complete waking up from sleep or hibernation. I think JET just isn’t that robust against that. It’s quite conceivable people manually closed your software when they shut down their computer.

I remember Exchange failing the same way if its database disks suddenly disappear due to network issues.

> Microsoft uses its own database engine

Microsoft is large and software quality varies. For instance look a Skype, they failed to use their own GUI frameworks and are using Electron i.e. Google Chrome to paint a few controls.

> I think JET just isn’t that robust against that.

I think it is, it's mentioned everywhere:

https://docs.microsoft.com/en-us/windows/win32/extensible-st...

https://stackoverflow.com/a/2369220/126995

Okay, ‘according to the documentation’ it probably is. In reality it isn’t.

Like they say,

In theory, there’s no difference between theory and practice. In practice, there is.

Here’s a list of things that can go wrong (here in the context of domain controllers):

https://docs.microsoft.com/en-us/troubleshoot/windows-server...

Some of them are just the unavoidable hardware failures, for others they suggest ‘Deploy the OS on server-class hardware’. Or the always helpful ‘restore from backup’. Not quite reasonable for a database containing a search index on a consumer device.

> Here’s a list of things that can go wrong (here in the context of domain controllers)

That article was written because DC is a business-critical infrastructure. Here’s a comparable one about Oracle: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/c...

NTFS: https://docs.microsoft.com/en-us/previous-versions/windows/i...

> Not quite reasonable for a database containing a search index on a consumer device.

Before I switched to MS Outlook, I was using Windows live mail (now discontinued) as an e-mail client for a decade or so, it used ESENT for everything.

When I run process explorer and search for “esent.dll”, it finds a dozen of system services using ESENT databases, many of them critical like CryptSvc.

I try to buy good hardware, but that’s not server-grade components. I don’t use ECC RAM nor a UPS, and I suffer from brief power outages couple times a year. If ESENT would be corrupting databases when the power is turned off suddenly, I would have noticed.

Your experiment with a sample size of 1 proves, with an uncertainty of 100%, that the database never corrupts.

Experiments with a larger sample size show a different result.

See what you need to do is stand up a Redis cache cluster in front of your app, and then a cassandra cluster to cache the writes and compact them, before synchronizing the data with your single-page SQLite datastore as the master data.

Obviously for your SQL queries we crank up a large SparkSQL cluster.

- AWS architects

You can thank me later.

Do you get more thanks than just telling people to use MySQL than your 3 layer complicated solution?

I'm sure they were being sarcastic.

Why would stop-the-world affect performance when you are doing batch inserts?

Stopping the database is even a common trick when bulk inserting into "real" databases.

I think they are referring to a OLTP type scenario where you have lots of inserts and reads, but the reads only look at a couple of rows at a time, often only a single row. MVCC is good for such cases because you can avoid the overhead of taking lots of locks but still get fine grained concurrency control.

You are right that the Sqlite approach actually works quite well for bulk operations since you only require a single lock. However, it's usually still better for bulk inserts without updates to use fine grained locking or MVCC since you can often avoid acquiring any locks at all beyond the basic ones guarding fundamental DB data structures (these aren't locks as far as SQL is concerned since they cannot cause deadlock, it's a big pet peeve of mine when people think lock-free = no use of mutexes).

As a side note, don't do the following pattern: "BEGIN TRAN; INSERT INTO T ...; SELECT max(id) FROM T; COMMIT;". I used to do this, but this is a very bad habit that may be incorrect (assuming that id is an autoincrementing column). It's only correct on systems with true serializability, when you have opted into full serializability, and where such systems consider autoincrementing IDs to be part of serializability. When I tested this, Postgres and MSSQL handled this as expected while MySQL allowed the select to return a different row. I just tested Sqlite, and it does seem to work there regardless of WAL since it only allows concurrent readers plus a single writer. Use last_insert_rowid() or the equivalent for your database[1].

[1]: https://sqlite.org/lang_corefunc.html#last_insert_rowid

> As a side note, don't do the following pattern: "BEGIN TRAN; INSERT INTO T ...; SELECT max(id) FROM T; COMMIT;". I used to do this, but this is a very bad habit that may be incorrect (assuming that id is an autoincrementing column). It's only correct on systems with true serializability, when you have opted into full serializability, and where such systems consider autoincrementing IDs to be part of serializability.

And even when it work, it'll create a lot of unnecessary conflicts.

> Use last_insert_rowid() or the equivalent for your database[1].

RETURNING is the best approach for that in postgres (although lastval() also works). https://www.postgresql.org/docs/devel/sql-insert.html

There's a way around it which is actively worked on apparently:

https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...

> Sqlite is full of locks, and any writes are single-threaded stop-the-world, and there's no way around it. It's Sqlite's philosophy.

SQLite is not the silver bullet of the DB world, but it's extremely useful in certain scenarios. Will you need a DB for a desktop app? Ditto. Want a portable file to carry some complex data structure? Ditto. Want to test your webapp during development, or provide a single-user version for users? Ditto.

Concurrent writing, multiple user, multiple producer scenarios need something bigger. MySQL Embedded, MySQL, Posgres, MSSQL, Oracle... List goes on.

SQLite makes databases accessible and useful in much more scenarios. I've hated databases until I've found SQLite, because I simply didn't see the point of running a big server which is designed to handle much bigger data sets to store 250KB of text tables.

SQLite is extremely underrated IMHO.

According to several heavy sqlite users on HN, performance is improved if you do locking at the application layer. No idea if this is true.

One trick of `pragma journal_mode = WAL` is that even with synchronous normal, SQLite syncs when you close the last connection [1]. So re-using a connection is key to getting the full benefits if you're executing commands from a single thread/process.

[1] https://sqlite.com/wal.html#the_wal_file

Reply on news.ycombinator.com