Repeated query inefficiencies

[pickscrape@…]

I just discovered that trac issues the following query:

SELECT username,action FROM permission;

At least 74 times per page on my trac install.

Looking at my query logs, 74 just happens to be the number of rows returned by this query:

   SELECT DISTINCT s.sid, n.value, e.value
     FROM session AS s
LEFT JOIN session_attribute AS n
       ON (n.sid=s.sid   and n.authenticated=1
      AND  n.name = 'name')
LEFT JOIN session_attribute AS e
       ON (e.sid=s.sid
      AND  e.authenticated=1
      AND  e.name = 'email')
    WHERE s.authenticated=1
 ORDER BY s.sid;

Which gets run before the repeated query.

On the roadmap page the query is run 1333 times, which just happens to be the number of milestones we have (18) times 74 + 1 (in this case that adds up to something like 200ms of additional page runtime). On that page the query to get the list of users is also run once per milestone. Other queries are also run multiple times (e.g. SELECT name,value FROM enum WHERE type='severity' ORDER BY CAST(value AS int)).

Furthermore, we don't actually have 74 active users: that number is more like 23, The 74 includes all users that we have ever had, most of which have been removed from within the Admin UI.

Obviously, this doesn't look right. I have had a look at the code, and there appears to be a number of functions in trac/perm.py that run these queries with no caching, so every time they are called the queries are run again with the same results.

There may be some factor here that I'm not aware of, but adding caching to some of these functions seems like quite an easy way to make trac a lot more efficient.

[Remy Blank]

This might be related to #6436.

[anonymous]

Forgot to specify the version.

[Christian Boos]

Replying to rblank:

This might be related to #6436.

Could be, yes.

@pickscrape, do you have the following setting in your trac.ini file?

[ticket]
restrict_owner = true

Does turning it off make a difference for your measuring?

[pickscrape]

We do have that turned on yes. I'll try the experiment you suggest later on today once all other users have gone home.

[Christian Boos]

Replying to pickscrape:

We do have that turned on yes.

See #6436.