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