OTRS 5 to 6 - Performance and some other issues

[obione85]

Hi guys,

im new on the board, but ive been reading here for a few years now and i was always amazed how crythias seems to have an answer to every OTRS question asked

we ran an OTRS 5 on a CentOS 6 host that got migrated to a CentOS7 host using OTRS 6 (Community Edition).
Performance is fine most of the time, but like every 10 minutes, we get some load spikes from OTRS DAEMON und HTTPD processes.
I dont see errors in the OTRS/HTTPD/MariaDB logfiles. Also checked the OTRS Daemon Logs and they are empty.
Any Idea what i can do to find the cause of the issue? Update: Changed some HTTPD.cnf/my.cnf settings and it seems to run a lot smoother

Another special issue is, that we dont get hyperlinks on the customerinformation widget, when creating a new ticket. The same widget shows hyperlinks when its shown on a different page. Update: This one is very annoying. OTRS5 did show us hyperlinks in this widget and OTRS6 does show them in the CustomerInformation Widget when opening the ticket. A Workaround, that gives us a button on the ticket creation page, which leads to the customercompany, as soon as a customeruser is selected, would help as well Update2: FIXED used a relativ path in Config.pm UserMap and the links pop up again

Is there a way to hide the customercompany Info field when editing company? I added a bigger textbox at the top of the page. This one is working fine, but as soon as we edit the content of that field in the actual small info field at the bottom, it wont save the changes, so i'd like to hide the small info field. Update: The behaviour was exatly the same in OTRS5, so we might have to live with it

In OTRS 5 we used an edited ShorterDate.pm to generate shorter TNs, but in OTRS6 it wont run. Tried to take the new Date.pm and edit it, but that didnt work as well.

And last but now least, this error: "FileID TicketID ArticleID Needed!" is sometimes logged in the systemprotokoll of OTRS. Any Idea what it may cause? Update: This problem is gone, but now im getting a few duplicate Ticket seen entrys per day.

thanks in advance, any help is highly appreciated.

and sorry for my bad english

[obione85]

most of my problems are gone by now, but the performance is still an issue

When i use Apache Benchmark to get some load, the server has some random lag on some of the tests. When i go hardcore and put in 1000 requests, the server goes into unresponsive mode for minutes. (same thing on the much less performant testsystem is done in a fraction of the time)

CPU Load was 150+ with 8 cores.

another strange thing is: i open several pages in the OTRS Admin area and everything loads instant ... i click queue or dashboard and it will take 4-5 seconds to load the page - does that hint to a DB Performance issue?

will update linux integration services tonight and add 2 grub options, that should help a little, but if anyone got an idea what to do, let me know

Some additional info:
New System:
CENTOS 7.5
OTRS 6.0.14
12GB RAM
8 cores @ 2GHZ
NO SSD HARDDRIVE!
MariaDB Mysql
MPM prefork module running

Old System:
CentOS 6
OTRS 5
12GB RAM
2 cores @ 2GHZ
NO SSD HARDDRIVE!
Mysql DB

the old system was much faster with just 2 cores instead of 8


if config files are needed, i can paste them here
also got mytop / sar / iostat logs if needed

[root]

Hi,

How many escalated tickets do you have in your system and what's the average age of them?

- Roy

[obione85]

Hi,

How many escalated tickets do you have in your system and what's the average age of them?

- Roy

71 escalated and the average should be around 30 days. Top 10 is more than 200 Days old.

[root]

Hi,

Looks ok to me, did you enabled the slow query log of the database? Maybe an index is missing.

- Roy

[obione85]

slow query log is enabled since yesterday evening. Until now, nothing was logged.
Also updated linux integration services yesterday.
will let you know if anything helps.

[obione85]

another thing that makes me think, that the DB is the bottleneck:
when i load the dashboard with all the settings active, it will take 4-5 seconds ... sometimes goes up to 8-10 seconds.
If i disable all settings, that will show me tickets, the page loads in less that a second.

[root]

Hi,

There's another possibility for a bottleneck. For each ticket on the dashboard, the CustomerUser and CustomerCompany data are loaded. Check the related CacheTTL setting. If they are disabled and you use a slow data source theses lookups will slow you definitely down.

- Roy

[obione85]

There are 2 entries in our Config.pm about CacheTTL

This one, which is not active:
# # cache time to live in sec. - cache database queries
# CacheTTL => 0,

and
This one, which is active and CacheTTL is 0

CustomerCompanyKey => 'customer_id',
CustomerCompanyValid => 'valid_id',
CustomerCompanyListFields => [ 'customer_id', 'name', 'city' ],
CustomerCompanySearchFields => ['customer_id', 'name', 'city', 'comments', 'info' ],
CustomerCompanySearchPrefix => '*',
CustomerCompanySearchSuffix => '*',
CustomerCompanySearchListLimit => 250,
CacheTTL => 0, # use 0 to turn off cache

