On Fri, Feb 26, 2010 at 12:33 PM, Joshua Kramer <[email protected]> wrote: > > What database engine are you using behind MySQL? If it's MyISAM that could > be part of the problem.
Why would MyISAM be a problem? I see in mysql that around 60,000 queries were inserted into cache, while 300,000+ were reused from cache. That shows a sign of good hit ration/performance, except for the select left outer join statement on ticket_custom with "like" comparison. If switching the field to a varchar does not help, > you might want to investigate switching to PostgreSQL - it has better > performance than MySQL on loads with a high volume of inserts. High value of inserts is not an issue here. Insert take less then 1 sec, its the query time (select) that takes a long time. > > On Fri, 26 Feb 2010, Lukasz Szybalski wrote: > >> Date: Fri, 26 Feb 2010 08:14:26 -0800 (PST) >> From: Lukasz Szybalski <[email protected]> >> Reply-To: [email protected] >> To: Trac Development <[email protected]> >> Subject: [Trac-dev] Re: Trac DB locks - mysql - 138K tickets - TEXT >> ticket_custom vs varchar ticket_custom >> >> >> >> On Feb 25, 7:07 pm, Chris Mulligan <[email protected]> wrote: >>> >>> What in the world are you doing that's generated 6000 tickets a day? >>> How can you possibly use the system at that rate? >> >> 600 not 6000. >> >> faxes mostly. We have ~25 people processing these tasks. >> >> Would anybody familiar with trac database structure confirm or deny >> that switching the ticket_custom from text to varchar will allow for >> substantial performance increase? >> >> Thanks, >> Lucas >> >>> >>> On Thu, Feb 25, 2010 at 5:33 PM, Lukasz Szybalski <[email protected]> >>> wrote: >>>> >>>> Switching from users to dev. >>> >>>> Hello, >>> >>>> I was wondering if people could help me on this issue. We found that >>>> searching anything in one of the custom fields takes 7 seconds per >>>> query when you have 140K+ tickets. We are adding 600 tickets a day, so >>>> soon this query time will be even higher. >>> >>>> 8:19:26 Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: >>>> 276682 >>>> 8:23:00 Query_time: 10 Lock_time: 0 Rows_sent: 0 Rows_examined: >>>> 276692 >>> >>>> Due to such a long time, I found a >>>> tickethttp://trac.edgewall.org/ticket/6986 >>>> which says, that if you replace the TEXT field with VARCHAR then you >>>> can go from 7 seconds to 0.1 seconds. >>> >>>> In addition "It seems that access to TEXT columns in mysql requires a >>>> table access rather than an index access." (http://forums.mysql.com/ >>>> read.php?24,105964,105964#msg-105964) and TEXT field cannot be stored >>>> in memory in mysql, compared to varchar that can. >>> >>>> Is the #6986 there are other changes not related to ticket_custom; I >>>> only care about the following query. Any pointers would be welcome. >>> >>>> SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status >>>> AS status,t.type AS type,t.priority AS priority,t.milestone AS >>>> milestone,t.component AS component,t.time AS time,t.changetime AS >>>> changetime,priority.value AS priority_value,contract_number.value AS >>>> contract_number >>>> FROM ticket AS t >>>> LEFT OUTER JOIN ticket_custom AS contract_number ON >>>> (id=contract_number.ticket AND contract_number.name='contract_number') >>>> LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND >>>> priority.name=priority) >>>> WHERE COALESCE(t.status,'') IN >>>> ('accepted','assigned','closed','new','reopened') AND >>>> COALESCE(contract_number.value,'') LIKE '%123456%' ESCAPE '/' >>>> ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS >>>> signed),t.id) AS foo; >>> >>>> Thanks, >>>> Lucas >>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Trac Development" group. >>>> To post to this group, send email to [email protected]. >>>> To unsubscribe from this group, send email to >>>> [email protected]. >>>> For more options, visit this group >>>> athttp://groups.google.com/group/trac-dev?hl=en. >> >> > > -- > > ----- > http://www.globalherald.net/jb01 > GlobalHerald.NET, the Smarter Social Network! (tm) > > -- > You received this message because you are subscribed to the Google Groups > "Trac Development" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/trac-dev?hl=en. > > -- OpenLdap server for User/Client Authentication in 1min. http://lucasmanual.com/mywiki/OpenLdap#SetupOpenLdapserver.sh -- You received this message because you are subscribed to the Google Groups "Trac Development" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.
