4 ms·

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

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 https://www.sqlite.org/pragma.html#pragma_synchronous [2] https://sqlite.org/wal.html https://sqlite.org/wal.html

pornel 6y ago
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.
- quietbritishjim 6y ago
  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.
  - Const-me 6y ago
    > 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 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.
    - tinus_hn 6y ago
      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.
      - Const-me 6y ago
        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.
        tinus_hn 6y ago
        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.
- AtlasBarfed 6y ago
  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.
  - mekster 6y ago
    Do you get more thanks than just telling people to use MySQL than your 3 layer complicated solution?
    - quietbritishjim 6y ago
      I'm sure they were being sarcastic.
- xorcist 6y ago
  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.
  - slaymaker1907 6y ago
    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 https://sqlite.org/lang_corefunc.html#last_insert_rowid
    - anarazel 6y ago
      > 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 https://www.postgresql.org/docs/devel/sql-insert.html
- ngrilly 6y ago
  There's a way around it which is actively worked on apparently: https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
- bayindirh 6y ago
  > 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.
- lenkite 6y ago
  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.
mshenfield 6y ago
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 https://sqlite.com/wal.html#the_wal_file

pornel 6y ago

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.

quietbritishjim 6y ago
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.
- Const-me 6y ago
  > 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 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.
  - tinus_hn 6y ago
    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.
    - Const-me 6y ago
      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.
      - tinus_hn 6y ago
        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.
AtlasBarfed 6y ago
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.
- mekster 6y ago
  Do you get more thanks than just telling people to use MySQL than your 3 layer complicated solution?
  - quietbritishjim 6y ago
    I'm sure they were being sarcastic.
xorcist 6y ago
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.
- slaymaker1907 6y ago
  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 https://sqlite.org/lang_corefunc.html#last_insert_rowid
  - anarazel 6y ago
    > 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 https://www.postgresql.org/docs/devel/sql-insert.html
ngrilly 6y ago
There's a way around it which is actively worked on apparently: https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
bayindirh 6y ago
> 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.
lenkite 6y ago
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.

mshenfield 6y ago

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 https://sqlite.com/wal.html#the_wal_file