Note: There are a few cases of duplicates in user autocompletion which are being worked on.

QuickSearch queries are slow and time out

RESOLVED FIXED in Bugzilla 2.18

Status

()

Bugzilla
Query/Bug List
--
major
RESOLVED FIXED
15 years ago
5 years ago

People

(Reporter: myk, Assigned: bbaetz)

Tracking

({perf})

unspecified
Bugzilla 2.18
Dependency tree / graph

Details

Attachments

(5 attachments)

(Reporter)

Description

15 years ago
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

15 years ago
Created attachment 106105 [details]
sample explain

Comment 2

15 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

15 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

15 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

15 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...)

Updated

15 years ago
Keywords: perf
(Reporter)

Comment 6

15 years ago
Created attachment 106423 [details]
more explains

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

15 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

15 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

15 years ago
Created attachment 106560 [details]
even more explains

>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

15 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

15 years ago
Created attachment 106562 [details]
you guessed it; explains;

These are explains from a pre-upgrade copy of the database.
(Assignee)

Comment 12

15 years ago
Created attachment 106617 [details] [diff] [review]
here we go

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

15 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

15 years ago
Comment on attachment 106617 [details] [diff] [review]
here we go

nice.

r=joel
Attachment #106617 - Flags: review?(bugreport) → review+
a= justdave
(Assignee)

Comment 16

15 years ago
Fixed.
Status: NEW → RESOLVED
Last Resolved: 15 years ago
Resolution: --- → FIXED
QA Contact: matty_is_a_geek → default-qa

Updated

5 years ago
Blocks: 824399
You need to log in before you can comment on or make changes to this bug.