Performance problems with search templates

Moderator: crythias

Post Reply
wheelshot
Znuny expert
Posts: 188
Joined: 15 Dec 2014, 20:49
Znuny 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
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
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 / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
wheelshot
Znuny expert
Posts: 188
Joined: 15 Dec 2014, 20:49
Znuny 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
Znuny expert
Posts: 188
Joined: 15 Dec 2014, 20:49
Znuny 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
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: Performance problems with search templates

Post by reneeb »

Did you try to set Ticket::SearchIndexModule to "StaticDB" ?
Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
wheelshot
Znuny expert
Posts: 188
Joined: 15 Dec 2014, 20:49
Znuny 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
Znuny expert
Posts: 167
Joined: 26 Nov 2014, 20:34
Znuny Version: 6.3.4
Real Name: Samuel Casimiro
Company: Câmara dos Deputados
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

Znuny 6.3.4 | OTRS 5.0.17
OS: Debian 11 | CentOS 6.5
Database: Postgres | Oracle 12.1
Number of agents: 450 | Number of customers: 20000 | Number of CIs: 30000
schulmann
Znuny wizard
Posts: 470
Joined: 20 Nov 2011, 16:08
Znuny Version: 6.5.4
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.
Znuny6/Debian/ESXi
Post Reply