OTRS Performance Slow
Moderator: crythias
OTRS Performance Slow
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.
# 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.
-
- Administrator
- Posts: 4011
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: OTRS Performance Slow
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 ?
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 ?
-
- Znuny newbie
- Posts: 32
- Joined: 22 Sep 2011, 16:32
- Znuny Version: 5.0.16
- Real Name: Kevin Pattison
Re: OTRS Performance Slow
I'm having the same problem with the same query:
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?
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;
What action in the UI is triggering this query?
-
- Administrator
- Posts: 4011
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: OTRS Performance Slow
Hey Kevin,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?
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 ?
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 ?
-
- Znuny newbie
- Posts: 32
- Joined: 22 Sep 2011, 16:32
- Znuny Version: 5.0.16
- Real Name: Kevin Pattison
Re: OTRS Performance Slow
Updated my profile now!!!
-
- Administrator
- Posts: 4011
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: OTRS Performance Slow
Hi Kevin,
what's the output of
when executing in a database query tool?
- Roy
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;
- 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 ?
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 ?
-
- Znuny newbie
- Posts: 32
- Joined: 22 Sep 2011, 16:32
- Znuny Version: 5.0.16
- Real Name: Kevin Pattison
Re: OTRS Performance Slow
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)
-
- Administrator
- Posts: 4011
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: OTRS Performance Slow
Hi,
Looks like there failed something when installing or upgrading your system. My reference, vanilla system says for the first entry of the explain:
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
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 |
- 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 ?
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 ?
-
- Znuny newbie
- Posts: 32
- Joined: 22 Sep 2011, 16:32
- Znuny Version: 5.0.16
- Real Name: Kevin Pattison
Re: OTRS Performance Slow
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.
Possibly have incorrect schema. May consider rebuilding.
Upgraded to 6.0.10 now on a new machine and having other issues.
-
- 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
Hello,
I have performance issues as well, how did you optimise the ticket_history table?
Thanks
I have performance issues as well, how did you optimise the ticket_history table?
Thanks
-
- Znuny newbie
- Posts: 32
- Joined: 22 Sep 2011, 16:32
- Znuny Version: 5.0.16
- Real Name: Kevin Pattison
Re: OTRS Performance Slow
Log into the database and run:
Code: Select all
optimise table ticket_history;