Fulltext search slow

[Manfred89]

Hey guys,

Over time, the ticket system has become a bit sluggish. The calls for various views, dashboard, own tickets, view by queue etc. have become minimally slow. However, the main problem is that the full text search takes quite a long time.


Measured with Firefox network analysis:

• Calling up the overview: 2.6 seconds

• Calling up a ticket from the overview: 2.6 seconds

• View by queue: 2 seconds

• View sub-queue with 104 tickets: 3 seconds

• View by status: 4 seconds

• View by escalations: 0.5 seconds (empty)

Various searches

• Search with 697 results: 20.48 seconds

• Search with 10000 (maximum) results: 20.48 seconds

• Search with 53 results: 20.48 seconds

• Search with 11 results: 25.6 seconds

• Search with 1060 results: 25.6 seconds

• Search with 2414 results: 25.6 seconds

• Search with 8574 results: 25.6 seconds

• Page change in the search results: 2.5 - 3.2 seconds (35 tickets)

On a second ticket system, which is only used for one department and has significantly fewer tickets, articles, attachments, etc., the calls of several thousand tickets run through in a maximum of 1.92 seconds. So everything is in the green zone there.


My system:

• Znuny 6.5.5 LTS (previously OTRS 6 or 5 Community)

• Debian 12.4

• VSphere VM with 2 cores and 8 GB RAM is running.

• Changed the ArticleStorage from DB to FS.

Second system:

• Znuny 6.5.5 LTS (previously OTRS 4)

• Debian 12.4

• VSphere VM with 2 cores and 4 GB RAM.

• ArticleStorage: DB

htop:

Bild2.png

SupportDaten-Analyse:

Bild3.png

Bild4.png

Are the attachments also searched or can I somehow outsource them?

I am aware that the performance can be due to many factors and we will get professional help in a few weeks, but I hope that we can solve this problem.

[Manfred89]

Since I think you can only upload 3 pictures, I have added 3 more.

Bild5.png

Bild6.png

Bild7.png

[skullz]

try archive
https://doc.znuny.org/znuny_lts/standar ... index.html

[Manfred89]

Thank you for the quick response.

I actually forgot to mention that yesterday I also archived all tickets that have been closed for at least 2 years.

This already performed as described. Before that it was even worse.

[skullz]

how about this?
https://doc.znuny.org/legacy/manual/adm ... uning.html

last time i had super slow issue is on postgresql..vacuum some table, its gained some performance.

[Manfred89]

Thank you again for your quick reply.

I had already followed the tuning page.

TicketIndexModule
Kernel::System::Ticket::IndexAccelerator::StaticDB selected

Ticket Search Index
Default values (exactly as on the second system)

Article Storage (Email, Phone and Internal Articles)
Kernel::System::Ticket::Article::Backend::MIMEBase::ArticleStorageFS configured

Archiving tickets
Tickets that have been closed for 2 years are archived (approx. 40% fewer tickets in the search)

Cache
/opt/otrs/bin/otrs.Console.pl Maint::Session::DeleteAll
/opt/otrs/bin/otrs.Console.pl Maint::Cache::Delete

executed and immediately made a test -> without success

[Manfred89]

Update: I have increased the CPU to 4 cores and the RAM to 16 GB and the Firefox network analysis shows the following results:


Measured with Firefox network analysis:

• Calling up the overview: 1,5 - 2 (2,6) seconds

• Calling up a ticket from the overview: 0.6 - 2 (before 2.6) seconds

• View by queue: 0.6 -1 (before 2) seconds

• View by status: 0.6 - 2 (before 4) seconds

• View by escalations: 0.3 seconds (before 0.5 - empty)

Various searches

• Search with 53 results: 7.6 -13 (before 20.48) seconds

• Search with 2414 results: 7,6 -13 (before 25.6) seconds

• Search with 8574 results: 7,6 -13 (before 25.6) seconds

• Page change in the search results: 1,4 -1,6 (before 2.5 - 3.2) seconds (35 tickets)

What I noticed is that the RAM is still very low, virtually no change.
The CPU, on the other hand, is increased up to 75% during the search. The distribution to 4 cores seems to have doubled the search performance.

• But how are things going for you? How fast is your search?

• Should I have the cores increased even further?

[Johannes]

Hi,

there are multiple things to check. But the overall performance of a fulltext search without
a special index is limited due to the nature of the request.
It ssearches any string in Subject, Body, Title for any given ticket. This will never be fast, because (in MySQL) it lacks
the use of indizies when wildcards are used.
There are mechanisms to optimize this in MySQL / MariaDB but we do not have them implemented (Keyword: Fulltext-index) due to other restrictions
that come with it.

Regarding your analysis:
1) Enable IO Wait (Detailed CPU Informaiton) in htop.
Your current setup misses this.
We often see (especially on mysql) high IO wait times.
To make them visible:
(In HTOP)
F2->Setup>enable: Detailed CPU time (System/IO-Wait/Hard-IRQ/Soft-IRQ/Steal/Guest)
in the Display options. Then go to meters and add the 1/1 CPU: All Cpus (just hit Enter and one time spacebar)

You should see somehting like that:

htop 2024-01-11 11-54-39.png

the "WA" is important. If WA goes above 2-5% your IO is to slow for the requested task and the CPU waits for data.

2) Check the DB for long running queries and maybe improve the handling in the mysql config
3) Maybe switch to Postgresql, if possible and experience is there
4) Use the elasticsearch addon - if an elasticsearch is available und experience is available

Regards
Johannes

[Manfred89]

thx for your answer.

Code: Select all

Regarding your analysis:
1) Enable IO Wait (Detailed CPU Informaiton) in htop.
...the "WA" is important. If WA goes above 2-5% your IO is to slow for the requested task and the CPU waits for data.

I have round about 25% WA when i'm searching in the fulltext. (its the right value from wa?)

Code: Select all

2) Check the DB for long running queries and maybe improve the handling in the mysql config

Any tips for that?

Code: Select all

3) Maybe switch to Postgresql, if possible and experience is there
4) Use the elasticsearch addon - if an elasticsearch is available und experience is available

I dont have any experience and have to wait for professional support.

[nico2306]

Any progress here? Our fulltext search becomes slow (40+ seconds) since an update to 6.5.6, before it was like 10s.
I can't find any solution for that. When i look into htop it looks like this:

2024-03-08 11_57_12-Remote Desktop Manager [otrs-test.hlkomm.net - root].png

[Johannes]

Hi Nico,

there is not much we can do. As you see, you have a high IO Wait, which is - as I already wrote - never a good sign.

I think you need to tune your DB a bit. The defaults for MariaDB are not the best - so MySQLTuner should be your friend.
It also supports MariaDB .

Regards
Johannes