Loading changesets results in many MySQL queries and timeout

[dreas@…]

We're experiencing a strange issue with Trac. Whilst trying to view some ​changesets, we get a 504 Gateway Time-out error. MySQL is taking up a lot of CPU while it's loading. When checking the MySQL processlist, there are many queries shortly after each-other:

localhost | trac1 | Query   |    0 | Copying to tmp table | SELECT DISTINCT rev FROM node_change
                    WHERE repos=1 AND rev>='0000000000' AND rev<='0000053918'
                      AND (path='website/domain.com/branches/2.3/application' OR path LIKE 'website//domain.com//branches//2.3//application//%' COLLATE utf8_general_ci ESCAPE '/')

SELECT DISTINCT rev FROM node_change
                    WHERE repos=1 AND rev>='0000019988' AND rev<='0000053918'
                      AND (path='website/domain.com/trunk/application' OR path LIKE 'website//domain.com//trunk//application//%' COLLATE utf8_general_ci ESCAPE '/') |    0.000

SELECT DISTINCT rev FROM node_change
                    WHERE repos=1 AND rev>='0000000000' AND rev<='0000053918'
                      AND (path='website/domain.com/branches/ticket_11990/application' OR path LIKE 'website//domain.com//branches//ticket/_11990//application//%' COLLATE utf8_general_ci ESCAPE '/')

Etc.

These branches are very old and completely unrelated to the changeset which only has files modified in a single branch. Most changesets don't demonstrate this issue, so I'm unsure what's exactly causing this. Happy to provide any additional information of course.

[Christian Boos]

Right. You just hit what I call the "query from hell".

Now that someone else has this issue, maybe I'll find the motivation to fix it at last! But if anyone else wants to have a try at the fix, I'm fine with that as well…

[Jun Omae]

Probably related #8519.

[Christian Boos]

Replying to jomae:

Probably related #8519.

That may play a role as well. But the " rev>='0000000000' AND rev<='0000053918' one implies a scan on the whole table.

[dreas@…]

This is regularly (re)occurring so let me know if I can provide any further help here.

[Christian Boos]

This issue is addressed in #8813.