Ticket view is very slow. Queue / Query related

KevinTheJedi

Check your server resource usage and provide more resources where needed. You might need a better/more CPUs or more RAM.

We are looking into how we can improve count query performance but until then, you'll just need to make your server handle the load.

Cheers.

DA7276727

Thanks Kevin, so this is expected behavior?
is it the number of concurrent users or the ticket count that I need to curtail while we wait for you to do optimizations.
I'll give it more resources nonetheless....

Thanks for your response

DA7276727

Hi any thoughts on when this could be addressed? @ntozier ?

mLipok

I think recently I post very similar problem on this forum or on github
Check my post and if you found this, check if this is related to your case.

riccardo_centropaghe

how to modify the code for disabling the count of the queue?
this is killing osticket, I have a lot of ticket and I lot of queues

mLipok

Take a look:
https://YoursURL/scp/settings.php?t=tickets

ntozier

What mLipok is saying is that you do not need to edit the code to achieve this. Simply go to:
Admin panel -> Settings -> Tickets
Locate Top-Level Ticket Counts
uncheck it.
Click Save Changes

KevinTheJedi

@riccardo_centropaghe

You should look at converting all MyISAM tables to InnoDB and removing keywords from all the queues (even personal queues for all agents). Once you do this they should be fast again.

Cheers.

fnixt

Hello boys,
Same problem here, it happened to me several times without realizing that the problem had to do with the queues. I tried several hosts and I have reinstalled osticket several times. unfortunately the queues are one of the last things that one configures since in general the help topics and their forms are generated first.
I was very frustrated, because every time I had the system ready to go into production I found that the database stopped working, and the response times became terrible. in fact, every time this problem happened I lost access to the database either via phpmyadmin or via dbeaver. even after deleting the osticket files the query kept looping. Two different hosts blocked my service because the problem was generating a very high response time throughout the server. I have not found a solution yet, but I can confirm that the increase in response time has a direct relationship with the queues. now i'm scared to add queues with custom form fields. It's a shame because it is a super useful tool, and I would encourage you to say necessary to use. hopefully they can identify the problem and find a solution.

thanks for this thread, it helped me not to feel alone and understand that the problem has nothing to do with a personal failure to implement the system, but a problem that affects us all.

I would like to know how it is that in the self-hosted version that you can hire, you do not experience these problems, and if so, how do you deal with them?

fnixt

For those with the same problem, I recommend making a backup of the database before adding every new queue, in order to identify which is the custom field that generates the problem and avoid it. this way you can restore the last working state without losing big ammount of information. unfortunately when this problem happens in general the database stops responding and the cpu usage skyrockets until the instance or service is restarted, or the sql queries are stopped manually, which I cannot manage in general in the standard hosting with cpanel.

Another tip is to have a test database to make the queues without putting the production system at risk, so that if it fails, it will do so in the test environment, and if it works we will know that it is safe to replicate in the environment. productive.

I insist it is not a problem of server hardware capacity, since I tried with several hosting and also with cloud services such as aws and the problem was the same regardless of the hardware. it is a loop of death that knocks down even the most powerful server until the cycle is cut.

I also take the opportunity to tell you that I also experience the fairly common problem that the ticket count throws me ghost numbers, that is, it throws me a number that does not correspond to the result it shows when I select the indicated queue. I consider that both problems must be related.

KevinTheJedi

@fnixt

I do not agree. While the queues and counts can be optimized a little better, the problem is the host’s hardware and services. A good example can be found in a recent thread where the OP was having slowness when loading queues. He was using Debian 11 and when he switched o Debian 10 he has no issues at all with the same config and everything. Another example is a customer was having super slowness on the queues, so we converted the tables to InnoDB, upgraded to MySQL 8, boosted the server specs, made a few MySQL config optimizations and the issue was resolved. The common factor in all the cases comes down to the host machine.

You can improve slightly on the code side by editing the following file:

https://github.com/osTicket/osTicket/blob/develop/include/class.search.php#L994-L999

You need to remove/comment out the try part and just leave the catch like:

//        try {
//            $counts = array_merge($counts, $query->values()->one());
//        }  catch (Exception $ex) {
            foreach ($queues as $q)
                $counts['q'.$q->getId()] = $q->getTotal();
//        }

fnixt

il try the code mod and come back to comment.
anyway i cant find a clear way to convert to InnoDB, searched the forum finded some threads but, cant realize it.
if you know about some step by step to realize it i will appreciate it a lot.

as allways, thanks a lot KevinTheJedi you are great and your labor is taking the food to ours mouths.

sorry about my english it's 1am and im not using translator.

eteich

KevinTheJedi

Well, depending on how crazy the agents setup these queues, no amount of server config/resources are going to be able to mitigate the potential insane query that is generated by that all in one approach. I mean, not trying to be mean, but this is just bad design. It's giving the end user more than enough rope to hang themselves and the rest of the team with.

Look at the query @fnixt posted.

