OST Application got hanging

Rakesh_1223

HI KevinTheJedi,
We have recently increased the server resources. However, we are now facing an issue where a specific query is hanging and causing the application to become unresponsive.

Could you please help us in resolving this?
Query:

25629', 'root', 'localhost', '00:24:00', 'SELECT B5.address AS userdefault_emailaddress, B6.subject AS cdatasubject, B4.name AS username, A1.ticket_id AS ticket_id, A3.id AS threadid, A1.flags AS flags, null AS number, COUNT(DISTINCT A1.ticket_id) AS tickets, COUNT(DISTINCT B7.id) AS tasks, COUNT(DISTINCT B8.id) AS collaborators, COUNT(DISTINCT C0.id) AS entries FROM ost_ticket A1 INNER JOIN ost_ticketcdata B6 ON (A1.ticket_id = B6.ticket_id) 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_user B4 ON (A1.user_id = B4.id) Left JOIN ost_user_email B5 ON (B4.default_email_id = B5.id) Left JOIN ost_task B7 ON (A1.ticket_id = B7.object_id) Left JOIN ost_thread_collaborator B8 ON (A3.id = B8.thread_id) Left JOIN ost_thread_entry C0 ON (A3.id = C0.thread_id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST (\'Hi Shubha, As discussed, kindly coordinate with customer as per given remarks shared by CM team.\' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST (\'Hi Shubha, As discussed, kindly coordinate with customer as per given remarks shared by CM team.\' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = \'H\' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND (Z3.object_type = \'T\' OR Z3.object_type = \'C\')) LEFT JOIN ost_ticket Z5 ON (Z1.object_type = \'T\' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = \'U\') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = \'O\') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 WHERE ((A2.state IN (\'open\', \'closed\') AND (A1.staff_id = 141 OR A5.staff_id = 141 OR A6.object_type = \'C\' AND A8.staff_id = 141 OR A1.team_id IN (3, 2) OR B0.team_id IN (3, 2) OR A6.object_type = \'C\' AND B1.team_id IN (3, 2))) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8) OR B2.id IN (1, 2, 4, 5, 6, 7, 8) OR A6.object_type = \'C\' AND B3.id IN (1, 2, 4, 5, 6, 7, 8)) AND A1.ticket_id = Z1.ticket_id GROUP BY B5.address ORDER BY SUM(Z1.relevance) DESC, Z1.relevance LIMIT 25

KevinTheJedi

Rakesh_1223

That’s simply a search query. If your server is still having issues after applying the code changes and increasing resources then your server still simply cannot handle the search query. You can archive some data, or further increase the resources, or go through the MySQL/MariaDB optimization guides.

Cheers.

visham

Rakesh_1223 How many tickets do you have? Are you storing attachments in DB or FileServer?

shreyasgandhi

We are also facing the same issue. Open Ticket Query is taking almost 25-30 Secs to execute.

Also applied and updated all the my.cnf variables [As per RAM Capacity]:

Current Ticket Stats:

This facing very slowness issue. Kindly provide resolution.

KevinTheJedi

shreyasgandhi

See my comment above:

https://forum.osticket.com/d/106936-ost-application-got-hanging/4

Cheers.

shreyasgandhi

KevinTheJedi It works! Thank you @KevinTheJedi!

shreyasgandhi

@KevinTheJedi:

As per my previous comment, it works absolutely fast after the page caching (i.e. After Login). But found that during any user(s) first time login, it was taking 20-25 seconds to load the window.

I have already applied the suggested changes: [https://forum.osticket.com/d/106936-ost-application-got-hanging/4]

Please suggest.

KevinTheJedi

shreyasgandhi

Then it sounds like your server needs more resources and/or you need to update your database configurations to take advantage of more resources. Google MySQL optimization guides and MySQL resource increase guides. You may also want to do the same with your webserver and php.

Cheers.

KevinTheJedi

shreyasgandhi

You can also try this:

https://forum.osticket.com/d/95642-ticket-view-is-very-slow-queue-query-related/18

But the code has been updated since then so the new lines are here:

https://github.com/osTicket/osTicket/blob/develop/include/class.search.php#L1014-L1019

But this is just a bandaid (temporary) “fix”. The true fix is to do what I said above of increasing resources and optimizing the database.

Cheers.

shreyasgandhi

KevinTheJedi : It worked for this case as well. Very much appreciated and thanks again for your help.

Rakesh_1223

HI,

We have increse our server resorces and we got some improvemnet then before. still we have facing some hanging issue in a day 4 to 5 times so please suggest how to over come this and we are facing issue for the below query due to the below query we are facing blockings issue

cdata__subject, B2.name AS user__name, A1.ticket_id AS ticket_id, A3.id AS thread__id, A1.flags AS flags, A1.number AS number, 1 AS tickets, COUNT(DISTINCT B5.id) AS tasks, COUNT(DISTINCT B6.id) AS collaborators, COUNT(DISTINCT B7.id) AS entries FROM ost_ticket A1 INNER JOIN ost_ticket__cdata B4 ON (A1.ticket_id = B4.ticket_id) 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_user B2 ON (A1.user_id = B2.id) Left JOIN ost_user_email B3 ON (B2.default_email_id = B3.id) Left JOIN ost_task B5 ON (A1.ticket_id = B5.object_id) Left JOIN ost_thread_collaborator B6 ON (A3.id = B6.thread_id) Left JOIN ost_thread_entry B7 ON (A3.id = B7.thread_id) WHERE ((A2.state IN ('open', 'closed') AND (A1.staff_id = 130 OR A5.staff_id = 130 OR A6.object_type = 'C' AND A8.staff_id = 130)) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8) OR B0.id IN (1, 2, 4, 5, 6, 7, 8) OR A6.object_type = 'C' AND B1.id IN (1, 2, 4, 5, 6, 7, 8)) AND A1.number LIKE 'test@example.com%' GROUP BY A1.ticket_id ORDER BY A1.number ASC) UNION ALL (SELECT B3.address AS user__default_email__address, B4.subject AS cdata__subject, B2.name AS user__name, A1.ticket_id AS ticket_id, A3.id AS thread__id, A1.flags AS flags, null AS number, COUNT(DISTINCT A1.ticket_id) AS tickets, COUNT(DISTINCT B5.id) AS tasks, COUNT(DISTINCT B6.id) AS collaborators, COUNT(DISTINCT B7.id) AS entries FROM ost_ticket A1 INNER JOIN ost_ticket__cdata B4 ON (A1.ticket_id = B4.ticket_id) 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_user B2 ON (A1.user_id = B2.id) Left JOIN ost_user_email B3 ON (B2.default_email_id = B3.id) Left JOIN ost_task B5 ON (A1.ticket_id = B5.object_id) Left JOIN ost_thread_collaborator B6 ON (A3.id = B6.thread_id) Left JOIN ost_thread_entry B7 ON (A3.id = B7.thread_id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('test@example.com' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('test@example.com' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND (Z3.object_type = 'T' OR Z3.object_type = 'C')) LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 WHERE ((A2.state IN ('open', 'closed') AND (A1.staff_id = 130 OR A5.staff_id = 130 OR A6.object_type = 'C' AND A8.staff_id = 130)) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8) OR B0.id IN (1, 2, 4, 5, 6, 7, 8) OR A6.object_type = 'C' AND B1.id IN (1, 2, 4, 5, 6, 7, 8)) AND A1.ticket_id = Z1.ticket_id GROUP BY B3.address ORDER BY Z1.relevance  LIMIT 25) LIMIT 25

KevinTheJedi

Rakesh_1223

Upgrade to v1.18.3, clear all cache/cookies/sessions, restart webserver/php/php-fpm, and retest. We merged a patch that moved the CDATA joins to the top of the queries that should improve 10 fold or more.

Cheers.