[08:20:45] PROBLEM - MariaDB sustained replica lag on s7 on db2121 is CRITICAL: 350 ge 2 https://wikitech.wikimedia.org/wiki/MariaDB/troubleshooting%23Replication_lag https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-server=db2121&var-port=9104 [08:21:09] ^ that's me [08:29:27] I'm currently executing a long running process analysing commons media backups [08:29:52] going for a walk while it happens and thinking of next steps [08:49:49] RECOVERY - MariaDB sustained replica lag on s7 on db2121 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=db2121&var-port=9104 [09:51:29] process is still going by the letter 'B' (although to be fair, ther are tons of files starting with a number) [10:17:12] performance of a virtualised swift cluster isn't great, who knew? :) [10:17:59] (I needed to also set up dispersion reporting for the lowlatency policy in the pontoon-ms cluster) [10:39:29] yeah, usually virtual storage sucks, unless spetialized solutions [10:39:55] Emperor: is this a test cluster you mention? [11:00:54] jynus: there's a swift cluster inside pontoon, which is indeed for light testing [11:04:50] I wouldn't mind adding swift backups that that cluster for integration testing 😇 [11:08:24] we should probably at least let me finish breaking it first :) [11:09:07] yeah, no rush, just giving early wishes [11:55:15] there will be around 2.5 million new files uploaded to commons since september- probably should run backups more frequently :-D [12:01:05] godog: not sure if this is a pontoon thing or something stupid I've done, but I've set up an rsyncd on ms-fe-01 in pontoon, and I can connect to it from ms-fe-01 but not from puppet-01, even though tcpdump on ms-fe-01 shows packets arriving from puppet-01.swift.eqiad1.wikimedia.cloud and rsyncd.conf has hosts allow = puppet-01.swift.eqiad1.wikimedia.cloud localhost. Rsync isn't even logging the connection arriving, which is making m [12:01:05] wonder if there isn't something else getting in the way? [12:01:42] lsof shows rsync listening on *:rsync [12:06:16] (and commenting out the host allow line doesn't change this behaviour, so I don't think it's that I fouled that up somehow) [12:07:08] urbanecm: thanks for accommodating! :) [12:07:34] marostegui: no problem! [12:17:44] Hm, yes, iptables is blocking [12:24:02] right, it seems that as well as setting up an rsync::server::module I also need to separately enable a ferm::service? Hmph. [12:24:38] Ah, there's a defaults-to-off auto_ferm [13:21:23] sobanski: Hello! I think nicholas/balloons talked to you about wmcs getting some help with toolsdb maintenance; does that sound familiar? [13:22:27] andrewbogott: Lukasz is mostly out today [13:23:31] ok. Maybe I'll just make a general call, then: who wants to help me with this? :D [13:24:22] with what? [13:26:19] The toolsdb databases are running on Stretch VMs, on hypervisors running Buster. I'm hoping someone will assist with a replication/failover/upgrade dance that will get things onto modern distros without a ton of downtime. [13:26:52] some context on T301949 [13:26:53] T301949: ToolsDB upgrade => Bullseye, MariaDB 10.4 - https://phabricator.wikimedia.org/T301949 [13:27:13] andrewbogott: if you are able to upgrade the OS without formatting the data partition, you should be fine. We did it production [13:27:40] andrewbogott: I will bring this to our team meeting on monday if that's ok? [13:27:56] yep, that'd be great. thank you! [13:28:21] marostegui: that in-place upgrade is how I'm leaning as well, although it still means some amount of downtime I think. [13:29:00] andrewbogott: Do you have master-slave environment? [13:29:41] Yes (with one slave) but iirc not all of the databases are replicated due to lag issues. I need to investigate this further [13:29:51] Brooke had all of this in her head but I'm playing catch-up. [13:29:55] Ah right, so the normal toolsdb inventory [13:30:06] yeah [13:30:06] Yeah, I think I know that environment, are those the old labsdb1004-1005? [13:30:23] I think that's right, yes. [13:30:52] The part that seems scariest to me is the mariadb version upgrade -- should I not be worrying about that bit? [13:31:35] andrewbogott: We've upgraded that in production without many issues, as long as you stop mariadb before the reimage and then start it with replication stopped...once the daemon is up, you can run mysql_upgrade and it should be ok [13:32:00] I am not sure if that environment use specific 10.1 things that might be deprecated in 10.4 [13:32:03] ie: tokudb [13:32:18] Seems unlikely but I wouldn't stake my life on it [13:32:32] Double check it, as tokudb isn't supported on 10.4 [13:32:36] So that can be a pain [13:33:34] Regarding the downtime....you can either assume the reimage downtime or...copy the data to another host and promote that host to master,although you'll need to assume the copy time downtime (as mysqld needs to be stopped, as xtrabackup won't work with 10.1 -> 10.4) and if I remember correctly it was quite a lot data [13:33:38] TBs right? [13:34:32] Another option would be to upgrade the replica, promote that to master and then reimage the old master and then promote it again. But as you mentioned there's data that is not replicated to that slave so you'd have some incosistencies there [13:34:35] looks like 2.7T [13:34:52] ok, I'm at the mariadb prompt; how do I check for tokudb? [13:35:00] andrewbogott: first: show engines; [13:35:16] is it enabled? [13:35:16] https://www.irccloud.com/pastebin/0wFwKH1P/ [13:35:23] looks like not [13:35:29] then you are safe [13:35:33] great :) [13:35:42] One less thing to worry about [13:36:27] the scheme that taavi mentions on that ticket also seems possible: build a whole new DB on modern distro/mariadb, replicate everything over there, make that the new master. [13:37:01] yes, that's what I said above. But you'd need to stop mysql for that copy [13:37:10] if that's doable, then that's the safest thing to do [13:37:13] ah, ok, sorry, rereading... [13:37:58] so that would mean hours (days?) of downtime during the copy [13:38:16] 2.7tb shouldn't take that long to copy (hours) [13:38:35] I would upgrade the replica first [13:38:38] And see what you face there [13:38:54] And how long it takes,as the reimage is probably faster than the copy if it goes well [13:39:14] If the replicas didn't have replication filters, you could upgrade the replica and promote it to master [13:39:36] ok. the addition of hv upgrades complicates this but let me try to write down the steps... [13:39:36] But as it has different data from the master....that's dangerous [13:39:56] andrewbogott: I will bring this up in our Monday meeting and check who can assist [13:41:57] thanks. [13:41:58] andrewbogott: what are the hostnames of these two hosts? [13:42:22] I'm actually not sure that the reimage-and-save-data-partition thing makes sense for a VM now that I think about it more :/ [13:43:00] I don't 100% know which is master and which is replica, but here are the hosts involved: clouddb1001, clouddb1004 on HV cloudvirt1019; clouddb1002, clouddb1003 on HV cloudvirt1020 [13:45:49] ah so you have 4 DBs? [13:46:03] in the past it used to be 1 master and 1 slave [13:46:55] I think those other ones are smaller/less important but I need to research [13:48:57] ok, yes, those are postgres servers so I think they're used by the maps project which is more tolerant of downtime [13:49:23] although of course I know even less about how to maintain postgres :( [13:50:55] yeah I cannot help there [13:53:22] so which ones are the MySQL ones? [14:01:07] Ah I see, 1004 and 1003 are the osm ones [14:01:12] so 1001 and 1002 are the mysql ones [14:02:44] Emperor: yes indeed, ferm/iptables as you identified [14:03:26] andrewbogott: I have commented on the tas [14:03:27] task [14:03:56] marostegui: thank you! I suspect we'll need to do this in the most tedious way possible but I'll do some more research in the meantime