The Ram drive is unlikely to work. It will still have the same cache invalidation. You need to get things logically working first. Ram drives are great to help improve performance where seeks are and rotational access requirements dictate.
pompomJuice <[EMAIL PROTECTED]> wrote: 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 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] -----------------------------------------------------------------------------
