AArrgh. That is the one thing that I wont be able to do. It would require a complete system redesign. I can adapt my program easy but now to get it to work in the greater scheme of things would be a nightmare.
My current efforts are being focussed into making a ram drive and putting the file in there. I hope it works. ken-33 wrote: > > Can you consolidate your multiple binaries to a Single Binary? > Then Use threading and sqlite's shared caching to perform the Lookups and > updates. > That way the cache wouldn't get invalidated??? > > Someone else here correct me if this is a bad idea!!! > > > > pompomJuice <[EMAIL PROTECTED]> wrote: > I suspected something like this, as it makes sense. > > I have multiple binaries/different connections ( and I cannot make them > share a connection ) using this one lookup table and depending on which > connection checks first, it will update the table. > > My question is then, if any one connection makes any change to the > database > ( not neccesarily to the huge lookup table ) will all the other > connections > invalidate their entire cache? Or is it per table/btree that the cache is > dropped? > > Thanks for that reponse. Already I can move ahead now with better knowlege > of how the caching works. > > Regards. > > > > Dan Kennedy-4 wrote: >> >> On Tue, 2007-06-19 at 01:06 -0700, pompomJuice wrote: >>> 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. >> >> Probably right. >> >>> The update >>> is not very big, say 200000 rows in a table that has about 45 million >>> rows. >>> >>> What exactly is happening here? >> >> Are you using 3.3.17? And is it an external process (or at least a >> different connection doing) doing the update? >> >> If so, the update is modifying the pager change-counter, invalidating >> the pager cache held by the lookup application. The lookup app has >> to start loading pages from the disk again, instead of just reading >> it's cache. >> >> The only way around this performance hit is to do the UPDATE through >> the lookup app, using the same database connection. >> >> Dan. >> >>> Regards. >> >> >> ----------------------------------------------------------------------------- >> To unsubscribe, send email to [EMAIL PROTECTED] >> ----------------------------------------------------------------------------- >> >> >> > > -- > View this message in context: > http://www.nabble.com/Cache-invalidation-after-insert-statements.-tf3944908.html#a11192121 > Sent from the SQLite mailing list archive at Nabble.com. > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > > -- View this message in context: http://www.nabble.com/Cache-invalidation-after-insert-statements.-tf3944908.html#a11208520 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
