From 82f5b527d8cb79e8d1309b42faa461bcea12924d Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 16 Jul 2013 11:53:47 +0200 Subject: [PATCH] Bug 10611: Use mysql_auto_reconnect instead of ping DBD::Mysql provides a mysql_auto_reconnect flag. Using it avoids the time required to do a $dbh->ping(). Benchmarks: use Modern::Perl; use C4::Context; for ( 1 .. 1000 ) { $dbh = C4::Context->dbh; } * without this patch on a local DB: perl t.pl 0,49s user 0,02s system 98% cpu 0,525 total * without this patch on a remote DB: perl t.pl 0,52s user 0,05s system 1% cpu 37,358 total * with this patch on a local DB: perl t.pl 0,46s user 0,04s system 99% cpu 0,509 total * with this patch on a remote DB: perl t.pl 0,49s user 0,02s system 56% cpu 0,892 total Testing the auto reconnect: use Modern::Perl; use C4::Context; my $ping = $dbh->ping; say $ping; $dbh->disconnect; $ping = $dbh->ping; say $ping; Signed-off-by: Bernardo Gonzalez Kriegel Comment: Real improvement. No koha-qa errors prove t/db_dependent/Circulation_issuingrules.t produces no error prove t/db_dependent/Context.t produces no error Test 1) dumped Koha DB, load it on a non-local server 2) run sample script whit and without patch, local and remote use Modern::Perl; use C4::Context; for ( 1 .. 100000 ) { my $dbh = C4::Context->dbh; } Main difference I note is with remote server a) without patch real 0m16.357s user 0m2.592s sys 0m2.132s b) with patch real 0m0.259s user 0m0.240s sys 0m0.012s I think this could be good for DBs placed on remote servers Bug 10611: add a "new" parameter to C4::Context->dbh When dbh->disconnect is called and the mysql_auto_reconnect flag is set, the dbh is not recreated: the old one is used. Adding a new flag, we can now force the C4::Context->dbh method to return a new dbh. Signed-off-by: Bernardo Gonzalez Kriegel Bug 10611: Followup: remove useless calls to dbh->disconnect These 3 calls to disconnect are done at the end of the script, they are useless. Signed-off-by: Bernardo Gonzalez Kriegel Signed-off-by: Paul Poulain Signed-off-by: Galen Charlton (cherry picked from commit 4ae3665ad8afb5d283d989c4622a91690e1c4f8a) Signed-off-by: Fridolin Somers Conflicts: C4/Context.pm --- C4/Context.pm | 16 +++++++++++++--- installer/install.pl | 2 -- misc/cronjobs/j2a.pl | 1 - sms/sms_listen.pl | 1 - t/db_dependent/Context.t | 5 +---- 5 files changed, 14 insertions(+), 11 deletions(-) diff --git a/C4/Context.pm b/C4/Context.pm index 39927d4c3d..b65e645fa9 100644 --- a/C4/Context.pm +++ b/C4/Context.pm @@ -788,11 +788,12 @@ sub _new_Zconn { # Internal helper function (not a method!). This creates a new # database connection from the data given in the current context, and # returns it. +our $db_driver = 'mysql'; sub _new_dbh { ## $context - ## correct name for db_schme + ## correct name for db_scheme my $db_driver; if ($context->config("db_scheme")){ $db_driver=db_scheme2dbi($context->config("db_scheme")); @@ -823,6 +824,10 @@ sub _new_dbh $dbh->{RaiseError} = 0; } + if ( $db_driver eq 'mysql' ) { + $dbh->{mysql_auto_reconnect} = 1; + } + my $tz = $ENV{TZ}; if ( $db_driver eq 'mysql' ) { # Koha 3.0 is utf-8, so force utf8 communication between mySQL and koha, whatever the mysql default config. @@ -857,10 +862,15 @@ possibly C<&set_dbh>. sub dbh { my $self = shift; + my $params = shift; my $sth; - if (defined($context->{"dbh"}) && $context->{"dbh"}->ping()) { - return $context->{"dbh"}; + unless ( $params->{new} ) { + if ( defined $db_driver && $db_driver eq 'mysql' && $context->{"dbh"} ) { + return $context->{"dbh"}; + } elsif ( defined $db_driver && defined($context->{"dbh"}) && $context->{"dbh"}->ping()) { + return $context->{"dbh"}; + } } # No database handle or it died . Create one. diff --git a/installer/install.pl b/installer/install.pl index 39ba0fee85..c9c1361976 100755 --- a/installer/install.pl +++ b/installer/install.pl @@ -382,8 +382,6 @@ elsif ( $step && $step == 3 ) { ); } } - - $dbh->disconnect; } } else { diff --git a/misc/cronjobs/j2a.pl b/misc/cronjobs/j2a.pl index 87edaa4bb7..75d4da18af 100755 --- a/misc/cronjobs/j2a.pl +++ b/misc/cronjobs/j2a.pl @@ -234,4 +234,3 @@ if ( not $noaction) { } $sth->finish( ); } -$dbh->disconnect(); diff --git a/sms/sms_listen.pl b/sms/sms_listen.pl index b36b25da46..a7420d91be 100755 --- a/sms/sms_listen.pl +++ b/sms/sms_listen.pl @@ -108,7 +108,6 @@ foreach my $user(@phones){ } print $reply; } -$dbh->disconnect; sub send_message { my ($mes,$message,$smsid)=@_; diff --git a/t/db_dependent/Context.t b/t/db_dependent/Context.t index d47385488b..d9f6f32da0 100755 --- a/t/db_dependent/Context.t +++ b/t/db_dependent/Context.t @@ -44,9 +44,6 @@ ok($config = $koha->{config}, 'Getting $koha->{config} '); diag "Testing syspref caching."; -my $dbh = C4::Context->dbh; -$dbh->disconnect; - my $module = new Test::MockModule('C4::Context'); $module->mock( '_new_dbh', @@ -58,7 +55,7 @@ $module->mock( ); my $history; -$dbh = C4::Context->dbh; +$dbh = C4::Context->dbh({ new => 1 }); $dbh->{mock_add_resultset} = [ ['value'], ['thing1'] ]; $dbh->{mock_add_resultset} = [ ['value'], ['thing2'] ]; -- 2.39.5