Person:+mailinglist-moderate timeout : error while trying to moderate messages

[Mariano Mara]

Moderating mailing lists times out.

https://lp-oops.canonical.com/oops.py/?oopsid=1717K407

Branch: launchpad-rev-9760
Revno: 9760
SQL time: 13920 ms
Non-sql time: 2311 ms
Total time: 16231 ms
Statement Count: 1197

https://launchpad.net/%7Eopenjdk/+mailinglist-moderate

Repeated pattern:
main-slave SELECT Message.datecreated, Message.id, Message.owner, Message.parent, Message.raw, Message.rfc822msgid, Message.subject FROM Message WHERE Message.id = %s LIMIT 1
797. 11463 1ms SQL-launchpad-main-slave SELECT LibraryFileAlias.content, LibraryFileAlias.date_created, LibraryFileAlias.expires, LibraryFileAlias.filename, LibraryFileAlias.hits, LibraryFileAlias.id, LibraryFileAlias.last_accessed, LibraryFileAlias.mimetype, LibraryFileAlias.restricted FROM LibraryFileAlias WHERE LibraryFileAlias.id = %s LIMIT 1
798. 11465 1ms SQL-launchpad-main-slave SELECT LibraryFileContent.datecreated, LibraryFileContent.filesize, LibraryFileContent.id, LibraryFileContent.md5, LibraryFileContent.sha1 FROM LibraryFileContent WHERE LibraryFileContent.id = %s LIMIT 1
799. 11467 32ms librarian-connection http://mizuho.canonical.com:8000/....msg
800. 11500 0ms librarian-read http://mizuho.canonical.com:8000/....msg
801. 11503 1ms SQL-launchpad-main-slave SELECT MessageChunk.blob, MessageChunk.content, MessageChunk.id, MessageChunk.message, MessageChunk.sequence FROM MessageChunk WHERE MessageChunk.message = %s ORDER BY MessageChunk.sequence
802. 11507 1ms SQL-launchpad-main-slave SELECT Message.datecreated, Message.id, Message.owner, Message.parent, Message.raw, Message.rfc822msgid, Message.subject FROM Message WHERE Message.id = %s LIMIT 1
803. 11510 1ms SQL-launchpad-main-slave SELECT LibraryFileAlias.content, LibraryFileAlias.date_created, LibraryFileAlias.expires, LibraryFileAlias.filename, LibraryFileAlias.hits, LibraryFileAlias.id, LibraryFileAlias.last_accessed, LibraryFileAlias.mimetype, LibraryFileAlias.restricted FROM LibraryFileAlias WHERE LibraryFileAlias.id = %s LIMIT 1

The LibraryFileContent queries will be gone on edge, because that bug is fixed.

The librarian connection and reads are a considerable amount of time; we probably want to issue many connection-reads at once rather than serialising them all, or as curtis says batch it. Batching is almost certainly needed in extremis anyway, so lets do that first.

[Mariano Mara (marplatense)]

Ok, all of the sudden it's working again.
Thanks guys!

[Curtis Hovey (sinzui)]

The issue is a timeout. There are too many messages in the queue to be reliably retrieved.You can discard/approve as many messages as you can to ensure you can use the page reliably.

I think we need to batch this page. We know some lists are not moderated often and they may have hundred of messages that Launchpad did not consider would happen,

[Robert Collins (lifeless)]

The other thing we can -and should- do is read all the LFA's at once rather than piecemeal; that will reduce DB time.

[Launchpad QA Bot (lpqabot)]

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

[Curtis Hovey (sinzui)]

I think bug 640700 relates the the batch addition.

[Robert Collins (lifeless)]

I don't think this is qa-bad, that other bug already existed; the fact that the page works now simply serves to show it up.

[Curtis Hovey (sinzui)]

I agree that this is qa-ok. Now that we know the action loop is brittle, the error we are seeing predates the batching

[Launchpad QA Bot (lpqabot)]

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