SQLite

View Ticket
Login
Ticket Hash: ba7fdb568d6c80b90dfdc66d44e8a6f29a9ebd0f
Title: Page size should auto default to file system block size
Status: Fixed Type: Feature_Request
Severity: Minor Priority: Immediate
Subsystem: VFS Resolution: Fixed
Last Modified: 2014-03-11 13:05:46
Version Found In: 3.6.18
Description:
See http://www.sqlite.org/cvstrac/tktview?tn=2931 which detects the sector size (not the same as page size or filesystem block size!)

With the hard drive industry wanting to move to 4,096 byte sectors and SSD drives preferring operations that match their erase block size it is becoming increasingly important that SQLite defaults match the hardware for performance as well as reliability (erase blocks).

On POSIX (f)statvfs is useful. On Windows GetDiskFreeSpace is useful (cluster size matches page size well).


rogerb added on 2009-10-12 04:24:32:
See also http://www.sqlite.org/cvstrac/tktview?tn=3452 although it confuses sector size with page size.

In any event the VFS reports on sector size (the minimum unit of work the disk can do atomically).

Probably the easiest way of adding this functionality is to allow "pragma page_size=0" to mean "please set it to a reasonable default" and using fstatvfs under posix to get a reasonable value.

Ticket 3452 does mention how ZFS gets especially bad performance with the default SQLite page size.