1159431 - Revisions feeds load very, very slowly

Status: RESOLVED FIXED

(developer.mozilla.org Graveyard :: API, defect)

Description

[Eric Shepherd [:sheppy]]

The MDN RSS feeds for recent documents and recent revisions are both very slow, with the revisions feed not loading at all most of the time. The l10-updates feed is failing to load at least sometimes, too. This is troublesome, since the l10n teams use it to track what they need to update.

Recent documents feed: https://developer.mozilla.org/en-US/docs/feeds/rss/all (loads but slowly)

Revisions feed: https://developer.mozilla.org/en-US/docs/feeds/atom/revisions (times out essentially every time)

As has the feed of pages changed since they were last localized: https://developer.mozilla.org/fr/docs/feeds/atom/l10n-updates

The new files feed works fine, though, or seems to: https://developer.mozilla.org/en-US/docs/feeds/atom/files

Comment 1

[Luke Crouch [:groovecoder]]

Looks like this started when we did p2v on the MDN db servers in bug 1144314.

http://screencast.com/t/PvNRBNdf

Adding :sheeri and :jakem

Comment 2

[Sheeri Cabral [:sheeri]]

updated the configuration so the database buffer pool is now 6G in size, which is about half the size of the total database.

Comment 3

[Justin Crawford [:hoosteeno] [:jcrawford]]

:groovecoder, can you update on steps taken to date?

In bug triage, anecdote suggests these steps did not resolve the issue yet.

Comment 4

[Luke Crouch [:groovecoder]]

What :sheeri said in https://bugzilla.mozilla.org/show_bug.cgi?id=1159431#c2 ... we increased memory to the DB server, so at least it stopped taking the site down.

Agree with the 'minor' severity for now, since the revision dashboard isn't affected by these queries. May need to bump the severity later if we start to see more problems that affect the rest of the site too.

Comment 5

[Sheeri Cabral [:sheeri]]

Are these feeds read-only? If so, they're great candidates to put on the read-only VIP. Right now the slave is a failover point and no queries are running against it, so by naming a new handler and using the read-only VIP you might be able to glean more performance.

Comment 6

[Luke Crouch [:groovecoder]]

They are read-only. We'll look at adding multi-db-router sometime so we can route reads to the slave db.

Comment 7

[Jannis Leidel [:jezdez]]

I've made some improvements to the SQL queries we use in bug 1169673 that should have made the revision feed a lot faster ~3 seconds. We still do some heavy content diffing when rendering the feed, but the LB should cache the response at least.