AgentTicketQueue slow

Moderator: crythias

Post Reply
Laurens
Znuny newbie
Posts: 22
Joined: 26 Apr 2016, 08:23
Znuny Version: 5.0.4
Real Name: Laurens Van Acker
Company: Myriade N.V.

AgentTicketQueue slow

Post by Laurens »

Hi

Our AgentTicketQueue is really slow for all queues with more then 1 ticket. When I fetch the page for queues with no tickets or just 1 ticket then it's fast. So the page time is linear with the number of tickets in the page. All other pages are acceptable from speed.

It looks like it's querying something for each ticket. When I do a query log I do see that I get SELECT queries for each customer in that view. Will this mean that I have 31 queries for a AgentTicketQueue with 30 tickets?

OTRS version: CE Free 6 patch level 8
LDAP disabled, just a local user and agent database
163 000 tickets in the database, but only 200 active (open), most tickets are archived.
Kernel::System::Ticket::IndexAccelerator::StaticDB
Kernel::System::Ticket::ArticleSearchIndex::RuntimeDB (most tickets are archived)
Kernel::System::Ticket::ArticleStorageDB (there are no attachments)

My.cnf of MariaDB:

Code: Select all

[mysqld]
open_files_limit = 8192
max_connections = 2000

tmp_table_size = 1024M
max_heap_table_size = 1024M
table_cache = 512

slow_query_log
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes

