confusion re: docs for PRAGMA schema.cache_size
(1) By michaelb on 2022-09-03 18:07:27 [link] [source]
The beginning of pragma.html#pragma_cache_size indicates
PRAGMA schema.cache_size;
PRAGMA schema.cache_size = pages;
PRAGMA schema.cache_size = -kibibytes;
In the first paragraph that follows it's indicated
The default suggested cache size is -2000
However, when creating a new database with a recent version (e.g. 3.37.0), I get this
sqlite> PRAGMA cache_size;
2000
The second paragraph starts with
If the argument N is positive then the suggested cache size is set to N. If the argument N is negative, then the number of cache pages is adjusted to be a number of pages that would use approximately abs(N*1024) bytes of memory based on the current page size.
But that (1) seems to conflict with the beginning of the docs for cache_size and (2) the value 1024 appears to be out-of-date re: the default page_size since v3.12.0 per pragma.html#pragma_page_size.
What parts of the cache_size docs are accurate and what parts need to be updated?
Most pressing for my current needs is to know whether this part is accurate
PRAGMA schema.cache_size = pages;
PRAGMA schema.cache_size = -kibibytes;
(2) By Richard Hipp (drh) on 2022-09-03 22:41:14 in reply to 1 [link] [source]
However, when creating a new database with a recent version (e.g. 3.37.0), I get this sqlite> PRAGMA cache_size; 2000
It could be that whoever compiled your CLI added some nonstandard compile-time options to change the default cache size. When I try this, I get "-2000" not "2000".
(3.5) By michaelb on 2022-09-07 15:39:34 edited from 3.4 in reply to 2 [source]
Thank you for prompting me to take another look.
I thought I was running this build but was actually running the system build of sqlite3 on macOS (didn't notice the difference in version numbers at first).
I haven't found an explanation yet, but I'm guessing whoever at Apple is in charge of the system build chose to make the default cache_size effectively 2000*current_default_page_size instead of 2000*legacy_default_page_size.
After reading pgszchng2016, the docs make more sense to me, including cache_size = -kibibytes.
What do you think of revising a couple of sentences in the docs for cache_size?
If the argument N is positive then the suggested cache size will use N
*page_size bytes of memory. If the argument N is negative, then the number of cache pages is adjusted to be a number of pages that would use approximately abs(N*1024) bytes of memory, where 1024 is the legacy default page_size.
(4) By Keith Medcalf (kmedcalf) on 2022-09-07 17:35:55 in reply to 3.5 [link] [source]
How many legacy page sizes does your computer have?
If the number is >0 then the unit is pages (of whtever size a page happens to be), if the number <0 then the unit is kilobytes (and the magnitude in kilobytes is the absolute value).
A kilobyte existed as a unit long before a "page" was a gleam in your eye (or a brown stain on the sheet). ie: Sometime in the 1940's, just a wee bit shy of a century (100 years) ago.
(5) By michaelb on 2022-09-08 05:46:59 in reply to 4 [link] [source]
I'm familiar with the units, and have been fairly technically minded for some decades (just new to working with SQLite directly), but thanks for the nurple.
There's really no need to conjure Ermey's spirit into this discussion, unless you're hiding a jelly doughnut.