Closed
Bug 179960
Opened 22 years ago
Closed 22 years ago
QuickSearch queries are slow and time out
Categories
(Bugzilla :: Query/Bug List, defect)
Bugzilla
Query/Bug List
Tracking
()
RESOLVED
FIXED
Bugzilla 2.18
People
(Reporter: myk, Assigned: bbaetz)
References
Details
(Keywords: perf)
Attachments
(5 files)
QuickSearch queries are so slow they time out, making them useless and dragging the server's performance down with them. Single site searches from the Mozilla search sidebar have the same problem. I've disabled QuickSearch on b.m.o until the problem gets resolved and will look into blocking Mozilla sidebar searches as well.
Reporter | ||
Comment 1•22 years ago
|
||
Comment 2•22 years ago
|
||
Ick! We need to redefing what quicksearch purports to do. It should probably INSTR in only the bug alias and summary (and perhaps comments).
Assignee | ||
Comment 3•22 years ago
|
||
How long does that query take to run? How many products/compoents match the string 'mht', and what happens if you replace those INSTRs with |bugs.product_id IN (....)| for teh appropriate valiues, and drop the join? Its using keys for everything...
Assignee | ||
Comment 4•22 years ago
|
||
that queyr thinks that there are approximately 3540 results going to be returned by that query. Is that in the right ballpark?
Assignee | ||
Comment 5•22 years ago
|
||
nm, answer appears to be 'its a lot quicker if you pull out product/component manually' myk - on a *copy* of bmo, does ANALYZE on the various tables help the timing of this? (Do it on a copy due to the corruption we saw last time we tried this...)
Reporter | ||
Comment 6•22 years ago
|
||
Here are more explains. ANALYZE TABLE didn't seem to affect the query significantly. Removing product/component tables made a big difference. Pre-looking up product/component IDS didn't seem to make a difference, although my example is probably biased. The explains look very different for b.m.o versus my test installation. More cardinality snafus? We need a newer version of MySQL.
Assignee | ||
Comment 7•22 years ago
|
||
myk - you tried with hard coded product ids, but still did the join - can you try agaon without teh compoents_0/products_0 tables? OTOH, we're seeing that bmo queries are 40-50 times as slow as your test server. I don't suppose theres a plan to upgrade bmo anytime soon?
Assignee | ||
Comment 8•22 years ago
|
||
Also, the cardinality wil be different; its done baed on a random sampling of the table. As long as they're still roughly in the same ballpart, it should be ok.
Reporter | ||
Comment 9•22 years ago
|
||
>myk - you tried with hard coded product ids, but still did the join - can you >try agaon without teh compoents_0/products_0 tables? Here ya go. >OTOH, we're seeing that bmo queries are 40-50 times as slow as your test >server. I don't suppose theres a plan to upgrade bmo anytime soon? Keep in mind that those tests were run while b.m.o was under heavy load and my test installation was under no load. Under light load (f.e. with this last set of explains) b.m.o is much faster. That being said, it's still quite slow compared to my test installation, which is running on a single processor 2.4Ghz machine. Yes, there are plans to upgrade b.m.o, although nothing concrete yet.
Assignee | ||
Comment 10•22 years ago
|
||
OK, that looks fine, and is a substantial improvment (although not as much as an upgrade would be :) I'll hack on this tomorrow.
Assignee: endico → bbaetz
Severity: normal → major
Target Milestone: --- → Bugzilla 2.18
Reporter | ||
Comment 11•22 years ago
|
||
These are explains from a pre-upgrade copy of the database.
Assignee | ||
Comment 12•22 years ago
|
||
OK, this works. This function isn't yet set up to handle cases like cc where we need a value from the outer query (bugs.bug_id) to be part of the inner one. However, it works for product/component, which is the only issue here.
Assignee | ||
Comment 13•22 years ago
|
||
Comment on attachment 106617 [details] [diff] [review] here we go build_subselect cna move to to the dbcompat module when we have one, probably. I also haven't mearsured perf, but myk's explains from earlier do suggest that this will be an improvement.
Attachment #106617 -
Flags: review?(bugreport)
Comment 14•22 years ago
|
||
Comment on attachment 106617 [details] [diff] [review] here we go nice. r=joel
Attachment #106617 -
Flags: review?(bugreport) → review+
Comment 15•22 years ago
|
||
a= justdave
Assignee | ||
Comment 16•22 years ago
|
||
Fixed.
Status: NEW → RESOLVED
Closed: 22 years ago
Resolution: --- → FIXED
Updated•12 years ago
|
QA Contact: matty_is_a_geek → default-qa
![]() |
||
Updated•12 years ago
|
Blocks: CVE-2013-0786
You need to log in
before you can comment on or make changes to this bug.
Description
•