Johannes, thanks a lot for your suggestions!
> I would start using the regular questions:
> - OTRS 5 was used on this exact server with this exact Software version (OS / Database...)?
They're not actually the same server.
Otrs 5 runs on ubuntu 10.04(!), with znuny 6.0.38 on ubuntu 22.04;
although they are on a VM with same amount of memory.
> - What is the MariaDB slow log showing? - When the load is high, what is your MariaDB (full) process list showing at the time?
I didn't enable slow query log, although I grabbed the full process list of mariadb and saved it into a .sql file (you can find it below for reference).
I executed the script in a shell like this `cat a2.sql | mysql -u root -p otrs` and verified that it took no time to execute. To be sure, I also ran every single query in a sql shell and they are indeed instantly fast.
I tried to test the writing speed of the disk. I tested like this:
Code: Select all
dd if=/dev/zero of=./test1.img bs=1G count=1 oflag=dsync
1073741824 bytes (1.1 GB, 1.0 GiB) copied, 7.15582 s, 150 MB/s
(for reference, the same command gave ~750MB/s with my dell i7 laptop, with ssd drive)
`lscpu` says that the processor is quad core @2.0GHz
- CPU(s):4 -- Model name: Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz
> - I would use htop top, enabled detailed CPU stats and check for IO wait during the run
I tried both htop and top and when I click for a ticket-ATZoomView I do not notice a high %wa value (actually it is almost always 0%).
> - Next check, if enabled, should be the external backends (Customer User backends)
Mmh, I do not know if there are any (i guess not).
Can it be the slow disk-writing time the problem? or maybe the slow processor?
**PS**: full mariadb process list .sql file:
Code: Select all
SET NAMES utf8 ;
SELECT id, data_key, data_value, serialized FROM sessions WHERE session_id = 'NvtIEvhPUssxnh8UWkgVfykRnTpIu5tm' ORDER BY id ASC ;
SELECT id, value_text, value_date, value_int, field_id FROM dynamic_field_value WHERE object_id = '1183083' ORDER BY id ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st WHERE 1=1 AND st.ticket_state_id IN ( 1, 12, 13, 18 ) AND ( st.responsible_user_id IN (1) ) AND archive_flag = 0 LIMIT 10000 ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN ticket_flag tf1 ON st.id = tf1.ticket_id WHERE 1=1 AND st.ticket_state_id IN ( 1, 12, 13, 18 ) AND ( st.responsible_user_id IN (1) ) AND tf1.ticket_key = 'Seen' AND tf1.ticket_value = '1' AND tf1.create_by = 1 AND archive_flag = 0 LIMIT 10000 ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st WHERE 1=1 AND st.ticket_state_id IN ( 12 ) AND ( st.responsible_user_id IN (1) ) AND st.ticket_state_id IN (12) AND st.until_time <= 1664263904 AND archive_flag = 0 LIMIT 10000 ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st WHERE 1=1 AND ( st.ticket_lock_id IN (2, 3) ) AND ( st.user_id IN (1) ) AND archive_flag = 0 LIMIT 10000 ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN ticket_flag tf1 ON st.id = tf1.ticket_id WHERE 1=1 AND ( st.ticket_lock_id IN (2, 3) ) AND ( st.user_id IN (1) ) AND tf1.ticket_key = 'Seen' AND tf1.ticket_value = '1' AND tf1.create_by = 1 AND archive_flag = 0 LIMIT 10000 ;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st WHERE 1=1 AND st.ticket_state_id IN ( 12 ) AND ( st.ticket_lock_id IN (2, 3) ) AND ( st.user_id IN (1) ) AND st.ticket_state_id IN (12) AND st.until_time <= 1664263904 AND archive_flag = 0 LIMIT 10000 ;
SELECT id FROM system_maintenance WHERE start_date <= 1664263964 and stop_date >= 1664263964 AND valid_id IN (1) ORDER BY id ;
SELECT start_date FROM system_maintenance WHERE start_date > 1664263964 and start_date <= 1664371964 AND valid_id IN (1) ORDER BY id ;
SELECT article.id, article_flag.article_key, article_flag.article_value FROM article_flag, article WHERE article.id = article_flag.article_id AND article.ticket_id = '1183083' AND article_flag.create_by = '1' LIMIT 1500 ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT id, value_text, value_date, value_int, field_id FROM dynamic_field_value WHERE object_id = '2473697' ORDER BY id ;
SELECT time_unit FROM time_accounting WHERE ticket_id = '1183083' ;
SET NAMES utf8 ;
SELECT id, data_key, data_value, serialized FROM sessions WHERE session_id = 'NvtIEvhPUssxnh8UWkgVfykRnTpIu5tm' ORDER BY id ASC ;
SELECT DISTINCT(session_id) FROM sessions ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT article_id, message_id, log_message, create_time FROM article_data_mime_send_error WHERE article_id = '2473697' ;
SELECT article_id, create_time, attempts, due_time FROM mail_queue WHERE article_id = '2473697' ;
SELECT article_key, article_value FROM article_flag WHERE article_id = '2473697' AND create_by = '1' LIMIT 1500 ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT queue_id FROM system_address WHERE valid_id IN ( 1 ) AND value0 = 'm......g......@g.....com' LIMIT 1 ;
SELECT clo.communication_id, clol.communication_log_object_id, clol.object_type ,clol.object_id FROM communication_log_obj_lookup clol JOIN communication_log_object clo ON clol.communication_log_object_id = clo.id WHERE (clol.object_id = '2473697') AND (clol.object_type = 'Article') ;
SELECT article_key, article_value FROM article_flag WHERE article_id = '2473697' AND create_by = '1' LIMIT 1500 ;
SELECT article_id, message_id, log_message, create_time FROM article_data_mime_send_error WHERE article_id = '2473697' ;
SELECT article_id, create_time, attempts, due_time FROM mail_queue WHERE article_id = '2473697' ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT sadm.a_from, sadm.a_reply_to, sadm.a_to, sadm.a_cc, sadm.a_bcc, sadm.a_subject, sadm.a_message_id, sadm.a_in_reply_to, sadm.a_references, sadm.a_content_type, sadm.a_body, sadm.incoming_time FROM article_data_mime sadm WHERE sadm.article_id = '2473697' LIMIT 1 ;
SELECT article_id, message_id, log_message, create_time FROM article_data_mime_send_error WHERE article_id = '2473697' ;
SELECT article_id, create_time, attempts, due_time FROM mail_queue WHERE article_id = '2473697' ;
INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES ('NvtIEvhPUssxnh8UWkgVfykRnTpIu5tm','UserLastRequest','1664263963','0') ;
DELETE FROM sessions WHERE session_id = 'NvtIEvhPUssxnh8UWkgVfykRnTpIu5tm' AND data_key = 'UserLastRequest' AND id <= '2286' ;