gistfile1.pl

#!/usr/bin/env perl

use strict;
use warnings;
use lib 'extlib/lib/perl5';
use DBIx::Sunny;
use Parallel::ForkManager;
use File::Temp qw/tempfile/;
use Time::HiRes qw/gettimeofday tv_interval/;

my $dbh = DBIx::Sunny->connect(
    "dbi:mysql:test",
    "root","",
);
my $filename;
(undef, $filename) = tempfile(OPEN => 0, UNLINK => 0);

$dbh->query(q!DROP TABLE IF EXISTS idpot!);
$dbh->query(<<'EOF');
CREATE TABLE idpot (
   id bigint unsigned NOT NULL default '0'
)  ENGINE=InnoDB DEFAULT CHARSET=utf8
EOF
$dbh->query(q!INSERT INTO idpot (id) VALUES (1)!);

undef $dbh;
warn "start bench";
my $max_process = 200;
my $max_update_per_process = 50;
my $pm = Parallel::ForkManager->new($max_process);
my $tt = [gettimeofday];

foreach my $i ( 1..$max_process ) {
    $pm->start and next;
    srand();

    my $dbh = DBIx::Sunny->connect(
        "dbi:mysql:test",
        "root","",
    );
    open my $fh, '>>', $filename;

    foreach my $k ( 1..$max_update_per_process ) {
        my $t0 = [gettimeofday];   
        $dbh->select_one('SELECT GET_LOCK("idpot_lock",30)');
        $dbh->query(
            q!UPDATE idpot SET id = LAST_INSERT_ID(id+1)!,
        );
        my $id = $dbh->last_insert_id;
        $dbh->select_one('SELECT RELEASE_LOCK("idpot_lock")');
        my $elapsed = tv_interval ( $t0 );
        print $fh "$elapsed\n";
    }
    
    $pm->finish;
}
$pm->wait_all_children;
my $total_elapsed = tv_interval ( $tt );

open(my $fh, $filename);
my $slow1 = 0;
my $slow5 = 0;
my $slow10 = 0;
my @elapsed;
while (my $l = <$fh>) {
    chomp $l;
    if ( $l > 10 ) {
        $slow10++;
    }
    elsif ( $l > 5 ) {
        $slow5++;
    }
    elsif ( $l > 1 ) {
        $slow1++;
    }
    push @elapsed, $l;
}

$dbh = DBIx::Sunny->connect(
    "dbi:mysql:test",
    "root","",
);
my $id = $dbh->select_one('SELECT id FROM idpot');

my @sort = sort { $a <=> $b } @elapsed;
my $ids = scalar @sort;
print "======= TEST FAILD =========\n" if $ids != $max_update_per_process * $max_process;
print <<EOF;
total elapsed: $total_elapsed
max_process: $max_process
max_update_per_process: $max_update_per_process
gen id: $ids
last id: $id
min: $sort[0]
max: $sort[-1]
slow1: $slow1
slow5: $slow5
slow10: $slow10
EOF

__END__

#参考
http://alpha.mixi.co.jp/2007/10681/

# GET_LOCKあり
start bench at idpot.pl line 27.
total elapsed: 15.015134
max_process: 200
max_update_per_process: 50
gen id: 10000
last id: 10001
min: 0.000822
max: 1.225444
slow1: 1
slow5: 0
slow10: 0

# GET_LOCKなし
start bench at idpot.pl line 27.
total elapsed: 88.909466
max_process: 200
max_update_per_process: 50
gen id: 10000
last id: 10001
min: 0.000542
max: 3.480628
slow1: 7860
slow5: 0
slow10: 0

# MyISAM LOCKなし
start bench at idpot.pl line 27.
total elapsed: 2.089079
max_process: 200
max_update_per_process: 50 
gen id: 10000
last id: 10001
min: 0.000159
max: 0.06707
slow1: 0
slow5: 0
slow10: 0