Performance problems with search templates

Moderator: crythias

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

Performance problems with search templates

Post by wheelshot »

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: 4797
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 »

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 expert
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 »

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 expert
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 »

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

reneeb
OTRS guru
Posts: 4797
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 »

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 expert
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 »

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!

nedmaj
OTRS expert
Posts: 160
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 »

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 wizard
Posts: 399
Joined: 20 Nov 2011, 16:08
OTRS Version?: 5.0.38
Real Name: Schulmann

Re: Performance problems with search templates

Post by schulmann »

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