Bug 16156

Summary: Do not ensure the dbh is connected all the time
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart>
Status: In Discussion --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: abl, dcook, glasklas, jweaver, m.de.rooy, martin.renvoize, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16168
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 15342    
Attachments: Bug 16156: Do not ensure the dbh is connected all the time

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
Comment 9 Jonathan Druart 2022-02-02 13:51:14 UTC
I don't think this is still valid. I am not seeing ensure_connect calls anymore (or at least way less), but I had a bit of trouble to make Nytprof working with the last ktd (?)
Comment 10 Katrin Fischer 2023-09-09 19:00:55 UTC
(In reply to Jonathan Druart from comment #9)
> I don't think this is still valid. I am not seeing ensure_connect calls
> anymore (or at least way less), but I had a bit of trouble to make Nytprof
> working with the last ktd (?)

So... can we close?