Re: why autocommit mode is slow?

On 04/08/2011 04:59 AM, Szymon Guz wrote:
> Hi,
> this is maybe a stupid question, but I don't know how to explain to my
> coworkers why performing many inserts in autocommit mode is so much
> slower than making all of them in one transaction.
>
> Why is that so slow?

Unless you have synchronous_commit disabled and/or a commit_delay, each
commit requires synchronization of all outstanding writes to the disk
before the commit can return success. That stops the OS intelligently
reordering and batching the writes of multiple statements for better
disk I/O efficiency. It also adds pauses before each statement can
return, effectively adding latency between statements.

The trade-off you get is that after each statement, you know for certain
that the results of that statement are on-disk and safe. That's not the
case when you do them all in one transaction, or with synchronous_commit
off.

BTW, the other area to look at with this is query batching. If the
client isn't on the same host as the server or at least on a very low
latency network, it can be much more efficient to batch queries together
to minimize the number of network round trips required.

--
Craig Ringer