Hello, I am trying to make my DBI application faster. According to
DBD::mysql documentation I should be able to prepare a query that I'm
going to use in a loop so that when I execute it inside the loop, it
goes faster. In order to maximize this performance improvement, you
need to add "mysql_server_prepare=1" to the connection string.
The problem is that when I do this I get a "Bus Error". Is it just me
or prepared statements are broken under Mac OS X?
Now the details:
All test are passing with mysql_server_prepare=1:
$ cd ~/src/DBD-mysql-3.0007
$ export MYSQL_SERVER_PREPARE=1
$ export SLOW_TESTS=1
$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.............ok
t/10dsnlist..........ok
t/20createdrop.......ok
t/30insertfetch......ok
t/35limit............ok
t/35prepare..........ok
t/40bindparam........ok
t/40bindparam2.......ok
t/40blobs............ok
t/40listfields.......ok
t/40nulls............ok
t/40numrows..........ok
t/41bindparam........ok
t/41blobs_prepare....ok
t/42bindparam........ok
t/50chopblanks.......ok
t/50commit...........ok
t/60leaks............ok
t/dbdadmin...........ok
t/insertid...........ok
t/param_values.......ok
t/prepare_noerror....skipped
all skipped: test - will only run with MySQL 5.1 and above.
t/texecute...........ok
All tests successful, 1 test skipped.
Files=23, Tests=443, 58 wallclock secs (14.84 cusr + 9.45 csys =
24.29 CPU)
All test are also passing without mysql_server_prepare=1:
$ unset MYSQL_SERVER_PREPARE
$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.............ok
t/10dsnlist..........ok
t/20createdrop.......ok
t/30insertfetch......ok
t/35limit............ok
t/35prepare..........ok
t/40bindparam........ok
t/40bindparam2.......ok
t/40blobs............ok
t/40listfields.......ok
t/40nulls............ok
t/40numrows..........ok
t/41bindparam........ok
t/41blobs_prepare....ok
t/42bindparam........ok
t/50chopblanks.......ok
t/50commit...........ok
t/60leaks............ok
t/dbdadmin...........ok
t/insertid...........ok
t/param_values.......ok
t/prepare_noerror....skipped
all skipped: test - will only run with MySQL 5.1 and above.
t/texecute...........ok
All tests successful, 1 test skipped.
Files=23, Tests=449, 46 wallclock secs (12.13 cusr + 7.90 csys =
20.03 CPU)
Now the details of my program. It is a small script with a single
prepare outside a loop:
#!/usr/bin/perl
use GD;
use DBI; # Load Database Interface Module
use Data::Dumper;
# Connect to database
my $dbh = DBI->connect
('DBI:mysql:database=database_name;host=localhost:mysql_server_prepare=1
',
'myUser', 'myPassword', {RaiseError => 1})
or die "$0: Can not connect to database: " . $DBI::errstr;
# create a new image
$im = new GD::Image(6490,4000);
# allocate color black
$black = $im->colorAllocate(0,0,0);
# The maximum id value in table is 25958999
my $sth = $dbh->prepare("SELECT id FROM myTable WHERE id=?");
my $id = 1;
foreach $x (0..6489) {
print "x=$x/6490 id=$id\n";
foreach $y (0..3999){
$sth->execute($id);
if ($sth->fetchrow_array) {
$im->setPixel($x,$y,$black);
}
$id = $id + 1;
}
open(OUT,">/Users/gdelfino/id.png") or die "can not write output
file";
binmode OUT;
print OUT $im->png;
close(OUT);
# I wish there were a "tail -f" equivalent for images
}
When I run it I get the following error:
$ perl -w id_photo.pl
x=0/6490 id=1
Bus error
If I replace mysql_server_prepare=1 with mysql_server_prepare=0, the
program runs just fine on my Power Mac G5 (Dual 2.5 GHz).
My DBD::mysql version is:
$ perldoc -m DBD::mysql | grep '$VERSION ='
$VERSION = '3.0007';
My perl version is:
$ perl -v
This is perl, v5.8.6 built for darwin-thread-multi-2level
The version of mysql that I am using is:
$ mysql --version
mysql Ver 14.7 Distrib 4.1.21, for apple-darwin8.6.0 (powerpc) using
readline 4.3
With DBD::mysql 3.0003 or 3.0004 (I don't remember) I was able to run
this program with mysql_server_prepare=1 and I was amazed with the
improvement in performance (this was on my PowerBook G4).
Any advice on how to solve this?
Regards,
Gustavo Delfino
Caracas, Venezuela
