Bug 16156 - Do not ensure the dbh is connected all the time
Summary: Do not ensure the dbh is connected all the time
Status: In Discussion
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 19819 (view as bug list)
Depends on:
Blocks: 15342
  Show dependency treegraph
 
Reported: 2016-03-29 12:59 UTC by Jonathan Druart
Modified: 2020-01-17 08:22 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 16156: Do not ensure the dbh is connected all the time (3.34 KB, patch)
2016-03-29 13:14 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Druart 2016-03-29 12:59:05 UTC
I have profiled `/catalogue/search.pl?q=d` (20 results, xslt) and found than at least 600ms are spent in DBIx::Class::Storage::DBI::ensure_connected, which is called 5749 times (almost 1 per query).
Comment 1 Jonathan Druart 2016-03-29 13:14:07 UTC
Created attachment 49632 [details] [review]
Bug 16156: Do not ensure the dbh is connected all the time

I have profiled `/catalogue/search.pl?q=d` (20 results, xslt) and found
that at least 600ms are spent in
DBIx::Class::Storage::DBI::ensure_connected, which is called 5749 times
(almost 1 per query).
Ideally we could work on the basis that the connection is only checked
when a thread is started. This is what does this patch.
Unfortunately this patch will revive timeout issues with long run
process (SIP server for instance).

Before this patch:
901398 statements and 346866 subroutine calls in 406 source files and 55
string evals
spent 637ms (33.0+604) within
DBIx::Class::Storage::DBI::ensure_connected which was called 5749 times,
avg 111µs/call

After this patch:
817161 statements and 265940 subroutine calls in 406 source files and 55
string evals
spent 49.8ms (6.58+43.2) within
DBIx::Class::Storage::DBI::ensure_connected which was called 343 times,
avg 145µs/call
Comment 2 Jonathan Druart 2016-03-29 13:17:33 UTC
We could also imagine a 'ensure_connected' flag, not to use the $context->{dbh} even if exists, for long run processes.
Comment 3 Jonathan Druart 2016-05-02 19:47:40 UTC
And? Is this stupid? Any thoughts?
Comment 4 Jonathan Druart 2019-11-27 10:37:15 UTC
*** Bug 19819 has been marked as a duplicate of this bug. ***
Comment 5 David Cook 2019-11-28 00:26:31 UTC
I don't have time to think about this right now, but I'd do things with persistent/long running processes as the target?

I figure this is one of those things that is best handled at the start of a request but I don't know.

I ran into this problem with Wildfly/JBoss and this is how I dealt with it: https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/6.4/html/administration_and_configuration_guide/sect-database_connection_validation
Comment 6 Jonathan Druart 2019-11-28 08:42:40 UTC
(In reply to David Cook from comment #5)
> I don't have time to think about this right now, but I'd do things with
> persistent/long running processes as the target?

To answer I am going to quote content from previous comments:

(In reply to Jonathan Druart from comment #1)
> Created attachment 49632 [details] [review] [review]
> Unfortunately this patch will revive timeout issues with long run
> process (SIP server for instance).

(In reply to Jonathan Druart from comment #2)
> We could also imagine a 'ensure_connected' flag, not to use the
> $context->{dbh} even if exists, for long run processes.


It is in discussion. So first "Do you think it makes sense?" then either we abandon or we go further and I continue to implement the idea.

As you can see I have been waiting for "discussion" for more than 3 years now ;)
Comment 7 Marcel de Rooy 2020-01-17 08:22:26 UTC
As we want to minimize ensure_connected, we probably should not add a connected test to Koha::Database like bug 20089#c2 suggested?
Comment 8 Marcel de Rooy 2020-01-17 08:22:55 UTC
(In reply to Marcel de Rooy from comment #7)
> As we want to minimize ensure_connected, we probably should not add a
> connected test to Koha::Database like bug 20089#c2 suggested?

c2 => c1