User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1) Gecko/20061010 Firefox/2.0 Build Identifier: 3.0.1 When I use Advanced Search in bugzilla v.3.0.1 I can get output very fast (i.e. 2 sec.). If I use quicksearch from main our bugzilla search with this same words it kills our database backend - 99% of CPU usage in next 2-3 minutes or more :/ Is there any problem with this main search (not advanced) ? Reproducible: Always Steps to Reproduce: Search two or more words with quicksearch form (with space between them - AND operator). Actual Results: top on server (you can see one mysql process with 99% CPU) Expected Results: Just response or faster response. Backend: Mysql 4.1.22

What did you type exactly? Did you use any "magic" keyword? Note that you can append &debug=1 to the URL to get the SQL query executed by QuickSearch. Maybe you will find something wrong in it.

I'm guessing this is a duplicate of bug 347721, but I'm not certain.

I've typed "TEST SERVER" so it's not anything special :/ It seems that I have a problem when I'm using words which occured very often in database (i.e. I have this same problem when I type i.e. "problem big"). After add "&debug=1" I can not see any interestin but i'm not a database specialist :/

(In reply to comment #2) > I'm guessing this is a duplicate of bug 347721, but I'm not certain. It can be :/

checked this problem using query that Andy proposed. After removing common parts 'query' looks like: quick search: INNER JOIN longdescs AS longdescs_0 ON (longdescs_0.bug_id = bugs.bug_id ) INNER JOIN longdescs AS longdescs_1 ON (longdescs_1.bug_id = bugs.bug_id ) WHERE ((bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED'))) AND (((bugs.product_id IN (2,33,27)) OR (1=2) OR (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('test' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('test' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(longdescs_0.thetext) AS BINARY), CAST('test' AS BINARY)) > 0))) AND (((bugs.product_id IN (17)) OR (bugs.component_id IN (101)) OR (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('server' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('server' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(longdescs_1.thetext) AS BINARY), CAST('server' AS BINARY)) > 0))) GROUP BY bugs.bug_id ORDER BY bugs.delta_ts,priority.sortkey,priority.value,bug_severity.sortkey,bug_severity.value advanced search: INNER JOIN longdescs AS longdescs_ ON (longdescs_.bug_id = bugs.bug_id ) WHERE ((INSTR(CAST(LOWER(longdescs_.thetext) AS BINARY), CAST('test' AS BINARY)) > 0 AND INSTR(CAST(LOWER(longdescs_.thetext) AS BINARY), CAST('server' AS BINARY)) > 0)) GROUP BY bugs.bug_id ORDER BY bugs.delta_ts,priority.sortkey,priority.value,bug_severity.sortkey,bug_severity.value btw. where and how I could change quicksearch query to be more like advanced?

I can add some details to this. The query looks like SELECT bugs.bug_id /* Some field dropped */ FROM bugs INNER JOIN profiles AS map_assigned_to ON bugs.assigned_to = map_assigned_to.userid INNER JOIN longdescs AS longdescs_0 ON longdescs_0.bug_id = bugs.bug_id AND longdescs_0.isprivate < 1 INNER JOIN longdescs AS longdescs_1 ON longdescs_1.bug_id = bugs.bug_id AND longdescs_1.isprivate < 1 INNER JOIN longdescs AS longdescs_2 ON longdescs_2.bug_id = bugs.bug_id AND longdescs_2.isprivate < 1 INNER JOIN longdescs AS longdescs_3 ON longdescs_3.bug_id = bugs.bug_id AND longdescs_3.isprivate < 1 LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id NOT IN (7,8) LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 1090 WHERE bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') AND bugs.creation_ts IS NOT NULL AND (bug_group_map.group_id IS NULL OR (bugs.reporter_accessible = 1 AND bugs.reporter = 1090) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR bugs.assigned_to = 1090) AND (bugs.component_id IN (29) OR INSTR(CAST(LOWER(bugs.keywords) AS BINARY), CAST('kernel' AS BINARY)) > 0 OR INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('kernel' AS BINARY)) > 0 OR INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('kernel' AS BINARY)) > 0 OR INSTR(CAST(LOWER(longdescs_0.thetext) AS BINARY), CAST('kernel' AS BINARY)) > 0 ) AND (bugs.product_id IN (12) OR bugs.component_id IN (11,53,100) OR INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('bug' AS BINARY)) > 0 OR INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('bug' AS BINARY)) > 0 OR INSTR(CAST(LOWER(longdescs_1.thetext) AS BINARY), CAST('bug' AS BINARY)) > 0 ) AND (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('at' AS BINARY)) > 0 OR INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('at' AS BINARY)) > 0 OR INSTR(CAST(LOWER(longdescs_2.thetext) AS BINARY), CAST('at' AS BINARY)) > 0 ) AND (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('extents' AS BINARY)) > 0 OR INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('extents' AS BINARY)) > 0 OR INSTR(CAST(LOWER(longdescs_3.thetext) AS BINARY), CAST('extents' AS BINARY)) > 0 ) GROUP BY bugs.bug_id ORDER BY bugs.bug_id This makes mysql freezes: bash-3.00# mysqladmin -u bugs processlist +--------+------+-----------+------+---------+------+----------------------+------------------------------------------------------ ------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------+------+---------+------+----------------------+------------------------------------------------------ ------------------------------------------------+ | 135209 | bugs | localhost | bugs | Query | 1609 | Copying to tmp table | SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_sev | | 135217 | bugs | localhost | bugs | Query | 1506 | Locked | SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_sev | | 135245 | bugs | localhost | bugs | Query | 1144 | Locked | SELECT 1, reporter, assigned_to, qa_contact, The problem is that the query uses temporary table to get the data mysql> EXPLAIN __query__; +----+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | bugs | range | PRIMARY,bugs_reporter_idx,bugs_product_id_idx,bugs_creation_ts_idx,bugs_assigned_to_idx,bugs_bug_status_idx,bugs_component_id_idx | bugs_bug_status_idx | 194 | NULL | 1713 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | map_assigned_to | eq_ref | PRIMARY | PRIMARY | 3 | bugs.bugs.assigned_to | 1 | | | 1 | SIMPLE | longdescs_0 | ref | longdescs_bug_id_idx | longdescs_bug_id_idx | 3 | bugs.bugs.bug_id | 9 | Using where | | 1 | SIMPLE | longdescs_1 | ref | longdescs_bug_id_idx | longdescs_bug_id_idx | 3 | bugs.longdescs_0.bug_id | 9 | Using where | | 1 | SIMPLE | longdescs_2 | ref | longdescs_bug_id_idx | longdescs_bug_id_idx | 3 | bugs.longdescs_0.bug_id | 9 | Using where | | 1 | SIMPLE | longdescs_3 | ref | longdescs_bug_id_idx | longdescs_bug_id_idx | 3 | bugs.longdescs_0.bug_id | 9 | Using where | | 1 | SIMPLE | bug_group_map | ref | bug_group_map_bug_id_idx,bug_group_map_group_id_idx | bug_group_map_bug_id_idx | 3 | bugs.longdescs_0.bug_id | 36 | Using where; Using index | | 1 | SIMPLE | cc | eq_ref | cc_bug_id_idx,cc_who_idx | cc_bug_id_idx | 6 | bugs.longdescs_3.bug_id,const | 1 | Using where; Using index | +----+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------+------+----------------------------------------------+ 8 rows in set (0.02 sec)

Sorry, tables got a bit messed.... Key thing there - "Using where; _Using temporary;_ Using filesort" in the mysql EXPLAIN $query.

Read also bug 443892, which I marked as a dupe of this bug. It seems the slowness comes from INNER JOIN which should be replaced by sub-selects to make the query faster. We should fix this asap as running queries prevent the replication between the master and slave DBs to happen.

I doubt the INNER JOIN is a problem. More likely the problem is all the unnecessary CAST AS BINARY stuff which is actually gone for MySQL in 3.2 (unless you specifically pick "case sensitive"). We can fix up the performance, though.

Actually, more likely the problem is that we're doing a substring search on longdescs.thetext, which can't use indexes. This is a problem when your longdescs table is several GB, like bmo. This would be fixed by doing a fulltext search on longdescs.

My longdescs table is ~43MB and still takes way more than 5 minutes on some searches. However running the query with changes as I mentioned in bug 443892 the query completes in under 1 second. So it's not the CAST AS BINARY that's holding up *my* system as it is still used in my modified query. Quick summary of bug 443892: Replacing: ... INNER JOIN longdescs AS longdescs_1 ON (longdescs_1.bug_id = bugs.bug_id ) ... (INSTR(CAST(LOWER(longdescs_1.thetext) AS BINARY), CAST('crash' AS BINARY)) > 0) ... With: ... bugs.bug_id IN (SELECT longdescs.bug_id FROM longdescs WHERE (INSTR(CAST(LOWER(longdescs.thetext) AS BINARY), CAST('crash' AS BINARY)) > 0)) ... For all of the INNER JOINs, speeds it up enormously for me.

Looking at the MySQL documentation on INNER JOIN ( http://dev.mysql.com/doc/refman/5.0/en/join.html ) states that an INNER JOIN produces a Cartesian product between the specified tables. Use the ON clause for specifying how the tables will be joined and the WHERE clause to restrict the rows. This suggests to me, though I'm not a DB expert, that the temporary table created by the joins will get very big, very quickly. I suppose it depends on when the result set is restricted by the WHERE clause. I initially thought it happened at each JOIN, but I now think it happens after all the JOINs have been completed.

(In reply to comment #13) > This suggests to me, though I'm not a DB expert, that the temporary table > created by the joins will get very big, very quickly. Well, because it's an INNER JOIN with an ON clause, that join is the size of the entire longdescs table. However, that should be fine. Could I see (as an attachment, not in a comment) the EXPLAIN output for both queries on your DB--one with the INNER JOIN and one with the subselects?

That's interesting. Theoretically, the subselect query should be much slower than the JOIN-based query--selecting a substring through the entire longdescs table three times should be slower than selecting some subset of it. However, MySQL is doing some sort of query planning that I don't fully understand. In any case, switching to fulltext matching for the longdescs table should eliminate any problem and we wouldn't have to do any sort of gigantic rearchitecture of Search.pm (particularly one that might not be equally performant across DBs...).

I'll try to install Bugzilla 3.1.4 on to another machine and port the database across and see what results I get with the fulltext search. If you have other suggestions for me to try I'll see about giving them a go too.

I've ported the database to another machine running Bugzilla 3.1.4 & Mysql 5.0.51a-3ubuntu5.1-log The quicksearch query still didn't use FULLTEXT search. However the slow queries didn't block other queries on the Bugzilla DB which was good. I've run a number of queries and let them run to completion and checked the output of mysql-slow.log. Between each run I restarted Mysql so they would have a fair crack of the whip.

We've just "discovered" this same issue on the mandriva bugzilla as well, with both the 3.0.5 we have in production and the 3.rc1 we have setup in testing. Is there anything that we can do to help with this? Something we can patch and test or otherwise help get this fixed hopefully for the 3.2 final release? I'm not a big database guru, but I'm more than willing to be a guinea pig, especially with the 3.2rc1 install.

(In reply to comment #20) > We've just "discovered" this same issue on the mandriva bugzilla as well, with > both the 3.0.5 we have in production and the 3.rc1 we have setup in testing. Try setting quicksearch_comment_cutoff to 1 or 2, that usually works around the problem.

In Bugzilla/Config/Query.pm? Changing the default value of "4"?

(In reply to comment #22) > In Bugzilla/Config/Query.pm? Changing the default value of "4"? No, using editparams.cgi.

Sorry, been quite busy. Tried that and it definitely seems to speed things up a bit. Thanks!

I'm pretty sure this is because we're doing a substring search on longdescs. Making it use fulltext instead will fix this.

1) It's not the substring search on longdescs that's the problem as that happens really quickly. 2) It's the subsequent "copying to tmp table" from all the joins that takes thousands of seconds! As for reducing the number of search terms to 1 or 2 that is just plain stupid. 1 search term may return several hundred hits, 2 maybe 100, the third term can cut this down to just 10 or so.

reopen?

No, I've tested this with the patch in bug 314364 and it solves the problem.

Ok. This does help hugely. It's not as fast for me as the method I used, but it's fast enough (about 10 seconds = at least 150x faster on one search). At least moving to 3.2.x will allow other users to access the bugs while it's processing that search. Thanks