Thanks Dave, I guess I'll give the shared cache a try... actually, when I started I switched it on but removed afterward because I was not sure if it is a kind of sqlite standard usage.
I'll also look into the heap_limit function. Marcus > Hi Marcus > > My understanding of SQLite caching is that in your scenario, 40MB is the > *maximum* cache memory that will be used per connection - this memory is > not immediately pre-allocated when you open a new connection. Using the > default memory allocator (memsys1) SQLite will allocate from the heap as > and when it needs to, and will simply stop caching if it cannot allocate > any more memory. Using sqlite3_soft_heap_limit() or alternative memory > allocators (memsys3 or memsys5) you can place an upper-bound on the > total memory that SQLite consumes. Have a look at > http://www.sqlite.org/malloc.html for more details. > > Personally I can vouch for shared-cache mode, I've found it to be > essential for multiple DB connections on an embedded system with limited > memory. > > Cheers, > Dave. > > > -----Original Message----- > From: Marcus Grimm [mailto:mgr...@medcom-online.de] > Sent: 23 February 2009 06:07 > To: General Discussion of SQLite Database > Subject: [sqlite] sqlite cache question > > Hi all, > > I tried to scan the list and doc pages to understand better > the sqlite's concept of caching but I'm still not really > sure how to change the cache parameters to get the best > performance: > > Assuming I change the cache by pages size = 4096 and > cache_size = 10000 sqlite will then use appx. 40 MB cache memory > per db connection. > > I've learned in a recent reply here that sqlite will reload the cache > for every db connection when one of the connection did a write > to the db to avoid that the other connections have wrong cache data. > Doesn't this mean that increasing the cache size will actually slow > down the database read/write operation in a multi threaded / multi > connection > application, because now in the above example each connection will > reload 40MB prior going ahead ? > > Also: I think it is dangerous to add to much cache in a multi/connection > application since each connection will allocate the cache and by having > e.g. 20 > connections running it will allready allocate 1GB RAM, am I right ? > I've seen here much higher cache size recommendation (like 400MB) and > I'm wondering > how that can work in a multi connection application when I assume a > maximum > of 2GB RAM (on a Win-32 system). > > I know that there is the shared cache mode but I got the impression that > this option is not very often used or recommended and I skip it for now. > > -- > In the man pages for the cache pragma I read: > "If you are doing UPDATEs or DELETEs that change many rows > of a database and you do not mind if SQLite uses more memory, you can > increase... " > > So actually the cache is only useful for sql statements that alter the > database and not for simple select statements ? > I guess the man page is not really correct, right ? > Otherwise I don't see why to change the cache size because write > operation do wait for disk IO anyhow and I think that will be overruled > by any cache issue. > -- > > I'm not saying that I have a performance problem, sqlite is doing great! > -- I just > want to setup my application and the default cache sizes in the best way > to fit in > also when the db file growths in the future. > > Thank you > > Marcus Grimm > > > _______________________________________________ > 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
