Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1101817

Summary: [SLOW QUERY] Main system activity page
Product: [Retired] Beaker Reporter: Raymond Mancy <rmancy>
Component: web UIAssignee: Dan Callaghan <dcallagh>
Status: CLOSED CURRENTRELEASE QA Contact: tools-bugs <tools-bugs>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 0.15CC: aigao, asaha, dcallagh, dowang
Target Milestone: 20.0Keywords: Patch
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2015-04-20 02:22:50 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Raymond Mancy 2014-05-28 02:58:04 UTC 
Count         : 11  (0.11%)
Time          : 906 s total, 82.363636 s avg, 41 s to 128 s max  (0.52%)
  95% of Time : 778 s total, 77.8 s avg, 41 s to 116 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 1 avg, 1 to 1 max  (0.00%)
Rows examined : 18.57M avg, 18.49M to 18.73M max  (0.99%)
Database      : beaker
Users         : 
	beaker 127.0.0.1 : 100.00% (11) of query, 100.00% (10029) of all users

Query abstract:
SELECT COUNT(N) AS count_1 FROM activity INNER JOIN system_activity ON activity.id = system_activity.id;

Query sample:
SELECT count(1) AS count_1 
FROM activity INNER JOIN system_activity ON activity.id = system_activity.id;
Comment 1 Raymond Mancy 2014-05-28 03:00:51 UTC 
This query is being issued by turbogears.paginate module. It does a count() before any pagination (i.e LIMIT) clauses are added to the query.

As can be seen, this query does not run all that often (total of 11 times) but it is very very slow. Perhaps we could look at always requiring some kind of filter for this page.
Comment 3 Dan Callaghan 2014-05-28 04:17:43 UTC 
I would prefer to just skip showing a total number of rows or total number of pages in this case. So we avoid the COUNT() query, but users aren't forced to search before they can see anything.
Comment 4 Dan Callaghan 2014-07-02 02:53:21 UTC 
Actually this will be quite difficult to fix without either patching TurboGears (or forking TG @paginate into Beaker and then modifying it). In its current incarnation, there is no way to tell @paginate to avoid issuing the COUNT() query.
Comment 6 Dan Callaghan 2015-03-05 06:11:53 UTC 
Having ported the activity pages to use Backgrid, this patch removes the count from those grids which are likely to accumulate a very large number of rows:

http://gerrit.beaker-project.org/4022
Comment 8 Dan Callaghan 2015-03-26 05:18:47 UTC 
Steps to reproduce:
1. Open the system activity page (Activity -> System from the menu).

Expected results:
The page should load in a reasonable amount of time (< 10 seconds). The total number of results found will *not* be shown on this page, because calculating it is too expensive (that's the bug).

Suggested test cases:
We have ported all the activity pages to use Backgrid now, with different code for searching and for producing the grid data. We have addresses all the poorly performing queries that we could find, which included this one. It should be possible to open all the different activity pages and browse through them and perform simple searches, with the results loaded in a reasonable amount of time. Some searches will be slower than others but none should take long enough to enter the slow query log (> 10 seconds).
Comment 10 Dan Callaghan 2015-04-20 02:22:50 UTC 
Beaker 20.0 has been released.