Search and sorting search results takes too long (poor performance)

Moderator: crythias

Post Reply
MAL
OTRS newbie
Posts: 25
Joined: 30 Jul 2012, 13:36
OTRS Version?: 6.0.17
Real Name: MAL

Search and sorting search results takes too long (poor performance)

Post by MAL » 11 Jul 2019, 14:12

Hi.
  • When searching for tickets (also when not using full-text search) it takes a long time to get the results (at least 30 seconds)
  • When sorting the search results (even if the result set is rather small) by specific fields it takes very long (+30 seconds).
  • When opening the standard view "Tickets -> Status view", it takes around 75 seconds
This happens no matter how many/few agents are active (it might just take even longer).
During the waiting time there is 100% CPU load (for one core) on the database, and not much (basically zero) on OTRS/apache.

So I would like to know what I can do?

Here some relevant information/data:
OTRS: 6.0.17
Server OS: CentOS 7.5
DB: MariaDB (on the same server/machine)
Apache/2.4.6 (CentOS) mod_perl/2.0.10 Perl/v5.16.3

8 core CPU
6GB RAM

Here some numbers from OTRS:

Agents 224
Articles 152515
Attachments (DB, Without HTML) 4050
Customers With At Least One Ticket 1741
Dynamic Fields 64
Dynamic Field Values 141377
Groups 12
Invalid Dynamic Fields 29
Invalid Dynamic Field Values 55050
Processes 0
Process Tickets 0
Queues 21
Roles 6
Services 4
Tickets 14686
Ticket History Entries 1233342
Open Tickets 721
Months Between First And Last Ticket 146
Tickets Per Month (avg) 100
GenericInterface Webservices 0

+
Zero tickets in escalation view
+
Indexed Articles 100.0 % (152515/152515)


What I already did is to archive old tickets (all from 2007 to 2014). Basically no change.

What seems odd to me is that the cache on side of the database is empty.
query_cache_size is 64M (I increased it today. before it was set to 1M)
query_cache_limit is 8M (I increased it today. before it was set to 1M)

Using SHOW STATUS LIKE 'Qcache%'; shows
"Qcache_free_blocks" "1"
"Qcache_free_memory" "67091104"
"Qcache_hits" "0"
"Qcache_inserts" "0"
"Qcache_lowmem_prunes" "0"
"Qcache_not_cached" "17124007"
"Qcache_queries_in_cache" "0"
"Qcache_total_blocks" "1"

EDIT:
I just found out that
query_cache_type is OFF
as far as I understand this means the cache is not used, but could this cache really help in my situation?

Post Reply