Re: [sqlite] SQLite Cache Usage
Hi Pavel, On 7/9/2011 10:16 PM, Pavel Ivanov wrote: 1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated immediately and used or is it allocated as queries come in? Cache is not allocated immediately. It's allocated when SQLite needs to read something from disk (or write a newly created page). SQLite reads it and caches. 2. Does the cache keep filling up till it's full, i.e., each new query that accesses a different page causes the cache to fill up with those pages - or does it reuse pages when new queries happen even if the cache is not full? It fills up until it's full. 3. Does the cache store only results or does it store the table data? Cache stores raw database pages (which can be table data or indexes). So, I'd say the general rule should be the bigger the database the bigger the cache should be for a comfortable work. Thank you for your answers - this gives me enough ideas on managing memory in our systems better! Thanks, Mohit. 8/9/2011 | 1:28 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Cache Usage
> 1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated > immediately and used or is it allocated as queries come in? Cache is not allocated immediately. It's allocated when SQLite needs to read something from disk (or write a newly created page). SQLite reads it and caches. > 2. Does the cache keep filling up till it's full, i.e., each new query that > accesses a different page causes the cache to fill up with those pages - or > does it reuse pages when new queries happen even if the cache is not full? It fills up until it's full. > 3. Does the cache store only results or does it store the table data? Cache stores raw database pages (which can be table data or indexes). So, I'd say the general rule should be the bigger the database the bigger the cache should be for a comfortable work. Pavel On Wed, Sep 7, 2011 at 8:01 AM, Mohit Sindhwani wrote: > Hi All, > > We are right now reviewing our usage of SQLite3 and are seeing how we can > optimize its usage of disk space, run-time memory and its performance for > our applications. So far, we are discovering a lot of new things - and it's > very interesting to see things we may have overlooked at first glance. Most > of our usage of SQLite3 is in read-only databases and we're already using > CEROD. > > We are now starting to see how the performance of our queries is affected by > the usage of the cache. We have the cache designated as 1000 pages with a > page size of 1Kbyte. Increasing the page size helps and increasing the > cache size also helps. > > However, I am trying to understand how the cache is used. These are some of > the questions I have: > 1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated > immediately and used or is it allocated as queries come in? > 2. Does the cache keep filling up till it's full, i.e., each new query that > accesses a different page causes the cache to fill up with those pages - or > does it reuse pages when new queries happen even if the cache is not full? > 3. Does the cache store only results or does it store the table data? > > One of the reasons to ask is that currently, we use a few different > databases and each has the same cache setting. We are wondering if we > should tune the cache settings in each of the databases. > > Thanks for any help. > > Best Regards, > Mohit. > 7/9/2011 | 8:01 PM. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Cache Usage
Hi All, We are right now reviewing our usage of SQLite3 and are seeing how we can optimize its usage of disk space, run-time memory and its performance for our applications. So far, we are discovering a lot of new things - and it's very interesting to see things we may have overlooked at first glance. Most of our usage of SQLite3 is in read-only databases and we're already using CEROD. We are now starting to see how the performance of our queries is affected by the usage of the cache. We have the cache designated as 1000 pages with a page size of 1Kbyte. Increasing the page size helps and increasing the cache size also helps. However, I am trying to understand how the cache is used. These are some of the questions I have: 1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated immediately and used or is it allocated as queries come in? 2. Does the cache keep filling up till it's full, i.e., each new query that accesses a different page causes the cache to fill up with those pages - or does it reuse pages when new queries happen even if the cache is not full? 3. Does the cache store only results or does it store the table data? One of the reasons to ask is that currently, we use a few different databases and each has the same cache setting. We are wondering if we should tune the cache settings in each of the databases. Thanks for any help. Best Regards, Mohit. 7/9/2011 | 8:01 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
