347721 - Fulltext search can be extremely slow

Status: RESOLVED WORKSFORME

(Bugzilla :: Query/Bug List, defect)

Description

[Max Kanat-Alexander]

I've been writing a load-testing tool for Bugzilla, that measures how it holds up when many people are accessing it at the same time and doing common tasks.

One of these common tasks is doing a fulltext search. I'm running the tests currently on my very slow laptop. However, the only query that shows up in MySQL's slow query log, out of 48 total page runs, spread across 15 simultaneous users, is the "Simple Search" query, which can take more than a minute.

Now, granted, it wouldn't be that slow on any other system. Obviously my laptop is not optimized for databases. However, it's not the *number* of seconds it took that's important. It's the *relative* number of seconds it took compared to anything else in Bugzilla. It must have taken at least 15 times longer than anything else we do.

So, I suspect there's some optimization that we could do there.

Comment 1

[Max Kanat-Alexander]

Attachment: SELECT & EXPLAIN SELECT

Here's a standard SELECT statement, where I search for one word that won't ever exist in the database, and one word that's pretty common.

This also includes the EXPLAIN output from MySQL 5.0.22, to show how MySQL is running the query.

Comment 2

[Max Kanat-Alexander]

Okay, I bet this is the culprit:

>                     OR (bugs.short_desc REGEXP '(^|[^a-z0-9])be479onv2m($|[^a-z0-9])' 
>                         AND bugs.short_desc REGEXP '(^|[^a-z0-9])freeze($|[^a-z0-9])')))

  Why in the world are we doing that??

Comment 3

[Joel Peshkin]

(In reply to comment #2)
> Okay, I bet this is the culprit:
> 
> >                     OR (bugs.short_desc REGEXP '(^|[^a-z0-9])be479onv2m($|[^a-z0-9])' 
> >                         AND bugs.short_desc REGEXP '(^|[^a-z0-9])freeze($|[^a-z0-9])')))
> 
>   Why in the world are we doing that??
> 


That's why I simplified quicksearch on my (old) installation.  It is both doing a fulltext on longdesc and a mutiple word substring search on short_desc.  Quicksearch does way too much work.

Comment 4

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

I don't remember the details, but someone at some point made the "Find a Single Bug" search not search the fulltext index for bug summaries.  Perhaps lpsolit knows more.

Comment 5

[Frédéric Buclin]

This bug is retargetted to Bugzilla 3.2 for one of the following reasons:

- it has no assignee (except the default one)
- we don't expect someone to fix it in the next two weeks (i.e. before we freeze the trunk to prepare Bugzilla 3.0 RC1)
- it's not a blocker

If you are working on this bug and you think you will be able to submit a patch in the next two weeks, retarget this bug to 3.0.

If this bug is something you would like to see implemented in 3.0 but you are not a developer or you don't think you will be able to fix this bug yourself in the next two weeks, please *do not* retarget this bug.

If you think this bug should absolutely be fixed before we release 3.0, either ask on IRC or use the "blocking3.0 flag".

Comment 6

[Max Kanat-Alexander]

Bugzilla 3.2 is now frozen. Only enhancements blocking 3.2 or specifically approved for 3.2 may be checked in to the 3.2 branch. If you would like to nominate your enhancement for Bugzilla 3.2, set the "blocking3.2" flag to "?". Then, either the target milestone will be changed back, or the blocking3.2 flag will be granted, if we will accept this enhancement for Bugzilla 3.2.

This particular bug has not been touched in over eight months, and thus is being retargeted to "---" instead of "Bugzilla 4.0". If you believe this is a mistake, feel free to retarget it to Bugzilla 4.0.

Comment 7

[Frédéric Buclin]

Is this bug still valid now that we have bugs_fulltext?

Comment 8

[Max Kanat-Alexander]

Probably not. We should wait and see how things go on bmo.

Comment 9

[Max Kanat-Alexander]

Honestly, as of Bugzilla 3.2, it seems pretty speedy to me, even on bmo, which has a very large set of comments to search. And even when it's not very fast, I think it's always reasonably fast.

If it's ever too slow, we should focus on scaling the database, not on optimizing little things here and there that add code complexity.