Re: [sqlite] sqlite cache question
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 = 1 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
Re: [sqlite] sqlite cache question
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 = 1 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] 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 = 1 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] SQLite cache question.
Hello there. I need some insight into how SQLite's caching works. I have a database that is quite large (5Gb) sitting on a production server that's IO is severely taxed. This causes my SQLite db to perform very poorly. Most of the time my application just sits there and uses about 10% of a CPU where it would use a 100% on test systems with idle IO. Effectively what the application does is constantly doing lookups as fast as it can. To counteract this I increased the page size to 8192 (Unix server with advfs having 8K block sizes) and increased the MAX_PAGES value in sqliteInt.h to 512000. This worked. My application starts at low memory usage and as it gradually gains more memory. As it gains more memory it uses more CPU and reaches a point where it finally uses 100% CPU and 5Gb of ram. Every now and then the lookup table is udpated. As soon as the application does this however the performance goes back to a crawl and slowly builds up again as described in the previous paragraph. The memory usage stays at 5Gb. All that I can think of is that the update invalidates the cache. The update is not very big, say 20 rows in a table that has about 45 million rows. What exactly is happening here? Regards. -- View this message in context: http://www.nabble.com/SQLite-cache-question.-tf3944886.html#a11190296 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
