Thank for the clarification, Simon! I have a question based on my observation. According to your numbers for a 5400 RPM disk, one write op should take about 11 ms. However, it often takes only about 1 ms on HostGator drives on its shared hosting servers. Are there drives that are SO much faster than 5400 RPM ones?
On Mon, Mar 3, 2014 at 5:57 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 Mar 2014, at 9:11pm, romtek <rom...@gmail.com> wrote: > > > Simon, does a real disk have to be a rotating hard disk? Is there > problem > > with SSDs as far as SQLite is concerned? > > SSDs aren't a problem, and SQLite works fine with them, but they change > the timings associated with SQLite a great deal. Simplified explanation > follows. > > With rotating hard disks each read or write operation has to wait for the > disk to rotate to the right place. And if you have your files > defragmented, all of a SQLite database is clumped up together on disk right > next to the journal file for that database. An END TRANSACTION command can > involve six (more ? anyone ?) accesses of database or journal file, and if > all the parts of disk you're writing to are near one-another that can > involve six rotations of the hard disk. So ... > > Hard disk rotates at 5400rpm = 90rotations/s . > 6 reads or writes to disk, assuming no skipped rotations, takes 66ms = > 1/15th of a second . > A loop doing 100 INSERTs can take over six seconds ! > > This means that in real life SQLite can be faster if you do /not/ > defragment your drive. Because you don't have to wait for a full rotation > every time. > > SSDs, of course, don't have to wait for anything physical. The > improvement in speed gained by using transactions does not work the same > way: there are still fewer operations, so it should still be faster, but > not by as much. My original statement about so much of the time being > spent on the END TRANSACTION no longer holds. That's why I asked. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
