Since some days, we experience issues with long timeouts when browsing on the osticket frontend (1-2minutes). together with the timeouts, the ticket counts disappears from the menu. top level ticket count is disabled.

during that, the server runs 100% CPU on all cores for the "SELECT COUNT(DISTINCT..." queries which we think count the tickets in each queue. sometimes multiple of these queries run parallel and run from 45 to 200 seconds.

after a lot of try and error with figuring out which search criteria in which queue might cause these issues, we found out that it is probably caused by fields from custom forms.
When there is a custom form involved a LEFT JOIN to ost_form_entry_values is added to the count query like this:
LEFT JOIN ost_form_entry_values B2 ON (B1.id = B2.entry_id)
As long as there is only one of those the query runs fine in < 1s.
We couldn't figure out when exactly but sometimes a new queue or a new criteria in a queue adds the same LEFT JOIN again and just names it differently until we end up with something like this:
LEFT JOIN ost_form_entry_values B2 ON ( B1.id = B2.entry_id)
LEFT JOIN ost_form_entry_values B3 ON ( B1.id = B3.entry_id)
LEFT JOIN ost_form_entry_values B4 ON ( B1.id = B4.entry_id)

With 2 of those the query runs for about 3s and with 3 it starts to become unusable with about 120s. If we manually remove those additional joins and rename the referenced fields the query again runs in under 1s.

As a reference I attached full queries with the slow one running over 120s and the "fast" one 3s. I wasn't allowed to upload text files, so PDF it is đŸ™‚

CentOs 7
osticket version: 1.12.2
php 7.3.10
MariaDb 10.3.18

Probably similar issue as here: https://forum.osticket.com/d/95235-slow-staff-login-after-adding-ticket-queue

fast.pdf
63kB
slow.pdf
64kB
    3 months later

    yes. we have still the same behaviour
    **
    **

    Out of curiosity how many tickets do you have?
    and
    When you say "the server runs 100% CPU on all cores" how many cores and what speed are they?

    a month later

    I have the exact same issue...

    I dont think we have a lot of tickets. its this many
    But I have over 75 agents logged in and its killing OSticket. Same issue....its taking multiple seconds to open a ticket and view its details.

    Also if I make a search, i invariably end up with an "internal server error" message. This was all great with OSticket 1.10, we upgraded from 1.10 to 1.12 then to 1.14 ....love the addition of new features in 1.14 but the slowness is killing me

    I've noticed that its very slow when many agents are logged in. Right now I have over 35 agents logged in and the queries are down to a crawl it seems. Any tips/suggestions please?

    35 doesnt seem like a a lot of agents for MySQL queries at all

    @DA7276727

    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.

    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

    5 months later

    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.

    9 months later

    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

    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

    @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.

    5 months later

    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?

    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.

    @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:

    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();
//        }

      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.

        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

        @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):

        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.

        4 months later

        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

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

        2 months later

        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.

            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.

              4 months later

              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

              21 days later

              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.

                  9 months later

                  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.

                    6 months later

                    For example the statistics should count only on user/agent login once.
                    And till he login again he sees the same statistical data.
                    If user/agent wana to update statistical data he should to login again which should not be so difficult for user/agent to login few times per day (when new stats are needed)

                      mLipok

                      That’s how it works. Counts are calculated for each Agent on login. They are cached for 5 minutes then recalculated afterwards. To refresh counts immediately you can logout and back in.

                      Cheers.

                        So. to be more specific.
                        How to stop the recalculation after each passed 5 minutes ?

                          mLipok

                          Modify the codebase to remove the recalculation. Be careful though as you may disable it on login as well.

                          Cheers.

                            Write a Reply...