793169 - remembered search queries are slow

Status: RESOLVED WORKSFORME

(Bugzilla :: Query/Bug List, defect)

Description

[Markus Kolb]

User Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20100101 Firefox/14.0.1
Build ID: 20120713134347

Steps to reproduce:

Created a search and remembered this search long time ago...
Now click on remembered search name in footer and queries needs long time (10-30 seconds instead less than 1 second)


Actual results:

We can reproduce this with the following steps...

1. A user "florian.sailer" has a remembered search (namedqueries table) with query field value "bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&email1=florian.sailer&emailassigned_to1=1&emailtype1=substring&field-1-0-0=bug_status&field-1-1-0=assigned_to&field0-0-0=product&query_format=advanced&type-1-0-0=anyexact&type-1-1-0=substring&type0-0-0=notsubstring&value-1-0-0=NEW%2CASSIGNED%2CREOPENED&value-1-1-0=florian.sailer&value0-0-0=EB010P0052&order=bugs.target_milestone%2Cbugs.bug_status%2Cbugs.target_milestone%2Cbugs.bug_status%2Cbugs.priority%2Cmap_assigned_to.login_name%2Cbugs.bug_id&columnlist=bug_severity%2Cpriority%2Ctarget_milestone%2Cbug_status%2Cassigned_to%2Cproduct%2Ccf_arz_bugid%2Cshort_desc%2Cstatus_whiteboard%2Cestimated_time%2Cremaining_time"

Maybe this search request got generated with an older Bugzilla version.

2. This request generates a really slow DB SELECT...

SELECT bugs.bug_id AS bug_id, bugs.bug_severity AS bug_severity, bugs.priority AS priority, bugs.bug_status AS bug_status, bugs.resolution AS resolution, map_product.name AS product, bugs.target_milestone AS target_milestone, map_assigned_to.login_name AS assigned_to, bugs.cf_arz_bugid AS cf_arz_bugid, bugs.short_desc AS short_desc, bugs.status_whiteboard AS status_whiteboard, bugs.estimated_time AS estimated_time, bugs.remaining_time AS remaining_time  FROM bugs
LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (12,10,9,8,6,7,2,23,25,20,24,45) ) 
LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 9
INNER JOIN products AS map_product ON bugs.product_id = map_product.id
INNER JOIN profiles AS map_assigned_to ON bugs.assigned_to = map_assigned_to.userid
INNER JOIN milestones AS map_target_milestone ON bugs.target_milestone = map_target_milestone.value AND bugs.product_id = map_target_milestone.product_id
INNER JOIN bug_status AS map_bug_status ON bugs.bug_status = map_bug_status.value
INNER JOIN priority AS map_priority ON bugs.priority = map_priority.value
INNER JOIN profiles AS name_assigned_to_1 ON bugs.assigned_to = name_assigned_to_1.userid
INNER JOIN profiles AS name_assigned_to_3 ON bugs.assigned_to = name_assigned_to_3.userid
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 9)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 9
        OR bugs.qa_contact = 9)
   AND  bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')  AND INSTR(name_assigned_to_1.login_name, 'florian.sailer') > 0 AND  bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')  AND INSTR(name_assigned_to_3.login_name, 'florian.sailer') > 0 AND bugs.product_id IN (SELECT products.id FROM products WHERE INSTR(products.name, 'EB010P0052') = 0)
GROUP BY bugs.bug_id
ORDER BY map_target_milestone.sortkey, map_target_milestone.value, map_bug_status.sortkey, map_bug_status.value, map_target_milestone.sortkey, map_target_milestone.value, map_bug_status.sortkey, map_bug_status.value, map_priority.sortkey, map_priority.value, assigned_to, bug_id
LIMIT 500;

3. If we edit the search, the search request with our current Bugzilla Version becomes

f1=OP&f0=OP&columnlist=bug_severity%2Cpriority%2Ctarget_milestone%2Cbug_status%2Cassigned_to%2Cproduct%2Ccf_arz_bugid%2Cshort_desc%2
Cstatus_whiteboard%2Cestimated_time%2Cremaining_time&emailtype1=substring&query_based_on=Unerledigt%2FOther&o2=notequals&f4=CP&emailassigned_to1=1&query_format=advanced&j1=OR&f3=CP&f2=product&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&email1=florian.sailer&v2=EB010P0052

and a newly generated DB SELECT is fast. But see next...

4. There seems to be some kind of caching of the search DB query strings in Bugzilla.
Because if the slow request is used one time also the fast request url produces the same slow DB SELECT for the logged in user.

5. If the user logs out, and logs in again, does another unrelated fast search request, the cached DB SELECT strings might be emptied and now the fast request url produces a fast DB SELECT. If the user requests again the slow request url the slow DB SELECTs are used in both situations.


So how to handle this strange stuff?
We can not advise all the users to delete the remembered searches and recreate them to get fast searches again!
Also this is one example query. I could provide some more.
How does this caching work, where are the DB query strings stored?



Expected results:

Don't know!

Comment 1

[Frédéric Buclin]

Bugzilla does no caching at all, so the problem is not on our end.

Comment 2

[Markus Kolb]

Ok. Then forget this stuff about caching.
This would have been just an explanation for the seen behaviour.
What's about the list_id parameter. Isn't this some kind of caching?

But what is about the DB Select generation. 
Why there are different DB queries for same results?
Really slow ones for the remembered searches!
We use apache mod_perl and mysql.

Comment 3

[Frédéric Buclin]

This looks like a support question to me. See http://www.bugzilla.org/support for help. To debug your problem, append &debug=1 to the URL in buglist.cgi and see details reported by your DB server. If you do it as an admin, more details are displayed. But this really doesn't look like a bug in Bugzilla itself, unless you can provide more data which shows that it's the case.