[09:22:04] Amir1: do you have access to https://orchestrator.wikimedia.org/ yet? [09:23:10] now I do [09:23:14] \o/ [09:23:48] cool! [09:24:09] You are not in the superusers group (so you cannot make changes to the replicaiton topology) but we can sort that out later [09:24:17] Even if we don't use orchestrator for that yet [09:24:57] thanks [10:10:29] Hello. I have a quick question about MariaDB failover, related to what ottomata was discussing yesterday. [10:10:48] * kormat stares dubiously at 'qukc' [10:10:50] er [10:10:51] *quick [10:11:30] We have these two new dedidated MariaDB servers being installed now. What is the state of the art here in terms of HA and automatic/managed failover? [10:11:48] state of the art is: do not do automatic failover [10:11:56] Could we use orchestrator to manage this? What about HAProxy? [10:12:41] orchestrator could, in theory, manage this. but we're quite a long way from being confident it'll do the right thing in prod [10:12:54] haproxy would allow read-only access to continue if the primary goes away [10:13:13] btullis: In theory you can use both, but I wouldn't recommend implementing any automatic failover if you are not ready for some split brain anytime :) [10:13:20] (assuming you're in the same DC. can't do ssl with haproxy, so no cross-dc traffic) [10:14:33] Thanks both. Yes, in the same DC. I was hoping for something for something like this: https://github.com/openark/orchestrator/blob/master/docs/topology-recovery.md#graceful-master-promotion [10:14:39] read-only as your replica should be read-only, otherwise you're in for a World of hurt. [10:15:03] btullis: We do have orchestrator in production, but we are not trusting it for failovers yet (and won't happen in short term) [10:15:29] btullis: the thing you linked is a very different thing [10:15:35] we do do that sort of thing, using our own tools [10:15:43] it still requires a short read-only window [10:16:16] (i wouldn't put it in a HA category) [10:19:36] OK, cool. I did say I'd be interested in both automatic /and/ managed failover cases. I see that there are still some references to MHA tools here. https://wikitech.wikimedia.org/wiki/Switch_master#Using_mha_for_planned_failovers [10:20:03] btullis: Forget about mha :) [10:20:28] OK. *ping* Gone. :) [10:20:44] I don't think it is even maintained anymore [10:20:50] btullis: for managed failover, if you have a setup that's similar to prod, you could use our tooling [10:21:17] but so far otto has been determined to do something different :) [10:21:34] btullis: Doing automatic failover _right_ is VERY hard [10:22:09] Unless you consider your data volatile and wouldn't mind about possible incosistencies [10:22:26] and/or needing to restore from backups [10:22:29] btullis: As I told otto, I rather have downtime than corrupted data [10:22:58] marostegui: Yes, agreed. I'm not trying to force something new in, that's why I was just asking what's the state of the art here :-) [10:23:20] kormat: > you could use our tooling [10:23:32] That's what I was asking about :-) [10:23:36] btullis: I would eventually like orchestrator to do it for us (like in my previous job) but we are not close to that yet [10:23:52] btullis: ah. i wouldn't call it state-of-the-art :) [10:24:16] btullis: Our tooling is to do planned failovers, not emergency ones, in fact it wouldn't work if the master isn't available [10:24:31] ^ [10:25:02] That is tracked at https://phabricator.wikimedia.org/T196366 [10:28:44] OK, understood. Thanks again both. For what it's worth, I used to use Corosync and Pacemaker quite extensively for this: https://github.com/Percona-Lab/pacemaker-replication-agents/blob/master/doc/PRM-setup-guide.rst [10:28:44] ...but I'd be very interested to track the progress of our Orchestrator implementation. Maybe we've got a few databases that would be good candidates for early adoption status. ;-) [10:29:18] btullis: My plan would be to start with parsercache, since that data isn't super critical [10:29:56] btullis: Keep in mind that orchestrator can actually either fix your topology and not change the master or both [10:30:04] It can also give you the option to recover it or do it itself [10:30:07] btullis: you'd probably want to run your own orchestrator instance, fwiw [10:30:08] So it is quite flexible [10:30:15] (but that's probably not that hard) [10:31:11] relocating [10:31:13] ah, corosync/pacemaker. i have, uh, memories of those [10:32:35] Awwww I now remember https://mysql-mmm.org/ [10:33:05] btullis: manuel's testing of orchestrator changing replication tree: https://phabricator.wikimedia.org/T267133 [10:33:55] Ah thanks. And HAProxy is what we're using for high-availability and master-switching, right? I've also used proxysql in this capacity before. [10:34:27] btullis: we only use haproxy in a very small part of our infra [10:34:33] Not in MW production [10:35:04] MW has its own in-built loadbalancer, for better or worse [10:35:20] Ah, cool. [10:35:21] btullis: The haproxy part only handles the standby master in case of master failure, but obviously not the replication topology part, but in that part of the infra we only have 2 slaves, so not a big deal [10:38:22] Yes, directing writes to the active master. Not changing the replication topology. [10:38:52] yes, but we keep the standby master in RO, so it requires manual intervention to enable writes there, just in case there's a flapping issue [10:41:25] OK, well thanks again both. Lots to learn and think about. Just trying to think how we can make this pair of new DB servers fit with the current tooling, whilst not ruling out potential options for HA in future. [10:41:30] haproxy does 2 things for us (aiui, manuel can correct me if i'm wrong): [10:41:41] 1) if the master goes down, read-only queries can still succeed [10:41:59] 2) if we change master, applications don't need to have their config updated, as from their POV the host:port don't change [10:42:36] you are right! [10:42:42] 😅 [10:45:18] OK, thanks. So HAproxy doesn't *detect* the current master and change the routing accordingly? [10:45:52] btullis: yes, it has a primary and a secondary, if the primary goes down, it fails over to the secondary [10:45:59] I should learn not to preface my questions with the work /quick/ - kormat was right again :-) [10:46:03] :D [10:46:05] but the secondary is on read only [10:46:27] Oh I see. Gotcha. [10:46:50] btullis: the only thing 'quick' with databases is how quickly you regret getting involved with them. ;) [10:47:11] but if you enable writes on the secondary, will get the writes, but the primary's replica will not be moved, so you'd need to rebuild the crashed master and its replica [10:50:13] Yep, understood. Hence the question about our progress with orchestrator or similar. I'm going to leave now, before I do something stupid like mention Galera :-) [10:50:28] hahaha [10:51:53] Thanks again both. [10:52:13] btullis: <3 [10:59:57] kormat: can I add 10:46:50 btullis: the only thing 'quick' with databases is how quickly you regret getting involved with them. ;) to bash? [11:00:10] RhinosF1: be my guest :) [11:56:31] kormat: https://www.mangodb.io/ [11:56:48] afk for baber and dentist and travel [11:59:02] PROBLEM - MariaDB sustained replica lag on m1 on db2078 is CRITICAL: 12.4 ge 2 https://wikitech.wikimedia.org/wiki/MariaDB/troubleshooting%23Replication_lag https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-server=db2078&var-port=13321 [12:00:12] RECOVERY - MariaDB sustained replica lag on m1 on db2078 is OK: (C)2 ge (W)1 ge 0 https://wikitech.wikimedia.org/wiki/MariaDB/troubleshooting%23Replication_lag https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-server=db2078&var-port=13321 [14:12:15] > but so far otto has been determined to do something different :) [14:12:15] awwwww, i wouldn't say 'determined', but I certainly tried. [14:12:39] latest attempt is trying to go with everything yall recommend [14:34:56] PROBLEM - MariaDB sustained replica lag on m1 on db2078 is CRITICAL: 35.4 ge 2 https://wikitech.wikimedia.org/wiki/MariaDB/troubleshooting%23Replication_lag https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-server=db2078&var-port=13321 [14:43:09] RECOVERY - MariaDB sustained replica lag on m1 on db2078 is OK: (C)2 ge (W)1 ge 0 https://wikitech.wikimedia.org/wiki/MariaDB/troubleshooting%23Replication_lag https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-server=db2078&var-port=13321