We are using the osTicket application that's deployed on the Xampp server on a Windows machine hosted in the Azure cloud. As soon as the Agent logs-in to the OSTicket portal, the CPU exceeds 13%. The mysqld.exe process consumes so much CPU and multiple mysql queries gets triggered. Sometimes the CPU hits to 100% even if there is not much load. Do you know what could be the reason for this problem?

more details are shown below -

XAMPP version: 7.3.15
osTicket: v1.12
SQLs:

| 4323 | root | localhost:60483 | osticket | Query | 2064 | Sending data | SELECT COUNT(DISTINCT CASE WHEN A1.topic_id = 13 THEN A1.ticket_id END) AS q18, COUNT(DISTINCT | 0.000 |
| 4325 | root | localhost:60485 | osticket | Query | 2063 | Sending data | SELECT COUNT(DISTINCT CASE WHEN A1.topic_id = 13 THEN A1.ticket_id END) AS q18, COUNT(DISTINCT | 0.000 |
| 4327 | root | localhost:60487 | osticket | Query | 2056 | Sending data | SELECT COUNT(DISTINCT CASE WHEN A1.topic_id = 13 THEN A1.ticket_id END) AS q18, COUNT(DISTINCT | 0.000 |
| 4331 | root | localhost:60493 | osticket | Query | 2023 | Sending data | SELECT COUNT(DISTINCT CASE WHEN A1.topic_id = 13 THEN A1.ticket_id END) AS q18, COUNT(DISTINCT | 0.000 |

My guess is that the q18 (which is a non-standard custom queue) is doing something that it shouldn't be. It looks like it is counting (distinct) all your tickets for some reason each time someone views the queue. Also 1.12 is pretty old at this point. You might want to consider upgrading to current. (1.14.2)

Thank you for the quick response. I would consider upgrading to the latest version. Before that, I want to bring one more point into your attention. We have several agents who use the Ticket System. But, they all use the same administrator account details for login and Its obvious that multiple SQL queries will fire. Do you think could be the reason for this performance issue ? Also, I've posted complete query for your reference.

SELECT COUNT(DISTINCT CASE WHEN A1.topic_id = 13 THEN A1.ticket_id END) AS q18, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A1.dept_id = 4 AND A8.value REGEXP '\"?3(\"|,|$)' THEN A1.ticket_id END) AS q20, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A2.id IN (1) AND A1.topic_id = 13 AND B0.value = 'Fiber Internet Gig' THEN A1.ticket_id END) AS q21, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A1.dept_id = 4 AND A1.created BETWEEN '2020-06-28 07:00:00' AND '2020-06-29 06:59:59' THEN A1.ticket_id END) AS q22, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A1.dept_id = 4 AND A1.closed BETWEEN '2020-06-28 07:00:00' AND '2020-06-29 06:59:59' THEN A1.ticket_id END) AS q23, COUNT(DISTINCT CASE WHEN A2.id IN (1) AND A1.topic_id = 12 THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A2.id IN (1) THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN A2.id IN (1) AND A1.dept_id = 4 AND B1.value IS NOT NULL THEN A1.ticket_id END) AS q27, COUNT(DISTINCT CASE WHEN A1.topic_id = 13 AND A1.topic_id = 13 AND A2.id IN (1) AND A1.dept_id = 4 AND B1.value IS NOT NULL AND NOT A8.value IS NOT NULL THEN A1.ticket_id END) AS q28, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.dept_id = 4 AND B1.value IS NOT NULL THEN A1.ticket_id END) AS q30, COUNT(DISTINCT CASE WHEN A2.state = 'open' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A1.staff_id IN (42) AND A2.state = 'open' AND A1.staff_id IN (42) AND A2.state = 'open' AND A1.staff_id IN (42) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-06-28 07:00:00' AND '2020-06-29 06:59:59' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A1.staff_id IN (42) AND A2.state = 'open' AND A1.staff_id IN (42) AND A2.state = 'open' AND NOT A1.staff_id IN (42) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-06-27 07:00:00' AND '2020-06-28 06:59:59' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN A1.staff_id IN (42) AND A2.state = 'open' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-06-22 07:00:00' AND '2020-06-29 06:59:59' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-06-01 07:00:00' AND '2020-07-01 06:59:59' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-04-01 07:00:00' AND '2020-07-01 06:59:59' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 08:00:00' AND '2021-01-01 07:59:59' THEN A1.ticket_id END) AS q14 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_department A6 ON (A4.object_type = 'D' AND A4.object_id = A6.id) JOIN ost_form_entry A7 ON (A7.object_type = 'T' AND A1.ticket_id = A7.object_id) LEFT JOIN ost_form_entry_values A8 ON (A7.id = A8.entry_id) LEFT JOIN ost_form_entry_values B0 ON (A7.id = B0.entry_id) LEFT JOIN ost_form_entry_values B1 ON (A7.id = B1.entry_id) WHERE (A2.state = 'open' AND (A1.staff_id = 42 OR A5.staff_id = 42)) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 4) OR A6.id IN (1, 4)))

We tried upgrading to latest version. But, still same issue.

@sm1013t

You have a few options. You can cut down on the number of queues to shorten the COUNT() queries that run every couple of minutes or so. Similarly you can cut down on some of the more complex queue criteria so there aren't so many JOINS(), etc. You can archive some data so the queries have less tickets/data to sift through. However, the recommended option is to configure and fine-tune MySQL to perform better. There are many guides online that explain how to fine-tune MySQL to get peak speed and performance.

With this being said, we are always working to better the software and improve performance across the board.

Cheers.

Thanks for reply. Yeah, we already thought about tuning the queries since it has complex joins. But, this query is getting generated dynamically from osTicket code. We trying to analyze the code on the other hand.

Write a Reply...