Current System:
Server: vServer
CPU: 6 vCores
RAM: 16 GB
OS: Debian 12.8
Database: MariaDB Ver 15.1, Distrib 10.11.6 (for debian-linux-gnu, x86_64, using EditLine wrapper)
PHP: 8.3.14
Application: osTicket v1.18.1
Problem in Detail:
I have been running osTicket v1.14.1 for years on an intranet server without issues. A week ago, we noticed that osTicket started slowing down significantly -most of the time, it was even unreachable. Upon investigation, I discovered that long-running SQL queries were causing the issue. Until last week, the system performed at normal speed without interruptions. However, now it works normally about 70% of the time, while the remaining 30% it is blocked by SQL queries that take several minutes to execute (example below - one query took over 10 minutes).
I am unsure what might be causing SQL queries on the ticket system to suddenly take so long from one day to the next. No other tasks are running on the server that could consume CPU resources. I read in this forum about tuning the SQL database and applied several suggested optimizations, but without success.
Next, I created a new vServer from scratch. I tuned MariaDB, restored a fresh backup of the osTicket database, and upgraded to the latest osTicket version (v1.18.1) to investigate further. Unfortunately, the same poor performance persists.
Time: 241209 11:36:19
User@Host: custcare[custcare] @ localhost []
Thread_id: 6255 Schema: custcare QC_hit: No
Query_time: 605.249725 Lock_time: 0.000734 Rows_sent: 50 Rows_examined: 325285
Rows_affected: 0 Bytes_sent: 8981
SET timestamp=1733740579;
SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.lastupdate AS lastupdate, B2.subject AS cdata__subject, A1.isoverdue AS isoverdue, B4.address AS user__emails__address, B3.name AS user__name, B2.priority AS cdata__priority, B5.firstname AS staff__firstname, B5.lastname AS staff__lastname, B6.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, B7.expire > NOW() AND NOT B7.staff_id = 13 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(B5.firstname, B5.lastname, B6.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_user_email B4 ON (B3.id = B4.user_id) LEFT JOIN ost_staff B5 ON (A1.staff_id = B5.staff_id) LEFT JOIN ost_team B6 ON (A1.team_id = B6.team_id) LEFT JOIN ost_lock B7 ON (A1.lock_id = B7.lock_id) WHERE A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND ((A2.state = 'open' AND (A1.staff_id = 13 OR A5.staff_id = 13 OR A6.object_type = 'C' AND A8.staff_id = 13)) OR A1.dept_id IN (1, 4, 5) OR B0.id IN (1, 4, 5) OR A6.object_type = 'C' AND B1.id IN (1, 4, 5)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY A1.est_duedate DESC LIMIT 50;
The database size is about 9 gb.
innodb_buffer_pool_size = 12G
Is that too little for osticket? I'm surprised that the sql queries only took a few seconds before and now from one day to the next they sometimes take several minutes. That shouldn't really be the case.
Does anyone else have any ideas on what I can test / check?
