bugzilla performance
My setup is as follows:
- bugzilla 2.22
- Fedora Core 4 2.6.15
- mysql 4.1.20-1.FC4
Ever since we've upgraded to bugzilla 2.22, my mysql-slow.log has been pretty busy. Some queries are running upwards of 30 mins plus. The database isn't that large, but I think the joins are killing the performance. Below is a typical query that will bring the system to its knees. Any suggestions would be appreciated.
# Query_time: 2091 Lock_time: 0 Rows_sent: 30 Rows_examined: 50444219
SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc 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 ) INNER JOIN longdescs AS longdescs_1 ON (longdescs_1.bug_id = bugs.bug_id ) INNER JOIN longdescs AS longdescs_2 ON (longdescs_2.bug_id = bugs.bug_id ) LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id NOT IN (10,12,19,20,8,15,11,16,17,9,14,18,13,6,5,7) LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 21 WHERE ((bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED'))) AND (((1=2) OR (1=2) OR (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('parent' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('parent' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(longdescs_0.thetext) AS BINARY), CAST('parent' AS BINARY)) > 0))) AND (((1=2) OR (bugs.component_id IN (130)) OR (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('zone' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('zone' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(longdescs_1.thetext) AS BINARY), CAST('zone' AS BINARY)) > 0))) AND (((1=2) OR (1=2) OR (INSTR(CAST(LOWER(bugs.short_desc) AS BINARY), CAST('update' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(bugs.status_whiteboard) AS BINARY), CAST('update' AS BINARY)) > 0) OR (INSTR(CAST(LOWER(longdescs_2.thetext) AS BINARY), CAST('update' AS BINARY)) > 0))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL) OR (bugs.reporter_accessible = 1 AND bugs.reporter = 21) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 21) OR (bugs.qa_contact = 21) ) GROUP BY bugs.bug_id ORDER BY bugs.bug_id;
Explain:
| 1 | SIMPLE | longdescs_2 | ALL | longdescs_bug_id_idx
| NULL | NULL | NULL
| 103615 | Using temporary; Using filesort |
| 1 | SIMPLE | bugs | eq_ref | PRIMARY,bugs_bug_status_idx,bugs
_reporter_idx,bugs_assigned_to_idx,bugs_creation_ts_idx,bugs_qa_contact_idx,bugs
_component_id_idx | PRIMARY | 3 | bugs.longdescs_2.bug_id
| 1 | Using where |
| 1 | SIMPLE | map_assigned_to | eq_ref | PRIMARY
| PRIMARY | 3 | bugs.bugs.assigned_to
| 1 | |
| 1 | SIMPLE | bug_group_map | ref | bug_group_map_bug_id_idx,bug_gro
up_map_group_id_idx
| bug_group_map_bug_id_idx | 3 | bugs.bugs.bug_id
| 1 | Using where; Using index |
| 1 | SIMPLE | longdescs_0 | ref | longdescs_bug_id_idx
| longdescs_bug_id_idx | 3 | bugs.bugs.bug_id
| 6 | Using where |
| 1 | SIMPLE | longdescs_1 | ref | longdescs_bug_id_idx
| longdescs_bug_id_idx | 3 | bugs.bugs.bug_id
| 6 | Using where |
| 1 | SIMPLE | cc | eq_ref | cc_bug_id_idx,cc_who_idx
| cc_bug_id_idx | 6 | bugs.bugs.bug_id,const
| 1 | Using where; Using index |