[05:13:30] 10DBA, 10cloud-services-team (Kanban): Reimage labsdb1011 to Buster and MariaDB 10.4 - https://phabricator.wikimedia.org/T249188 (10Marostegui) These are the positions where labsdb1011 stopped before sending the "up-to-date" binary log to backups1002: ` May 18 05:01:04 labsdb1011 mysqld[13194]: 2020-05-18 5:0... [05:51:07] jynus: when you around, I would appreciate a safety check on: https://phabricator.wikimedia.org/T249188#6150935 to verify the current show all slaves status\G on labsdb1011 would replicate from they have to replicate [05:51:22] I am meanwhile operating the alter tables [05:51:31] for categorylinks tables and that specific index [06:25:39] <_joe_> database people, I come in peace [06:26:12] <_joe_> can any of you take a look at https://phabricator.wikimedia.org/T249065 and specifically to the proposed schema here https://www.mediawiki.org/wiki/User:MHolloway_(WMF)/Drafts/Push_DB_Schema ? [06:27:13] _joe_: will do, thanks for the heads up [07:01:09] 10DBA: inverse_timestamp column exists in text table, it shouldn't - https://phabricator.wikimedia.org/T250063 (10Marostegui) p:05Triage→03Medium [07:05:57] 10DBA: Productionize db114[1-9] - https://phabricator.wikimedia.org/T252512 (10Marostegui) [07:16:12] checking [07:20:55] :* [07:21:05] I did it quite quickly so it probably has errors [07:21:41] so no need to check? [07:21:57] no, the opposite, please check carefully [07:22:09] the positions at https://phabricator.wikimedia.org/T249188#6150935 [07:22:17] with the current show all slaves status\G [07:22:31] to make sure replication will start from where it is supposed to [07:22:44] if you copied from the same host, you wouldn't need to remove the replication information [07:22:53] it should "just work" [07:23:32] Yeah, but I did a reset all slaves and all that just to be fully clean [07:23:52] ok, is it up so I can check the data? [07:24:00] yes [07:24:06] you can run show all slaves status\G there [07:24:10] I have done all the index stuff [07:24:19] I have also done a count on categorylinks across all wikis [07:24:21] and no issues [07:24:33] just pending checking replication positions and files, and we should be good to start replication [07:24:47] I forgot one thing [07:24:58] didn't say because it wasn't really that important [07:25:18] the mydumper I took was from labsdb1009, not labsdb1012 [07:25:28] that's good [07:25:47] you may not have known that, based on the email [07:26:02] nope, I thought we used labsdb1012 [07:26:05] but that's better even [07:26:20] as labsdb1012 was cloned from labsdb1011 so we've broken that chain now [07:26:29] so that's good [07:31:36] I am doing a careful check (with data check) so please be patient [07:31:43] yes yes [07:31:44] no rush at all [07:31:54] it is easy to mess up positions with IO and SQL thread [07:32:03] so I want to make fully sure we are ok [07:32:05] db1141 restore of labsdb1011 data has completed [07:32:57] 10DBA, 10cloud-services-team (Kanban): Reimage labsdb1011 to Buster and MariaDB 10.4 - https://phabricator.wikimedia.org/T249188 (10Marostegui) The alter table to regenerate `categorylinks` indexes: ` for i in `mysql information_schema -e "select table_schema from tables where table_name='categorylinks' and ta... [07:34:06] I am wondering if we should try to do a table rebuilt for categorylinks [07:34:24] they are not too big (apart from commons, which is 100G) [07:34:24] marostegui: do you remember if you did the first transfer with no replication? [07:34:40] jynus: yes, the first transfer was done with no replication [07:34:54] then it was just rebuilt [07:34:57] you need to use: https://phabricator.wikimedia.org/T249188#6115163 [07:34:59] yep [07:35:00] for db1141 [07:35:14] I mean for labsdb1011 [07:35:19] I guess it is worth trying [07:35:24] up to you [07:35:32] I will do it I think [07:35:33] if it has crashed twice [07:35:40] I think there is nothing to lose [07:35:44] yeah [07:35:54] I will do an alter table engine=innodb,force [07:35:55] and that's it [07:36:20] check the row_format [07:36:40] I had to force the rebuilt with row_format to fix an issue [07:36:46] it should be row [07:36:52] sorry, compressed i mean [07:36:55] let me check [07:37:01] check just in case [07:37:12] it is compressed [07:37:44] kormat: can you follow https://phabricator.wikimedia.org/T249188#6115163 on your own? [07:37:54] sorry [07:37:57] I meant the wiki [07:38:16] https://wikitech.wikimedia.org/wiki/MariaDB#Recloning_a_Wiki_replica [07:38:39] yep, no problem [07:39:37] then stop it, start it again, and finally manuel's link, with the right passwords [07:39:55] kormat: remember to also run mysql_upgrade [07:40:22] ping me before start slave so I can check something else (role dissapearance) [07:40:29] ack [07:41:03] but yes, follow the guide and if there is something unclear, ask [07:41:25] I will be checking this for manuel meanwhile [07:41:48] jynus: confirmed role being gone in labsdb1011: default_role: [07:42:14] Let's also confirm on db1141 later and I will send a mariadb bug [07:56:57] all the ones replicating from db1124 are ok, now checking db1125 [07:57:52] jynus: db1141 is up and configured, but slaves not started yet. [07:57:53] I was thinking if it could have been a mydumper issue, but in this cases it cannot- as not grants were exported and imported, just the mysql tables [07:58:26] marostegui: maybe you can check the roles yourself, you will know more about that [07:58:41] while I continue checking labsdb1011 [08:00:01] kormat: how much time did mysql_upgrade took, 15 minutes or so? [08:00:09] jynus: thanks for checking the coordinates - I will check the roles now on db1141 [08:00:18] jynus: more like 2-3 minutes i think [08:00:25] oh, interesting [08:00:35] I think it is much slower on other hosts [08:00:46] i should file a task to get timestamps added to root's bash_history in prod [08:02:30] no prob with that, we should also really work towards not using bash at all and automating things + federated logs [08:02:35] the roles things smells like a bug [08:02:44] they are gone there too? [08:02:52] it is even weirder, let me prepare a paste [08:02:55] oh [08:03:16] I indeed saw weird things I remember [08:03:35] like maybe being there but not being effective or something, will let you share first [08:05:01] https://phabricator.wikimedia.org/P11243 [08:05:02] XD [08:06:50] and this was a copy from labsdb1009 [08:06:54] yeah [08:07:08] the ones on labsdb1011 was with no touching, right? [08:07:14] I remember I filed a bug about roles when we started using them with the new labsdb hosts 3 years ago [08:07:23] Yeah, labsdb1011 haven't been touched [08:08:04] wait, but we still don't know 100% what happened [08:08:42] as db1141 was after load [08:08:51] we have narrow it down [08:09:03] but we don't know if it was the load or the mysql_upgrade [08:09:11] or the restart after then [08:09:32] as this is not high priority, I would do later a more controlled test [08:09:43] yeah, we don't need to rush on this [08:09:50] let's fix the roles [08:09:53] we can probably inspect the text files from the backup even [08:09:53] manually [08:09:58] yeah [08:10:05] or import only the mysql tables [08:10:10] that too [08:10:13] anyways, for later [08:10:16] I will get the roles fixed [08:12:50] ok, fixed on both hosts [08:18:33] all positions on labsdb1011 checked, including data check [08:18:55] what do you mean with data check? [08:21:10] I not only checked that the positions on show slave matched the ones logged [08:21:16] aaah right [08:21:31] I checked that the ones on show slave point to the place that it should through binlog on the 2 masters [08:21:39] <3 [08:21:45] excellent [08:21:50] I will wait for the alters to finish [08:21:57] and once done, start all slaves [08:22:09] let's start db1141, ok, kormat? [08:22:22] let's start the io_threads first [08:22:23] do we want to recreate indexes there? [08:22:26] nah [08:22:39] it is the old import [08:22:47] sounds good [08:22:50] jynus: io_threads? [08:22:51] if it happens again, I want to see it repeating on a different host [08:22:57] cool [08:23:02] I really hope this is a HW issue [08:23:03] kormat: lets run there [08:23:53] START ALL SLAVES io_thread; [08:24:09] I can explain later that, don't worry:-D [08:24:21] for now, it just means connect, but do not alter the data [08:24:42] ok, can confirm the threads are running [08:24:44] so we check connections happen and we don't have firewall/network/user grants issues [08:25:33] no one is connecting/filed/no, right? [08:25:37] *failed [08:25:52] on io_threads, of course [08:25:57] i'm just looking at the `Slave_IO_Running: Yes` field in show slave all status\G [08:26:02] cool [08:26:03] where should i be looking? :) [08:26:07] that's the place [08:26:23] so that worked [08:26:35] now let's start the writing to the db [08:26:37] is there some way to query just this without the rest of the output of slave status? [08:26:48] pager grep XXX helps [08:26:57] otherwise prometheus/tendril [08:27:04] or icinga [08:27:12] this host may not be on prometheus [08:27:15] we can fix that [08:27:23] lets start all treads now [08:27:29] START ALL SLAVES; [08:27:42] done [08:27:46] or START ALL SLAVES sql_thread; they would be equivalent here [08:27:50] now wait for it to explode [08:28:20] kormat: I belive what you issue would be, you may have your prompt unconfigured [08:28:32] I will show you a couple of tricks I use [08:28:36] later [08:28:40] ok :) [08:28:41] https://46.media.tumblr.com/b6b775c3fd51eee7dec60287c69912aa/tumblr_p5meorFMN31wn2b96o2_r1_400.gif [08:29:01] marostegui: very apt :) [08:29:39] pager grep Running [08:29:53] show all slaves status\G [08:30:00] those 2 command will help here :-D [08:30:18] everything looks ok so far [08:30:40] lets get db1141 into prometheus [08:31:05] I will pm as manuel already knows this [08:31:17] if you have time, of course [08:31:21] sure [08:34:09] no pms needed, you already knew how to handle prometheus [08:39:20] to confirm (I am updating the task) you guys cloned db1141 from backups1001's backup right? [08:43:19] yes [08:43:31] cool thanks [08:43:45] 10DBA, 10cloud-services-team (Kanban): Reimage labsdb1011 to Buster and MariaDB 10.4 - https://phabricator.wikimedia.org/T249188 (10Marostegui) @Kormat and @jcrespo have finished the cloning from the backup1001 (the backup that contains the data right after the original import from the mydumper files) to db114... [08:44:49] let me know kormat when you are finished with prometheus so I can put it on my tabs :-D [08:45:06] 10DBA, 10cloud-services-team (Kanban): Reimage labsdb1011 to Buster and MariaDB 10.4 - https://phabricator.wikimedia.org/T249188 (10Marostegui) labsdb1011 has finished dropping and creating all `cl_sortkey` index on `categorylinks` table across all the wikis. Right now I am rebuilding `categorylinks` everywher... [08:45:33] replication is unlikely to break unless we f*up on first setup, what we should be monitoring now is Uptime for a crash [08:46:19] or unless a write causes it [08:46:23] but that never happened on labsdb1011 [08:46:32] as it replicated fine for like 5 days while catching up [08:46:34] I think db1141 should replicate faster [08:46:54] yeah, I think so [08:47:08] but I mean, maybe just 1 day shaved off, nothing too dramatic [08:52:22] jynus: it's added to zarcillo and tendril now [08:52:51] do you know how to force a prometheus update? [08:53:02] so we don't wait to happen automatically? [08:53:06] i assume run puppet on the 2 prom nodes in the same dc [08:53:36] it used to be on puppet [08:53:43] ohno [08:53:53] but it got removed as it changed state and people didn't like that [08:54:03] it got moved to a systemd timer [08:54:08] but it doesn't matter [08:54:19] mysqld_exporter_config.py eqiad /srv/prometheus/ops/targets [08:54:34] ^that is the command to do on the 4 prometheus hosts, as root [08:54:42] well, sorry [08:54:50] eqiad for the 2 eqiad ones [08:54:55] and codfw for the 2 codfw ones [08:55:19] we don't care about the other datacenters as we don't have prometheus host monitored there [08:55:52] normally we don't need to do that, it will eventually happen, but I think it is interesting for your task of setting up new servers [08:55:58] if you want to check them faster [08:56:19] that's useful, thanks. [08:57:34] for the other task you may be starting it may also be intersting to start recolecting .py scripts (or ideas) into spicerack [09:00:46] kormat: something may have gone badly? https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&from=1589961628890&to=1589965228890&refresh=1m&var-dc=eqiad%20prometheus%2Fops&var-server=db1141&var-port=9104 [09:01:05] oh, I see why [09:01:28] I think I may know why, [09:01:37] not your fault at all [09:01:55] special slaves need special treatment [09:02:02] because multisource [09:07:25] sending patch: https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/597476 [09:08:02] no time was invested in a proper fix beacause "multisource was going to be obsolete" [09:08:10] oh my, right [09:08:18] and here we are :-D [09:08:42] +1'd [09:09:42] let remember to remove it after it goes back to core [09:09:55] no problem in forgetting, prometheus will remind us :-D [09:20:18] it got fixed, but check if you can fix your labels here: https://grafana.wikimedia.org/d/000000273/mysql?panelId=6&fullscreen&orgId=1&from=1589966271169&to=1589966315380&var-dc=eqiad%20prometheus%2Fops&var-server=db1141&var-port=9104 [09:21:29] on it. [09:21:39] I mean, not "fix" [09:21:41] they work [09:21:44] :-D [09:21:52] just need some love :-P [09:22:17] they will be specially useful here [09:22:22] fixed [09:23:08] so worse is s2 [09:23:18] maybe there was a schema change [09:23:44] there's is one in s4 for sure [09:23:48] s2 maybe too, I cannot recall :) [09:24:31] speed 6 (s1) vs 31 (s5) [09:24:51] s5 is always suuuuper fast [09:24:52] we need to put more stuff on s5 from s3 [09:24:55] yeah [09:25:13] kormat: your adition will be super useful to balance sections [09:25:18] so thanks again [09:25:34] great :) [09:27:03] 10DBA: Upgrade and restart s1 (enwiki) primary database master: Thu 21th May - https://phabricator.wikimedia.org/T251982 (10Marostegui) Package upgraded on db1083 to 10.1.43-2 in preparation for tomorrow's maintenance. [09:30:29] 10DBA: Upgrade and restart s1 (enwiki) primary database master: Thu 21th May - https://phabricator.wikimedia.org/T251982 (10Marostegui) Maintenance day: - Silence all hosts in s1 - Set read only on s1: ` dbctl --scope eqiad section s1 ro "Maintenance on enwiki T251982" && dbctl config commit -m "Set enwiki as re... [09:32:16] all es backups except es3 finished [09:32:22] I will repool es1 and es2 hosts [09:32:44] is there anything else i need to do with db1141 for now? [09:32:55] I think not [09:33:02] just waiting for the crash [09:33:17] make sure alerting/icinga is happy [09:33:26] then you can continue with your tasks :-D [09:34:26] if we are going to reimage db1141 after testing [09:34:56] Looking at mysql.user.sql.gz the role is present [09:35:12] so it could be either the import or the upgrade, I will check [09:36:27] we can maybe make sure we will have enough space by reducing the number of reserved blocks- although I think xfs works differently than ext4 [09:37:12] ah no, it is just 32MB [09:37:17] so it wouldn't make a difference [09:43:45] jynus: want to see something funny? [09:44:10] you may scare me [09:47:42] https://phabricator.wikimedia.org/P11248 [09:49:26] so sys may need update [09:49:35] marostegui: i see that your definition of "funny" is about as reliable as your definition of "a good read" ;) [09:49:59] kormat: hahahaha [09:50:54] but I don't understand how taht could happen [09:51:01] me neither [09:51:09] is that live now? [09:51:14] yep [09:51:20] what is db1077 ? [09:51:21] btw https://phabricator.wikimedia.org/P11248#64629 [09:51:24] the testing host [09:52:59] like, if there was some kind of error [09:53:04] so what I have seen so far is [09:53:10] if I create a user with NO role assigned [09:53:12] it doesn't get wiped [09:53:26] but this is even a new thing, right? [09:53:31] yeah, definitely [09:53:37] but looks like it only affect users with roles [09:54:04] and db1077 was with 10.4 already right? [09:54:05] * marostegui goes to https://jira.mariadb.org/ [09:54:08] correct [09:55:31] check the table roles_mapping [09:55:37] the users are there [09:55:39] I did [09:55:45] what I mean is that [09:55:58] they were deleted but techinically still have the role [09:56:03] so not intended [09:56:11] as in "users with roles now are stored somewhere else" [09:56:49] this has other ramifications [09:57:07] I already didn't backed up the mysql db on non-snapshot backups [09:57:18] but we may want to do a pt-show-grants export [09:57:30] yeah, maybe that's more reliable [09:57:43] I wonder if that works well for roles [09:58:00] yeah, it does [09:58:12] the funny thing is that the user comes back to the user table if I apply the role [09:58:27] maybe "it is how it works" [09:58:37] or maybe it is just automatic user creation [09:58:44] does it have a password or is it empty? [09:58:47] yeah, but it breaks the installation [09:58:57] as we have seen with labsdb1011 and db1141 [09:59:10] so I would expect mysql_upgrade to take care of all that under the hood [09:59:15] if they've changed how that works [09:59:26] yeah, not disagreeing [09:59:32] I am just trying to understand [10:07:27] I will send a bug so they can let us know what is happening here [10:10:22] 10K IOPS on db1141, not bad [10:15:47] for db1141 [10:15:51] /dev/mapper/tank-data xfs 7.6T 7.3T 282G 97% /srv [10:15:59] :( [10:16:35] we can probably drop -rw-rw---- 1 mysql mysql 537G May 9 10:45 T248086_wb_terms.ibd [10:16:39] That will come thru replication [10:16:56] we can probably just truncate it and optimize it beforehand [10:19:48] It is growing fast /dev/mapper/tank-data xfs 7.6T 7.3T 268G 97% /srv [10:20:03] I would suggest: stop all slaves; truncate that table+optimize, start all slaves [10:20:06] kormat: jynus ^ [10:20:50] ok [10:21:22] if that doesn't work, we can stop replications threads and remove relay logs [10:21:28] that too [10:21:33] it is what taking the space, not as much the data [10:21:54] yep, anything works, whatever you guys prefer, I don't mind [10:22:22] 10DBA, 10cloud-services-team (Kanban): Reimage labsdb1011 to Buster and MariaDB 10.4 - https://phabricator.wikimedia.org/T249188 (10Marostegui) We have seen more weird things with the grants, for that I have created: https://jira.mariadb.org/browse/MDEV-22645 [10:22:28] the relay logs copy may have just finished: https://grafana.wikimedia.org/d/000000377/host-overview?panelId=8&fullscreen&orgId=1&refresh=5m&var-server=db1141&var-datasource=eqiad%20prometheus%2Fops&var-cluster=mysql&from=1589959334078&to=1589970134078 [10:22:51] right on time! [10:23:32] available space should start to grow back now [10:23:54] still want me to truncate+optimize wb_terms? [10:24:12] I think it shouldnt hurt, in case we have a sudden increase [10:24:19] only truncate is needed [10:24:23] yeah, it wouldn't hurt [10:24:26] it drops and recreates [10:24:37] no need to optimize an empty, just create table :-D [10:24:45] jynus: my understanding is that it doesn't release space [10:24:54] it does on truncate [10:24:57] it doesn't on delete [10:25:06] check it if you don't belive me :-D [10:25:21] nevert trust 100% what I say, always test! [10:25:36] i'm looking at what marostegui told me, back in the early days of my wmf career [10:25:38] I might have confused kormat with that, not sure if what we ended up doing on pc was delete or truncate [10:25:51] let's do: "truncate pc000; optimize table pc000;" [10:25:57] do the truncate [10:26:00] \o/ [10:26:03] you can do the optimize later [10:26:41] but I bet you it will be a few bytes the tablespace [10:27:04] truncate does drop + create basically [10:27:12] or it used to, at leasy [10:27:51] I am open to be wrong, but please prove me wrong with a test :-D [10:28:58] truncate done, [10:29:03] `537G ./wikidatawiki/T248086_wb_terms.ibd` [10:29:20] wait a bit [10:33:01] still 537G [10:33:47] interesting, I stand corrected [10:34:07] I was wrong -unless it is due to the history list lenght [10:34:36] uff - was i still supposed to stop all slaves before doing this? [10:34:42] nah [10:34:53] phew [10:34:59] it may have been safer in general, but not an issue here [10:35:04] ok. i'll optimize the table now [10:35:24] +1 [10:36:41] `mysql wikidatawiki -e 'optimize table wb_terms;'` completed immediately. no change to file size yet. hurm [10:36:53] aha [10:37:10] * marostegui taking a break [10:37:21] first of all, you are sure it was run on db1141? [10:38:05] yes. i'm running it from that host [10:38:15] select * FROM T248086_wb_terms limit 1; returns rows [10:38:43] so either you didn't run the truncate or it didn't work [10:39:21] https://phabricator.wikimedia.org/P11251 [10:39:41] wait, shit [10:39:49] `T248086_wb_terms ` != `wb_terms` [10:39:53] hehe yeah [10:40:07] you are lucky [10:40:07] * marostegui taking a break for real now [10:40:22] that wb_terms should be empty [10:40:35] jynus: eep. phew. [10:40:38] a few months a go and in production, that would be an outage on all wikis :-D [10:40:41] not only wikidata [10:40:49] *gulp* [10:41:12] so always always double check ids and hosts connected to :-D [10:41:20] don't worry, it happens [10:41:26] try the truncate now :-D [10:41:45] i had (wrongly) assumed that the T2... prefix was just some artifact of how mysql stored the `wb_terms` table on disk [10:41:45] T2: Get salt logs into logstash - https://phabricator.wikimedia.org/T2 [10:42:00] ignore the bot [10:42:20] didn't that sound similar to phabricator tickets :-D lol [10:42:24] kormat: no, TXXXX is normally the table name I rename the tables to, before dropping them, so we are sure they're not in use, otherwise errors will show up [10:42:33] marostegui: go to break [10:42:37] I do TXXXX task number of the table that needs dropping [10:42:37] :-D [10:42:39] jynus: yes yes [10:42:40] :) [10:42:44] marostegui: pls go away :) [10:42:56] jynus: truncate -> 96k .ibd file [10:43:00] he he [10:43:03] * marostegui feels he is being pushed away, so he goes away [10:43:09] I was starting to worry I wasn't right all the time [10:43:16] sarcasm [10:43:30] :D [10:43:50] so refact m's notes :-D [10:43:52] *redact [10:44:25] well the good news is that i always keep in mind that he might be lying to me ;) [10:44:25] so I hope the non important mistake doesn't stress you, ok? [10:45:03] shit just happens [10:45:35] specially when handling hundreds of thousand of objects over thousands of servers [10:45:53] i believe some wise man once told me "just remember, it's only a website" [10:46:04] indeed [10:46:12]