AgentTicketQueue slow

Moderator: crythias

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

AgentTicketQueue slow

Post by Laurens » 02 Jul 2018, 16:26

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
OTRS newbie
Posts: 4
Joined: 11 Mar 2013, 15:08
OTRS Version?: OTRS 5.x

Re: AgentTicketQueue slow

Post by Reedu » 04 Oct 2018, 12:25

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
OTRS newbie
Posts: 12
Joined: 30 Nov 2018, 12:44
OTRS Version?: 6.0.14

Re: AgentTicketQueue slow

Post by obione85 » 19 Dec 2018, 13:25

have you been able to resolve this?
same problem here :(
OTRS 6.0.14 (test/live) on CentOS7 with MySQL database (MariaDB)

Wolfgangf
OTRS guru
Posts: 1025
Joined: 13 Apr 2009, 12:26
OTRS Version?: 6.0.13
Real Name: Wolfgang Fürtbauer
Company: PBS Logitek GmbH
Location: Pinsdorf

Re: AgentTicketQueue slow

Post by Wolfgangf » 19 Dec 2018, 16:55

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
OTRS guru
Posts: 4781
Joined: 13 Mar 2011, 09:54
OTRS Version?: 3.3.x
Company: Perl-Services.de
Contact:

Re: AgentTicketQueue slow

Post by reneeb » 19 Dec 2018, 17:00

Perl / OTRS development: http://perl-services.de
Free OTRS add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de

Wolfgangf
OTRS guru
Posts: 1025
Joined: 13 Apr 2009, 12:26
OTRS Version?: 6.0.13
Real Name: Wolfgang Fürtbauer
Company: PBS Logitek GmbH
Location: Pinsdorf

Re: AgentTicketQueue slow

Post by Wolfgangf » 19 Dec 2018, 17:08

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
OTRS newbie
Posts: 12
Joined: 30 Nov 2018, 12:44
OTRS Version?: 6.0.14

Re: AgentTicketQueue slow

Post by obione85 » 20 Dec 2018, 09:44

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
OTRS wizard
Posts: 164
Joined: 24 Feb 2012, 03:58
OTRS Version?: OTRS 6

Re: AgentTicketQueue slow

Post by skullz » 15 Jan 2019, 09:29

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

Post Reply