OTRS Performance Slow

Moderator: crythias

Post Reply
eterjack
Znuny newbie
Posts: 55
Joined: 21 Sep 2012, 01:45
Znuny Version: 3.1.10

OTRS Performance Slow

Post by eterjack »

I am having problems with OTRS performance, enabling the mysql slow query, the most time consuming queries are:

# User@Host: otrs[otrs] @ [xxxxxxxxxxxxxx]
# Query_time: 199.811449 Lock_time: 0.000137 Rows_sent: 0 Rows_examined: 4373804
SET timestamp=1482515538;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id WHERE 1=1 AND sq.group_id IN (1, 9, 10, 11, 19, 206, 207, 208) AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time <= '2016-12-19 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time >= '2016-12-19 00:00:00' LIMIT 10000;
# User@Host: otrs[otrs] @ [xxxxxxxxxxxxxx]
# Query_time: 198.929593 Lock_time: 0.000153 Rows_sent: 0 Rows_examined: 4371975
SET timestamp=1482515538;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id WHERE 1=1 AND sq.group_id IN (1, 9, 10, 11, 19, 206, 207, 208) AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time <= '2016-12-19 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time >= '2016-12-19 00:00:00' LIMIT 10000;
# User@Host: otrs[otrs] @ [xxxxxxxxxxxxxx]
# Query_time: 199.326929 Lock_time: 0.000146 Rows_sent: 0 Rows_examined: 4373804
SET timestamp=1482515538;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id WHERE 1=1 AND sq.group_id IN (1, 9, 10, 11, 19, 206, 207, 208) AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time <= '2016-12-19 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time >= '2016-12-19 00:00:00' LIMIT 10000;
# User@Host: otrs[otrs] @ [xxxxxxxxxxxxxx]
# Query_time: 199.238014 Lock_time: 0.000280 Rows_sent: 0 Rows_examined: 4373804
SET timestamp=1482515538;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id WHERE 1=1 AND sq.group_id IN (1, 9, 10, 11, 19, 206, 207, 208) AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time <= '2016-12-19 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (11, 12) AND th.create_time >= '2016-12-19 00:00:00' LIMIT 10000;

# Time: 161223 15:04:13
# User@Host: otrs[otrs] @ [xxxxxxxxxxxxxx]
# Query_time: 958.216666 Lock_time: 0.000405 Rows_sent: 5 Rows_examined: 2124921
SET timestamp=1482512653;
SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN article_search art ON st.id = art.ticket_id WHERE 1=1 AND sq.group_id IN (1, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247) AND (((art.a_body LIKE '%xxxx%' ) AND (art.a_body LIKE '%xxx2%' ) AND (art.a_body LIKE '%xxxxxx444444%' ) ) OR ((art.a_cc LIKE '%xxxx%' ) AND (art.a_cc LIKE '%xxx2%' ) AND (art.a_cc LIKE '%xxx3%' ) ) OR ((art.a_from LIKE '%xxxx%' ) AND (art.a_from LIKE '%xxx2%' ) AND (art.a_from LIKE '%xxx3%' ) ) OR ((art.a_subject LIKE '%xxx2%' ) AND (art.a_subject LIKE '%xxx3%' ) AND (art.a_subject LIKE '%xxx3%' ) ) OR ((art.a_to LIKE '%xxx4%' ) AND (art.a_to LIKE '%xxx4%' ) AND (art.a_to LIKE '%xxx5%' ) )) ORDER BY st.create_time_unix DESC LIMIT 2000;

By reading the documentation, there is a recommendation to enable staticDB options for performance improvement. But still, we still have a problem.
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: OTRS Performance Slow

Post by root »

Please provide more information: Dedicated Host, OS, DB Version, RAM, etc.
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
kevinpattison
Znuny newbie
Posts: 32
Joined: 22 Sep 2011, 16:32
Znuny Version: 5.0.16
Real Name: Kevin Pattison

Re: OTRS Performance Slow

Post by kevinpattison »

I'm having the same problem with the same query:

Code: Select all

# Time: 180129 14:33:08
# Query_time: 1360.566931  Lock_time: 0.001636 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236388;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Time: 180129 14:42:50
# Query_time: 1306.449099  Lock_time: 0.000129 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236970;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Time: 180129 14:43:14
# Query_time: 1278.017573  Lock_time: 0.000184 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Query_time: 1275.682596  Lock_time: 0.000230 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Query_time: 1277.774119  Lock_time: 0.000195 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Query_time: 1277.598678  Lock_time: 0.000320 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Query_time: 1278.391030  Lock_time: 0.001135 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Query_time: 1271.047605  Lock_time: 0.000271 Rows_sent: 1  Rows_examined: 4908808
SET timestamp=1517236994;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
# Time: 180129 14:44:46
# Query_time: 1182.580134  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 4912459
SET timestamp=1517237086;
SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,17,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-24 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-24 00:00:00' LIMIT 10000;
This is on relatively low power hardware but this query is the only one causing significant problems.

