571368 - [k] Make SUMO 2.1 run with a master/slave DB set up

Status: VERIFIED FIXED

(support.mozilla.org :: Forum, task, P1)

Description

[James Socol [:jsocol, :james]]

Obviously there are some significant oversights in how we're handling database writes and it's causing pretty significant issues running in a replicated environment. We need to resolve those, and test on staging, before we can push this live.

Comment 1

[James Socol [:jsocol, :james]]

Erik, correct me if we forget anything:

We'll take a page from Facebook and set a cookie that will force your requests to master for ~15 seconds (configurable). This will ensure your next request has fresh data and allows us to avoid interstitial pages and *hoping* the time is long enough. 15 seconds is several sigmas out on our replication lag.

We'll also need to patch our views to make sure we're forcing queries that requires fresh data to master with `.using('default')`.

Comment 2

[Erik Rose [:erik][:erikrose]]

The first part, the cookie-based database affinity, is done: http://github.com/erikrose/django-multidb-router/tree/sticky

Now for the write-then-read queries...

Comment 3

[Erik Rose [:erik][:erikrose]]

Also done and awaiting review.

Comment 4

[Ricky Rosario [:rrosario, :r1cky]]

If you pin the initial POST request then you shouldn't need to make any changes for write-then-read queries. We do need to pin the initial POST because django does a bunch of reads when doing deletes (to emulate ON DELETE CASCADE), and there is no way to make it do .using('default'). So we need the router to handle it.

Thoughts?

Comment 5

[Erik Rose [:erik][:erikrose]]

So it checks for things referring to the row we're about to delete? Does it do that even if the underlying DB supports cascading deletes?

I have nothing against doing the POST pinning now. We lose a little master performance but gain correctness. You probably saw my comment in there to that effect.

However, there would still be races in there. My medium-term goal is to get every request pinned to one DB (whether the master or a particular slave) and then wrap each request in a transaction. That way, we would strictly avoid the race lurking in the Django-curated cascading deletes (and many other places). I'm halfway through writing up a bug on that and plan to talk with Tim Ellis next week to learn more about how the DB load balancer works.

Comment 6

[Ricky Rosario [:rrosario, :r1cky]]

(In reply to comment #5)
> So it checks for things referring to the row we're about to delete? Does it do
> that even if the underlying DB supports cascading deletes?
Yes, it goes through and figures out foreign keys and deletes appropriately. In fact, we set the database to `ON DELETE SET NULL` but django still cascades.

Comment 7

[Erik Rose [:erik][:erikrose]]

Went ahead and pinned the entirety of all POSTs.

Comment 8

[Erik Rose [:erik][:erikrose]]

Fixed in http://github.com/jbalogh/django-multidb-router/commit/b04f1e762978e9c666c3b9f452f89e6d1f5b77d2, which kitsune uses as of http://github.com/jsocol/kitsune/commit/203e06cbb92608a8f735aa129890cb6cba649590.