Closed Bug 1190361 Opened 9 years ago Closed 9 years ago

Offline defrag/failover for Treeherder stage and prod DBs

Categories

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

Product:
Data & BI Services Team
Component:
DB: MySQL
Type:
task
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: emorley, Unassigned)

References

Details

We'd like to do a defrag of all tables on Treeherder stage and Treeherder prod, since we've significantly reduced data usage in several tables - particularly on prod.

The defrag of the larger tables (eg job_artifact & performance_artifact) will likely need to be run offline with a failover (particularly on prod).

The nodes in question are:

prod...
treeherder1.db.scl3.mozilla.com
treeherder2.db.scl3.mozilla.com

stage...
treeherder1.stage.db.scl3.mozilla.com
treeherder2.stage.db.scl3.mozilla.com

Note: For prod, replication is unable to keep up if the load balancer points at both the RW and RO nodes, so both the RW and RO VIPs point at the same machine (ie the slave is just a hot spare, rather than being used for reads). As such, once this bug is fixed, we'll need it returned to this stage, and not with the RW and RO VIPS set to different nodes.

Many thanks!
s/returned to this stage/returned to this state/

(In reply to Ed Morley [:emorley] from comment #0)
> The defrag of the larger tables (eg job_artifact & performance_artifact)
> will likely need to be run offline with a failover (particularly on prod).

These two tables are under each of the *_jobs_* databases.
What's the best way to surface this in Pythian's queue? :-)
Sorry for the radio silence. Best way is to cc team73@pythian.com, which I've done here.

One question is, for how long have you reduced data usage? When's the best time to defrag for optimal use? (for instance, treeherder lifecycle is 6 months, though you may have changed that, we don't have details on that...but if it's 6 months, should we do it every month for 6 months? every 2 months? etc).
Our prod data lifecycle is 4 months and the data reductions were made > 4 months ago (bug 1161618 comment 0) so all data has now expired - so we only need to do this once. Thank you for CCing Pythian :-)
Aha, perfect!

Pythian - as for order of operations, defrag (OPTIMIZE) tables on treeherder2.stage.db.scl3.mozilla.com first, taking it out of the "ro" load balancer VIP, and making sure treeherder1.stage.db.scl3.mozilla.com is in the read-only load balancer VIP. Make sure to set sql_log_bin=0 so that the defrag does NOT replicate.

Then go through the failover process so treeherder1.stage is the slave, and repeat the process. Then stage will be defrag'd.

And then do the whole process again in production.
(In reply to Sheeri Cabral [:sheeri] from comment #5)
> Pythian - as for order of operations, defrag (OPTIMIZE) tables on
> treeherder2.stage.db.scl3.mozilla.com first, ...

Is there a timeline set for this? :-)
Flags: needinfo?(team73)
We can start as soon as the load balancer access is cleared :)
(In reply to Pythian Team73 from comment #7)
> We can start as soon as the load balancer access is cleared :)

I'm guessing that is bug 1187355?
Sorry Ed, it says we are not authorized to see that bug
Flags: needinfo?(team73)
That bug has now been duped to bug 1182029 (which is roughly about "try and wrangle Zeus configs so Pythian's LDAP access works, but probably needs upstream Zeus bugs fixed").
There's back and forth between Riverbed, the company that owns Zeus.

In the meantime, there are some folks who can update load balancers for you. I will update this bug first thing tomorrow with how that's done.
Ack. Thanks Sheeri. We're waiting for the issue to be cleared then.
For now, I have taken treeherder2.stage.db.scl3.mozilla.com out of the load balancer. Please proceed with defrag, remembering to make sure NOT to have anything replicate back to treeherder1.stage.
Optimized tables on treeherder2.stage.db.scl3.mozilla.com
Space before optimalization:
mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  2.5G   32G   8% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda1       969M   67M  852M   8% /boot
/dev/sdb1       394G   90G  285G  24% /data
After optimalization:
mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  2.5G   32G   8% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda1       969M   67M  852M   8% /boot
/dev/sdb1       394G   75G  299G  21% /data
So, next step is to failover stage, correct? 

