OTRS Performance Slow

Moderator: crythias

Post Reply
eterjack
OTRS expert
Posts: 55
Joined: 21 Sep 2012, 01:45
OTRS Version?: 3.1.10

OTRS Performance Slow

Post by eterjack » 23 Dec 2016, 22:03

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
Moderator
Posts: 1301
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: OTRS Performance Slow

Post by root » 24 Dec 2016, 11:55

Please provide more information: Dedicated Host, OS, DB Version, RAM, etc.
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

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

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

Re: OTRS Performance Slow

Post by kevinpattison » 29 Jan 2018, 16:59

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
Moderator
Posts: 1301
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: OTRS Performance Slow

Post by root » 31 Jan 2018, 14:21

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

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

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

Re: OTRS Performance Slow

Post by kevinpattison » 31 Jan 2018, 18:55

Updated my profile now!!!

root
Moderator
Posts: 1301
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: OTRS Performance Slow

Post by root » 31 Jan 2018, 21:20

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

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

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

Re: OTRS Performance Slow

Post by kevinpattison » 01 Feb 2018, 11:48

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
Moderator
Posts: 1301
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: OTRS Performance Slow

Post by root » 01 Feb 2018, 13:08

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

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

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

Re: OTRS Performance Slow

Post by kevinpattison » 14 Aug 2018, 12:37

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
OTRS wizard
Posts: 101
Joined: 24 Feb 2017, 16:45
OTRS Version?: 5.0.16.01
Real Name: Cristina Corrales
Company: -

Re: OTRS Performance Slow

Post by cris12345 » 05 Sep 2018, 20:28

Hello,

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

Thanks

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

Re: OTRS Performance Slow

Post by kevinpattison » 06 Sep 2018, 10:11

Log into the database and run:

Code: Select all

optimise table ticket_history;

Post Reply