launchpad-dev team mailing list archive

[Edwin Grubbs <edwin.grubbs@xxxxxxxxxxxxx>]

On Wed, Aug 11, 2010 at 8:08 AM, Guilherme Salgado
<salgado@xxxxxxxxxxxxx> wrote:
> On Tue, 2010-08-10 at 17:08 -0500, Edwin Grubbs wrote:
>> After looking at bugs 353950 and 615654 and their oopses, it appears
>> that the two main causes of the timeouts are
>> TeamMembership._fillTeamParticipation() and
>> TeamMembership._sendStatusChangeNotification(), which sends emails to
>> all the members of a team individually if it does not have a preferred
>> email address.
>>
>> The _sendStatusChangeNotification() method would be easy to batch, and
>> that would change the experience in the UI at all. However, the
>> _fillTeamParticipation() method is currently loading all the members
>> and submembers of a team and iterates over them, running a separate
>> query to check if the new superteam has an entry in the
>> TeamParticipation table and then inserting an entry by creating a
>> storm object that isn't going to be used after the insert. It seems
>> like even huge teams could be handled rather quickly with a query such
>> as:
>>
>>
>> INSERT INTO TeamParticipation (person, team)
>> SELECT Person, NEW_TEAM_ID
>> FROM TeamParticipation tp1
>> WHERE team = OLD_TEAM_ID
>>     AND NOT EXISTS (
>>         SELECT 1
>>         FROM TeamParticipation tp2
>>         WHERE tp2.person = tp1.person
>>             AND tp2.team = NEW_TEAM_ID
>>             );
>>
>
> I don't understand what NEW/OLD _TEAM_ID are supposed to be here, so I
> can't comment on this.


Oops, I don't know why I did that. It should be MEMBER_TEAM_ID and
SUPER_TEAM_ID, so all the member team's participants get added to the
super team if they don't already exist on the super team.

INSERT INTO TeamParticipation (person, team)
SELECT Person, SUPER_TEAM_ID
FROM TeamParticipation tp1
WHERE team = MEMBER_TEAM_ID
    AND NOT EXISTS (
        SELECT 1
        FROM TeamParticipation tp2
        WHERE tp2.person = tp1.person
            AND tp2.team = SUPER_TEAM_ID
            );

-Edwin

> One thing you might want to consider, though, is to make
> TeamParticipation maintained solely by triggers, using the same approach
> used on PackageSets.  That'd simplify things considerably, but it'd
> probably take a considerable amount of time to implement.  I had plans
> to do that myself, but never got around to it.
>
>> Is there is any reason that this hasn't been done already? Also, is
>
> Nope, except for the fact that the existing implementation is very
> simple and works well in most cases.
>
>> there anything else that could be taking up a lot of time that is not
>> readily apparent from the oopses?
>>
>
> It's very likely that _fillTeamParticipation() is one of your culprits
> here; we had similar problems with _cleanTeamParticipation(), which also
> used to do everything using python/storm but now does most of its stuff
> using SQL directly.
>
> --
> Guilherme Salgado <https://launchpad.net/~salgado>
>