Ed, when's a good time to failover stage? If there's no good time tomorrow (Friday), I can offer up Saturday almost any time, as there's a big tree-closing window on Saturday and I'm working anyway.
You can failover stage whenever you like - just update this bug at the time, so if/when we get New Relic alerts, we know it's a false alarm :-)
Hi Sheeri, 

I will be assisting you on this on Saturday.
Please let me know if we can schedule a time window for this process on Saturday.


Thanks,
Rohit Kumar
Failing over stage now.
Got caught up in a meeting and just got back to it. In the past 5 minutes the treeherder stage databases were failed over, so you can ignore recent newrelice errors.

For Pythian, feel free to defragment treeherder1.stage.db.scl3.mozilla.com, making sure not to have any commands replicate.

Rohit, it would be great if you can still help tomorrow (Saturday), so we can defragment the production slave while the trees are closed.
Hello Sheeri,

Rohit will do the treeherder1.stage tomorrow, his daytime (India timezone).
I will be available during the US daytime, so ping me when we should do the defrag on prod slave, I will be available on IRC, but if I dont reply, send us a mail, it beeps on my phone

Thank you
Janos
Acknowledged, will do.
Hello, 

treeherder1.stage.db.scl3.mozilla.com tables optimization is complete.
Please let us know if you see any issue.

Before Optimization
mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  4.7G   30G  14% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda1       969M   67M  852M   8% /boot
/dev/sdb1       394G   88G  287G  24% /data


After Optimization :
mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  4.7G   30G  14% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda1       969M   67M  852M   8% /boot
/dev/sdb1       394G   69G  305G  19% /data


Thanks
Rohit Kumar
Thanks Rohit! 

I have put treeherder1.stage.db.scl3.mozilla.com back into the load balancer as the master, treeherder2.stage is the read-only slave.
Janos - treeherder1.db.scl3.mozilla.com is out of the load balancer, and you can defrag it (this is production) any time. I'm in #db-alerts and #data if you want to check in.
Acknowledged, Will start working on it in ~10 minutes

Janos
Maintenance completed on backup4.db (treeherder-stage)

[root@backup4.db.scl3 treeherder_stage]# pwd
/data/treeherder_stage
[root@backup4.db.scl3 treeherder_stage]# du -shc .
40G	.
40G	total
[root@backup4.db.scl3 treeherder_stage]#
Maintenance completed on backup5.db (treeherder-prod)
Maintenance completed on treeherder1 (treeherder-prod)

[root@treeherder1.db.scl3 ~]# df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  6.8G   28G  20% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda1       969M   67M  852M   8% /boot
/dev/sdb1       985G   81G  854G   9% /data
[root@treeherder1.db.scl3 ~]#
We still have to defrag treeherder2, but that's the master, so we'll need to fail back sometime.
Hi Sheeri,

Please let us know when it is (treeherder2) ready to defrag.

Thanks
Depends on: 1202781
Hello Sheeri

FYI we cannot access bug: 1202781

Regards
Janos
Hi Sheeri,

treeherder2 defragmentation complete.


mysql> select @@hostname, now(), @@read_only, @@sql_log_bin;
+---------------------------------+---------------------+-------------+---------------+
| @@hostname                      | now()               | @@read_only | @@sql_log_bin |
+---------------------------------+---------------------+-------------+---------------+
| treeherder2.db.scl3.mozilla.com | 2015-09-15 06:39:39 |           1 |             0 |
+---------------------------------+---------------------+-------------+---------------+

Before:

mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  8.6G   26G  25% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sdb1       985G  321G  614G  35% /data
/dev/sda1       969M   67M  852M   8% /boot


After :

mysql> \! df -hP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        37G  8.6G   26G  25% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sdb1       985G   67G  868G   8% /data
/dev/sda1       969M   67M  852M   8% /boot


Thanks,
Rohit Kumar
Thanx! This is all complete, resolving.
Status: NEW → RESOLVED
Closed: 9 years ago
Resolution: --- → FIXED
Many thanks :-)
You need to log in before you can comment on or make changes to this bug.