Last Comment Bug 179960 - QuickSearch queries are slow and time out
: QuickSearch queries are slow and time out
Status: RESOLVED FIXED
: perf
Product: Bugzilla
Classification: Server Software
Component: Query/Bug List (show other bugs)
: unspecified
: All All
: -- major (vote)
: Bugzilla 2.18
Assigned To: Bradley Baetz (:bbaetz)
: default-qa
Mentors:
Depends on:
Blocks: 179176 CVE-2013-0786
  Show dependency treegraph
 
Reported: 2002-11-13 11:12 PST by Myk Melez [:myk] [@mykmelez]
Modified: 2012-12-23 17:01 PST (History)
3 users (show)
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
sample explain (2.21 KB, text/plain)
2002-11-13 11:18 PST, Myk Melez [:myk] [@mykmelez]
no flags Details
more explains (17.77 KB, text/plain)
2002-11-15 15:26 PST, Myk Melez [:myk] [@mykmelez]
no flags Details
even more explains (3.69 KB, text/plain)
2002-11-17 05:15 PST, Myk Melez [:myk] [@mykmelez]
no flags Details
you guessed it; explains; (2.59 KB, text/plain)
2002-11-17 05:33 PST, Myk Melez [:myk] [@mykmelez]
no flags Details
here we go (2.50 KB, patch)
2002-11-17 17:17 PST, Bradley Baetz (:bbaetz)
bugreport: review+
Details | Diff | Splinter Review

Description Myk Melez [:myk] [@mykmelez] 2002-11-13 11:12:09 PST
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] 2002-11-13 11:18:04 PST
Created attachment 106105 [details]
sample explain
Comment 2 Joel Peshkin 2002-11-13 11:27:50 PST
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) 2002-11-13 12:13:38 PST
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) 2002-11-13 12:37:32 PST
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) 2002-11-13 12:54:13 PST
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] 2002-11-15 15:26:33 PST
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.
Comment 7 Bradley Baetz (:bbaetz) 2002-11-15 16:53:02 PST
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) 2002-11-15 16:55:46 PST
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] 2002-11-17 05:15:03 PST
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.
Comment 10 Bradley Baetz (:bbaetz) 2002-11-17 05:22:06 PST
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] 2002-11-17 05:33:30 PST
Created attachment 106562 [details]
you guessed it; explains;

These are explains from a pre-upgrade copy of the database.
Comment 12 Bradley Baetz (:bbaetz) 2002-11-17 17:17:34 PST
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.
Comment 13 Bradley Baetz (:bbaetz) 2002-11-17 17:19:14 PST
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 2002-11-17 17:33:29 PST
Comment on attachment 106617 [details] [diff] [review]
here we go

nice.

r=joel
Comment 15 Dave Miller [:justdave] (justdave@bugzilla.org) 2002-11-17 17:57:08 PST
a= justdave
Comment 16 Bradley Baetz (:bbaetz) 2002-11-17 18:07:47 PST
Fixed.

Note You need to log in before you can comment on or make changes to this bug.