What database engine are you using behind MySQL? If it's MyISAM that
could be part of the problem. 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.
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.
