Hi Christopher, hm, that's very strange. The query you mentioned should be fast. I guess that it could be caused by missing indexes in your database. Could you run an EXPLAIN SELECT on your query? And can you check if any indexes might be missing from the article table?
The following indexes and foreign keys should be there: <Index Name="article_ticket_id"> <IndexColumn Name="ticket_id"/> </Index> <Index Name="article_article_type_id"> <IndexColumn Name="article_type_id"/> </Index> <Index Name="article_article_sender_type_id"> <IndexColumn Name="article_sender_type_id"/> </Index> <Index Name="article_message_id"> <IndexColumn Name="a_message_id" Size="255"/> </Index> <ForeignKey ForeignTable="valid"> <Reference Local="valid_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="ticket"> <Reference Local="ticket_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="article_type"> <Reference Local="article_type_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="article_sender_type"> <Reference Local="article_sender_type_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="users"> <Reference Local="create_by" Foreign="id"/> <Reference Local="change_by" Foreign="id"/> </ForeignKey> Regards, mg Am 11.09.12 10:45, schrieb Christopher Reemts: > 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): > > ------------------- > 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 -- Martin Gruner Senior Developer R&D OTRS AG Europaring 4 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel It's raining... OTRS Feature Add-Ons! Bis zu 10 kostenlose OTRS Feature Add-Ons und professionellen Hersteller-Support – Werden Sie jetzt Service-Subscription-Kunde! --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
