Trac 1.0.2 query to ticket_custom wrong SQL costing 15seconds vs left outer join would cost 0.05seconds

38 views
Skip to first unread message

Lukasz Szybalski

unread,
Jun 24, 2015, 12:11:10 PM6/24/15
to trac-users
Hello,
Can you tell me why trac 1.0.2 uses select from ticket_custom instead of inner or left outer join?
This is a major performance hit. The query runs in 15seconds vs 0.05second with left outer join?

How can I change that?



  SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,
  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='contract_number') AS `contract_number`
  FROM ticket AS t
  LEFT OUTER JOIN ticket_custom c2
    on c2.ticket=t.id
    and c2.name='contract_number'
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (t.status IN ('new','reopened') AND t.version NOT IN ('ILC','INC') AND (t.milestone='Endorsement'))



  SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone, c2.value as contract_number
AS `contract_number`
  FROM ticket AS t
  LEFT OUTER JOIN ticket_custom c2
    on c2.ticket=t.id
    and c2.name='contract_number'
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (t.status IN ('new','reopened') AND t.version NOT IN ('ILC','INC') AND (t.milestone='Endorsement'))

Peter Suter

unread,
Jun 24, 2015, 3:25:20 PM6/24/15
to trac-...@googlegroups.com
Hello,
I'm assuming this is a ticket query. Looking at the source control history it looks like this changed in #11140
http://trac.edgewall.org/ticket/11140
for 12.6, 1.0.2 and 1.1.2 because MySQL and SQLite limit in the number of joins, and queries require too many.

If this is a common performance regression, maybe Trac could use joins again if there are only a few custom fields.

Just out of curiosity, can you tell us what  DB you are running? How many tickets are there in total? How many matching this query?
--
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+...@googlegroups.com.
To post to this group, send email to trac-...@googlegroups.com.
Visit this group at http://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.

Jun Omae

unread,
Jun 24, 2015, 11:17:35 PM6/24/15
to trac-...@googlegroups.com
Hi,

On Thu, Jun 25, 2015 at 4:25 AM, Peter Suter <pets...@gmail.com> wrote:
> I'm assuming this is a ticket query. Looking at the source control history
> it looks like this changed in #11140
> http://trac.edgewall.org/ticket/11140
> for 12.6, 1.0.2 and 1.1.2 because MySQL and SQLite limit in the number of
> joins, and queries require too many.
>
> If this is a common performance regression, maybe Trac could use joins again
> if there are only a few custom fields.
>
> Just out of curiosity, can you tell us what DB you are running? How many
> tickets are there in total? How many matching this query?

It seems to be MySQL database since backquote characters are used for
quoting identifiers.

Additionally, would you please post results of EXPLAIN command for the
two queries?

EXPLAIN
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status
AS status,t.priority AS priority,t.component AS component,t.keywords
AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
milestone,
(SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND
c.name='contract_number') AS `contract_number`
FROM ticket AS t
LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
priority.name=priority)
WHERE (t.status IN ('new','reopened') AND t.version NOT IN
('ILC','INC') AND (t.milestone='Endorsement'))
;

EXPLAIN
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status
AS status,t.priority AS priority,t.component AS component,t.keywords
AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
milestone, c2.value AS `contract_number`
FROM ticket AS t
LEFT OUTER JOIN ticket_custom c2
on c2.ticket=t.id
and c2.name='contract_number'
LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
priority.name=priority)
WHERE (t.status IN ('new','reopened') AND t.version NOT IN
('ILC','INC') AND (t.milestone='Endorsement'))
;


--
Jun Omae <jun...@gmail.com> (大前 潤)

Jun Omae

unread,
Jun 28, 2015, 12:06:05 PM6/28/15
to trac-...@googlegroups.com
On Thu, Jun 25, 2015 at 12:17 PM, Jun Omae <jun...@gmail.com> wrote:
> On Thu, Jun 25, 2015 at 4:25 AM, Peter Suter <pets...@gmail.com> wrote:
>> I'm assuming this is a ticket query. Looking at the source control history
>> it looks like this changed in #11140
>> http://trac.edgewall.org/ticket/11140
>> for 12.6, 1.0.2 and 1.1.2 because MySQL and SQLite limit in the number of
>> joins, and queries require too many.
>>
>> If this is a common performance regression, maybe Trac could use joins again
>> if there are only a few custom fields.

I created http://trac.edgewall.org/ticket/12113 for the performance
regression on MySQL.

Lukasz Szybalski

unread,
Jul 17, 2015, 1:29:50 AM7/17/15
to trac-...@googlegroups.com


Here are the details:

