4 ms·

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there. > All machines use SSD except Win7 whi

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there. > All machines use SSD except Win7 which has a hard-drive. And yet Win10 is much slower than Win7 in all tests (note they have latency on the graphs). How’s that possible? For a drive spinning at 5400 RPM, average random read latency can’t be smaller than 2.2ms, this is how long it takes to rotate the disks 180° which is an average. An SSD should do much better than that. I have a couple possible explanation. Either that particular Win10 machine, 2016 Lenovo YOGA 910, has _extremely_ slow SSD. Or there’s something wrong with software/drivers/config of the PC, like an antivirus, or wrong chipset drivers.

karmakaze 7y ago
There's the other obvious explanation that because the Win7 machine has slower storage it automatically uses a larger cache than the Win10 machine that more than offsets the difference.
- Const-me 7y ago
  Windows kernel uses all free RAM as a file cache. Low priority one i.e. when an app wants to allocate RAM, the file system cached pages are evicted. Their Win7 test machine has 4GB RAM, Win10 16GB RAM. I think I know what’s going on. They don’t publish absolute numbers, they only publish result compared to SQLight on the same machine. Windows has approximately fixed overhead when opening files. Most of that overhead goes to access checks, NTFS security descriptors are way more sophisticated than 9 bits of security in Linuxes. With a fast disk that overhead dominates in the cost, SQLight doesn’t open any files while running the test, the DB already opened at startup. With a slow disk, that overhead is masked by large IO cost which affects both SQLight and NTFS.
  - kukx 7y ago
    Nitpick, but file permissions in Linux take normally 12 bits. You probably did not count SUID, SGID and sticky bit.
    - Const-me 7y ago
      I’ve only counted these famous RWX bits, settable by chmod. You’re right, there’s more security metadata involved in Linux. Still, Windows permissions are way more complicated, especially when AD domains are involved. These access control lists have arbitrary count of entries each. There’s another list, SACL, for audit. Access permissions are inherited through file system with non-trivial inheritance rules, e.g. allow + deny = deny. User permissions are inherited through group membership, and security groups can be nested i.e. include other groups. Security descriptors may include custom permissions unknown to MS. Any thread can run as any user in the forest through client impersonation, many network servers do. The kernel tries to apply effective permissions instantly, as soon as anything changes (but not for every read/write, that would be too slow, it only does access checks when opening files). Combined, these features make access checks way more expensive in Windows, by orders of magnitude.
    - fanf2 7y ago
      Or the ACLs or extended attributes https://linux.die.net/man/1/chattr https://linux.die.net/man/1/chattr https://linux.die.net/man/1/setfacl https://linux.die.net/man/1/setfacl
pixl97 7y ago
Maybe they have a bad benchmark setup and the 7 box is setup to cache writes to the disk.
- Const-me 7y ago
  After thinking for a bit, I think their results are actually reasonable. Their graphs are relative to how SQLight performs on the same machine. When measuring absolute numbers i.e. CPU cycles, all modern versions of Windows have comparable overhead on opening files, it’s the same file system and the kernels are quite similar. When a drive is slow, that overhead is masked by IO cost which almost equally applies to SQLight and NTFS. For a fast SSD that overhead is much higher than the cost of doing IO, hence the results.

karmakaze 7y ago

There's the other obvious explanation that because the Win7 machine has slower storage it automatically uses a larger cache than the Win10 machine that more than offsets the difference.

Const-me 7y ago
Windows kernel uses all free RAM as a file cache. Low priority one i.e. when an app wants to allocate RAM, the file system cached pages are evicted. Their Win7 test machine has 4GB RAM, Win10 16GB RAM. I think I know what’s going on. They don’t publish absolute numbers, they only publish result compared to SQLight on the same machine. Windows has approximately fixed overhead when opening files. Most of that overhead goes to access checks, NTFS security descriptors are way more sophisticated than 9 bits of security in Linuxes. With a fast disk that overhead dominates in the cost, SQLight doesn’t open any files while running the test, the DB already opened at startup. With a slow disk, that overhead is masked by large IO cost which affects both SQLight and NTFS.
- kukx 7y ago
  Nitpick, but file permissions in Linux take normally 12 bits. You probably did not count SUID, SGID and sticky bit.
  - Const-me 7y ago
    I’ve only counted these famous RWX bits, settable by chmod. You’re right, there’s more security metadata involved in Linux. Still, Windows permissions are way more complicated, especially when AD domains are involved. These access control lists have arbitrary count of entries each. There’s another list, SACL, for audit. Access permissions are inherited through file system with non-trivial inheritance rules, e.g. allow + deny = deny. User permissions are inherited through group membership, and security groups can be nested i.e. include other groups. Security descriptors may include custom permissions unknown to MS. Any thread can run as any user in the forest through client impersonation, many network servers do. The kernel tries to apply effective permissions instantly, as soon as anything changes (but not for every read/write, that would be too slow, it only does access checks when opening files). Combined, these features make access checks way more expensive in Windows, by orders of magnitude.
  - fanf2 7y ago
    Or the ACLs or extended attributes https://linux.die.net/man/1/chattr https://linux.die.net/man/1/chattr https://linux.die.net/man/1/setfacl https://linux.die.net/man/1/setfacl

pixl97 7y ago

Maybe they have a bad benchmark setup and the 7 box is setup to cache writes to the disk.

Const-me 7y ago
After thinking for a bit, I think their results are actually reasonable. Their graphs are relative to how SQLight performs on the same machine. When measuring absolute numbers i.e. CPU cycles, all modern versions of Windows have comparable overhead on opening files, it’s the same file system and the kernels are quite similar. When a drive is slow, that overhead is masked by IO cost which almost equally applies to SQLight and NTFS. For a fast SSD that overhead is much higher than the cost of doing IO, hence the results.