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
