5 ms·

For a long time I've been running many apps with a wrapper to disable fsync in SQLite integrations. Its a harsh solution. Am I correct that out of the box it's

by zbuf 6y ago

For a long time I've been running many apps with a wrapper to disable fsync in SQLite integrations. Its a harsh solution. Am I correct that out of the box it's implementing a full integrity around "commit" -- ie. The data has to fully hit the disk before the call returns? For most applications I don't want this. Browser history for example is fine if the most recent transactions are lost on a power failure. Of course I do not want the file to be corrupted and lost. At a previous employer we had similar issues with Chrome's internal SQLite database (Linux) in an environment with network mounted home directories. It was dealt with by a Chrome wrapper to change one of the internal flags on the user's SQLite database. Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format" In the docs. I only find "PRAGMA synchronous=OFF." Did I dream it, or is there a similar setting to preserve the integrity of the file through write barriers, journaling etc., but not have a full fsync on transaction? Because then on a power failure the file would contain "a stable commit" but not necessarily "the most recent commit" ... And it seems that for most SQLite integrations that is what we want? Instead we're now all burning through SSDs for our trivial user settings files, a bit like everything is O_SYNC and the page cache was never invented.

snuxoll 6y ago
> Because then on a power failure the file would contain "a stable commit" but not necessarily "the most recent commit" ... And it seems that for most SQLite integrations that is what we want? You HAVE to commit the journal to disk in a stable fashion - that requires fsync() because without there no guarantee that the data is actually on disk. Without the journal there’s no way to back out a partially applied write, leaving you with a corrupt database. Could an application maintain the state in memory and periodically flush out to SQLite to get around it? Sure. But many people would be pissed to lose their browser history for the past N time before a crash or power loss after just finding a page they needed for some purpose. Get a better storage solution for your home directories, ZFS with high lifespan Optane drives as a SLOG would dramatically increase the write endurance of standard NAND flash by batching writes while maintaining acceptable fsync performance (since synchronous writes don’t hit the pool itself until the transaction group commits every X seconds).
- zbuf 6y ago
  > You HAVE to commit the journal to disk in a stable fashion - that requires fsync() because without there no guarantee that the data is actually on disk. But does this indicate the issue; that userland only has fsync() API -- it's a case of full synchronous write, or not. Whereas the SQLite file could contain its own journal and data, and maintain the integrity using a write barrier. I still have no need for my browser history to be fully synchronous to disk. Yet that's what we're doing, and then... > Get a better storage solution for your home directories, ZFS with high lifespan Optane drives as a SLOG would dramatically increase the write endurance of standard NAND flash by batching writes while maintaining acceptable fsync performance ... spending on high grade tech in an attempt to solve the problem we just created. Fundamentally, we're asking these systems to do synchronous writes, when it's not what we actually want. When everything's a priority, nothing's a priority.
- theamk 6y ago
  Or maybe stop using sqlite, and serialize your state to disk periodically, just like this document says. Modern filesystems are pretty good in maintaining file integrity if you use “atomic overwrite via rename” trick, and for extra assurance you can store an older version of config or five. This will give you a “pretty robust” solution which will work “most of the time”, and that will completely avoid fsyncs and prolong your SSD life. What’s not to like about it?
