[09:09:07] I stopped this morning's rclone_sync, as it's just going to spam syslog to death with missing objects. [11:24:25] o/ I have started a full backup of ToolsDB on the primary (clouddb1001). so far it's going smoothly, I expect mariabackup to lock the database for writes in the last phase of the backup... from your experience, how long is the lock likely to last? seconds? minutes? it's quite a big db (3TB) [11:24:43] I would like to send out an email to Tools users with some reasonable estimation [11:25:38] context is at T301949 [11:25:39] T301949: ToolsDB upgrade => Bullseye, MariaDB 10.4 - https://phabricator.wikimedia.org/T301949 [11:30:03] it's impossible to say- it may take 1 milisecond, or it may block your database forever, including reads [11:30:27] as it depends on your workload and data structure [11:31:36] in production we always take backups from the replicas for that reason [11:32:08] e.g. if there is a long running transaction at the time, it will be blocked and could disrupt your entire process, unless you have configured a low timeout [11:32:21] (so it fails quickly) [11:33:12] yes unfortunately we don't have a working replica at the moment :/ [11:33:39] I guess I can only keep an eye on it, and try to manually kill transactions if it gets stuck [11:33:43] one thing you could do on a cloud env is switch on full consistency for the filesystem and database and create a snapshot, then copy it and recover it elsewhere, thanks to cloud functionality [11:34:05] mysql/mariadb innodb is disk-consistent [11:34:14] although it won't work with myisam [11:34:17] we do have some non-innodb tables though... [11:34:47] then you have the most difficult job in the world- make a consistent backup on a non-consistent db :-D [11:35:18] there are ways to solve that with locks and logical backups, but it will be quite involving manual work [11:35:57] yeah I think I will give mariabackup a chance, and see if it succeeds without locking forever... if the lock lasts for too long (more than a few mins), I will ctrl+c and think of something else [11:36:24] we were unsucessful on analytics, when we helped them, and asked to setup a replica [11:36:36] as they have quite a lot of long-running queries [11:36:56] we also don't use it on production, where locking for 1 second would DOS the whole stack [11:37:06] maybe you can lock stuff for longer on cloud? [11:37:13] thankfully yes [11:37:25] it shouldn't be dramatic, I saw tickets it was done before [11:37:56] I am not familiar with tools, I know it was set as read only in the past, but I don't know its current workload [11:37:58] but it will cause some disruption to some tools (hard to say which/how much) [11:38:31] e.g. some tools couldn't be replicated because they use non-OLTP queries (e.g. long imports) [11:39:16] have you considered splitting tools into smaller set of dbs? that would help later maintenance [11:39:21] workload shouldn't have changed much from the last time the db was locked in 2020 (T266587) [11:39:21] T266587: ToolsDB replication is broken - https://phabricator.wikimedia.org/T266587 [11:39:36] yes splitting is definitely in the roadmap [11:39:42] e.g. pointing some heavy dbs elsewhere [11:39:51] that would help with both load, replication and backups [11:40:23] I _hope_ I manage to upgrade it first and split it later (because upgrading is pretty urgent as it's on MariaDB 10.1) -- but we can also consider splitting first [11:40:24] (we had to do that with wikireplicas because once some size is reached, it is impossible to handle in practice) [11:40:59] that's for you team to decide, I know some things would be nice to have but not enough resources [11:41:19] but spliting when upgrading would maybe an opportunity [11:41:36] e.g. leaving the largests/heaviest dbs behind [11:42:20] yeah I considered moving the largest first, but also leaving them behind is an option [11:42:24] in any way, please note xtrabackup/mariabackups fails quite often (for me around 1 out of 20 runs) due to network or processes [11:42:57] interesting. it completed successfully once on the replica, then failed 3 times in a row in the replica. now it's at 50% progress on the primary... [11:43:57] yeah, that doesn't mean it is bad- it just means it needs tuning because it may fill its log, or be caused by network spikes on latency, etc [11:44:29] feel free to ask anything regarding xtrabackup, but the core of the work will be trial and error, I'm afraid [11:44:45] (which is why we gave up on backup in primary dbs) [11:45:05] thanks! [11:45:15] mydumper is more reliable, but it can take days to recover [11:45:37] but you may need to do logical backups when splitting it anyway [11:46:11] why do you think I would need logical for splitting? [11:46:11] we have a wmfbackups tool for automation, we have pending extending it to WMCS [11:47:29] dhinus: slitting it with a snapshot (more or less what xtrabackup does) usually requires backing up everything and then removing stuff (yes, technically there is partial backups but ejem, it takes almost as much size, plus table importing is very bad designed on mariadb) [11:47:45] splitting with a logical backups is immediate you just "read" what you want to export [11:48:18] e.g. it can let you even split by rows, it is slower but more precise [11:48:19] hmm I did a test a few weeks ago with mariabackup and "--databases" that did backup a single database in a reasonable tiem [11:48:28] *time [11:48:48] on a replica? [11:48:52] yes [11:49:03] see- that is the issue, everyting is easier on a replica :-D [11:49:09] hehehe [11:50:15] thing is- I have enough experience with xtrabackup to tell you it is a great tool (we use it!) but it is not a tool that obsoletes others (not because of the tool, but becaues of the backup method) [11:53:29] when you do 20 of those every day, you tend to see its rough edges :-): https://phab.wmfusercontent.org/file/data/jckjqg7m2przu7dkidqe/PHID-FILE-q3rri66w2fvhaov6wolp/Screenshot_20230116_125217.png [11:54:48] when you are in a more healthy status, let's try to collaborate on implementing the backup automation tooling for your data [11:56:18] sounds good, thanks! :) [11:56:35] I will keep you posted in this channel anyway [12:37:43] I can't even ssh into db1198. I'm going to power cycle it [12:40:37] +1 [12:45:46] have you tried to login via console too? [12:54:48] now it's recovering [14:36:04] the toolsdb backup failed with "InnoDB: Tried to read 294912 bytes at offset 0." (similar error I was getting when backing up the replica) :/ [14:36:21] I tried backing up only the single database where the error occurred, and that works fine [14:36:41] what could be the cause of this type of errors? [15:17:22] this task was 2015, so not that old: https://phabricator.wikimedia.org/T108081 [15:17:43] dhinus: sorry, everybody here was in a meeting [15:17:47] dhinus: corruption! [15:18:14] dhinus: export the table (if the server doesn't crash!), delete it, reimport it [15:18:32] then retry (remember what I said about trial and error :-P) [15:19:55] Emperor: let's create a ticket about swift files issue and get a way to classify the files by type (derived data vs original; referred by mw or not) [15:20:17] jynus: hmm but if the table is corrupted, why running 'mariabackup ... --databases {that_db}' works fine? [15:21:45] cannot say without looking in detail, e.g. maybe the process was not being done safely and it did some action during it; it is not easy to say without debugging [15:22:21] ha, yeah I sound like some kind of race condition :/ [15:22:25] *it sounds [15:22:50] can you understand what I meant about rough edges - even if not about the tool, the process? [15:23:02] yeah I see what you meant :) [15:23:19] and why we use it, but we have a backup plan of the backups :-D [15:23:26] the fact that it got to 85% of the backup (at least judging by the disk space) makes me (moderately) hopeful [15:23:42] there is also other possiblity [15:23:56] your are in cloud, are you on local files? [15:24:34] I don't like handling dbs in cloud storage (local or remote), it is prone to corruption, even more than normal [15:24:50] specially for 1TB+ sizes [15:24:51] the primary is a VM with a volume mounted on a local disk [15:25:15] yes, but still it is io that sometimes is unreliable by the vm [15:25:18] the new setup will include Cinder volume that might be worse (or maybe not, hard to say) [15:25:34] and that is why we don't support cloud dbs :-D [15:25:40] hehe :D [15:25:52] don't get me wrong, I am biased [15:26:19] and I will help with advice as much as I can, but we handle production in metal for a reason (and probably won't change soon) [15:26:37] specially our large dbs- smaller ones are not a huge issue [15:26:51] I think the main problem here is that it's a single huge db, that's why we should split it as we were discussing earlier [15:27:24] e.g. it scales much better -this model- for small dbs for webs [15:27:31] but that in turn has complications, and I'm still hoping I can resolve the "deprecated version" first (and also the fact we don't have a reliable replica right now) [15:27:35] yep, we reached the same conclusion [15:28:03] dhinus: if the backup is not to setup in parallel, but as a plan B [15:28:27] you can try upgrade in place- it is generaly reliable, and we have gone over it 200+ times in produciton [15:28:40] and get the backup by other method [15:28:41] the OS is also deprecated :D [15:29:27] it's still on stretch [15:30:41] our recipe allows us to reimage dbs but keeping the data partition, not sure if you can too [15:30:58] potentially yes, it's on a separate partition [15:31:06] but it's risky, we don't have a working replica atm [15:31:12] I mean we have one but it's incomplete [15:31:33] I'm thinking of retrying a full backup a couple times and see if I get lucky... [15:31:36] sorry you are in that position, I am trying to give suggestions but in the end it is you that should weight among the different options [15:31:45] yes that's very appreciated, thanks :) [15:31:50] by the way, tools used to have a replica? [15:31:56] toolsdb [15:32:06] yes, but it had issues multiple times [15:32:11] it was recreated from scratch in 2020 [15:32:20] but then again had corruption, tables that failed to replicate, etc. [15:32:26] uff [15:33:04] then it seems that multiple things failed / lot of technical debt- again, sorry you are in that position [15:33:19] haha don't worry, it's also an interesting problem :) [15:33:45] and to be fair I've seen similar situations in other companies where I worked :D [15:34:15] the thing is sadly there are no shortcuts, everything should be reviewed- getting a replica- splitting the db in smaller chunks, have regular backups, have a tested automation workflow [15:35:01] my current thinking: retry a full backup tomorrow, and once more on Wed if necessary... if that doesn't work, maybe identify the big dbs that can be excluded from the backup, and retry without those (or alternatively, backup _only_ those and restore them to a dedicated host) [15:35:26] we were like that, not very healty in our production, but slowly got better with time. I am happy to reuse any tooling and workflow and show it to you if helps. [15:36:11] thanks, I think we can definitely benefit from all the work you did for production dbs [15:36:34] for backup, I mean, it was manuel and Amir who fixed the dbs [15:37:30] e.g. we planned for sessions at https://phabricator.wikimedia.org/T284483 but never found the time [15:38:34] try to see if you can drop an recreate the offending table! [15:38:51] it is worth giving it a try [15:40:36] what I saw when trying mariabackup on the replica, is that error was happening on a different table every time... so I'm not convinced it's a problem with the table itself [15:40:50] because I can backup that table just fine now [15:41:19] let me try a mysqldump as well, but mariabackup on the db including that table worked fine [15:42:35] then you may have a consistency issues- maybe your backup options are too loose [15:42:41] or something else [15:43:53] the table that failed contains only 1 line right now [15:44:47] any backup options I could tweak? [15:45:17] I'm using a simple "mariabackup --backup --user root --target dir=/dir" [15:50:32] I have to check what is the defaults regarding locking and change monitoring, depending on the version [15:52:37] Problems solved by setting --lock-ddl-per-table (Mariabackup command-line option added in MariaDB 10.2.9): If a table exists when the backup starts, but it is dropped before the backup copies it, then the tablespace file can't be copied, so the backup would fail. [15:52:49] I am guessing youa re in 10.1 [15:53:23] "it is not recommended to execute concurrent DDL when using Mariabackup with MariaDB 10.1." [15:53:42] I don't discard your users are executing DDLs :-/ [15:53:44] yep 10.1 :/ [15:53:58] hence the race condition [15:54:12] at least we have an explanation [15:56:30] so retrying a few times _might_ work but hard to predict the odds [15:56:57] BACKUP LOCK is not available in 10.1 so not an option [15:58:24] maybe we could remove some permissions globally to enforce "no DDL statements" while the backup is running?