Should i try to change this one? what value would make sense?

Update:
tried to change both values in my testsystem, but it doesnt really change loading times at all. (even after reboot)
slowest pages in the system are the queues. The dashboard loads super fast, since i disabled the ticket views in dashboard.
Queues load twice as fast, if i set the "shown tickets" setting to 10 (from 35).
installed sysbench today and will do some tests tonight.

[root]

Hi,

For testing you may increase the TTL to at least 3600 (1 hour). After the second reload of the page it could be faster.

- Roy

[obione85]

CacheTTL increased to 3600. OTRS Performance Log enabled.
Its a little bit better today. A shoking information though:

This is a sysbench OLTP Read Write Test with 64 threads on the livesystem:
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Number of threads: 64
SQL statistics:
queries performed:
read: 28518
write: 8148
other: 4074
total: 40740
transactions: 2037 (197.26 per sec.)
queries: 40740 (3945.10 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.3180s
total number of events: 2037
Latency (ms):
min: 46.36
avg: 318.85
max: 803.51
95th percentile: 475.79
sum: 649499.53
Threads fairness:
events (avg/stddev): 31.8281/1.81
execution time (avg/stddev): 10.1484/0.08
-----------------------------------------------------------------------------------------
And this one was done on the testsystem with 3 GB Ram instead of 12GB and 2 cores instead of 8 cores:
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Number of threads: 64
SQL statistics:
queries performed:
read: 89278
write: 25508
other: 12754
total: 127540
transactions: 6377 (626.82 per sec.)
queries: 127540 (12536.33 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.1717s
total number of events: 6377
Latency (ms):
min: 7.17
avg: 100.48
max: 380.18
95th percentile: 167.44
sum: 640761.43
Threads fairness:
events (avg/stddev): 99.6406/3.44
execution time (avg/stddev): 10.0119/0.06
sysbench 1.0.9 (using system LuaJIT 2.0.4)
---------------------------------------------------------------------------------
how on earth can the testsystem be 3 times faster with 1/4 of the cores and RAM?

this is our my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
innodb_log_file_size=256M
innodb_additional_mem_pool_size=80M
innodb_buffer_pool_size=2G
max_allowed_packet=100M
key_buffer_size=300M
character-set-server = utf8
query_cache_size=64M
collation-server=utf8_general_ci
skip_name_resolve
bind-address=0.0.0.0
port=3306
read_buffer_size=4M
sort_buffer_size=4M
max_connections=400
slow_query_log=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
---------------------------------------------------------------------------------------------------
Does anyone see a big error in here?

[obione85]

while troubleshooting our performance issue, i found this query, which takes ages.

# Thread_id: 20931 Schema: otrs_db QC_hit: No
# Query_time: 5.047590 Lock_time: 0.000520 Rows_sent: 655 Rows_examined: 755596
SET timestamp=1547111492;
SELECT DISTINCT st.id, st.tn, st.create_time FROM ticket st INNER JOIN article art ON st.id = art.ticket_id LEFT JOIN article_search_index ArticleFulltext ON art.id = ArticleFulltext.article_id INNER JOIN queue sq ON sq.id = st.queue_id WHERE 1=1 AND sq.group_id IN (1,10,11,12,2,4,5,6,7,8,9) AND (((ArticleFulltext.article_value LIKE '%dies%' OR st.title LIKE '%dies%' ) AND (ArticleFulltext.article_value LIKE '%ist%' OR st.title LIKE '%ist%' ) AND (ArticleFulltext.article_value LIKE '%ein%' OR st.title LIKE '%ein%' ) AND (ArticleFulltext.article_value LIKE '%2.%' OR st.title LIKE '%2.%' ) AND (ArticleFulltext.article_value LIKE '%test%' OR st.title LIKE '%test%' ) )) ORDER BY st.create_time DESC LIMIT 2000;

it runs after using the OTRS fulltextsearch and it takes a lot of CPU power while running.

now this wont be the cause of our slow queues, but rows examined shows 750k and i think this is a lot.

we got around 40-50k tickets in our system. Should we start archiving?

I can also see queries that examine 40k+ rows, which is most likely all of our tickets. Those were logged, because they dont use an index. The execution time is below 0.2 seconds though.

# Thread_id: 13237 Schema: otrs_db QC_hit: No
# Query_time: 0.163980 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 45383
SET timestamp=1547059592;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id WHERE 1=1 AND st.ticket_state_id NOT IN ( 1, 4, 6 ) AND sq.group_id IN (1,12,4,5,6,7,8,9) LIMIT 10000;

-------------------------------------------------------------------------------
anything strange here? or working as intended?