MySQL Forums
Forum List  »  Performance

bugzilla performance
Posted by: mike solinap
Date: November 21, 2006 06:57PM

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 |

Options: ReplyQuote


Subject
Views
Written By
Posted
bugzilla performance
2337
November 21, 2006 06:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.