Bug 2084 - Warn staff about resource hogging scripts
Summary: Warn staff about resource hogging scripts
Alias: None
Product: Koha
Classification: Unclassified
Component: Circulation (show other bugs)
Version: master
Hardware: PC All
: P1 - high major (vote)
Assignee: Galen Charlton
QA Contact:
Depends on: 6773
  Show dependency treegraph
Reported: 2008-05-07 05:38 UTC by Henri-Damien LAURENT
Modified: 2013-12-05 19:53 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 00:46:22 UTC

---- Reported by henridamien@koha-fr.org 2008-05-07 05:38:32 ----

there is a problem with overdue.pl when there are more than 1000 lines in issue table.
the table generated by overdue.pl makes the script hang on and apache ask whether to continue the script.

Two reasons can account for this :
- jquery is not designed for such a huge table
- the table itself is too big

I tested disabling jquery. The page is loaded OK.
So I confirm it is a problem with jquery.

Problem :
jquery is really cool for sorting. But requires the whole table to be loaded into the page to be sharp (if we return only part of the data, the sorting will be done on this part of data only)
But if we return the whole data and it freezes, it is no use.
We can use the "good old way". But is there a better way ?

---- Additional Comments From oleonard@myacpl.org 2008-05-07 06:43:57 ----

I vote for: - the table itself is too big. With over 1400 results in an overdues report, the HTML for the overdues report comes to almost 14000 lines of markup, which enough to cause significant slowdown of the jquery table processing (it's Firefox, not Apache, that asks whether to continue running the script).

But without a quick solution to how to better manage such a large data set, the quick fix is to remove the table sorting. I'm submitting a patch for that. Long term we should consider paging the result set.

---- Additional Comments From oleonard@myacpl.org 2008-05-07 06:45:56 ----

Also: hdl is right, if we return only part of the data, the sorting will
be done only on the portion returned. So it may simply be impractical in all cases to offer a sort.

---- Additional Comments From jmf@liblime.com 2008-05-19 13:45:35 ----

Marking this as fixed ...

---- Additional Comments From rch@liblime.com 2008-05-19 21:43:01 ----

This is not fixed.
One should not be allowed to run this kind of report on a production server during up hours, imo.  At least not without a confirm dialog ( 'This is a processor-intensive report that should not be run on a production server.  Click here to continue').  Even better would be to actually fix the query or indexes so we're not copying to tmp table.

show full processlist after running this report:

|  354 | dbadmin | localhost | koha_db | Query   | 60732 | Sending data         | SELECT 
                GROUP_CONCAT(accountlines.accounttype ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_accounttype,
                GROUP_CONCAT(description ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_description,
                GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_amountoutstanding, 
                GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_date, 
                GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_itemnumber, 
                count(*) as cnt, 
                max(accountlines.date) as maxdate,
                round(sum(amountoutstanding),2) as sum_amount, 
                borrowers.borrowernumber as borrowernumber, 
                borrowers.surname as surname, 
                borrowers.firstname as firstname, 
                borrowers.email as email,
                borrowers.phone as phone,
                accountlines.date as accountdate
                        borrowers, accountlines
                        accountlines.borrowernumber = borrowers.borrowernumber
                AND accountlines.amountoutstanding <> 0 
                AND accountlines.borrowernumber 
                        IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date <= '2008-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25' ) 
                AND accountlines.borrowernumber 
                        NOT IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date < '2007-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25' ) 
 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25'  ORDER BY surname, firstname | 
| 3364 | dbadmin | localhost | koha_db | Sleep   |    17 |                      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

the bad one:  copying to tmp table :: 
| 3365 | dbadmin | localhost | koha_db | Query   |    17 | Copying to tmp table | SELECT date_due,concat(surname,' ', firstname) as borrower, 
  borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber,biblio.biblionumber,borrowers.branchcode 
  FROM issues
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
WHERE 1=1  && date_due<'2008-05-20'  ORDER BY date_due,borrower 

---- Additional Comments From jmf@liblime.com 2008-07-09 07:17:36 ----

I agree with Ryan, we _must_ at least put warnings next to all instances of reports or scripts that have the potential to hog resources. I'm marking this Blocker and I'll work to assemble such a list, and update the templates with warnings (despite the string freeze).

---- Additional Comments From jmf@liblime.com 2008-07-12 08:05:26 ----

Circ autocomplete 
Shelf browser
NoZebra (if you have more than 10K items, it's _extremely_ slow)
We need to check the reports and identify which ones are slow

---- Additional Comments From Andrew.moore@liblime.com 2008-08-06 09:17:36 ----

I sent in two patches:

These add warnings to:
staff interface circulation page concerning circ/overdues.pl
system preferences: OPACShelfBrowser, NoZebra, and CataloguingLog

This does not address the possible need for something to be done about ysearch.pl that may cause a problem for patron name completion in the circulation search box.

This also does not actually make these features work better. It just warns of the shortcomings.

---- Additional Comments From Andrew.moore@liblime.com 2008-08-06 10:44:02 ----

I added over 60,000 patrons with randomish names starting with 'a' into my test database, but couldn't reproduce any kind of slowness or high system load when making Koha recommend completions in the circulation search. So, I'm not sure how to patch ysearch.pl to fix any problem here.

If we see problems with this again, perhaps looking into one of these options may help:

* have ysearch.pl return only 10 results. I think only 10 are shown to the user anyway, so returning more may be unnecessary.
* check the indexes on the patron tables. There does not appear to be indexes on the firstname or surname fields.
* do no autocompletion until more than one character is typed into the field, maybe 3 or so. This can cut down on the number of matching names searched.

Since I don't know where the problem is, I can't recommend one of those or another fix. I'm moving on for now.

---- Additional Comments From jmf@liblime.com 2008-08-08 04:22:44 ----

OK, I'm satisfied with the state of things for 3.0, but I think we need to determine long-term solutions to some of these issues in 3.2 and beyond. Thanks Andy!

---- Additional Comments From Andrew.moore@liblime.com 2008-08-13 09:18:15 ----

This is my highest priority bug, but I don't have any next steps in this. Should it still be a priority 1, major bug? If anything, I think there should be a bug open for each feature that is unacceptably inefficient. If anyone has any recommendations on what needs to be done to what feature, please put them in here. I don't really know if we're done or not.

---- Additional Comments From koha@brinkerhoff.org 2009-01-02 09:51:32 ----


For the web-view use paginated JQuery boxes (which still allow sorting).  Add a 'print' button that disables JQuery on the table for printing.

--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 00:46 UTC  ---

This bug was previously known as _bug_ 2084 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=2084
This bug depended on bug(s) 2162.

Actual time not defined. Setting to 0.0
CC member joe.atzberger@liblime.com does not have an account here

Comment 1 Ian Walls 2011-08-25 02:12:00 UTC
Adding new sub-bug for CircAutocompl
Comment 2 Kyle M Hall 2012-02-08 12:49:27 UTC
Maybe this should be ajax-ified? That way the data is only pull as necessary. I'm sure that should be some jQuery code out there to do so. ExtJS has this functionality out of the box.
Comment 3 Katrin Fischer 2012-04-08 11:53:25 UTC
I checked all named places for warnings:

Warning in place:

OPACShelfBrowser: _____ a shelf browser on item details pages, allowing patrons to see what's near that item on the shelf. Note that this uses up a fairly large amount of resources on your server, and should be avoided if your collection has a large number of items.

NoZebra: ______ the Zebra search engine. It is recommended to use Zebra; the option to not use Zebra is deprecated and is not guaranteed to work. (counting this as warning)

Overdues (on circulation page): Overdues - Warning: This report is very resource intensive on systems with large numbers of overdue items.

CataoguingLog: _____ any changes to bibliographic or item records. Since this occurs whenever a book is checked in or out as well, it is not advisable to turn this on. 

No warning (but fixed?):

CircAutocompl: _____ to guess the patron being entered while typing a patron search on the circulation screen. Only returns the first 10 results at a time. 

So we have a lot of warnings, but are all those still needed?
Comment 4 Liz Rea 2012-05-02 14:17:35 UTC
We can reopen if there are other reports that need warnings. Or simply open a new bug.