What action in the UI is triggering this query?
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: OTRS Performance Slow

Post by root »

kevinpattison wrote: This is on relatively low power hardware but this query is the only one causing significant problems.

What action in the UI is triggering this query?
Hey Kevin,

What's your OTRS version? Really 3.0?

- Roy

P.S.: Don't hijack old threads, just create a new one
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
kevinpattison
Znuny newbie
Posts: 32
Joined: 22 Sep 2011, 16:32
Znuny Version: 5.0.16
Real Name: Kevin Pattison

Re: OTRS Performance Slow

Post by kevinpattison »

Updated my profile now!!!
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: OTRS Performance Slow

Post by root »

Hi Kevin,

what's the output of

Code: Select all

EXPLAIN SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
when executing in a database query tool?

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
kevinpattison
Znuny newbie
Posts: 32
Joined: 22 Sep 2011, 16:32
Znuny Version: 5.0.16
Real Name: Kevin Pattison

Re: OTRS Performance Slow

Post by kevinpattison »

Code: Select all

mysql> EXPLAIN SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                                                                                              | key                        | key_len | ref               | rows  | Extra       |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+-------+-------------+
|  1 | SIMPLE      | th    | range  | ticket_history_create_time,ticket_history_history_type_id,ticket_history_state_id,ticket_history_ticket_id | ticket_history_create_time | 8       | NULL              | 36240 | Using where |
|  1 | SIMPLE      | st    | eq_ref | PRIMARY,ticket_queue_id                                                                                    | PRIMARY                    | 8       | otrs.th.ticket_id |     1 |             |
|  1 | SIMPLE      | sq    | eq_ref | PRIMARY,queue_group_id                                                                                     | PRIMARY                    | 4       | otrs.st.queue_id  |     1 | Using where |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+-------+-------------+
3 rows in set (0.03 sec)

mysql> SELECT COUNT(DISTINCT(st.id)) FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN ticket_history th ON st.id = th.ticket_id  WHERE 1=1 AND sq.group_id IN (14,15,16,19,20)  AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND th.create_time <= '2018-01-26 23:59:59' AND th.history_type_id IN  (1, 27) AND  th.state_id IN (10, 2, 3) AND  th.create_time >= '2018-01-26 00:00:00' LIMIT 10000;
+------------------------+
| COUNT(DISTINCT(st.id)) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.11 sec)
Strangely this morning it's running instantly whereas yesterday it was VERY slow. No noticeable difference in ticket volume.
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: OTRS Performance Slow

Post by root »

Hi,

Looks like there failed something when installing or upgrading your system. My reference, vanilla system says for the first entry of the explain:

Code: Select all

1 | SIMPLE      | th    | range  | ticket_history_create_time,ticket_history_history_type_id,ticket_history_state_id,ticket_history_ticket_id | ticket_history_create_time | 8       | NULL              |   11 | Using index condition; Using where |
Looks like an index is missing there. Tell your DBA to check which index(es is/are missing. The schema files can be found in /opt/otrs/scripts/database. From there can check if the schema contains all needed indexes.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
kevinpattison
Znuny newbie
Posts: 32
Joined: 22 Sep 2011, 16:32
Znuny Version: 5.0.16
Real Name: Kevin Pattison

Re: OTRS Performance Slow

Post by kevinpattison »

I ended up resolving this by optimising the ticket_history table.

Possibly have incorrect schema. May consider rebuilding.

Upgraded to 6.0.10 now on a new machine and having other issues.
cris12345
Znuny advanced
Posts: 105
Joined: 24 Feb 2017, 16:45
Znuny Version: 5.0.16.01
Real Name: Cristina Corrales
Company: -

Re: OTRS Performance Slow

Post by cris12345 »

Hello,

I have performance issues as well, how did you optimise the ticket_history table?

Thanks
kevinpattison
Znuny newbie
Posts: 32
Joined: 22 Sep 2011, 16:32
Znuny Version: 5.0.16
Real Name: Kevin Pattison

Re: OTRS Performance Slow

Post by kevinpattison »

Log into the database and run:

Code: Select all

optimise table ticket_history;
Post Reply