query_cache_type = 1
query_cache_limit = 256K
query_cache_size = 512M
Apache conf (relevant modified part to get it working on root directory:

Code: Select all

Alias /otrs-web/ "/opt/otrs/var/httpd/htdocs/"
ScriptAlias / "/opt/otrs/bin/cgi-bin/"

<IfModule mod_perl.c>
    Perlrequire /opt/otrs/scripts/apache2-perl-startup.pl (added MySQL preloading to this)

    PerlModule Apache2::Reload
    PerlInitHandler Apache2::Reload

    <Location /*.pl>
        ErrorDocument 403 /otrs/index.pl
        SetHandler  perl-script
        PerlResponseHandler ModPerl::Registry
        Options +ExecCGI
        PerlOptions +ParseHeaders
        PerlOptions +SetupEnv

        <IfModule mod_version.c>
            <IfVersion < 2.4>
                Order allow,deny
                Allow from all
            </IfVersion>
            <IfVersion >= 2.4>
                Require all granted
            </IfVersion>
        </IfModule>
        <IfModule !mod_version.c>
            Order allow,deny
            Allow from all
        </IfModule>
    </Location>

    # mod_perl2 options for GenericInterface
    <Location /nph-genericinterface.pl>
        PerlOptions -ParseHeaders
    </Location>

</IfModule>
2018-07-02 16_24_00-Performance Log - Admin - OTRS 6.png
Slow query log of MariaDB only shows stuff from background tasks (Daemon, communication table writes, mail queue stuff, etc..) nothing during the loading of the AgentTicketQueue.
You do not have the required permissions to view the files attached to this post.
Reedu
Znuny newbie
Posts: 29
Joined: 11 Mar 2013, 15:08
Znuny Version: Znuny 6.1.2
Real Name: Adam

Re: AgentTicketQueue slow

Post by Reedu »

Hello Laurens
Have you found any solution? We've got similiar problem.

I've already rebuild indexes (Escalation, Queue, Tickets), deleted cache, rebuild config on our test machine.
It's strange, but we've got some queues where AgentTicketQueue is showing from 100 to 200 open ticket without any problem.
Yet our main queue (around 100 open tickets) with four subqueue is jus so ... slow.

I've tried to debug what's happening and it seems that you're right.
Each ticket is checked for last article (different methods for customer/agent/other ... ), then it's looking for cached data (if it's not cached DB is called) and doing some math to calculate escalation times.

//added
Testing: OTRS ((Community Edition )) 6.0.11 - upgraded OTRS 5 (free).
OS: Debian 8.6
DB: PostgreSQL 9.4.9

On test machine we didn't got problem with indexes on DB as stated in
viewtopic.php?f=62&t=38448&p=156580#p156580
yet we had problem with migrating scripts - Bug#13623 now solved in 6.0.12

performance.png
QueueID=26 has more than 100 open tickets
QueueID=14 has only 20 open tickets
QueueID=5 has more than 100 open tickets

I've already mounted /opt/otrs/var/tmp on ramdisk - yet "slowness" still persist
You do not have the required permissions to view the files attached to this post.
obione85
Znuny newbie
Posts: 13
Joined: 30 Nov 2018, 12:44
Znuny Version: 6.0.14

Re: AgentTicketQueue slow

Post by obione85 »

have you been able to resolve this?
same problem here :(
OTRS 6.0.14 (test/live) on CentOS7 with MySQL database (MariaDB)
Wolfgangf
Znuny ninja
Posts: 1029
Joined: 13 Apr 2009, 12:26
Znuny Version: 6.0.13
Real Name: Wolfgang Fürtbauer
Company: PBS Logitek GmbH
Location: Pinsdorf

Re: AgentTicketQueue slow

Post by Wolfgangf »

I have the same problem;
after a long debugging .. it's the escalation time calculation in <otrspath>/Kernel/System/Datetime.pm sub Delta which runs up to 5 seconds per ticket
in my case 35 tickets x 5 sceconds = ~ approx 2 minutes on navigating through the pages

1) I made a copy of Ticket.pm to /opt/otrs/Custom/Kernel/System/Ticket.pm and commented out the delta function
which of course has the drawback that you will not be able to use that column but execution time is dramatically better

Code: Select all

2464         # ticket is overtime ($TimeTillEscalation < 0)
2465         else {
2466             my $StartTimeObj = $Kernel::OM->Create(
2467                 'Kernel::System::DateTime',
2468                 ObjectParams => {
2469                     Epoch => $Ticket{$Key}
2470                     }
2471             );
2472
2473             #my $DeltaObj = $StartTimeObj->Delta(
2474             #    DateTimeObject => $DateTimeObject,
2475             #    ForWorkingTime => 1,
2476             #    Calendar       => $Escalation{Calendar},
2477             #);
2478
2479             $WorkingTime = 0;
2480             #if ( $DeltaObj && $DeltaObj->{AbsoluteSeconds} ) {
2481             #    $WorkingTime = '-' . $DeltaObj->{AbsoluteSeconds};
2482             #}
2483
2484             # set escalation
2485             $Data{ $Map{$Key} . 'TimeEscalation' } = 1;
2486         }
2487
2) I filed a bug for that
https://bugs.otrs.org/show_bug.cgi?id=14318

Hope that helps
Wolfgang
Produktiv:
OTRS 6.0.13/ ITSM 6.0.13
OS: SUSE Linux (SLES 12, Leap), MySql 5.5.x, 5.6.x
Windows 2012 AD Integration (agents and customers), Nagios integration (incidents, CMDB), Survey, TimeAccounting
reneeb
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: AgentTicketQueue slow

Post by reneeb »

Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
Wolfgangf
Znuny ninja
Posts: 1029
Joined: 13 Apr 2009, 12:26
Znuny Version: 6.0.13
Real Name: Wolfgang Fürtbauer
Company: PBS Logitek GmbH
Location: Pinsdorf

Re: AgentTicketQueue slow

Post by Wolfgangf »

reneeb wrote: 19 Dec 2018, 17:00 Maybe this one: https://bugs.otrs.org/show_bug.cgi?id=14318
:) that's excactly the one I filed today
Produktiv:
OTRS 6.0.13/ ITSM 6.0.13
OS: SUSE Linux (SLES 12, Leap), MySql 5.5.x, 5.6.x
Windows 2012 AD Integration (agents and customers), Nagios integration (incidents, CMDB), Survey, TimeAccounting
obione85
Znuny newbie
Posts: 13
Joined: 30 Nov 2018, 12:44
Znuny Version: 6.0.14

Re: AgentTicketQueue slow

Post by obione85 »

sounds like this is our problem as well. got the error message "delta calculation took too long, aborting." from time to time and i can see the HTTPD processes using a lot of CPU when i browse the queues.
OTRS 6.0.14 (test/live) on CentOS7 with MySQL database (MariaDB)
skullz
Znuny superhero
Posts: 618
Joined: 24 Feb 2012, 03:58
Znuny Version: LTS and Features
Real Name: Mo Azfar
Location: Kuala Lumpur, MY
Contact:

Re: AgentTicketQueue slow

Post by skullz »

Mine upgrade FROM 4->5->6 with postgreSQL DB.
However, the slowness happen with default view (small screen) .

Executed this SQL, solve the issue..however not sure the impact.

- VACUUM FULL ANALYZE ticket;
- VACUUM FULL ANALYZE ticket_history;
- VACUUM FULL ANALYZE article_data_mime;

good luck
mariantor
Znuny newbie
Posts: 1
Joined: 16 Jul 2023, 18:00
Znuny Version: 6.5.2
Real Name: Mariusz

Re: AgentTicketQueue slow

Post by mariantor »

I've done many search to fix this issue.

I was migrating form otrs 4 to znuny 6.5. It seems that issue occurred when upgrading form otrs 5 to otrs 6.
I'n my case L view load time was to long. Queue with over 900 tickets loaded for about 30 sec.
I've discovered that disableing {'Frontend::Module'}->{'AgentTicketNoteToLinkedTicket'} solved the issue and shortened load time to 6 sec.

In the end I didn't need to dispable it because it seems to be fixed in znuny 6.5 LTE

Regards
Post Reply