3 ms·

The synchronous writes benchmark is suspect--on a 7200rpm disk it seems impossible to get 2,400 dependent transactions (or even sqlite's 430) with a full disk s

The synchronous writes benchmark is suspect--on a 7200rpm disk it seems impossible to get 2,400 dependent transactions (or even sqlite's 430) with a full disk sync. Is this due to ext3's relaxed notion of fsync?

MichaelGG 15y ago
Well look how SQLite does write ahead logging (assuming I understand it). Transactions can go into the log and commit, without modifying the original data. Now and then, it can "checkpoint" to move those logged transactions into the database. This way, transactions are mainly sequential writes, with a bit of maintenance now and then. I've used a similar approach for a constant database. The transaction log goes to disk, but also stays in memory. Scanning it linearly isn't that slow. When it grows too large, the log is converted into a disk-optimized format, which is a rather quick process. This way, it can take tons of writes, service reads acceptably, while still being able to store much more data than RAM and offering fast access to on-disk data.
- ryanpetrich 15y ago
  I understand that to be the case when not using synchronous. The point of synchronous full is that sqlite should only consider a transaction committed when it is fully flushed to disk. Assuming a 7200rpm disk that isn't lying to you, at most 120 dependent transactions a second should be even possible. (this should apply equally to other databases that have a similar option)
  - MichaelGG 15y ago
    Why does the disk need to re-seek to write to the next sector? With things like SQLite WAL, you're just performing sequential writes to disk.
    - ryanpetrich 15y ago
      I hadn't thought of that. If the disk can perform some localized remapping, it's possible to get sequential fsynced writes to exceed the rotation speed.
aaronblohowiak 15y ago
" Is this due to ext3's relaxed notion of fsync?" -- I believe so, especially given the warning about ext4 at the bottom. >The preceding numbers are for an ext3 file system. Synchronous writes are much slower under ext4 (LevelDB drops to ~34 writes / second, TreeDB drops to ~5 writes / second; SQLite3 drops to ~24 writes / second) due to ext4's different handling of fsync / msync calls. Even LevelDB's asynchronous write performance drops somewhat since it spreads its storage across multiple files and issues fsync calls when switching to a new file.

MichaelGG 15y ago

Well look how SQLite does write ahead logging (assuming I understand it). Transactions can go into the log and commit, without modifying the original data. Now and then, it can "checkpoint" to move those logged transactions into the database. This way, transactions are mainly sequential writes, with a bit of maintenance now and then. I've used a similar approach for a constant database. The transaction log goes to disk, but also stays in memory. Scanning it linearly isn't that slow. When it grows too large, the log is converted into a disk-optimized format, which is a rather quick process. This way, it can take tons of writes, service reads acceptably, while still being able to store much more data than RAM and offering fast access to on-disk data.

ryanpetrich 15y ago
I understand that to be the case when not using synchronous. The point of synchronous full is that sqlite should only consider a transaction committed when it is fully flushed to disk. Assuming a 7200rpm disk that isn't lying to you, at most 120 dependent transactions a second should be even possible. (this should apply equally to other databases that have a similar option)
- MichaelGG 15y ago
  Why does the disk need to re-seek to write to the next sector? With things like SQLite WAL, you're just performing sequential writes to disk.
  - ryanpetrich 15y ago
    I hadn't thought of that. If the disk can perform some localized remapping, it's possible to get sequential fsynced writes to exceed the rotation speed.

aaronblohowiak 15y ago

" Is this due to ext3's relaxed notion of fsync?" -- I believe so, especially given the warning about ext4 at the bottom. >The preceding numbers are for an ext3 file system. Synchronous writes are much slower under ext4 (LevelDB drops to ~34 writes / second, TreeDB drops to ~5 writes / second; SQLite3 drops to ~24 writes / second) due to ext4's different handling of fsync / msync calls. Even LevelDB's asynchronous write performance drops somewhat since it spreads its storage across multiple files and issues fsync calls when switching to a new file.