sql query slow down server

sbumba

I know v1.14 is end-of-life, but I won’t upgrade to PHP 8 due to dependencies on other software pieces running on this server.

However, I have been running v1.14.1 for years on an intranet Debian machine without issues. Since last week, one SQL query issued by osTicket has been causing the whole server to crash.

From the SQL slow query log:

Time: 241207 17:07:34

User@Host: custcare[custcare] @ localhost []

Thread_id: 548 Schema: custcare QC_hit: No

Query_time: 890.809118 Lock_time: 0.000411 Rows_sent: 50 Rows_examined: 325319

Rows_affected: 0 Bytes_sent: 11232

use custcare;
SET timestamp=1733587654;
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 = 2 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) 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.staff_id = 2 OR A5.staff_id = 2 OR A6.object_type = 'C' AND A8.staff_id = 2)) 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 A3.object_type = 'T' GROUP BY A1.ticket_id ORDER BY A1.lastupdate DESC LIMIT 50;

I have read many threads, like this one: https://forum.osticket.com/d/104196-slow-ticket-queue-after-update-from-1912/3 and tried improving database settings, but I cannot resolve the issue.

I don’t know what action triggers the above SQL statement. osTicket runs smoothly for a few hours, but then MariaDB encounters issues with the above SQL statement, causing osTicket to crash. I always have to restart MariaDB to get osTicket working again.

Does anyone have a hint about what action might be causing this SQL statement? Or how to resolve it?

In the SQL statement, I see "B6.name, 'zzz'", which seems odd, doesn’t it? However, I found that it is hard-coded in some of osTicket's PHP files.

system:
osTicket (v1.14.1)
debian 11.11
mysql Ver 15.1 Distrib 10.5.26-MariaDB

KevinTheJedi

sbumba

Just looks like an agent viewing an open queue; Agent with ID of 2.

Cheers.

sbumba

In fact, 2 is my ID, so it seems that I am causing the trouble.

The last few hours I worked with the ticket system, answered about 20 tickets, closed some tickets, and browsed through the open queue a couple of times. In most cases, it works like a charm, but twice the ticket system slowed down. Once again, it happened with this long-running query (see log excerpt at the end of this post).

What the heck is causing this? And why does it run so long, and how can I get rid of it? Does anyone have a clue for me? As I said, this happens only for about a week a couple of times a day. I never had such trouble before with osTicket.

Here from log:

Time: 241208 8:40:31

User@Host: custcare[custcare] @ localhost []

Thread_id: 1573 Schema: custcare QC_hit: No

Query_time: 701.124368 Lock_time: 0.000379 Rows_sent: 50 Rows_examined: 325700

Rows_affected: 0 Bytes_sent: 9933

SET timestamp=1733643631;
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 = 2 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) 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.staff_id = 2 OR A5.staff_id = 2 OR A6.object_type = 'C' AND A8.staff_id = 2)) 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 A3.object_type = 'T' GROUP BY A1.ticket_id ORDER BY A1.lastupdate DESC LIMIT 50;

Time: 241208 10:16:13

User@Host: custcare[custcare] @ localhost []

Thread_id: 1781 Schema: custcare QC_hit: No

Query_time: 1248.842565 Lock_time: 0.000678 Rows_sent: 50 Rows_examined: 325792

Rows_affected: 0 Bytes_sent: 9614

SET timestamp=1733649373;
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 = 2 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) 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.staff_id = 2 OR A5.staff_id = 2 OR A6.object_type = 'C' AND A8.staff_id = 2)) 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 A3.object_type = 'T' GROUP BY A1.ticket_id ORDER BY A1.lastupdate DESC LIMIT 50;

KevinTheJedi

sbumba

Again, it simply looks like you are visiting an open queue. It’s grabbing the records for a queue’s columns (number, source, subject, etc.).

You can optimize your database and/or dedicate more resources to MySQL. If you can’t do so then you can simply archive (delete) data so your database doesn’t struggle so much.

Cheers.

sbumba

Thanks for the help and the hint due to new thread. I thought that since this is a sub-forum for osTicket 1.14.x (End of Life) and I have set up a new server with osTicket 1.18.1, it would be better to use a new thread in the sub-forum for version 1.18.x, as the issue also exists on the new server. Sorry for that.

I created a new vServer from scratch, 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.

I am unsure what might be causing the SQL queries in the ticket system to suddenly take so long from one day to the next. No other tasks are running on this (and the old) server that could consume CPU resources. I read in this forum about tuning the SQL database and applied several suggested optimizations, but without success.

The new system details are as follows:

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
The database size is approximately 9 GB.
innodb_buffer_pool_size = 12G

Is this too little for osTicket? I'm surprised that the SQL queries, which previously took only a few seconds, now sometimes take several minutes from one day to the next. This shouldn't be happening.

Does anyone else have any ideas on what I can test or check?

KevinTheJedi

sbumba

Just to let you know, recently (in the past year or two) I've personally seen that MySQL is significantly better/faster out of the box vs MariaDB. With that being said, whichever provider you go with, you should probably set the optimizer_search_depth setting to 0 and retest. If that works be sure to persist the change in your my.cnf file. What this does is let the database server choose the most optimal plan itself.

Cheers.

KevinTheJedi

sbumba

Also, you should always read the requirements of the software before installing/upgrading. You would've seen that v1.18.1 only supports PHP 8.1-8.2. You are running 8.3 which will cause issues down the line. You should downgrade to 8.2 to avoid later issues. PHP 8.3 support is coming in v1.18.2 so stay tuned for that.

Cheers.

sbumba

KevinTheJedi

Thank you so much for your assistance.
optimizer_search_depth=0 did the trick. Now, the longest run time for an SQL query is about 6 seconds.

In reply to

You would've seen that v1.18.1 only supports PHP 8.1-8.2.

You are correct. I overlooked this. I just set up a quick and dirty test environment. I will wait until osTicket supports PHP 8.3 and then create a fresh server setup for production.