On Sat, Feb 12, 2011 at 7:11 PM, Jim Wilcoxson <pri...@gmail.com> wrote:
> > > Unless I'm missing something, SQLite has to update the first page of the > database on every commit, to update the change counter. Assuming you are > using rotating media, that record can only be updated 120 times per second, > maximum, on a 7200RPM drive. > > Hmm, I think there's something wrong with this theory of a single flush per round Yesterday I posted here results about from tests that ended with something like 50 commits per second in case of single flush (journal_mode=off). I decided to run a simple program that writes a byte to a file and does windows FlushFileBuffers. It reported 53 writes per second. This was expected results if this theory is right. But when I connected an external drive (WD1200E1MS, bus-powered, it seems it is even 5400 rpm), this number jumped to 253. I though "Ok, something, maybe os maybe drive tried to fool me, let's do this other way". I did the following: - Checked the removable drive "Quick removaI" is on so no write cache on os side - opened the same test db on this drive, journal_mode=off for best scenario - Executed 10,000 updates to a single record made as separated transactions ( UPDATE TestTable SET Id=Id+1 ) - When the program reported ok, I quickly unplugged the usb cord. The device is bus-powered so had no power to complete any postponed operation. The total time was about 50 seconds and to do the real quick unplug I prepared my hands and the delay was no more than 200 ms (subjectively). - Replug the drive again and opened the db in the tool to see was there any corruption or other lost of data. So the final results: - the db was always ok and contains the correct value (id=10001 for initial 1). - the speed was about 227 commits per second so very close to my system flush test (253) So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if it's 7200 (manufacturers sometimes upgrade drives inside portable hd without prior notice), it's still twice as much as 7200/60=120. Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
