On Fri, 22 Jan 2016 06:24:08 +0000 Simon Slavin <slavins at bigfraud.org> wrote:
> > On 22 Jan 2016, at 4:01am, Rowan Worth <rowanw at dugeo.com> wrote: > > > To a point I agree, but in reality there's a fixed amount of work > > involved with each write transaction. I recently profiled an > > operation involving ~75,000 rows that took ~8.5 minutes to > > complete, and found that 80% of the time was spent waiting for > > COMMIT to complete. Rewriting the code so that all the work > > happened in a single transaction immediately dropped the overall > > time down to ~1.66 minutes. > > Nice stats. > > This is, of course, all about waiting for a rotating disc to be in > the right place. 4500 rotations per minute is 75 rotations per > second or 13ms per rotation. If a transaction which involves a > single row being written involves five write operations then it can > take up to 65ms just waiting for the rotating disc. Call it an > average of 33ms per transaction. Do that 75,000 times and you're > waiting up to 2475 seconds == 40 minutes. All true, but I think you're exaggerating if you're implying that's what the user will see. A call to write(2) doesn't necessarily involve the rotating media; it merely transfers the data from userspace to the kernel buffer cache (using Linux as an example). Even fsync, on consumer-grade disks, may return when the data have been flushed to the device's cache, before they come to rest on the platter. Both buffers ameliorate the effects of latency and track-to-track seek. At the towering height of a SQLite transaction, there's a lot of machinery between the caller and the platter. Given the time involved for the OP's commit (minutes) and the capacity of the raw disk (about 100 MB/s), unless the amount of data is in the 10's of GB, the performance issues are unlikely to lie in the I/O substrate. --jkl
