----- Original Message ---- > From: Christian Smith <[EMAIL PROTECTED]>
> Is this a likely usage scenario? Will your application regularly > umount/mount the filesystem between transactions? While sounding > facetious, I'm not trying to. Your otherwise excellent example is let down > by a probably unreasonable usage pattern. My example was just that, a test case trying to explain why my actual app goes to a crowl. The umount/mount is there to insure that I am not benchmarking the OS caching abilities. What this does is that it gives a better idea on the performance of the caching of sqlite. > The pages containing nodes closer to the root of the affected btrees will > be cached pretty quickly, but the random access pattern and constant > random updates will make for very non-localised updates to the DB file, > which would require lots of seeks on sync. > You might be well advised to look at how long it takes to do the updates, > then the COMMIT seperately. I'd hazard a guess that you'll be spending the > majority of the time in the COMMIT commands. Something like: All right, so I added the intermediate timing to see where the time is spend. My results show that on the typical 7 seconds operation (after the numbers stabilise) 2 seconds are spend between the BEGIN / END and 5 seconds to perform the COMMIT. The first transactions show things like: When I saw this, what I tried later on was to play around with PRAGMA synchronous =OFF and PRAGMA synchronous = NORMAL While synchronous = NORMAL didn't show any real improvement on the timings compared to FULL full async mode was dramatically faster: the random test that was taking 600+ seconds to execute now runs in 172 seconds (and running the global sync after the test only took a couple seconds, meaning most data was already written on disk). I looked at timings, and now the time is basically spend between the BEGIN/END, the COMMIT is very fast. I was very excited about this result, so I tried async access on my actual app (that holds 8M+ records), but I was disapointed to see that async actually even slow things down?! I also tried to mount the filesystem (reiser) in "journal" mode (rw,noatime,data=journal), I had it in "writeback", and things get slower with this setting. So what I tried next was to simply run my app after doing a cat generaldb.db > /dev/null (this takes about 15 seconds on my system, it's a 600Megs file) This has the effect of preloading the db file in memory in the OS caches. After I do this, the app becomes very fast (as in 4 times faster). note: I noticed that even if I specify some ridiculous number like PRAGMA cache_size = 450000 (that should be enough to cache the whole db by the way) for the cache, the memory footprint of my app stays pretty low, as if it was not caching much!? So at this point, I think the caching/polling subsytem seems to be the problem. Oh also, a way to improve performance: reads can be reordered, so if sqlite was doing async reads of several places in the db file at the same time, the OS/drive would reorder things the same way they are reordered when writing for a good boost in performance. Nicolas
