4 ms·

Regarding shared caching: Use `PRAGMA mmap_size` to enable mmap for reading your database. This way SQLite won't add another layer of page caching on top savin

Regarding shared caching: Use `PRAGMA mmap_size` to enable mmap for reading your database. This way SQLite won't add another layer of page caching on top saving RAM and making things faster. SQLite only uses mmap for reads and will continue to write to the database with pwrite(). You must set it to a value higher than the size of your DB. I use: PRAGMA mmap_size = 1099511627776; (1TB)

rogerbinns 1y ago
Unless you compile SQLite yourself, you'll find the maximum mmap size is 2GB. ie even with your pragma above, only the first 2GB of the database are memory mapped. It is defined by the SQLITE_MAX_MMAP_SIZE compile time constant. You can use pragma compile_options to see what the value is. https://sqlite.org/compile.html#max_mmap_size https://sqlite.org/compile.html#max_mmap_size Ubuntu system pragma compile_options: MAX_MMAP_SIZE=0x7fff0000
- otterley 1y ago
  That seems like a holdover from 32-bit days. I wonder why this is still the default.
  - rogerbinns 1y ago
    SQLite has 32 bit limits. For example the largest string or blob it can store is 2GB. That could only be addressed by an incompatible file format change. Many APIs also use int in places again making limits be 32 bits, although there are also a smattering of 64 bit APIs. Changing this default requires knowing it is a 64 bit platform when the C preprocessor runs, and would surprise anyone who was ok with the 2GB value. There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, and buggy stray writes by other code in the process could corrupt the database. It is best practise to directly include the SQLite amalgamation into your own projects which allows you to control version updating, and configuration.
    - otterley 1y ago
      All great points. Thank you!
    - wmanley 1y ago
      >There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, True. https://www.sqlite.org/mmap.html https://www.sqlite.org/mmap.html lists 3 other issues as well. > and buggy stray writes by other code in the process could corrupt the database. Not true: "SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file."

rogerbinns 1y ago

Unless you compile SQLite yourself, you'll find the maximum mmap size is 2GB. ie even with your pragma above, only the first 2GB of the database are memory mapped. It is defined by the SQLITE_MAX_MMAP_SIZE compile time constant. You can use pragma compile_options to see what the value is. https://sqlite.org/compile.html#max_mmap_size https://sqlite.org/compile.html#max_mmap_size Ubuntu system pragma compile_options: MAX_MMAP_SIZE=0x7fff0000

otterley 1y ago
That seems like a holdover from 32-bit days. I wonder why this is still the default.
- rogerbinns 1y ago
  SQLite has 32 bit limits. For example the largest string or blob it can store is 2GB. That could only be addressed by an incompatible file format change. Many APIs also use int in places again making limits be 32 bits, although there are also a smattering of 64 bit APIs. Changing this default requires knowing it is a 64 bit platform when the C preprocessor runs, and would surprise anyone who was ok with the 2GB value. There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, and buggy stray writes by other code in the process could corrupt the database. It is best practise to directly include the SQLite amalgamation into your own projects which allows you to control version updating, and configuration.
  - otterley 1y ago
    All great points. Thank you!
  - wmanley 1y ago
    >There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, True. https://www.sqlite.org/mmap.html https://www.sqlite.org/mmap.html lists 3 other issues as well. > and buggy stray writes by other code in the process could corrupt the database. Not true: "SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file."