mytherin 6y ago
> Am I correct that out of the box it's implementing a full integrity around "commit" -- ie. The data has to fully hit the disk before the call returns? Yes, as is the default of any ACID-compliant RDBMS. This is what the "D" in ACID stands for (durability): > Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). There is no way to guarantee durability without fsync, as without fsync there is no guarantee that the data has been written to disk and is not simply cached by the OS. Any database that guarantees durability performs an fsync after every transaction. > Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format" I would argue it is a very sane default. The database system does not know how you value the data that you ask it to store. By default it takes the position that your data is very valuable, and that you would not want to lose it even in the case of a crash. In some cases that is overkill, but in some cases it is not. The safer default is to be cautious. You can always disable fsync if you find that you need the performance boost. Valuable data that is lost is lost forever. I do agree that a mode that you describe ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion.
- zbuf 6y ago
  > ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion. PostgreSQL has such a capability; "synchronous_commit=off". If you do want performance at all costs at the risk of integrity there's a separate "fsync=off". The former is very useful, exactly what we needed on a database application I was developing. The latter I can't see a benefit to using. I'm interested now as to how it's implemented -- which APIs and what assumptions of the filesystem it makes. And therefore whether SQLite would be capable of something similar. If it exists in SQLite (I can't find it) then it needs promoting and using more. In general my interactions with SQLite have been good, but its ubiquity seems to be spreading a little plague of disk chatter and bottleneck effects on I/O. It's anecdotal, but a lot of the pauses on my Android phone seem like they might be rooted in this, too. [*] https://www.postgresql.org/docs/12/wal-async-commit.html https://www.postgresql.org/docs/12/wal-async-commit.html
liuliu 6y ago
WAL mode can help large part of the fsync issues, but it does break the "one file" niceties SQLite has. If you don't care about losing some recent commits, WAL is definitely the way to go: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html I would also say that WAL has been receiving improvements from what I can see. For example, 3 years ago, you cannot open a WAL mode SQLite file in read-only mode, but now you can since 3.22.0.
vbezhenar 6y ago
If you don't need fsync, don't issue commit. It's pretty simple.
tobias3 6y ago
Sure, use WAL journal mode and then run "PRAGMA synchronous=NORMAL". Documentation is at https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous . The default is FULL (compile time option SQLITE_DEFAULT_SYNCHRONOUS ) which makes SQLite durable, but naturally causes a fsync() at every commit. With NORMAL it only runs fsync() once it checkpoints from WAL to db file. I run it with synchronous=NORMAL, but set it to FULL for specific transactions which I want to be durable. Then I turn off auto checkpointing and manually run WAL checkpointing in another thread once it is relatively large. That way it runs fsync() really rarely. That more performant setup is a bit complicated (and error prone) so it is no wonder that it is rarly done. Though I hope Firefox does it nowadays.

by zbuf 6y ago

snuxoll 6y ago

> Because then on a power failure the file would contain "a stable commit" but not necessarily "the most recent commit" ... And it seems that for most SQLite integrations that is what we want? You HAVE to commit the journal to disk in a stable fashion - that requires fsync() because without there no guarantee that the data is actually on disk. Without the journal there’s no way to back out a partially applied write, leaving you with a corrupt database. Could an application maintain the state in memory and periodically flush out to SQLite to get around it? Sure. But many people would be pissed to lose their browser history for the past N time before a crash or power loss after just finding a page they needed for some purpose. Get a better storage solution for your home directories, ZFS with high lifespan Optane drives as a SLOG would dramatically increase the write endurance of standard NAND flash by batching writes while maintaining acceptable fsync performance (since synchronous writes don’t hit the pool itself until the transaction group commits every X seconds).

zbuf 6y ago
> You HAVE to commit the journal to disk in a stable fashion - that requires fsync() because without there no guarantee that the data is actually on disk. But does this indicate the issue; that userland only has fsync() API -- it's a case of full synchronous write, or not. Whereas the SQLite file could contain its own journal and data, and maintain the integrity using a write barrier. I still have no need for my browser history to be fully synchronous to disk. Yet that's what we're doing, and then... > Get a better storage solution for your home directories, ZFS with high lifespan Optane drives as a SLOG would dramatically increase the write endurance of standard NAND flash by batching writes while maintaining acceptable fsync performance ... spending on high grade tech in an attempt to solve the problem we just created. Fundamentally, we're asking these systems to do synchronous writes, when it's not what we actually want. When everything's a priority, nothing's a priority.
theamk 6y ago
Or maybe stop using sqlite, and serialize your state to disk periodically, just like this document says. Modern filesystems are pretty good in maintaining file integrity if you use “atomic overwrite via rename” trick, and for extra assurance you can store an older version of config or five. This will give you a “pretty robust” solution which will work “most of the time”, and that will completely avoid fsyncs and prolong your SSD life. What’s not to like about it?

mytherin 6y ago

> Am I correct that out of the box it's implementing a full integrity around "commit" -- ie. The data has to fully hit the disk before the call returns? Yes, as is the default of any ACID-compliant RDBMS. This is what the "D" in ACID stands for (durability): > Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). There is no way to guarantee durability without fsync, as without fsync there is no guarantee that the data has been written to disk and is not simply cached by the OS. Any database that guarantees durability performs an fsync after every transaction. > Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format" I would argue it is a very sane default. The database system does not know how you value the data that you ask it to store. By default it takes the position that your data is very valuable, and that you would not want to lose it even in the case of a crash. In some cases that is overkill, but in some cases it is not. The safer default is to be cautious. You can always disable fsync if you find that you need the performance boost. Valuable data that is lost is lost forever. I do agree that a mode that you describe ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion.

zbuf 6y ago
> ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion. PostgreSQL has such a capability; "synchronous_commit=off". If you do want performance at all costs at the risk of integrity there's a separate "fsync=off". The former is very useful, exactly what we needed on a database application I was developing. The latter I can't see a benefit to using. I'm interested now as to how it's implemented -- which APIs and what assumptions of the filesystem it makes. And therefore whether SQLite would be capable of something similar. If it exists in SQLite (I can't find it) then it needs promoting and using more. In general my interactions with SQLite have been good, but its ubiquity seems to be spreading a little plague of disk chatter and bottleneck effects on I/O. It's anecdotal, but a lot of the pauses on my Android phone seem like they might be rooted in this, too. [*] https://www.postgresql.org/docs/12/wal-async-commit.html https://www.postgresql.org/docs/12/wal-async-commit.html

liuliu 6y ago

WAL mode can help large part of the fsync issues, but it does break the "one file" niceties SQLite has. If you don't care about losing some recent commits, WAL is definitely the way to go: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html I would also say that WAL has been receiving improvements from what I can see. For example, 3 years ago, you cannot open a WAL mode SQLite file in read-only mode, but now you can since 3.22.0.

vbezhenar 6y ago

If you don't need fsync, don't issue commit. It's pretty simple.

tobias3 6y ago

Sure, use WAL journal mode and then run "PRAGMA synchronous=NORMAL". Documentation is at https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous . The default is FULL (compile time option SQLITE_DEFAULT_SYNCHRONOUS ) which makes SQLite durable, but naturally causes a fsync() at every commit. With NORMAL it only runs fsync() once it checkpoints from WAL to db file. I run it with synchronous=NORMAL, but set it to FULL for specific transactions which I want to be durable. Then I turn off auto checkpointing and manually run WAL checkpointing in another thread once it is relatively large. That way it runs fsync() really rarely. That more performant setup is a bit complicated (and error prone) so it is no wonder that it is rarly done. Though I hope Firefox does it nowadays.