There's no defending that mess... the patch needs to be made official.

nandi96

KevinTheJedi
It it a good solution, thank you!
But: After comment out the try part, all agents see all ticket counters (not just tickets what the agent has access to), even where they don't see tickets in the queue.

Michal-Zgr

KevinTheJedi
Your solution works perfectly. I have many queries to manage tickets with my Team and now I have instant result. Previously, I waited up to 2 min.

fnixt

Something else, the db admin gived me this query as the one wich gives the problem (i restored the failing DB and tryed to login and the loop comed back again). they finded this manually and gived me this:

SELECT COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q16, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?81(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?82(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?83(\"|,|$)|\"?84(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q27, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q28, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q29, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 19 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q30, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 15 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q31, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 20 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q32, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 16 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q33, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 22 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q34, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?19(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q35, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?18(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q36, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q37, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?17(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q38, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?11(\"|,|$)|\"?12(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q39, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?9(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q40, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND B3.address IS NOT NULL AND A1.topic_id = 23 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q41, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 18 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q42, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 17 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q43, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?10(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q44, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?13(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q45, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?14(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q46, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?15(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q47, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?16(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q48, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B7.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q49, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q50, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?92(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q51, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?93(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q52, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?94(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q53, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?95(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q54, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?96(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q55, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND C0.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q56, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND C1.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q57, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q58, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?297(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q59, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?299(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q60, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?300(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q61, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?301(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q62, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?302(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q63, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?298(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q64, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-27 22:00:00' AND '2021-09-28 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-26 22:00:00' AND '2021-09-27 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-26 22:00:00' AND '2021-10-03 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-08-31 22:00:00' AND '2021-09-30 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-06-30 22:00:00' AND '2021-09-30 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-12-31 23:00:00' AND '2021-12-31 22:59:59' AND A3.object_type = 'T' 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_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.id = B3.user_id) JOIN ost_form_entry B4 ON (B4.object_type = 'T' AND A1.ticket_id = B4.object_id) LEFT JOIN ost_form_entry_values B5 ON (B4.id = B5.entry_id) LEFT JOIN ost_form_entry_values B6 ON (B4.id = B6.entry_id) LEFT JOIN ost_form_entry_values B7 ON (B4.id = B7.entry_id) LEFT JOIN ost_form_entry_values B8 ON (B4.id = B8.entry_id) LEFT JOIN ost_form_entry_values C0 ON (B4.id = C0.entry_id) LEFT JOIN ost_form_entry_values C1 ON (B4.id = C1.entry_id) LEFT JOIN ost_form_entry_values C2 ON (B4.id = C2.entry_id) WHERE ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1)) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 3, 4, 5) OR B0.id IN (1, 3, 4, 5))) OR A6.object_type = 'C' AND B1.id IN (1, 3, 4, 5))

my queues are not much (about 10) and the filter criteria are really simple, every one checks one field with one condition.

hugs for everyone

KevinTheJedi

@fnixt

anyway i cant find a clear way to convert to InnoDB, searched the forum finded some threads but, cant realize it.
if you know about some step by step to realize it i will appreciate it a lot.

There are many guides online. Here is one from MySQL Documentation (scroll down to Convert an Existing Table section):

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

You will need to repeat this step for every table that is MyISAM.

they finded this manually and gived me this:

Yea, the patch I suggested above will break up the query into individual queries for each queue.

Cheers.

rolek

Hi!
We're experiencing the same problem as @fnixt and I too am sure that this is not a resource problem. Currently evaluating the patch above (thanks!) and I'll report back about the results.
Best regards, Roel

ntozier

@rolek I would advise that you that you star your own thread with your specific environment information and log errors, etc.

KevinTheJedi

eteich

We know slower systems and non-optimized systems can have issues which is why we have an optimization on our todo list (to account for the lower end systems).

Cheers.

marco-b

Hi. I am the initial poster.

Nobody really looked at my output/findings i think.

i think you can clearly see that there is a inefficient way of the query when having multiple criteria.
i am not sure this is fixed. we just delete agent custom save searches when we experience the issue.

@ntozier cpu/memory is not an issue. we have big server farms and selfhost and can assign immense ammount of cpu/cores/memory on SSD arrays.... bad query kills every hardware.

our environment only has 5 agents logged in normaly.
tickets something over 12k

i dont want to complain! we love osticket! thanks to EVERYONE putting work in this great product

if we struggle, we struggle with the fixed width of osticket screen (i think something below 960px)
we are in IT since 1997, so we know how hard "simple" things can be to change if gone that road at the beginning...

if anyone had a nice or elegant way to "fix" that limitation, we really appreciate a hint. or we also pay for someone willing to do some work on that. we tried some osticket overlays like osticketawesome.com but had other issues with these.

we saw in 2.0 that should be fixed :-) thanks to everyone involved !

stay safe!
Marco

KevinTheJedi

nandi96

Then you must have a separate issue.

Cheers.