[Bug 19599] New: anonymise_issue_history can be very slow on large systems
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Bug ID: 19599 Summary: anonymise_issue_history can be very slow on large systems Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org When running anonymise_issue_history we now call it on a set of patrons from search_patrons_to_anonymise, however, this generates a slow SQL query that can take hours to run. UPDATE `old_issues` SET `old_issues`.`borrowernumber` = '9807' WHERE ( `issue_id` IN ( SELECT * FROM ( SELECT `old_issues`.`issue_id` FROM ( SELECT `me`.`borrowernumber`, `me`.`cardnumber`, `me`.`surname`, `me`.`firstname`, `me`.`title`, `me`.`othernames`, `me`.`initials`, `me`.`streetnumber`, `me`.`streettype`, `me`.`address`, `me`.`address2`, `me`.`city`, `me`.`state`, `me`.`zipcode`, `me`.`country`, `me`.`email`, `me`.`phone`, `me`.`mobile`, `me`.`fax`, `me`.`emailpro`, `me`.`phonepro`, `me`.`B_streetnumber`, `me`.`B_streettype`, `me`.`B_address`, `me`.`B_address2`, `me`.`B_city`, `me`.`B_state`, `me`.`B_zipcode`, `me`.`B_country`, `me`.`B_email`, `me`.`B_phone`, `me`.`dateofbirth`, `me`.`branchcode`, `me`.`categorycode`, `me`.`dateenrolled`, `me`.`dateexpiry`, `me`.`gonenoaddress`, `me`.`lost`, `me`.`debarred`, `me`.`debarredcomment`, `me`.`contactname`, `me`.`contactfirstname`, `me`.`contacttitle`, `me`.`guarantorid`, `me`.`borrowernotes`, `me`.`relationship`, `me`.`sex`, `me`.`password`, `me`.`flags`, `me`.`userid`, `me`.`opacnote`, `me`.`contactnote`, `me`.`sort1`, `me`.`sort2`, `me`.`altcontactfirstname`, `me`.`altcontactsurname`, `me`.`altcontactaddress1`, `me`.`altcontactaddress2`, `me`.`altcontactaddress3`, `me`.`altcontactstate`, `me`.`altcontactzipcode`, `me`.`altcontactcountry`, `me`.`altcontactphone`, `me`.`smsalertnumber`, `me`.`sms_provider_id`, `me`.`privacy`, `me`.`privacy_guarantor_checkouts`, `me`.`checkprevcheckout`, `me`.`updated_on`, `me`.`lastseen`, `me`.`lang`, `me`.`login_attempts`, `me`.`overdrive_auth_token` FROM `borrowers` `me` LEFT JOIN `old_issues` `old_issues` ON `old_issues`.`borrowernumber` = `me`.`borrowernumber` WHERE ( ( `old_issues`.`borrowernumber` IS NOT NULL AND `privacy` <> '0' AND `returndate` < '2017-05-13 00:00:00' ) ) GROUP BY `borrowernumber`) `me` JOIN `old_issues` `old_issues` ON `old_issues`.`borrowernumber` = `me`.`borrowernumber` WHERE ( `returndate` < '2017-05-13 00:00:00' ) ) `_forced_double_subquery` ) ) I think anonymise_issue_history should be a standalone function taking the same params as search_patrons_to_anonymise to eliminate nesting of queries -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |16966 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16966 [Bug 16966] Koha::Patrons - Move GetBorrowersWithIssuesHistoryOlderThan to search_patrons_to_anonymise -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 69052 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=69052&action=edit Bug 19599: Execute 1 query per patron -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 --- Comment #2 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 69054 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=69054&action=edit Bug 19599: Simplify the query -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org --- Comment #3 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Nick, can you compare these 2 approaches please? They are not dependent on each others. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |In Discussion -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |Signed Off -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #69052|0 |1 is obsolete| | Attachment #69054|0 |1 is obsolete| | --- Comment #4 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 69778 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=69778&action=edit Bug 19599: Execute 1 query per patron Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 --- Comment #5 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #3)
Nick, can you compare these 2 approaches please? They are not dependent on each others.
The first approach is much faster on a large dataset. The second can generate a very long query, I think one per patron works best. Signed off and obsoleted second patch -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Julian Maurice <julian.maurice@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |julian.maurice@biblibre.com Status|Signed Off |Failed QA --- Comment #6 from Julian Maurice <julian.maurice@biblibre.com> --- koha-qa complains: FAIL Koha/Patron.pm FAIL pod coverage POD is missing for old_checkouts -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |19830 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19830 [Bug 19830] Add the Koha::Patron->old_checkout method -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #69778|0 |1 is obsolete| | --- Comment #7 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 69860 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=69860&action=edit Bug 19599: Speed anonymise_issue_history up The query generated by anonymise_issue_history is unecessary complex and can be very slow on large DB. With this patch we will execute one query per patron instead. See patch "Bug 19599: Simplify the query" on the bug report for an alternative. Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Signed Off Assignee|koha-bugs@lists.koha-commun |jonathan.druart@bugs.koha-c |ity.org |ommunity.org --- Comment #8 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- I have moved the code creating Koha::Patron->old_checkouts to its own bug report (bug 19830). -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com QA Contact|testopia@bugs.koha-communit |kyle@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #69860|0 |1 is obsolete| | --- Comment #9 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 69999 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=69999&action=edit Bug 19599: Speed anonymise_issue_history up The query generated by anonymise_issue_history is unecessary complex and can be very slow on large DB. With this patch we will execute one query per patron instead. See patch "Bug 19599: Simplify the query" on the bug report for an alternative. Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #10 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Pushed to master for 18.05, thanks to everybody involved! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #11 from Nick Clemens <nick@bywatersolutions.com> --- Awesome work all! Pushed to stable for 17.11.02 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Bug 19599 depends on bug 19830, which changed state. Bug 19830 Summary: Add the Koha::Patron->old_checkout method https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19830 What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Stable |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Status|Pushed to Stable |RESOLVED Resolution|--- |FIXED --- Comment #12 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- Depends on Bug 19830 that is not in 17.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 --- Comment #13 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- (In reply to Fridolin SOMERS from comment #12)
Depends on Bug 19830 that is not in 17.05.x
I've changed my mind and push to 17.05.x for v17.05.08 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19599 --- Comment #14 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- I think not needed for 16.11.x because does not contain Bug 16966 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org