Triggered event log times out for large-data sites

Bug #1207533 reported by Mike Rylander on 2013-08-01
114
This bug affects 24 people
Affects Status Importance Assigned to Milestone
Evergreen
Medium
Unassigned
2.8
Medium
Unassigned
2.9
Medium
Unassigned

Bug Description

The reason is that we tried to improve usability by combining all of a user's events into one stream. However, there's just no good way, with current data, to make the queries needed to drive the UI fast.

Current proposal for fix:

  1) add 2 new fields to the event definition table: context_user_path and context_ou_path. These will store the path, from the target object, for retrieving the id of the relevant context user and context org -- or, against whom the event occurred, and where (which may be different that the definition owner).

  2) add 2 new fields to the event table to record the event-specific instances of the context ou and context user

  3) have the "atul" and related IDL-views use /those/ values instead of having to look up hold.usr or circ.usr, and hold.pickup_lib or circ.circ_lib, for the various "usr" and "perm_lib" values.

  4) profit

Here's the current query generated:

SELECT "atul".hook, "atul".name, "atul".reactor, "atul".id, "atul".event_def, "atul".add_time, "atul".run_time, "atul".start_time, "atul".update_time, "atul".complete_time, "atul".update_process, "atul".state, "atul".user_data, "atul".template_output, "atul".error_output, "atul".async_output, "atul".target_circ, "atul".target_hold, "atul".perm_lib FROM (
SELECT atevdef.hook,
atevdef.name,
atevdef.reactor,
atev.id,
atev.event_def,
atev.add_time,
atev.run_time,
atev.start_time,
atev.update_time,
atev.complete_time,
atev.update_process,
atev.state,
atev.user_data,
atev.template_output,
atev.error_output,
atev.async_output,
targ_circ.id AS target_circ,
targ_ahr.id AS target_hold,
COALESCE(
targ_circ.circ_lib,
targ_ahr.pickup_lib) AS perm_lib
FROM action_trigger.event atev
JOIN action_trigger.event_definition atevdef ON (atevdef.id = atev.event_def)
JOIN action_trigger.hook ath ON (ath.key = atevdef.hook)
LEFT JOIN action.circulation targ_circ ON (ath.core_type = 'circ' AND targ_circ.id = atev.target)
LEFT JOIN action.hold_request targ_ahr ON (ath.core_type = 'ahr' AND targ_ahr.id = atev.target)
WHERE atev.add_time > NOW() - (SELECT MAX(value) FROM (
SELECT value::INTERVAL FROM actor.org_unit_ancestor_setting('circ.staff.max_visible_event_age', COALESCE(targ_circ.circ_lib, targ_ahr.pickup_lib)) UNION
SELECT '1000 YEARS'::INTERVAL AS value) ous)) AS "atul"
LEFT JOIN action_trigger.hook AS "__core_type_ath" ON ( "__core_type_ath".key = "atul".hook )
LEFT JOIN action.circulation AS "__AUTO_target_circ_desk_renewal_circ" ON ( "__AUTO_target_circ_desk_renewal_circ".id = "atul".target_circ )
LEFT JOIN action.hold_request AS "__AUTO_target_hold_current_shelf_lib_ahr" ON ( "__AUTO_target_hold_current_shelf_lib_ahr".id = "atul".target_hold )
LEFT JOIN action.circulation AS "__target_circ_copy_author_circ" ON ( "__target_circ_copy_author_circ".id = "atul".target_circ )
LEFT JOIN asset.copy AS "__target_circ_copy_author_acp" ON ( "__target_circ_copy_author_acp".id = "__target_circ_copy_author_circ".target_copy )
LEFT JOIN asset.call_number AS "__target_circ_copy_author_acn" ON ( "__target_circ_copy_author_acn".id = "__target_circ_copy_author_acp".call_number )
LEFT JOIN biblio.record_entry AS "__target_circ_copy_author_bre" ON ( "__target_circ_copy_author_bre".id = "__target_circ_copy_author_acn".record )
LEFT JOIN reporter.materialized_simple_record AS "__target_circ_copy_author_rmsr" ON ( "__target_circ_copy_author_rmsr".id = "__target_circ_copy_author_bre".id )
LEFT JOIN action.hold_request AS "__target_hold_copy_title_ahr" ON ( "__target_hold_copy_title_ahr".id = "atul".target_hold )
LEFT JOIN asset.copy AS "__target_hold_copy_title_acp" ON ( "__target_hold_copy_title_acp".id = "__target_hold_copy_title_ahr".current_copy )
LEFT JOIN asset.call_number AS "__target_hold_copy_title_acn" ON ( "__target_hold_copy_title_acn".id = "__target_hold_copy_title_acp".call_number )
LEFT JOIN biblio.record_entry AS "__target_hold_copy_title_bre" ON ( "__target_hold_copy_title_bre".id = "__target_hold_copy_title_acn".record )
LEFT JOIN reporter.materialized_simple_record AS "__target_hold_copy_title_rmsr" ON ( "__target_hold_copy_title_rmsr".id = "__target_hold_copy_title_bre".id )
LEFT JOIN action.circulation AS "__target_circ_patron_barcode_circ" ON ( "__target_circ_patron_barcode_circ".id = "atul".target_circ )
LEFT JOIN actor.usr AS "__target_circ_patron_barcode_au" ON ( "__target_circ_patron_barcode_au".id = "__target_circ_patron_barcode_circ".usr )
LEFT JOIN actor.card AS "__target_circ_patron_barcode_ac" ON ( "__target_circ_patron_barcode_ac".id = "__target_circ_patron_barcode_au".card )
LEFT JOIN action.circulation AS "__target_circ_copy_barcode_circ" ON ( "__target_circ_copy_barcode_circ".id = "atul".target_circ )
LEFT JOIN asset.copy AS "__target_circ_copy_barcode_acp" ON ( "__target_circ_copy_barcode_acp".id = "__target_circ_copy_barcode_circ".target_copy )
LEFT JOIN action.hold_request AS "__target_hold_patron_barcode_ahr" ON ( "__target_hold_patron_barcode_ahr".id = "atul".target_hold )
LEFT JOIN actor.usr AS "__target_hold_patron_barcode_au" ON ( "__target_hold_patron_barcode_au".id = "__target_hold_patron_barcode_ahr".usr )
LEFT JOIN actor.card AS "__target_hold_patron_barcode_ac" ON ( "__target_hold_patron_barcode_ac".id = "__target_hold_patron_barcode_au".card )
WHERE ( ( "__AUTO_target_circ_desk_renewal_circ".usr = '466086' ) OR ( "__AUTO_target_hold_current_shelf_lib_ahr".usr = '466086' ) ) AND ( "__core_type_ath".core_type IN ('circ', 'ahr') ) AND ( ( "atul".state = 'complete' ) AND ( ( "__core_type_ath".core_type = 'circ' ) ) ) AND "atul".perm_lib IN (120) ORDER BY "atul".run_time ASC LIMIT 25 OFFSET 0;

