On 22 January 2016 at 14:24, 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. > I failed to include the number of transactions invoked by the original code, mainly because I hadn't measured it. I've done so now - there was 1,511 transactions to begin with however some of them would have been reads. It will take too long to measure precisely, but I'd estimate there was 1,132 write transactions. 80% of ~8.5 minutes is 410 seconds, so that's 362ms of I/O per transaction vs. 5 seconds of I/O in the single transaction approach. > 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. > Yes, good point. Each of the transactions in my case will end up modifying 3 tables and 12 indices, which puts us up around 15 write ops. Which doesn't quite explain the observed I/O times, but does reveal that the "fixed amount of work" in my original claim is not fixed at all but highly dependent on the database schema! > No wonder solid state storage speeds things up. > It would be interesting to test on an SSD for sure, but I have other bugs to track down :) Cheers, -Rowan
