←back to thread

SQLite async connection pool for high-performance

(github.com)
197 points slaily | 5 comments | 11 Jul 25 10:26 UTC | HN request time: 0s | source
Show context
wmanley ◴[15 Jul 25 14:53 UTC] No.44571770[source]▶
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)
replies(1): >>44571951 #
1. rogerbinns ◴[15 Jul 25 15:09 UTC] No.44571951[source]▶
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

Ubuntu system pragma compile_options:

    MAX_MMAP_SIZE=0x7fff0000
replies(1): >>44572884 #
2. otterley ◴[15 Jul 25 16:27 UTC] No.44572884[source]▶
That seems like a holdover from 32-bit days. I wonder why this is still the default.
replies(1): >>44581736 #
3. rogerbinns ◴[16 Jul 25 12:49 UTC] No.44581736[source]▶
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.

replies(2): >>44584306 #>>44586027 #
4. otterley ◴[16 Jul 25 16:41 UTC] No.44584306{3}[source]▶
All great points. Thank you!
5. wmanley ◴[16 Jul 25 19:40 UTC] No.44586027{3}[source]▶
>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 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."