Hi everyone, I recently migrated my osTicket installation from Ubuntu 14.x/PHP 7.1/mysql 5.5.x to a brand new Ubuntu 20.04/PHP 7.4.3/mysql 8.0.21. Hardware specs of both server are the same.
Everything went smoothly and server is up and running with all the previous data.
HOWEVER, certain sql queries that use a lot of joins [1] and are generated by osTicket UI seem to take around 4x longer to execute. For example to see all closed tickets takes around 12 sec, where the SAME sql query in the same dataset in the old mysql server took around 3 sec.
Running analyze query in both old/new mysql servers indicate that different number of rows is produced in respective steps in each case, so this probably means that newer mysql version interprets and executes the SQL query differently.
See the highlighed lines:
mysql 8.0.x
mysql 5.x
mysqltuner doesn't see anything really wrong with my current settings, however I tried modifying some mysql parameters (like join_buffer_size -tried values up to 300M !), but got no real difference.
So, have you experienced such an issue in your installation? I'm running the current latest version (v1.14.3)
What can be done to boost sql performance to previous levels? For example I'm thinking of moving to MariaDB OR MySQL 5.7.x
Thanks in advance for your time!
[1] For example:
SELECT A1.number
AS number
, A1.ticket_id
AS ticket_id
, A1.source
AS source
, A1.closed
AS closed
, B4.subject
AS cdata__subject
, A1.isoverdue
AS isoverdue
, B5.name
AS user__name
, 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
= 3 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_user
B5 ON (A1.user_id
= B5.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
= 'closed' AND ((A2.state
= 'open' AND (A1.staff_id
= 3 OR A5.staff_id
= 3 OR A6.object_type
= 'C' AND A8.staff_id
= 3 OR A1.team_id
IN (4) OR B0.team_id
IN (4) OR A6.object_type
= 'C' AND B1.team_id
IN (4))) OR A1.dept_id
IN (1, 4) OR B2.id
IN (1, 4) OR A6.object_type
= 'C' AND B3.id
IN (1, 4)) AND (A1.ticket_pid
IS NULL OR A1.flags
& 8 != 0) GROUP BY A1.ticket_id
ORDER BY A1.closed
DESC LIMIT 30