Performance comparison between AutoCommit=on and AutoCommit=off with explicitly commit

autocommit_performance.pl

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;
use Time::HiRes qw(time);

# *** DDL ***
# CREATE TABLE `autocommit_test` (
#     `id` int(10) unsigned NOT NULL,
#     PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sub dbh {
    my $autocommit = shift // 1;
    DBI->connect('dbi:mysql:test', 'root', '', {
        AutoCommit => $autocommit,
        RaiseError => 1,
        PrintError => 0,
    });
}

sub bench(&$) {
    my ($code, $msg) = @_;
    my $start = time;
    $code->();
    my $end = time;
    printf "[$msg] took %f sec.\n", ($end - $start);
}

sub insert_rows {
    my ($autocommit) = @_;
    my $dbh = dbh $autocommit;
    $dbh->do(
        "truncate table autocommit_test"
    );

    my $sql = 'INSERT INTO autocommit_test (id) VALUES (?)';
    my $sh = $dbh->prepare($sql);
    for (1..10000) {
        $sh->execute($_);
        $dbh->commit unless $autocommit;
    }
}

bench { insert_rows 1; } "InnoDB:AutoCommit=1";
bench { insert_rows 0; } "InnoDB:AutoCommit=0";