Closed Ticket Queue Slow

dschuett

The Closed ticket queue seems to be unusable. Everytime I click on the tab, it times out or takes forever to load.

Specs:
osTicket: 1.16.3
MariaDB: 10.4.8
Storage Engine: InnoDB
4 CPUs
16GB RAM

Here is the query that seems to be causing the issue when running SHOW FULL PROCESSLIST:
SELECT A1.numberASnumber, A1.ticket_idASticket_id, A1.sourceASsource, A1.closedASclosed, B2.subjectAScdatasubject, A1.isoverdueASisoverdue, B3.nameASusername, B4.firstnameASstafffirstname, B4.lastnameASstafflastname, B5.nameASteamname, A1.team_idASteam_id, A1.dept_idASdept_id, B6.nameASdeptname, A1.isanswered= '0' AS2GkBZxz, (SELECT COUNT(R0.id) AScountFROMost_threadQ7 JOINost_ticketQ8 ON (Q7.object_type= 'T' AND Q7.object_id= Q8.ticket_id) LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id) WHERE Q8.ticket_id= A1.ticket_idAND NOT R0.flags& 4 != 0) AS _thread_count, (SELECT COUNT(R1.id) AScountFROMost_threadQ7 JOINost_ticketQ8 ON (Q7.object_type= 'T' AND Q7.object_id= Q8.ticket_id) LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id) LEFT JOINost_attachmentR1 ON (R1.type= 'H' AND R0.id= R1.object_id) WHERE Q8.ticket_id= A1.ticket_idAND R1.inline= 0) AS _att_count, B7.expire> NOW() AND NOT B7.staff_id= 1 AS_locked, (SELECT COUNT(R0.id) AScountFROMost_threadQ7 JOINost_ticketQ8 ON (Q7.object_type= 'T' AND Q7.object_id= Q8.ticket_id) LEFT JOINost_thread_collaboratorR0 ON (Q7.id= R0.thread_id) WHERE Q8.ticket_id= A1.ticket_id) AS _collabs, COALESCE(B4.firstname, B4.lastname, B5.name, 'zzz') ASassigneeFROMost_ticketA1 JOINost_ticket_statusA2 ON (A1.status_id= A2.id) LEFT JOINost_threadA3 ON (A3.object_type= 'T' AND A1.ticket_id= A3.object_id) LEFT JOINost_thread_referralA4 ON (A3.id= A4.thread_id) LEFT JOINost_staffA5 ON (A4.object_type= 'S' AND A4.object_id= A5.staff_id) LEFT JOINost_threadA6 ON (A1.ticket_id= A6.object_idAND A6.object_type= 'C') LEFT JOINost_thread_referralA7 ON (A6.id= A7.thread_id) LEFT JOINost_staffA8 ON (A7.object_type= 'S' AND A7.object_id= A8.staff_id) LEFT JOINost_departmentB0 ON (A4.object_type= 'D' AND A4.object_id= B0.id) LEFT JOINost_departmentB1 ON (A7.object_type= 'D' AND A7.object_id= B1.id) LEFT JOINost_ticket__cdataB2 ON (A1.ticket_id= B2.ticket_id) LEFT JOINost_userB3 ON (A1.user_id= B3.id) LEFT JOINost_staffB4 ON (A1.staff_id= B4.staff_id) LEFT JOINost_teamB5 ON (A1.team_id= B5.team_id) LEFT JOINost_departmentB6 ON (A1.dept_id= B6.id) LEFT JOINost_lockB7 ON (A1.lock_id= B7.lock_id) WHERE A2.state= 'closed' 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.dept_idIN (1, 5, 10, 11, 12) OR B0.idIN (1, 5, 10, 11, 12) OR A6.object_type= 'C' AND B1.idIN (1, 5, 10, 11, 12)) AND (A1.ticket_pidIS NULL OR A1.flags& 8 != 0) GROUP BY A1.ticket_idORDER BY A1.closedDESC LIMIT 50

KevinTheJedi

dschuett

You will either need to:

EXPLAIN the query in MySQL to see where its pain points are and fix them yourself.
Add more resources/tune your MySQL instance.
Wait for us to optimize the queue queries. This is only on our todo list at the moment. I don't see this happening for a good bit of time so you might be better off doing the above.

Cheers.

dschuett

KevinTheJedi I tried running EXPLAIN on the query, but I am given the following error:

Table 'support.ost_threadQ7' doesn't exist

ntozier

That is correct. There is no table by that name in osTicket.

It looks like you are missing a space between support.ost_thread and Q7

dschuett

Just following up on this. It appears the problem is that B2.subject is stored in a longblob, so including it in the result set destroys the query since it is a huge memory column.

I have found a way to re-write the query to make it much quicker. All that I did was comment out the JOIN on B2, and select out the subject using a subquery as shown in the snippet below. Does anyone know what file and line number I need to change to make this change in the code?

SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`closed` AS `closed`, 

-- B2.`subject` AS `cdata__subject`, 
   (SELECT subject FROM ost_ticket__cdata B2 WHERE A1.ticket_id = B2.ticket_id) AS `cdata__subject`,

    A1.`isoverdue` AS `isoverdue`, B3.`name` AS `user__name`, B4.`firstname` AS `staff__firstname`, B4.`lastname` AS `staff__lastname`, B5.`name` AS `team__name`, 
    A1.`team_id` AS `team_id`, A1.`dept_id` AS `dept_id`, B6.`name` AS `dept__name`, 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, 
    B7.`expire` > NOW() AND NOT B7.`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(B4.`firstname`, B4.`lastname`, B5.`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_department` B0 ON (A4.`object_type` = 'D' AND A4.`object_id` = B0.`id`) 
LEFT JOIN `ost_department` B1 ON (A7.`object_type` = 'D' AND A7.`object_id` = B1.`id`) 
-- LEFT JOIN `ost_ticket__cdata` B2 ON (A1.`ticket_id` = B2.`ticket_id`) 
LEFT JOIN `ost_user` B3 ON (A1.`user_id` = B3.`id`) 
LEFT JOIN `ost_staff` B4 ON (A1.`staff_id` = B4.`staff_id`) 
LEFT JOIN `ost_team` B5 ON (A1.`team_id` = B5.`team_id`) 
LEFT JOIN `ost_department` B6 ON (A1.`dept_id` = B6.`id`) 
LEFT JOIN `ost_lock` B7 ON (A1.`lock_id` = B7.`lock_id`) 
WHERE A2.`state` = 'closed' 
    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.`dept_id` IN (1, 5, 10, 11, 12) 
        OR B0.`id` IN (1, 5, 10, 11, 12) 
        OR A6.`object_type` = 'C' 
        AND B1.`id` IN (1, 5, 10, 11, 12)) 
    AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) 
GROUP BY A1.`ticket_id`
ORDER BY A1.`closed` DESC LIMIT 50

ntozier

@KevinTheJedi did you see this?

KevinTheJedi

ntozier

Yes, thank you. We will need to do some extensive testing and have some discussions about this however. It may cause unintended side effects, missing results, etc.

Cheers.