Created
October 21, 2013 11:02
-
-
Save ken39arg/7082070 to your computer and use it in GitHub Desktop.
LOAD DATA INFILE はやっぱり早かった
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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