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] -----------------------------------------------------------------------------
