The cache_size pragma can get or temporarily set
the maximum size of the in-memory page cache. The
value represents the number of pages in the cache.
Normally, each attached database has an independent
cache.pages
Adjustments made by the cache_size pragma only persist
for the lifetime of the database connection. A common use of
this pragma is to temporarily boost the cache size for I/O
intensive operations. When building a new index on a large
table, raising the cache size (sometimes as much as 100× or even
1,000× the default size) can often result in a considerable
performance increase.
Care must be taken when using extremely large cache sizes, however. If the cache size is so large that the cache grows to exceed the available physical memory, the overall performance is likely to be much lower than simply using a smaller cache. The actual amount of memory used by the cache is determined by the size of the database pages (plus some overhead) and the cache size (in pages).
The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages. The full data cache is not allocated immediately, but grows on demand with the cache size acting as a limiter on that growth. If the cache size is made larger, the limit is simply raised. If the cache size is made smaller, the limit is lowered but the cache is not necessarily immediately flushed to recover the memory.
The built-in page cache requires
some overhead. The exact size of the cache overhead depends on
the platform, but it is in the neighborhood of 100 bytes. The
maximum memory foot-print for the cache can be approximated with
the formula ( page_size + 128 ) *
cache_size.