Edgewall Software
Modify

Opened 14 years ago

Closed 14 years ago

#7678 closed defect (duplicate)

Repeated query inefficiencies

Reported by: pickscrape@… Owned by:
Priority: normal Milestone:
Component: general Version: 0.11
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

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.

Attachments (0)

Change History (5)

comment:1 by Remy Blank, 14 years ago

This might be related to #6436.

comment:2 by anonymous, 14 years ago

Version: 0.11

Forgot to specify the version.

in reply to:  1 comment:3 by Christian Boos, 14 years ago

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?

comment:4 by pickscrape, 14 years ago

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

in reply to:  4 comment:5 by Christian Boos, 14 years ago

Resolution: duplicate
Status: newclosed

Replying to pickscrape:

We do have that turned on yes.

See #6436.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.