1045190 - MySQL database for Review Board

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Mark Côté [:mcote]]

We need a MySQL database stood up for Review Board (https://wiki.mozilla.org/Auto-tools/Projects/CodeReviewTool).  20 GB should be good, but feel free to throw a few more GB in there if you want. :)

We want to deploy in a few weeks if possible, but we'll need the db before we can do much else.

Comment 1

[Mark Côté [:mcote]]

Also, we'd like redundancy to phx, since this will be a critical system for developers.

Comment 2

[Sheeri Cabral [:sheeri]]

When you say 20G you mean of disk space for the database, right? (just checking that you don't mean something like 20G RAM).

If that's the case, we can spin up 2 virtual machines in phx and 2 virtual machines in scl3, and make read and write VIPs in each data center. 

Can the application handle the fact that autoincrement values will increment by 2, instead of 1? That way writes to scl3 can be all the odd numbers, and writes to phx can be all the even #'s, and not worry about conflicts there.

Comment 3

[Mark Côté [:mcote]]

Yeah, 20 GB of disk space for the db.  And yes, Review Board should be fine with autoincrement of 2; it might be a bit weird, since we generate multiple review requests for one review, but it shouldn't be a problem.

Comment 4

[Sheeri Cabral [:sheeri]]

OK, VMs requested.

Comment 5

[Sheeri Cabral [:sheeri]]

VMs received, running puppet to install software and pick up configurations.

Comment 6

[Sheeri Cabral [:sheeri]]

Working from: https://mana.mozilla.org/wiki/display/SYSADMIN/How+to+onboard+a+new+MySQL+server

Need to do:
Nagios alerting
Newrelic trend graphing
backups
replication
Load balancer
set the root pw so puppet doesn't fail on grants

Comment 7

[Sheeri Cabral [:sheeri]]

set the root pw and put it in hiera, puppet runs smoothly now.

Comment 8

[Sheeri Cabral [:sheeri]]

Backups are set up. Replication is set up except the cross-DC links, which need netflows.

Comment 9

[Sheeri Cabral [:sheeri]]

cross-DC links are open, working on making replication via SSL for the repl user (so, all replication users will require secure replication. It's just easier that way).

Comment 10

[Sheeri Cabral [:sheeri]]

all repl users on reviewboard are now using SSL.

Comment 11

[Sheeri Cabral [:sheeri]]

DONE:
        Set up MySQL and Backups
        Database Setup
            Adding the Server to Puppet
            Adding the Server to Newrelic
        Backups
        Replication

In progress:
        Load Balancer - bug 1050956 and bug 1050957

Left to do for DBA team:
        Nagios Monitoring

Comment 12

[Sheeri Cabral [:sheeri]]

Added to Nagios, it's all green.

Left to do: set up checksums so that they run on different databases in phx and scl3.

Comment 13

[Sheeri Cabral [:sheeri]]

Hrm, looking at bedrock that's not how we do it (we do it on different tables for heartbeat, so that's likely why I had it in my head we did it). 

At any rate, I should do it "how we do it on bedrock". :D

Comment 14

[Sheeri Cabral [:sheeri]]

Hi Mark,

Sorry for taking so long on this, summer got a little thin there with vacations and travel and such.

You should be able to connect using your existing rb_prod user and password, but please connect to:
reviewboard-rw-vip.db.phx1.mozilla.com (10.8.70.27) - for writes
reviewboard-ro-vip.db.phx1.mozilla.com (10.8.70.26) - for reads

Please use these virtual IPs instead of the hostnames. This is because when we do maintenance we will make sure the load balancer has the right information, but we may take a particular server offline.

Monitoring has been enabled. 

The last item for this bug is to remove the current rb_prod users, EXCEPT the ones that allow from the load balancer. That is, these users

rb_prod@10.22.70.94_ 
rb_prod@10.8.70.27_  
rb_prod@10.8.70.26_  
rb_prod@10.22.70.209  -- we have a 20_ privilege that includes this
rb_prod@10.22.70.95_ 

Mark, please let me know when you are connecting via the load balancers, and we'll drop the other users.

Comment 15

[Sheeri Cabral [:sheeri]]

Looking a bit deeper, it looks like you're using the scl3 databases directly. You can still use the phx virtual IPs if you want, but I'm assuming that would be for failover in a multi-home situation, since you're currently using scl3 databases.

The scl3 virtual IPs are:
reviewboard-rw-vip.db.scl3.mozilla.com - 10.22.70.95 - for writes 

reviewboard-ro-vip.db.scl3.mozilla.com - 10.22.70.94 - for reads

Comment 16

[Mark Côté [:mcote]]

Fubar's in charge of deployment to the webheads, so I'll get him to verify.

Comment 17

[Kendall Libby [:fubar] (he/him)]

Yup, we're configured to use the VIPs; PHX1 is failover so nothing should be using it yet. Nuke those extra users at your convenience; if something goes awry we'll shout. :-)

Comment 18

[Sheeri Cabral [:sheeri]]

OK, users dropped. Let me know if there are any problems and I'll put whatever you need back. We now only have the reviewboard users allowed from the load balancers.