Good day. Recently, I started to have osTicket lags, the site simply did not open for a while. I didn't attach much importance to this, but this problem began to appear over and over again. I checked the logs and found Sql queries that take a long time to execute. Apparently the site lags because of this. I attach my logs, is there a possibility to somehow restore the system's performance?

SELECTidFROM ost_file A1 WHERE (A1.ft = 'T' AND A1.created < NOW() - INTERVAL 1 DAY) AND NOT EXISTS (SELECT id FROM ost_attachment A2 WHERE A1.id= A2.file_id`)
88227 a141427_support
localhost a141427_support

Query 00:14:57 DELETE ost_attachment.* FROM ost_attachment JOIN ost_draft A1 ON (ost_attachment.type = 'D' AND ost_attachment.object_id = A1.id) WHERE A1.namespace LIKE 'ticket.\%.16870%'
88235 a141427_support
localhost a141427_support

Query 00:13:29 SELECT A3.staff_id AS lock__staff_id, A1.staff_id AS staff_id, A1.isoverdue AS isoverdue, A1.team_id AS team_id, A1.ticket_id AS ticket_id, A1.number AS number, A4.subject AS cdata__subject, A6.address AS user__default_email__address, A1.source AS source, A7.priority_color AS cdata__:priority__priority_color, A7.priority_desc AS cdata__:priority__priority_desc, A1.status_id AS status_id, A8.name AS status__name, A8.state AS status__state, A1.dept_id AS dept_id, B0.name AS dept__name, A5.name AS user__name, A1.lastupdate AS lastupdate, A1.isanswered AS isanswered, B1.firstname AS staff__firstname, B1.lastname AS staff__lastname, B2.name AS team__name, (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 collab_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 attachment_count, (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 FROM ost_ticket A1 JOIN (SELECT Q7.ticket_id FROM ost_ticket Q7 JOIN ost_ticket_status Q8 ON (Q7.status_id = Q8.id) WHERE (Q7.staff_id = 16 OR (Q7.staff_id = 0 AND Q7.team_id > 0)) AND Q8.state = 'open' AND ((Q8.state = 'open' AND (Q7.staff_id = 16 OR Q7.team_id IN (2))) OR Q7.dept_id IN ('3')) ORDER BY Q7.lastupdate DESC LIMIT 21) A2 LEFT JOIN ost_lock A3 ON (A1.lock_id = A3.lock_id AND A3.expire > NOW()) LEFT JOIN ost_ticket__cdata A4 ON (A1.ticket_id = A4.ticket_id) JOIN ost_user A5 ON (A1.user_id = A5.id) LEFT JOIN ost_user_email A6 ON (A5.default_email_id = A6.id) LEFT JOIN ost_ticket_priority A7 ON (A4.priority = A7.priority_id) JOIN ost_ticket_status A8 ON (A1.status_id = A8.id) JOIN ost_department B0 ON (A1.dept_id = B0.id) LEFT JOIN ost_staff B1 ON (A1.staff_id = B1.staff_id) LEFT JOIN ost_team B2 ON (A1.team_id = B2.team_id) WHERE A1.ticket_id = A2.ticket_id GROUP BY A3.staff_id, A1.staff_id, A1.isoverdue, A1.team_id, A1.ticket_id, A1.number, A4.subject, A6.address, A1.source, A7.priority_color, A7.priority_desc, A1.status_id, A8.name, A8.state, A1.dept_id, B0.name, A5.name, A1.lastupdate, A1.isanswered, B1.firstname, B1.lastname, B2.name ORDER BY A1.lastupdate DESC
88238 a141427_support
localhost a141427_support

Query 00:13:19 SELECT A3.staff_id AS lock__staff_id, A1.staff_id AS staff_id, A1.isoverdue AS isoverdue, A1.team_id AS team_id, A1.ticket_id AS ticket_id, A1.number AS number, A4.subject AS cdata__subject, A6.address AS user__default_email__address, A1.source AS source, A7.priority_color AS cdata__:priority__priority_color, A7.priority_desc AS cdata__:priority__priority_desc, A1.status_id AS status_id, A8.name AS status__name, A8.state AS status__state, A1.dept_id AS dept_id, B0.name AS dept__name, A5.name AS user__name, A1.lastupdate AS lastupdate, A1.isanswered AS isanswered, B1.firstname AS staff__firstname, B1.lastname AS staff__lastname, B2.name AS team__name, (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 collab_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 attachment_count, (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 FROM ost_ticket A1 JOIN (SELECT Q7.ticket_id FROM ost_ticket Q7 JOIN ost_ticket_status Q8 ON (Q7.status_id = Q8.id) WHERE Q8.state = 'open' AND ((Q8.state = 'open' AND Q7.staff_id = 20) OR Q7.dept_id IN ('3')) ORDER BY Q7.created DESC LIMIT 38) A2 LEFT JOIN ost_lock A3 ON (A1.lock_id = A3.lock_id AND A3.expire > NOW()) LEFT JOIN ost_ticket__cdata A4 ON (A1.ticket_id = A4.ticket_id) JOIN ost_user A5 ON (A1.user_id = A5.id) LEFT JOIN ost_user_email A6 ON (A5.default_email_id = A6.id) LEFT JOIN ost_ticket_priority A7 ON (A4.priority = A7.priority_id) JOIN ost_ticket_status A8 ON (A1.status_id = A8.id) JOIN ost_department B0 ON (A1.dept_id = B0.id) LEFT JOIN ost_staff B1 ON (A1.staff_id = B1.staff_id) LEFT JOIN ost_team B2 ON (A1.team_id = B2.team_id) WHERE A1.ticket_id = A2.ticket_id GROUP BY A3.staff_id, A1.staff_id, A1.isoverdue, A1.team_id, A1.ticket_id, A1.number, A4.subject, A6.address, A1.source, A7.priority_color, A7.priority_desc, A1.status_id, A8.name, A8.state, A1.dept_id, B0.name, A5.name, A1.lastupdate, A1.isanswered, B1.firstname, B1.lastname, B2.name ORDER BY A1.created DESC
88239 a141427_support
localhost a141427_support

Query 00:13:18 SELECT A3.staff_id AS lock__staff_id, A1.staff_id AS staff_id, A1.isoverdue AS isoverdue, A1.team_id AS team_id, A1.ticket_id AS ticket_id, A1.number AS number, A4.subject AS cdata__subject, A6.address AS user__default_email__address, A1.source AS source, A7.priority_color AS cdata__:priority__priority_color, A7.priority_desc AS cdata__:priority__priority_desc, A1.status_id AS status_id, A8.name AS status__name, A8.state AS status__state, A1.dept_id AS dept_id, B0.name AS dept__name, A5.name AS user__name, A1.lastupdate AS lastupdate, A1.isanswered AS isanswered, B1.firstname AS staff__firstname, B1.lastname AS staff__lastname, B2.name AS team__name, (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 collab_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 attachment_count, (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 FROM ost_ticket A1 JOIN (SELECT Q7.ticket_id FROM ost_ticket Q7 JOIN ost_ticket_status Q8 ON (Q7.status_id = Q8.id) WHERE Q8.state = 'open' AND ((Q8.state = 'open' AND Q7.staff_id = 20) OR Q7.dept_id IN ('3')) ORDER BY Q7.created DESC LIMIT 38) A2 LEFT JOIN ost_lock A3 ON (A1.lock_id = A3.lock_id AND A3.expire > NOW()) LEFT JOIN ost_ticket__cdata A4 ON (A1.ticket_id = A4.ticket_id) JOIN ost_user A5 ON (A1.user_id = A5.id) LEFT JOIN ost_user_email A6 ON (A5.default_email_id = A6.id) LEFT JOIN ost_ticket_priority A7 ON (A4.priority = A7.priority_id) JOIN ost_ticket_status A8 ON (A1.status_id = A8.id) JOIN ost_department B0 ON (A1.dept_id = B0.id) LEFT JOIN ost_staff B1 ON (A1.staff_id = B1.staff_id) LEFT JOIN ost_team B2 ON (A1.team_id = B2.team_id) WHERE A1.ticket_id = A2.ticket_id GROUP BY A3.staff_id, A1.staff_id, A1.isoverdue, A1.team_id, A1.ticket_id, A1.number, A4.subject, A6.address, A1.source, A7.priority_color, A7.priority_desc, A1.status_id, A8.name, A8.state, A1.dept_id, B0.name, A5.name, A1.lastupdate, A1.isanswered, B1.firstname, B1.lastname, B2.name ORDER BY A1.created DESC
88244 a141427_support
localhost a141427_support
Query 00:12:55 UPDATE ost_staff SET lastlogin=NOW() WHERE staff_id=4
88246 a141427_support
localhost a141427_support
Query 00:12:37 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88250 a141427_support
localhost a141427_support
Query 00:11:32 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88264 a141427_support
localhost a141427_support
Query 00:07:55 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88266 a141427_support
localhost a141427_support
Query 00:07:50 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88274 a141427_support
localhost a141427_support
Query 00:06:40 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='muta-gen@mail.ru' OR email='muta-gen@mail.ru')
88277 a141427_support
localhost a141427_support
Query 00:06:26 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='muta-gen@mail.ru' OR email='muta-gen@mail.ru')
88286 a141427_support
localhost a141427_support
Query 00:05:36 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='muta-gen@mail.ru' OR email='muta-gen@mail.ru')
88294 a141427_support
localhost a141427_support
Query 00:03:44 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88296 a141427_support
localhost a141427_support
Query 00:03:33 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88325 a141427_support
localhost a141427_support
Query 00:03:07 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88328 a141427_support
localhost a141427_support
Query 00:02:25 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='e.parysheva@klincity.ru' OR email='e.parysheva@klincity.ru')
88331 postfix
localhost postfix
Sleep 00:00:51
88335 a141427_support
localhost a141427_support
Query 00:00:15 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='muta-gen@mail.ru' OR email='muta-gen@mail.ru')
88336 a141427_support
localhost a141427_support
Query 00:00:12 SELECT backend FROM ost_staff WHERE backend IS NOT NULL AND (username='muta-gen@mail.ru' OR email='muta-gen@mail.ru')

`

I've asked the devs to take a look at these logs.

However, I notice that you haven't told us what your hardware running the site is. Or what the load on your server is. How many tickets do you have? Do you have attachments in the database?

Write a Reply...