Mike Rylander (mrylander) wrote :

Another possible angle of attack is to split the hold and circ UI components into separate tabs, as in the previous interface. This /might/ be enough to address the speed, but I think materializing more state information up front will pay larger rewards in the long run.

Rogan Hamby (rogan-hamby) wrote :

I don't have any objection to splitting the hold and circ components into separate tabs. It's less convenient but only mildly and if it alleviates the problem it's well worth it. In some scenarios with a lot of data it might help staff with sorting it anyway.

But, it sounds like materializing the information up front is needed whether we do it or not.

Ben Shum (bshum) wrote :

Marking confirmed due to high "affects me" counter

Changed in evergreen:
status: New → Confirmed
importance: Undecided → Medium
tags: added: performance
Srey Seng (sreyseng) wrote :

Just wondering, (even though the "Assigned to" is set as   Unassigned) whether anyone is currently looking into this?

For those with large data sets being affected, is there a workaround to successfully navigate the Triggered event log interface? Or is that interface being avoided in favor of some other way of viewing these logs?

Chris Sharp (chrissharp123) wrote :

Just hitting this bug in PINES. MIke, I believe that your inital proposed approach is the correct one. At this point, even retrieving information directly from the database requires a similarly (sorry, but) convoluted query as the generated one above. Having a hard link between usr and context OU would go far when having to track these.

Not sure if the lack of recent updates here indicates a perceived lack of need or not, but please know that PINES would need this fixed in order to move off of legacy overdue notice generation and onto A/T (which still works well for us).

Thanks for listening!

Chris

Chris Sharp (chrissharp123) wrote :

Updating the series targets - this still affects us in 2.9, FYI.

no longer affects: evergreen/2.5
no longer affects: evergreen/2.4
Scott Thomas (scott-thomas-9) wrote :

This is a big problem for us in Pennsylvania Integrated Library System. We would be in favor of splitting Circ and Holds into separate tabs or any other solutions that addresses the problem.

Chris Sharp (chrissharp123) wrote :

Still an issue in 2.11, FYI.

Michele Morgan (mmorgan) wrote :

Still an issue in 2.12.

Andrea Neiman (aneiman) on 2017-08-25
Changed in evergreen:
milestone: none → 3.next
Andrea Neiman (aneiman) on 2017-08-25
Changed in evergreen:
milestone: 3.next → none
Scott Thomas (scott-thomas-9) wrote :

This is still a big problem for us. We were hoping it would be fixed in Webby.

Jason Boyer (jboyer) wrote :

By all outward appearances this is the bug that won't die but I may have accidentally wounded it. Now that we have a fix for bug 1672775 that removes outdated events and their output, the branch in bug 1778940 speeds up access to event outputs to the point that this interface is sometimes functional again in Evergreen Indiana (7940263 events and 2428921 outputs) though it still takes almost a full minute to return results. There's still no way to page to additional results, though I expect the angular-ization of this interface to take care of that in time.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers