784109 - Transition -dev MySQL to use mixed-mode replication

Status: RESOLVED FIXED

(Cloud Services :: Operations: Marketplace, task)

Description

[Matt Basta [:basta]]

Using mixed-mode replication should fix a number of issues that we're seeing, such as bug 779740.

After this has been completed and QAed, we should create a bug to perform the same transition for prod.

Comment 1

[Wil Clouser [:clouserw]]

This seems like a big deal!

Comment 2

[Sheeri Cabral [:sheeri]]

I think it's a big deal too - hopefully it will be a good one, but it will involve a LOT of testing.

Comment 3

[Sheeri Cabral [:sheeri]]

These are the databases that will be affected if we change the stage addons db to mixed binary logging:

| addons_allizom_org_new         |
| addons_dev_allizom_org_new     |
| builder_dev                    |
| builder_stage                  |
| marketplace_altdev_allizom_org |
| payments_allizom_org           |
| payments_dev_allizom_org       |

Is that OK with folks? I don't need a downtime, per se, as binlog_format is a dynamic variable, but I'd feel better restarting MySQL with the variable in place, so there's no question that the binary log is doing the right thing.

I could do that at a low period, and/or over the weekend, if desired. Just let me know when is best, assuming you'd like to proceed with this.

Comment 4

[Wil Clouser [:clouserw]]

As far as I know the only cons (or pros, for that matter) are that we move from calculating dynamic values on the slaves to actually replicating the data from the masters.  Is that true?

Comment 5

[Matt Basta [:basta]]

> Is that true?

It will be true in some cases. MySQL will use the most appropriate replication technique depending on the query. Many queries will continue to behave as they do now.

Comment 6

[Sheeri Cabral [:sheeri]]

Mixed mode - by default, MySQL will log statements to the binary log. If it comes across a statement it believes is non-deterministic, it will log the data change to the binary log.

This reduces CPU on the slave. The cost is increased bandwidth, because for the SQL statement is usually shorter than the data it changes.

Comment 7

[Wil Clouser [:clouserw]]

I think both of those comments are replying "yes," so, sure, flip it.  Just let marketplace-devs@ know when you do if it's going to take a minute to come back up.

Comment 8

[Sheeri Cabral [:sheeri]]

Did this on addons1.stage, the master:

set global binlog_format='MIXED'; FLUSH LOGS;

Then I exited mysql and re-entered (in case there was any session specialness),created a test table called foo, inserted some rows, and did a DELETE from foo LIMIT 1 (non-deterministic) and found that the logfile cooperated:

#120822 11:27:46 server id 8070139  end_log_pos 2291    Table_map: `test`.`foo` mapped to number 1935
#120822 11:27:46 server id 8070139  end_log_pos 2325    Delete_rows: table id 1935 flags: STMT_END_F

BINLOG '
oiQ1UBP7I3sAKgAAAPMIAAAAAI8HAAAAAAEABHRlc3QAA2ZvbwABAwAB
oiQ1UBn7I3sAIgAAABUJAAAAAI8HAAAAAAEAAf/+BQAAAA==
'/*!*/;

So we're good to go. I'll change the other stage machines, but it shouldn't matter since their binary logs aren't used for replication (unless we failover to one of them as the master)

Comment 9

[Sheeri Cabral [:sheeri]]

added to the other stage servers. You should be able to test this now.

Comment 10

[Matt Basta [:basta]]

I had Krupa test to see whether one of the problems we've been having is showing up, and it isn't! We'll play around for another week, but things are looking positive so far :)