Skip to content

Instantly share code, notes, and snippets.

@ken39arg
Created October 21, 2013 11:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ken39arg/7082070 to your computer and use it in GitHub Desktop.
Save ken39arg/7082070 to your computer and use it in GitHub Desktop.
LOAD DATA INFILE はやっぱり早かった
use common::sense;
use Benchmark qw/timethese cmpthese/;
use DBI;
use Test::mysqld;
use Time::Piece::MySQL;
use File::Temp qw/tempfile/;
my $mysqld = Test::mysqld->new(
my_cnf => { 'skip-networking' => '' }
);
my $dsn = $mysqld->dsn;
for my $tablename ( qw/ta tb tc/ ) {
my $dbh = DBI->connect( $dsn );
$dbh->do(qq{
create table $tablename (
id int auto_increment primary key,
name varchar(64) not null,
num1 int not null,
created datetime,
flg tinyint not null default 1,
nullable varchar(128),
key name(name),
key num1(num1)
);
});
}
my $rows = 30000;
my $result = timethese( 10, {
simple => sub {
my $dbh = DBI->connect( $dsn );
my ($before_count) = $dbh->selectrow_array( "select count(*) from ta" );
$dbh->begin_work;
my $sth = $dbh->prepare("insert into ta values (?,?,?,?,?,?)");
for ( 1..$rows ) {
$sth->execute( 0, "name$_", $_ % 10 + 1, localtime->mysql_datetime, 1, undef );
}
$dbh->commit;
my ($after_count) = $dbh->selectrow_array( "select count(*) from ta" );
warn "inport failed" unless $before_count + $rows == $after_count;
},
bulk => sub {
my $dbh = DBI->connect( $dsn );
my ($before_count) = $dbh->selectrow_array( "select count(*) from tb" );
my $bulk_rows = 1000;
my @insert_rows;
for ( 1..$rows ) {
push @insert_rows, [ 0, "name$_", $_ % 10 + 1, localtime->mysql_datetime, 1, undef ];
next if ( @insert_rows < $bulk_rows );
my ( $stmt, @bind ) = buile_bulk_insert_sql( 'tb', \@insert_rows );
my $sth = $dbh->prepare_cached( $stmt );
$sth->execute( @bind );
@insert_rows = ();
}
if ( @insert_rows ) {
my ( $stmt, @bind ) = buile_bulk_insert_sql( 'tb', \@insert_rows );
my $sth = $dbh->prepare_cached( $stmt );
$sth->execute( @bind );
}
my ($after_count) = $dbh->selectrow_array( "select count(*) from tb" );
warn "inport failed" unless $before_count + $rows == $after_count;
},
load_data_infile => sub {
my $dbh = DBI->connect( $dsn );
my ($before_count) = $dbh->selectrow_array( "select count(*) from tc" );
my ($fh, $file) = tempfile;
for ( 1..$rows ) {
print $fh join("\t", (0, "name$_", $_ % 10 + 1, localtime->mysql_datetime, 1, '\N' ) ). "\n";
}
close $fh;
$dbh->do( qq{LOAD DATA INFILE '$file' INTO TABLE tc} );
my ($after_count) = $dbh->selectrow_array( "select count(*) from tc" );
warn "inport failed" unless $before_count + $rows == $after_count;
},
} );
cmpthese $result;
sub buile_bulk_insert_sql {
my ($table_name, $rows) = @_;
my $value = join( ',', map { '?' } (1..6) );
my $sql = "INSERT INTO $table_name VALUES "
. join( ',', map { "($value)" } (1..scalar(@$rows)) );
return ( $sql, map { @$_ } @$rows );
}
__END__
--
$rows = 500000
timing = 3
Benchmark: timing 3 iterations of bulk, load_data_infile, simple...
bulk: 222 wallclock secs (111.08 usr + 0.66 sys = 111.74 CPU) @ 0.03/s (n=3)
(warning: too few iterations for a reliable count)
load_data_infile: 171 wallclock secs (97.82 usr + 0.80 sys = 98.62 CPU) @ 0.03/s (n=3)
(warning: too few iterations for a reliable count)
simple: 397 wallclock secs (137.09 usr + 51.37 sys = 188.46 CPU) @ 0.02/s (n=3)
(warning: too few iterations for a reliable count)
s/iter simple bulk load_data_infile
simple 62.8 -- -41% -48%
bulk 37.2 69% -- -12%
load_data_infile 32.9 91% 13% --
--
$rows = 30000
timing = 10
Benchmark: timing 10 iterations of bulk, load_data_infile, simple...
bulk: 18 wallclock secs (10.47 usr + 0.06 sys = 10.53 CPU) @ 0.95/s (n=10)
load_data_infile: 18 wallclock secs ( 9.88 usr + 0.08 sys = 9.96 CPU) @ 1.00/s (n=10)
simple: 53 wallclock secs (19.36 usr + 7.13 sys = 26.49 CPU) @ 0.38/s (n=10)
s/iter simple bulk load_data_infile
simple 2.65 -- -60% -62%
bulk 1.05 152% -- -5%
load_data_infile 0.996 166% 6% --
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment