OSTicket v1.14.1 (f1e9e88) 400% slower that 1.12-git (bcf1a6f)

rsclmumbai

I have a VM. 1 Core, 2GB RAM.
I have 2 different installations of OSTicket on the same VM.
Side by side comparison of both OST is as below.

Since both installations of OSTicket are on the same server with starkly different load times, my inclination to try and optimize mariadb or php is low.

Is there a known issue with OSTicket 1.14?

How do I go about fixing this?

Thanks

rsclmumbai

DB structure between 1.14 & 1.12 is significantly different.

Following new tables have been introduced in 1.14:

ost_organization__cdata
ost_schedule
ost_schedule_entry
ost_thread_entry_merge

@KevinTheJedi Whenever you get a moment can you pls suggest how to go about debugging the speed issue. Thanks

KevinTheJedi

@rsclmumbai

You can enable MySQL slow query log and see if there are long running queries. Also look at server performance and resource usage to see if it spikes at all.

Cheers.

rsclmumbai

@KevinTheJedi

RAM:
loading scp/index.php | v1.12 | peaks to 30% for a moment & drops back to 0
loading scp/index.php | v1.14 | peaks to 30% for a moment & drops back to 0

CPU:
loading scp/index.php | v1.12 | peaks to 10% for a moment & drops back to 0
loading scp/index.php | v1.14 | peaks to 99% for a 3-7 sec depending on time taken to load page & drops back to 0

SLOW QUERY LOG:

I executed the slow queries manually against both 1.12 & 1.14.
Here are the results.
On 1.14

SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`ticket_pid` AS `ticket_pid`, A1.`flags` AS `flags`, A1.`created` AS `created`, A3.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, A4.`name` AS `user__name`, A3.`priority` AS `cdata__priority`, A5.`firstname` AS `staff__firstname`, A5.`lastname` AS `staff__lastname`, A6.`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, COALESCE(A5.`firstname`, A5.`lastname`, A6.`name`, 'zzz') AS `assignee` FROM `ost_ticket` A1 JOIN (SELECT Q7.`ticket_id`, Z1.relevance AS `relevance` FROM `ost_ticket` Q7 JOIN `ost_ticket_status` Q8 ON (Q7.`status_id` = Q8.`id`) LEFT JOIN `ost_thread` R0 ON (R0.`object_type` = 'T' AND Q7.`ticket_id` = R0.`object_id`) LEFT JOIN `ost_thread_referral` R1 ON (R0.`id` = R1.`thread_id`) LEFT JOIN `ost_staff` R2 ON (R1.`object_type` = 'S' AND R1.`object_id` = R2.`staff_id`) LEFT JOIN `ost_thread` R3 ON (Q7.`ticket_id` = R3.`object_id` AND R3.`object_type` = 'C') LEFT JOIN `ost_thread_referral` R4 ON (R3.`id` = R4.`thread_id`) LEFT JOIN `ost_staff` R5 ON (R4.`object_type` = 'S' AND R4.`object_id` = R5.`staff_id`) LEFT JOIN `ost_department` R6 ON (R1.`object_type` = 'D' AND R1.`object_id` = R6.`id`) LEFT JOIN `ost_department` R7 ON (R4.`object_type` = 'D' AND R4.`object_id` = R7.`id`) JOIN /* large SQL query (3.5 KiB), snipped at 2,000 characters */
/* Affected rows: 0  Found rows: 25  Warnings: 0  Duration for 1 query: 0.422 sec. */

On 1.12

`SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, A7.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B0.`name` AS `user__name`, A7.`priority` AS cdata__priority`, A1.`isanswered` = '0' AS `2GkBZxz`, (SELECT COUNT(R0.`id`) AS `count` FROM `ostjobs_thread` Q7 JOIN `ostjobs_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ostjobs_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 `ostjobs_thread` Q7 JOIN `ostjobs_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ostjobs_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ostjobs_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, B1.`expire` > NOW() AND NOT B1.`staff_id` = 1 AS `_locked`, (SELECT COUNT(R0.`id`) AS `count` FROM `ostjobs_thread` Q7 JOIN `ostjobs_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ostjobs_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs FROM `ostjobs_ticket` A1 JOIN `ostjobs_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `ostjobs_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `ostjobs_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `ostjobs_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `ostjobs_department` A6 ON (A4.`object_type` = 'D' AND A4.`object_id` = A6.`id`) LEFT JOIN `ostjobs_ticket__cdata` A7 ON (A1.`ticket_id` = A7.`ticket_id`) LEFT JOIN `ostjobs_ticket_priority` A8 ON (A7.`priority` = A8.`priority_id`) JOIN `ostjobs_user` B0 ON (A1.`user_id` = B0.`id`) LEFT JOIN `ostjobs_lock` B1 ON (A1.`lock_id` = B1.`lock_id`) WHERE A2.`state`/* large SQL query (2.4 KiB), snipped at 2,000 characters */
/* Affected rows: 0  Found rows: 50  Warnings: 0  Duration for 1 query: 0.109 sec. */

There is indeed a big difference between the execution time for both queries.

I've also attached the slow-query-log file for your reference here:
https://gofile.io/?c=LfL70n

Let me know if you need any additional information which I can provide.

I appreciate your looking into this.

Thanks!!!

KevinTheJedi

rsclmumbai

Yea, we need to look at how we can optimize the COUNT queries but for now you can just either add a better CPU or add another CPU as the slowness is the CPU usage spike.

Note:
It looks like you are running SELECT on a lot more columns in v1.14 than in v1.12. Also, the table names are different so I assume you have two different DBs which confuses me a little.

You can try indexing a few things as well to see if that helps. We have a patch being added to the next release that adds some new indexes so hopefully this helps you a little:

https://github.com/osTicket/osTicket/pull/5362

Cheers.

rsclmumbai

KevinTheJedi

add a better CPU
I believe that may not help as v1.12 is performing better than v1.14

It looks like you are running SELECT on a lot more columns in v1.14 than in v1.12
Both have 5 columns.

It still beats me why v1.12 performs better that v1.14. In the ideal world, it should be the other way round, right?

Thanks for your continued support, appreciate it!!

KevinTheJedi

@rsclmumbai

Your issue with v1.14 is the CPU spike when the COUNT queries run. In the long run we need to optimize our queries. In order to address this in the meantime though you can upgrade your CPU or add another more CPUs. If you CPU is at 99% usage then it makes sense to add more CPUs or upgrade your current one so it can actually handle the load.

It looks like you are running SELECT on a lot more columns in v1.14 than in v1.12
Both have 5 columns.

Right, but in your query examples above you can see a lot more columns be selected from v1.14 than in the v1.12 query. (I'm not talking about the columns in the UI I'm talking about db columns in the query)

Cheers.

rsclmumbai

@KevinTheJedi
Both installations (v1.12 & v1.14) are on the same server.
The question which is bothering me is why should CPU spike when loading list page on v1.14 which it does not on v1.12.

I'm not made any special config changes to v.1.4 (to include more columns). How can I reduce the number of columns looked up, which would surely speed up things?

Thanks

KevinTheJedi

The question which is bothering me is why should CPU spike when loading list page on v1.14 which it does not on v1.12.

As I've said multiple times, we need to optimize our queries...simple as that. I'm just giving you a temp fix until we have done so.

Cheers.