Kim Gräsman <kim.gras...@gmail.com> wrote: > Hi all, > > In my battles with lots of connections competing over precious cache > memory, I've considered giving some non-critical connections zero > cache using `PRAGMA cache_size=0`. > > Is this a reasonable thing to do? If zero is too extreme, what might a > more moderate small cache size be? 32? 64? 100? > > Some of these connections are only opened to do `PRAGMA quick_check;`, > I'm guessing that operation is not very cache-intensive, assuming it > reads pages sequentially and checks them? > > Thanks for any advice on this, > - Kim
I think it's probably best to give a large cache_size to each connection, and limit the total amount of memory used by SQLite with sqlite3_soft_heap_limit64(). This will effectively limit the global amount of cache used if cache pages comes from the heap. For example, if you give 20MB to each connection (PRAGMA cache_size=....) and limit the global SQLite heap usage to 30 MB with sqlite3_soft_limit() then even if you have say 100 connections, SQLite will not use more than 30 MB of heap. If a connection needs to cache pages, unpinned cache pages from other connections will be discarded in LRU order. Strictly speaking, SQLite could use more than the 30 MB soft limit if it has no other choice to allocate memory (hence a soft limit), but in general that does not happen. That way, inactive connections (connections that are opened, but no queries have been done in a long time) do hog pages in memory. Their pages get discarded after a while. Memory will be best used automatically to cache pages of the most active connections. At least that's my understanding. Dominique _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
