:D Am 14.09.12 11:07, schrieb Christopher Reemts: > Hi Martin, > > I fixed the problem. For reasons unknown, the indexes were disabled. > This might be caused by the script I used to backup and restore the > database. After enabling them by "alter table foobar enable keys", > everything works fine. > > Thanks! > > Chris > > > > On 13.09.2012 08:44, Martin Gruner wrote: >> 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 >> > --------------------------------------------------------------------- > 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 >
-- 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
