Following a few other discussions, I had the feeling that sqlite should
benefit from a cache which discards cached pages in a least frequently
used order.
It generally means, index pages, often used data pages, etc, should be
preferred (meaning kept in memory) compared to some infrequent used pages.
This helps me where I have big files which are mostly written once, but
I also have some small tables with summaries; these should be better
cached, the same for indices.
I first implemented a custom cache in Delphi (Pascal) using some high
level (generic) containers (hash for keys, heap for usage data); there
is a significant overhead due to the classes I used and maybe also
compiler differences.
My own usage shows some visible improvements, therefore I took some time
to implement it directly in core (pcache1).
I would like to ask anyone who sees this interesting to try and give
some feedback about benefits (if at all :)).
Feedback / results / benchmarks are welcome.
If it is useful, I would be happy to contribute it.
The diff is done against 3.7.15.2 ; I'm not sure if it makes it to the
list, so here is the diff text:
http://pastebin.com/RrzqWjWv
Technical details:
- each page has a fetch counter
- the LRU list is changed to a heap, arranged according to this counter
- when discarding pages, the page with the minimum fetch counter is selected
Apart from the heap operations, the other changes are quite straightforward.
I run some tests to check for errors, maybe someone can check if the
initialization is done in proper place (especially for shared cache group).
There is an important catch; the fetch counter overflow. I don't have
yet a definitive idea how/when to limit or to correct it.
So this problem is currently postponed until the tests show actual
benefit / interest.
Regards,
Gabriel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
