179960 - QuickSearch queries are slow and time out

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Myk Melez [:myk] [@mykmelez]]

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.

Comment 1

[Myk Melez [:myk] [@mykmelez]]

Attachment: sample explain

Comment 2

[Joel Peshkin]

Ick!  We need to redefing what quicksearch purports to do.  It should probably
INSTR  in only the bug alias and summary (and perhaps comments).

Comment 3

[Bradley Baetz (:bbaetz)]

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

Comment 4

[Bradley Baetz (:bbaetz)]

that queyr thinks that there are approximately 3540 results going to be returned
by that query. Is that in the right ballpark?

Comment 5

[Bradley Baetz (:bbaetz)]

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

Comment 6

[Myk Melez [:myk] [@mykmelez]]

Attachment: 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.

Comment 7

[Bradley Baetz (:bbaetz)]

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?

Comment 8

[Bradley Baetz (:bbaetz)]

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.

Comment 9

[Myk Melez [:myk] [@mykmelez]]

Attachment: 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.

Comment 10

[Bradley Baetz (:bbaetz)]

OK, that looks fine, and is a substantial improvment (although not as much as an
upgrade would be :)

I'll hack on this tomorrow.

Comment 11

[Myk Melez [:myk] [@mykmelez]]

Attachment: you guessed it; explains;

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

Comment 12

[Bradley Baetz (:bbaetz)]

Attachment: 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.

Comment 13

[Bradley Baetz (:bbaetz)]

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.

Comment 14

[Joel Peshkin]

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

nice.

r=joel

Comment 15

[Dave Miller [:justdave]]

a= justdave

Comment 16

[Bradley Baetz (:bbaetz)]

Fixed.