Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 31551
Status: rejected
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: thedude_rog [...] yahoo.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 4.005
Fixed in: (no value)



Subject: Inefficient fetching when mysql_server_prepare is set
MIME-Version: 1.0
X-Mailer: MIME-tools 5.418 (Entity 5.418)
Charset: utf8
X-RT-Original-Encoding: utf-8
Content-Type: multipart/mixed; boundary="----------=_1197678694-8745-13"
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Length: 2194
Download (untitled) / with headers
text/plain 2.1k
I'm testing DBD::mysql-4.005 and perl 5.8.8 on Mac OS X 10.4.11, dual core Intel 2.33GHz. Fetching the result set data is much slower when using server-prepared statements. It would seem to be an implementation problem as the data send back from the server appears the same. I used wireshark to capture the network traffic for client-prepared versus server- prepared statements. The requests are different as expected but the responses from the server look the same. By running the attached script with DBI::Profile, you can see that the execute method runs faster for server-prepared statements as expected, but the fetchall_arrayref() is MUCH slower. BTW, I also tried bind_column/fetch calls as saw the same slow performance. Here are the numbers on my machine for reference: Server-prepared DBI::Profile: 1.334358s 44.21% (40004 calls) dbi_bug_report.pl @ 2007-12-14 10:45:55 'DESTROY' => 0.000080s / 2 = 0.000040s avg (first 0.000044s, min 0.000036s, max 0.000044s) 'STORE' => 0.000101s 'bind_param' => 0.010730s / 10000 = 0.000001s avg (first 0.000024s, min 0.000000s, max 0.000024s) 'execute' => 1.193439s / 10000 = 0.000119s avg (first 0.000267s, min 0.000095s, max 0.009568s) 'fetchall_arrayref' => 0.123249s / 10000 = 0.000012s avg (first 0.000082s, min 0.000009s, max 0.000086s) 'finish' => 0.006153s / 10000 = 0.000001s avg (first 0.000001s, min 0.000000s, max 0.000025s) 'prepare' => 0.000606s Client_prepared DBI::Profile: 1.455457s 48.23% (40007 calls) dbi_bug_report.pl @ 2007-12-14 10:45:55 'DESTROY' => 0.000084s / 2 = 0.000042s avg (first 0.000025s, min 0.000025s, max 0.000059s) 'STORE' => 0.000043s 'bind_param' => 0.011236s / 10000 = 0.000001s avg (first 0.000002s, min 0.000001s, max 0.000011s) 'do' => 0.013926s / 3 = 0.004642s avg (first 0.010250s, min 0.000825s, max 0.010250s) 'execute' => 1.371118s / 10000 = 0.000137s avg (first 0.000218s, min 0.000121s, max 0.000776s) 'fetchall_arrayref' => 0.053881s / 10000 = 0.000005s avg (first 0.000013s, min 0.000005s, max 0.000094s) 'finish' => 0.005113s / 10000 = 0.000001s avg (first 0.000001s, min 0.000000s, max 0.000020s) 'prepare' => 0.000057s
Subject: dbi_bug_report.pl
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_1197678694-8745-12"
X-Mailer: MIME-tools 5.418 (Entity 5.418)
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: text/x-perl-script; name="dbi_bug_report.pl"
Content-Disposition: inline; filename="dbi_bug_report.pl"
Content-Transfer-Encoding: binary
Content-Length: 1537
Download dbi_bug_report.pl
text/x-perl 1.5k
use strict; use DBI qw(:sql_types); use DBI::Profile; use Time::HiRes qw(gettimeofday tv_interval); my $dbh_prepared = DBI->connect("dbi:mysql:test;mysql_server_prepare=1","user","password"); my $dbh = DBI->connect("dbi:mysql:test","user","password"); $dbh_prepared->{'Profile'} = DBI::Profile->new; $dbh_prepared->{'Profile'} = 4; $dbh->{'Profile'} = DBI::Profile->new; $dbh->{'Profile'} = 4; $dbh->do('DROP TABLE IF EXISTS sample'); $dbh->do('CREATE TABLE sample ( id INTEGER, col2 VARCHAR(16), col3 INTEGER, col4 TIMESTAMP, col5 VARCHAR(16), col6 INTEGER, col7 INTEGER )'); $dbh->do('INSERT INTO sample VALUES (1, \'some text for you\', 67890, CURRENT_TIMESTAMP, \'still more text here\', 1234567, -7623)'); my $rows = 10000; my $sql = 'SELECT *,CURRENT_TIMESTAMP FROM sample WHERE id = ?'; my $sth_prepared = $dbh_prepared->prepare($sql); my $t0_prepared = [gettimeofday]; for (my $i = 0; $i < $rows; $i++) { $sth_prepared->bind_param(1, 1, { 'TYPE' => SQL_INTEGER }); $sth_prepared->execute(); my $result_prepared = $sth_prepared->fetchall_arrayref; $sth_prepared->finish(); } my $elapsed_prepared = tv_interval ($t0_prepared); print "Elapsed server prepared ($rows rows): ${elapsed_prepared}s\n"; my $sth = $dbh->prepare($sql); my $t0 = [gettimeofday]; for (my $i = 0; $i < $rows; $i++) { $sth->bind_param(1, 1, { 'TYPE' => SQL_INTEGER }); $sth->execute(); my $result = $sth->fetchall_arrayref; $sth->finish(); } my $elapsed = tv_interval ($t0); print "Elapsed client prepared ($rows rows): ${elapsed}s\n";
X-Ymail-Osg: zO6WXqcVM1kk7jg6rjudpxi7fLyt1eK52IW8lUCf1z8X.VRadGf._p1Zj8nchtec0OThoyA3E8qOFXtbVRseyymoKYVRQNS1M4mduBA3L4si2z.UXYB.HHRPhTeRhUPOic3tsQfbbIIiLuM-
MIME-Version: 1.0
X-Spam-Status: No, hits=-2.4 required=8.0 tests=ANY_BOUNCE_MESSAGE,BAYES_00,DKIM_SIGNED,DKIM_VERIFIED,DK_POLICY_TESTING,DK_SIGNED,DK_VERIFIED,HTML_MESSAGE,VBOUNCE_MESSAGE
X-Mailer: YahooMailRC/818.31 YahooMailWebService/0.7.158.1
X-Virus-Checked: Checked by ClamAV on 16.mx.develooper.com
Content-Type: multipart/alternative; boundary="0-2058126587-1197679632=:27375"
Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 23B094D8300 for <bug-DBD-mysql [...] rt.cpan.org>; Fri, 14 Dec 2007 19:47:29 -0500 (EST)
Received: (qmail 30028 invoked from network); 15 Dec 2007 00:47:28 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 15 Dec 2007 00:47:28 -0000
Received: from web63005.mail.re1.yahoo.com (HELO web63005.mail.re1.yahoo.com) (69.147.96.216) by 16.mx.develooper.com (qpsmtpd/0.40-dev) with SMTP; Fri, 14 Dec 2007 16:47:15 -0800
Received: (qmail 27877 invoked by uid 60001); 15 Dec 2007 00:47:12 -0000
Received: from [74.95.10.218] by web63005.mail.re1.yahoo.com via HTTP; Fri, 14 Dec 2007 16:47:12 PST
Delivered-To: cpan-bug+DBD-mysql [...] diesel.bestpractical.com
Subject: Re: [rt.cpan.org #31551] AutoReply: Inefficient fetching when mysql_server_prepare is set
Domainkey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=kFUrk/qtSUqDk+IArnxsg1qPAKUPryJ5m/hJpzc/C/FgSo0gnabwYiEMU6miz20UfyFeuL9+LXD5pPa8n4q1Sqnzk/cnC4eoRAOe65pVO0G58pQY7ncj8epWZ/YZnHF0GOkfHTOEhV7BUADQ2RZZHx0P0wPp1tUk+/C37yT5if0=;
Return-Path: <thedude_rog [...] yahoo.com>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: bug-DBD-mysql [...] rt.cpan.org
Date: Fri, 14 Dec 2007 16:47:12 -0800 (PST)
X-Spam-Level: *
Message-Id: <154964.27375.qm [...] web63005.mail.re1.yahoo.com>
To: bug-DBD-mysql [...] rt.cpan.org
From: Roger Hoover <thedude_rog [...] yahoo.com>
RT-Message-ID: <rt-3.6.HEAD-8716-1197679655-606.31551-0-0 [...] rt.cpan.org>
Content-Length: 0
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: us-ascii
X-RT-Original-Encoding: utf-8
Content-Length: 3510
Download (untitled) / with headers
text/plain 3.4k
Sorry, I must have hit submit twice. Show quoted text
----- Original Message ---- From: Bugs in DBD-mysql via RT <bug-DBD-mysql@rt.cpan.org> To: thedude_rog@yahoo.com Sent: Friday, December 14, 2007 4:31:46 PM Subject: [rt.cpan.org #31551] AutoReply: Inefficient fetching when mysql_server_prepare is set Greetings, This message has been automatically generated in response to the creation of a trouble ticket regarding: "Inefficient fetching when mysql_server_prepare is set", a summary of which appears below. There is no need to reply to this message right now. Your ticket has been assigned an ID of [rt.cpan.org #31551]. Your ticket is accessible on the web at: http://rt.cpan.org/Ticket/Display.html?id=31551 Please include the string: [rt.cpan.org #31551] in the subject line of all future correspondence about this issue. To do so, you may reply to this message. Thank you, bug-DBD-mysql@rt.cpan.org ------------------------------------------------------------------------- I'm testing DBD::mysql-4.005 and perl 5.8.8 on Mac OS X 10.4.11, dual core Intel 2.33GHz. Fetching the result set data is much slower when using server-prepared statements. It would seem to be an implementation problem as the data send back from the server appears the same. I used wireshark to capture the network traffic for client-prepared versus server- prepared statements. The requests are different as expected but the responses from the server look the same. By running the attached script with DBI::Profile, you can see that the execute method runs faster for server-prepared statements as expected, but the fetchall_arrayref() is MUCH slower. BTW, I also tried bind_column/fetch calls as saw the same slow performance. Here are the numbers on my machine for reference: Server-prepared DBI::Profile: 1.334358s 44.21% (40004 calls) dbi_bug_report.pl @ 2007-12-14 10:45:55 'DESTROY' => 0.000080s / 2 = 0.000040s avg (first 0.000044s, min 0.000036s, max 0.000044s) 'STORE' => 0.000101s 'bind_param' => 0.010730s / 10000 = 0.000001s avg (first 0.000024s, min 0.000000s, max 0.000024s) 'execute' => 1.193439s / 10000 = 0.000119s avg (first 0.000267s, min 0.000095s, max 0.009568s) 'fetchall_arrayref' => 0.123249s / 10000 = 0.000012s avg (first 0.000082s, min 0.000009s, max 0.000086s) 'finish' => 0.006153s / 10000 = 0.000001s avg (first 0.000001s, min 0.000000s, max 0.000025s) 'prepare' => 0.000606s Client_prepared DBI::Profile: 1.455457s 48.23% (40007 calls) dbi_bug_report.pl @ 2007-12-14 10:45:55 'DESTROY' => 0.000084s / 2 = 0.000042s avg (first 0.000025s, min 0.000025s, max 0.000059s) 'STORE' => 0.000043s 'bind_param' => 0.011236s / 10000 = 0.000001s avg (first 0.000002s, min 0.000001s, max 0.000011s) 'do' => 0.013926s / 3 = 0.004642s avg (first 0.010250s, min 0.000825s, max 0.010250s) 'execute' => 1.371118s / 10000 = 0.000137s avg (first 0.000218s, min 0.000121s, max 0.000776s) 'fetchall_arrayref' => 0.053881s / 10000 = 0.000005s avg (first 0.000013s, min 0.000005s, max 0.000094s) 'finish' => 0.005113s / 10000 = 0.000001s avg (first 0.000001s, min 0.000000s, max 0.000020s) 'prepare' => 0.000057s
____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: iso-8859-1
X-RT-Original-Encoding: utf-8
Content-Length: 4511
MIME-Version: 1.0
X-Mailer: MIME-tools 5.418 (Entity 5.418)
Content-Disposition: inline
Charset: utf8
Message-Id: <rt-3.6.HEAD-8709-1197680163-468.31551-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Original-Encoding: utf-8
Content-Length: 29
This is a duplicate of #31550
MIME-Version: 1.0
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.16-28071-1378987843-259.31551-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 80
Closing because of duplicate of https://rt.cpan.org/Ticket/Display.html?id=31550


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.