Hi everyone,
I am taking the steps to upgrade from osticket v1.10.4 to 1.14.x series and I have noticed a huge performance drop, the default open ticket queue now takes about 15 seconds to load for a single user (testing it on my dev machine, on a snapshot of our production db ~ 30.000 tickets, 20gb db). The performance drop is ironic as I am currently running 1.10.4 on commodity hardware without the system ever breaking a sweat (congrats by the way), 10+ simultaneous users.
Under closer inspection memory footprint is about the same, its the cpu usage that spikes during this query:
SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B4.`priority` AS `cdata__priority`, B6.`firstname` AS `staff__firstname`, B6.`lastname` AS `staff__lastname`, B7.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`,
(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count,
(SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count,
B8.`expire` > NOW() AND NOT B8.`staff_id` = 1 AS `_locked`,
(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B6.`firstname`, B6.`lastname`, B7.`name`, 'zzz') AS `assignee`
FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`)
LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`)
LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`)
LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`)
LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C')
LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`)
LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`)
LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`)
LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`)
LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`)
LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`)
LEFT JOIN `ost_ticket__cdata` B4 ON (A1.`ticket_id` = B4.`ticket_id`)
LEFT JOIN `ost_ticket_priority` B5 ON (B4.`priority` = B5.`priority_id`)
LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`)
LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`)
LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`)
WHERE A2.`state` = 'open' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR A1.`dept_id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR B2.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16)) AND A3.`object_type` = 'T'
GROUP BY A1.`ticket_id` ORDER BY B5.`priority_urgency` ASC, A1.`lastupdate` DESC LIMIT 25;
25 rows in set (14.595 sec)
Running an explain statement for this query I understand that the slow query and hanging queues lies to the left join between ost_ticket and ost_ticket__cdata, the DBMS seems to not take advantage of the primary key in ost_ticket__cdata (ticket_id) or any index whatsoever.
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | A2 | ref | PRIMARY,state | state | 51 | const | 4 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | A1 | ref | PRIMARY,dept_id,staff_id,team_id,status_id | status_id | 4 | osticket.A2.id | 2684 | Using index condition |
| 1 | PRIMARY | A3 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | osticket.A1.ticket_id | 1 (50%) | Using where; Using rowid filter |
| 1 | PRIMARY | A4 | ref | thread_id | thread_id | 4 | osticket.A3.id | 1 | |
| 1 | PRIMARY | A5 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | A6 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | osticket.A1.ticket_id | 1 (0%) | Using where; Using rowid filter |
| 1 | PRIMARY | A7 | ref | thread_id | thread_id | 4 | osticket.A6.id | 1 | Using where |
| 1 | PRIMARY | A8 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B0 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B1 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B2 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B3 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B4 | ALL | PRIMARY | NULL | NULL | NULL | 26814 | Using where; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | B5 | eq_ref | PRIMARY | PRIMARY | 1 | osticket.B4.priority | 1 | Using where |
| 1 | PRIMARY | B6 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A1.staff_id | 1 | |
| 1 | PRIMARY | B7 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A1.team_id | 1 | |
| 1 | PRIMARY | B8 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
| 4 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 4 | DEPENDENT SUBQUERY | Q7 | ref | object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 4 | DEPENDENT SUBQUERY | R0 | ref | collab | collab | 4 | osticket.Q7.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | Q7 | ref | PRIMARY,object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 3 | DEPENDENT SUBQUERY | R0 | ref | PRIMARY,thread_id | thread_id | 4 | osticket.Q7.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | R1 | ref | file-type | file-type | 4 | osticket.R0.id | 1 | Using index condition; Using where |
| 2 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | Q7 | ref | PRIMARY,object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 2 | DEPENDENT SUBQUERY | R0 | ref | thread_id | thread_id | 4 | osticket.Q7.id | 1 | Using where |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
After adding an optimizer hint to the initial query to force to use of the index performance is back as expected, with a massive improvement of 25 rows in set (0.081 sec).
I also tried to alter ost_ticket__cdata such that ticket_id has ost_ticket(ticket_id) as a foreign key but I dunno if that change had any impact whatsoever, probably not.
SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B4.`priority` AS `cdata__priority`, B6.`firstname` AS `staff__firstname`, B6.`lastname` AS `staff__lastname`, B7.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`,
(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count,
(SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count,
B8.`expire` > NOW() AND NOT B8.`staff_id` = 1 AS `_locked`,
(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B6.`firstname`, B6.`lastname`, B7.`name`, 'zzz') AS `assignee`
FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`)
LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`)
LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`)
LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`)
LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C')
LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`)
LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`)
LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`)
LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`)
LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`)
LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`)
LEFT JOIN `ost_ticket__cdata` B4 force index for join (primary) ON (A1.`ticket_id` = B4.`ticket_id`)
LEFT JOIN `ost_ticket_priority` B5 ON (B4.`priority` = B5.`priority_id`)
LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`)
LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`)
LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`)
WHERE A2.`state` = 'open' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR A1.`dept_id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR B2.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16)) AND A3.`object_type` = 'T'
GROUP BY A1.`ticket_id` ORDER BY B5.`priority_urgency` ASC, A1.`lastupdate` DESC LIMIT 25;
Updated DBMS plan after forced index use.
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | A2 | ref | PRIMARY,state | state | 51 | const | 4 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | A1 | ref | PRIMARY,dept_id,staff_id,team_id,status_id | status_id | 4 | osticket.A2.id | 2684 | Using index condition |
| 1 | PRIMARY | A3 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | osticket.A1.ticket_id | 1 (50%) | Using where; Using rowid filter |
| 1 | PRIMARY | A4 | ref | thread_id | thread_id | 4 | osticket.A3.id | 1 | |
| 1 | PRIMARY | A5 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | A6 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | osticket.A1.ticket_id | 1 (0%) | Using where; Using rowid filter |
| 1 | PRIMARY | A7 | ref | thread_id | thread_id | 4 | osticket.A6.id | 1 | Using where |
| 1 | PRIMARY | A8 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B0 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B1 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B2 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B3 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B4 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A1.ticket_id | 1 | |
| 1 | PRIMARY | B5 | eq_ref | PRIMARY | PRIMARY | 1 | osticket.B4.priority | 1 | Using where |
| 1 | PRIMARY | B6 | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | B7 | eq_ref | PRIMARY | PRIMARY | 4 | osticket.A1.team_id | 1 | |
| 1 | PRIMARY | B8 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
| 4 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 4 | DEPENDENT SUBQUERY | Q7 | ref | object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 4 | DEPENDENT SUBQUERY | R0 | ref | collab | collab | 4 | osticket.Q7.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | Q7 | ref | PRIMARY,object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 3 | DEPENDENT SUBQUERY | R0 | ref | PRIMARY,thread_id | thread_id | 4 | osticket.Q7.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | R1 | ref | file-type | file-type | 4 | osticket.R0.id | 1 | Using index condition; Using where |
| 2 | DEPENDENT SUBQUERY | Q8 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | Q7 | ref | PRIMARY,object_id,object_type | object_id | 4 | func | 1 | Using index condition; Using where |
| 2 | DEPENDENT SUBQUERY | R0 | ref | thread_id | thread_id | 4 | osticket.Q7.id | 1 | Using where |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
Have other find this kind of severe performance drops? Is there a know fix? Is there a way to pass optimizer hints to the ORM? Is there an ORM overhaul on the works? We were really looking into upgrading our system, but I guess we will have to wait for a patch for this problem, security considered it's a big compromisse.
