Too much repeated SQL in send-bug-notifications job

[Ian Booth]

This issue was found when investigating the incident about slow sending of bug emails, see https://wiki.canonical.com/IncidentReports/2011-03-24-LP-bugmail-extremely-slow-or-offline

When the job is run, for each user to be notified, the following SQL is run:

SELECT <stuff> FROM Person WHERE Person.id = 1259057 LIMIT 1
SELECT <stuff> FROM EmailAddress WHERE EmailAddress.person = 1259057 AND EmailAddress.status = 4 ORDER BY EmailAddress.email

and

SELECT <stuff> FROM BugNotificationFilter, BugSubscriptionFilter, StructuralSubscription, TeamParticipation WHERE BugSubscriptionFilter.id = BugNotificationFilter.bug_subscription_filter AND BugNotificationFilter.bug_notification = 8230822 AND BugSubscriptionFilter.structuralsubscription = StructuralSubscription.id AND TeamParticipation.person = 2855352 AND TeamParticipation.team = StructuralSubscription.subscriber

There are 100s of such statements. These should be done as a single SQL select eg where person.id in (...)

I'm marking as critical because it resulted from an incident report. It may be that further analysis means this can be reprioritised lower.

[Stuart Bishop (stub)]

If you need to ensure 100s of objects are cached, you will likely need to increase the Storm cache size too. I think it will be set to 500 at the moment - it is controlled by the storm_cache_size property in the current launchpad.conf database config section. I don't know which database config section is in use by this script though - looks like the database username is configured in the [malone] section (as well as being shared by bug expiration... grrr...), so this looks non-standard and it is probably inheriting the default from the [database] section.

[Robert Collins (lifeless)]

I thought we had a non-ejected storm cache? Can we bring that back [it
fits our problem space /much/ better than an LRU cache].

[Launchpad QA Bot (lpqabot)]

Fixed in stable r12715 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12715>.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r12733 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12733>.