Launchpad itself

Bug #723999
Comment #9

Comment 9 for bug 723999

Revision history for this message

Gary Poster (gary) wrote on 2011-02-24: Re: [Bug 723999] Re: BugNomination:+edit-form structural subscriptions taking 4.8 seconds during nomination editing POST

On Feb 24, 2011, at 3:08 PM, Robert Collins wrote:

> - Showing the subscribers abstractly--for the web UI, in particular--is
> a different question than the one this query is asking. The latter takes
> structural subscription filters into account, and the former does not.
> The code that is timing out is exclusively about figuring out who should
> be notified for a particular notification, not about who is subscribed
> in the abstract. The notification code could be run offline.
>
> This is very relevant to the discussion about whether we should show
> subscribers : direct subscribers are equally interesting to 'who will be
> notified', if the question people want to answer is is 'will fred be
> told'.

Direct subscribers are not part of the problem here. Structural subscription filters are the key difference, as I said. Perhaps I miss your point.

In any case, it is a good question whether these pages will need to consider filters in the future, and one we had not considered deeply. I will ask Jono. It would have pros and cons.

> The bind variables are now rendered in the OOPS: skip the 'long
> requests' and 'repeated requests' sections - go down to the complete
> query list and each of those has been substituted already - you may need
> to handle the occasional u"foo" because we capture the variables outside
> the sql layer, but other than string literals it should be good to go.

Ah, thank you! Cool, and good to know.

I did do an explain on staging and nothing looked obviously wrong to my untrained eye other than it being very large. I then did an explain analyze and was surprised to see much shorter query times than I expected. I then turned on \timing and ran the queries. I consistently got around 60-70 ms for the first query, returning 78 rows. This is in comparison to 1499 ms on the OOPS report.

Maybe something else was going on in the production database at the time? Maybe the production database is significantly different?

We've had about 5000 of these in the past week, though, according to the OOPS tool. I don't know. :-/

I'll ask a LOSA to try this on production if I haven't heard back from Stuart or others.

On Feb 24, 2011, at 3:08 PM, Robert Collins wrote:

> - Showing the subscribers abstractly--for the web UI, in particular--is
> a different question than the one this query is asking. The latter takes
> structural subscription filters into account, and the former does not.
> The code that is timing out is exclusively about figuring out who should
> be notified for a particular notification, not about who is subscribed
> in the abstract. The notification code could be run offline.
> 
> This is very relevant to the discussion about whether we should show
> subscribers : direct subscribers are equally interesting to 'who will be
> notified', if the question people want to answer is is 'will fred be
> told'.

Direct subscribers are not part of the problem here.  Structural subscription filters are the key difference, as I said.  Perhaps I miss your point.

In any case, it is a good question whether these pages will need to consider filters in the future, and one we had not considered deeply.  I will ask Jono.  It would have pros and cons.

Ah, thank you!  Cool, and good to know.

I did do an explain on staging and nothing looked obviously wrong to my untrained eye other than it being very large.  I then did an explain analyze and was surprised to see much shorter query times than I expected.  I then turned on \timing and ran the queries.  I consistently got around 60-70 ms for the first query, returning 78 rows.  This is in comparison to 1499 ms on the OOPS report.

Maybe something else was going on in the production database at the time?  Maybe the production database is significantly different?

We've had about 5000 of these in the past week, though, according to the OOPS tool.  I don't know. :-/

I'll ask a LOSA to try this on production if I haven't heard back from Stuart or others.