# Time: 150716 23:43:30
# User@Host: xxxx @ xxxxx[xxxx]
# Query_time: 12.570735  Lock_time: 0.000199 Rows_sent: 100  Rows_examined: 3926507
SET timestamp=1437108210;
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value,t.`policy_number` AS `policy_number`
FROM (

  SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,
  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='policy_number') AS `policy_number`
  FROM ticket AS t) AS t

  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (COALESCE(t.status,'') IN ('new','reopened') AND COALESCE(t.version,'') NOT IN ('ILC','INC') AND (COALESCE(t.milestone,'')='UW_Cancel'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id LIMIT 100 OFFSET 0;
 
see attached
Explain_slowquery20150716.csv
run: 12.856seconds



Now converted to innerjoins:

SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value,t.`policy_number` AS `policy_number`
FROM (

  SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS status,t.priority AS priority,t.component AS component,t.keywords AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,
  c.value AS `policy_number`
  FROM ticket t
  inner join ticket_custom c
    on c.ticket=t.id
    and c.name='policy_number'
) AS t

  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (COALESCE(t.status,'') IN ('new','reopened') AND COALESCE(t.version,'') NOT IN ('ILC','INC') AND (COALESCE(t.milestone,'')='UW_Cancel'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id LIMIT 100 OFFSET 0;

Explain_innerjoin_query20150716.csv
run 9.5sec
....


I'm not an expert on queries but I wish this query could be optimized to be used in memory. I'm not sure why mysql keeps creating the tmp table on disc to get this query created. There is enough hardware and memory to keep the whole thing in memory.
Explain_innerjoin_query20150716.csv
Explain_slowquery20150716.csv

Ryan Ollos

unread,
Jul 17, 2015, 1:32:42 AM7/17/15
to trac-...@googlegroups.com
I believe Jun already fixed the issue in Trac 1.0.7, which was released a few hours ago, so please evaluate the performance of the latest version with your queries.

- Ryan 

Lukasz Szybalski

unread,
Sep 16, 2015, 3:37:01 PM9/16/15
to Trac Users, ryan.j...@gmail.com


Explain_innerjoin_query20150716.csv
run 9.5sec
....


I'm not an expert on queries but I wish this query could be optimized to be used in memory. I'm not sure why mysql keeps creating the tmp table on disc to get this query created. There is enough hardware and memory to keep the whole thing in memory.

I believe Jun already fixed the issue in Trac 1.0.7, which was released a few hours ago, so please evaluate the performance of the latest version with your queries.

Hello,
Could you point me to the diff for the query.py that fixes this?. I am running a debian stable for last 7 years and we are not planning on upgrading to next trac until debian does it which will probably in in 2 years based on their prior schedule or at least until its in a jessie-backports if really necessary.
I can modify the query.py manually to fix the problem above if need be;  if you could point me to exact changes that I need to copy over?


Is it this it or??
http://trac.edgewall.org/changeset/14140

Also,
Is that issue going to resolve "my tickets" query? See the output from slow-query.log
Its taking 23 seconds to check "my tickets".
Between

my speed went from myisam 2-3 sec in 0.12.5 to 27sec innodb in Trac 1.0.2



# Time: 150916 14:30:47
# User@Host: trac[trac] @ xxxx [xxxxx]
# Query_time: 15.664268  Lock_time: 0.000249 Rows_sent: 1  Rows_examined: 0
use trac;
SET timestamp=1442431847;
SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,priority.value AS priority_value,t.`policy_number` AS `policy_number`
FROM (
  SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,

  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='policy_number') AS `policy_number`
  FROM ticket AS t) AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
WHERE ((COALESCE(t.owner,'')='user12') AND COALESCE(t.status,'') IN ('accepted','assigned','new','reopened'))
ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id) AS x;

# Time: 150916 14:31:03
# User@Host: trac[trac] @ xxxx [xxxxx]
# Query_time: 16.354391  Lock_time: 0.000256 Rows_sent: 32  Rows_examined: 4005081
SET timestamp=1442431863;
SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,priority.value AS priority_value,t.`policy_number` AS `policy_number`
FROM (
  SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,

  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='policy_number') AS `policy_number`
  FROM ticket AS t) AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
WHERE ((COALESCE(t.owner,'')='user12') AND COALESCE(t.status,'') IN ('accepted','assigned','new','reopened'))
ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id;

Thanks
Lucas

Lukasz Szybalski

unread,
Sep 16, 2015, 3:45:16 PM9/16/15
to Trac Users, ryan.j...@gmail.com
Forgot to paste in the "explain" command from mysql:
 
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '1351228', 'Using where; Using temporary; Using filesort'
'1', 'PRIMARY', 'priority', 'eq_ref', 'PRIMARY', 'PRIMARY', '1000', 'const,t.priority', '1', ''
'1', 'PRIMARY', 'milestone', 'eq_ref', 'PRIMARY', 'PRIMARY', '767', 't.milestone', '1', ''
'2', 'DERIVED', 't', 'ALL', NULL, NULL, NULL, NULL, '890440', ''
'3', 'DEPENDENT SUBQUERY', 'c', 'eq_ref', 'PRIMARY', 'PRIMARY', '504', 'trac.t.id', '1', 'Using where'


Reply all
Reply to author
Forward
0 new messages