Performance problems with search templates

Moderator: crythias

Post Reply
wheelshot
OTRS wizard
Posts: 188
Joined: 15 Dec 2014, 20:49
OTRS Version?: 4.0.24
Location: QC, Canada

Performance problems with search templates

Post by wheelshot » 06 Jul 2016, 17:21

Hey all,

We're using OTRS 4.0.16 and we're beginning to see some performance issue using search templates and the fulltext search. Actually in our article table we only have around 65K entries which is surely not a lot compared to some of you.

We've monitored the DB server and the web server and nothing shows anormal peaks so I was wondering how you guys work with that or if im the only one having this issue.

Thanks and let me know if you need additionnal info.

reneeb
OTRS guru
Posts: 4667
Joined: 13 Mar 2011, 09:54
OTRS Version?: 3.3.x
Company: Perl-Services.de
Contact:

Re: Performance problems with search templates

Post by reneeb » 06 Jul 2016, 17:35

So the fulltext search performance is ok without search templates? All other functions work as expected?
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

wheelshot
OTRS wizard
Posts: 188
Joined: 15 Dec 2014, 20:49
OTRS Version?: 4.0.24
Location: QC, Canada

Re: Performance problems with search templates

Post by wheelshot » 06 Jul 2016, 17:54

Hi,
reneeb wrote:So the fulltext search performance is ok without search templates? All other functions work as expected?
no both are being slower and slower by each weeks passing.

All other functions works as expected.

We checked the query and found a

SELECT [...]
FROM [...]
INNER JOIN [...]
WHERE [...]
AND (art.a_body LIKE '...' )
OR (art.a_cc LIKE '...' )
OR (art.a_from LIKE '...' )
OR (art.a_subject LIKE '...' )
OR (art.a_to LIKE '...' )

and what hurts is that all fields are text fields so no index can be added on those. We added an index on xml_storage.xml_type and it helped a little but not that much unfortunatly.

wheelshot
OTRS wizard
Posts: 188
Joined: 15 Dec 2014, 20:49
OTRS Version?: 4.0.24
Location: QC, Canada

Re: Performance problems with search templates

Post by wheelshot » 11 Jul 2016, 17:20

Bump! Are we alone with those problems? Have people found any fix for that? Thanks

reneeb
OTRS guru
Posts: 4667
Joined: 13 Mar 2011, 09:54
OTRS Version?: 3.3.x
Company: Perl-Services.de
Contact:

Re: Performance problems with search templates

Post by reneeb » 12 Jul 2016, 10:13

Did you try to set Ticket::SearchIndexModule to "StaticDB" ?
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

wheelshot
OTRS wizard
Posts: 188
Joined: 15 Dec 2014, 20:49
OTRS Version?: 4.0.24
Location: QC, Canada

Re: Performance problems with search templates

Post by wheelshot » 13 Jul 2016, 17:04

reneeb wrote:Did you try to set Ticket::SearchIndexModule to "StaticDB" ?
Nope! I'll try it and tell you how it goes. Thinking about it, is there a doc somewhere about optimizing OTRS performance for heavy needs? Havent made a search about it yet though since the problem is quite new.

Thanks for the tip reneeb!

User avatar
nedmaj
OTRS wizard
Posts: 156
Joined: 26 Nov 2014, 20:34
OTRS Version?: 5.0.17
Real Name: Samuel Casimiro
Company: CD
Contact:

Re: Performance problems with search templates

Post by nedmaj » 09 May 2018, 18:51

Hi wheelshot,

Any update on this?

I'm having a similar problem. FullTextSearch is getting very slow as the database increases its size.

Already using StaticDB since the very beggining.

[]s
Samuel

OTRS 5.0.17 | OTRS::ITSM 5.0.17
OS: CentOS 6.5 | Database: Oracle 12.1
Reporting: Pentaho | DW Database: PostgreSQL
User database and auth module: LDAP (Active Directory)
Mail server: Microsoft Exchange | Sendmail module: Sendmail
3rd party packages: Yes | SSL: Yes
Number of agents: 450 | Number of customers: 20000 | Number of CIs: 30000

schulmann
OTRS superhero
Posts: 386
Joined: 20 Nov 2011, 16:08
OTRS Version?: 5.0.28

Re: Performance problems with search templates

Post by schulmann » 13 May 2018, 15:03

nedmaj wrote:FullTextSearch is getting very slow as the database increases its size.
  • What is very slow?
    10 seconds, 1 minute, 2 minutes, 5 minutes?
  • How many articles do you have?
  • How complicated is your full text search?
    A typical example may be sufficient.
In Sysconfig -> Ticket -> Core::FulltextSearch you should review your configuration and rebuild your search index with "bin/otrs.Console.pl Maint::Ticket::FulltextIndexRebuild".
Then it's a matter of database performance.
OTRS5/SLES/openSUSE/ESXi/VirtualBox

Post Reply