slow mysql queries after upgrade 2.2.7 -> 3.1.10

[mowso]

hello everyone,

I'm trying to upgrade OTRS from version 2.2.7 to 3.1.10 while migrating the system to another server.
I first "cloned" the 2.2.7 system on the new server and successfully imported the database and all configurations.
then I upgraded the OTRS: 2.2 -> 2.3. -> 2.4. -> 3.0 -> 3.1. I executed all migration scripts and followed every instruction mentioned in the documentation.
the system works well, except some very slow mysql queries when I try to view tickets.
I activated mysql-slow-query-log, example (viewing a ticket):

Code: Select all

SET timestamp=1347279327;
SELECT sa.ticket_id, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject,  sa.a_reply_to, sa.a_message_id, sa.a_in_reply_to, sa.a_references, sa.a_body,  st.create_time_unix, st.ticket_state_id, st.queue_id, sa.create_time,  sa.a_content_type, sa.create_by, st.tn, article_sender_type_id, st.customer_id,  st.until_time, st.ticket_priority_id, st.customer_user_id, st.user_id,  st.responsible_user_id, sa.article_type_id,  sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2,  sa.a_freekey3, sa.a_freetext3, st.ticket_answered,  sa.incoming_time, sa.id,  st.freekey1, st.freetext1, st.freekey2, st.freetext2, st.freekey3, st.freetext3, st.freekey4, st.freetext4, st.freekey5, st.freetext5, st.freekey6, st.freetext6, st.freekey7, st.freetext7, st.freekey8, st.freetext8,  st.freekey9, st.freetext9, st.freekey10, st.freetext10,  st.freekey11, st.freetext11, st.freekey12, st.freetext12,  st.freekey13, st.freetext13, st.freekey14, st.freetext14,  st.freekey15, st.freetext15, st.freekey16, st.freetext16,  st.ticket_lock_id, st.title, st.escalation_update_time,  st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6,  st.type_id, st.service_id, st.sla_id, st.escalation_response_time,  st.escalation_solution_time, st.escalation_time, st.change_time  FROM article sa, ticket st WHERE sa.ticket_id = '28373' AND sa.ticket_id = st.id  ORDER BY sa.create_time, sa.id ASC;
# Time: 120910 14:15:35
# User@Host: otrs[otrs] @ localhost []
# Query_time: 8.183378  Lock_time: 0.000041 Rows_sent: 3  Rows_examined: 61222

the system takes about 5-30 seconds to view a ticket. sometimes it just works perfectly for a while, the next moment it's slow again.

table "article" has about 68.000 records
table "ticket" has about 28.000 records

if you need to know other data or log files, let me know.

thanks in advance,

chris

[Wolfgangf]

did you read the chapter about performance in OTRS admin docu already?
did you install and run the Support module already?

[mowso]

yes, I did.

I fixed the error: there were some indexes disabled (reasons unknown).
after activating them by "alter table foobar enable keys" everything works fine.
I used a script to backup and restore the database, maybe that deactivated the indexes...

[Wolfgangf]

ah! that's the reason i forgot ...
I had it once myself ...
Anyway, the support module also give a lot of hints about performance