On Sun, Feb 13, 2011 at 1:15 AM, Max Vlasov <max.vla...@gmail.com> wrote:
> 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. > If we go with the theory that a single record/sector can only be written once per revolution, that means writes can occur 90 times per second, or about every 11ms on your 5400 rpm drive. So for this test to show corruption, you'd have to unplug the drive within 11ms of the last commit, which I think is practically impossible. My hypothesis to explain the behavior you are seeing is that it takes 4ms (1/253) for your OS to flush its buffers to the hard drive cache, issue the sync to the drive, and the hard drive to acknowledge the sync. When it is convenient, the drive will write this data to the disk. The sync command may make this more urgent than usual, which would explain why I/O slows down if sync is used. Or, the drive could simply have a sync timer: the first sync command starts the timer, and when 4ms have passed, the drive dumps its cache to the platters. Jim _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
