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.

Post Reply