[00:32:55] 10DBA, 10MediaWiki-Watchlist, 10Growth-Team (Current Sprint), 10Patch-For-Review, 10Wikimedia-production-error: Deleting large watchlist takes > 4 seconds causing rollback due to write time limit - https://phabricator.wikimedia.org/T171898 (10Catrope) >>! In T171898#4635679, @kostajh wrote: > Manually re... [05:25:21] 10DBA, 10Patch-For-Review: Drop ct_ indexes on change_tag - https://phabricator.wikimedia.org/T205913 (10Marostegui) [05:27:27] 10DBA, 10Patch-For-Review: Drop ct_ indexes on change_tag - https://phabricator.wikimedia.org/T205913 (10Marostegui) [05:41:03] 10DBA, 10User-Banyek: BBU problems dbstore2002 - https://phabricator.wikimedia.org/T205257 (10Marostegui) @Papaul if you want to test if the spare BBU works on another host, we can test it on db2042 (T202051) to see if the server boots up fine or has the same issue as dbstore2002. If you want to do that test,... [05:41:18] 10DBA, 10User-Banyek: BBU problems dbstore2002 - https://phabricator.wikimedia.org/T205257 (10Marostegui) p:05Triage>03Normal [06:47:24] so db1110 has an up-to-date version of the migrated wikis [06:47:27] from s3 [06:47:32] Nice!!! [06:47:37] you can check if there is any issue [06:48:06] otherwise I will import them on all s5 hosts except the multisource ones [06:48:23] that sounds good [06:48:47] What I checked is that show slave status still works, as you just used the "general" thread and then an additional 's3' one, which is what we discussed yesterday [06:48:53] so the failover script checks should work [06:49:03] they replicated a whole wiki of data [06:49:08] My concern was if we use two threads, each with 's5' and 's3' [06:49:10] *week [06:49:16] which show slave status will be empty [06:50:11] so they caught up a week of data for those wikis in a few hours? [06:52:06] yes, it is s3, plus only a fraction of the writes [06:52:23] e.g. debwiki may be 500GB [06:52:27] but it is all templatelinks [06:52:34] it really has very little activity [06:52:44] (compared to other wikis) [06:52:50] *cebwiki [06:53:06] Yeah [06:53:14] And also we have SSDs! [06:53:25] yes, all hosts have them there [06:53:55] except the master [06:54:05] so I expect no lag or very little [06:54:15] and it shouldn't affect the main replication thread [06:54:19] as it will happen in parallel [06:54:59] I will do a compare.py [06:55:02] for sanity [06:55:06] sounds good! [06:55:20] then put a filter on labsdb1009/10/11 [06:55:26] and dbstore1002 [06:55:40] and start the import [06:55:57] with last night's backup [06:56:28] so you'll put a filter on labsdb for 's3' channel to ignore those? [06:57:29] on s5 channel? [06:57:38] although now you made me doubt [06:57:53] it should be on s5 for now, and we can later sync and switch [06:58:28] Sure, it doesn't really matter really [06:58:33] so they don't import anything [06:58:39] as they are active [06:59:10] yes! [06:59:26] I am not going to allow replication of heartbeat [06:59:43] because from the moment they are active, they should be properly on s5, never on s3 [06:59:55] Yeah, I am trying to think if there is any drawback [07:00:05] and I thank you for that [07:00:06] Will row base replication break? because there is no s3 master row? [07:00:24] ? [07:00:36] I am going to import to the master [07:00:51] and let it replicate [07:00:55] And s5 master will not replicate heartbeat from s3? [07:01:03] no [07:01:07] then we should be good yep [07:01:29] we will see because the master is less powerful and I will enable binlogs [07:01:38] so it may take 1 full day or 2 [07:02:02] I may start other imports in parallel when it goes into 1-2 threads [07:03:31] because if not it is too inefficient: https://grafana.wikimedia.org/dashboard/db/prometheus-machine-stats?orgId=1&from=1538466997810&to=1538498409540&var-server=db1110&var-datasource=eqiad%20prometheus%2Fops [07:05:32] yeah, but the master has no SSDs [07:06:20] dbstore1001:s6 compression should finish any time now [07:06:31] and so do db1116:s8 [07:06:49] the 800 GB wb_terms is crazy [07:07:11] and that on an SSD [07:07:20] Yeah, I cannot wait till they will that table [07:19:19] 10DBA, 10Operations, 10cloud-services-team, 10wikitech.wikimedia.org, and 2 others: Move some wikis to s5 - https://phabricator.wikimedia.org/T184805 (10jcrespo) So the plan is: * Ignore the extra dbs for s5 connection on multisource host (dbstore1002, labsdb1009/10/11) with: ``` STOP SLAVE 's5'; set def... [07:20:07] marostegui: see what I may be missing: https://phabricator.wikimedia.org/T184805#4637651 [07:20:26] let me see [07:21:21] 10DBA, 10Datasets-General-or-Unknown, 10Patch-For-Review, 10Release-Engineering-Team (Watching / External), and 2 others: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and slaves - https://phabricator.wikimedia.org/T104459 (10Smalyshev) [07:22:06] • When catched up, at some point, migrate the multisource to the s5 channel -> can you explain a bit that? Not sure I understand [07:22:45] labsdbs for example, I will just ignore the import with the ignore [07:23:18] but at some point we need to migrate the tables from replicationg thouse tables from s3 to replicate those on s5 [07:23:34] we need to sync the s3 part of s5 with the one from the real s3 [07:23:38] So you'll move the ignore from s5 to s3 [07:23:40] and stop ignoring on one channel [07:23:41] exactly [07:23:45] got it [07:23:54] it is not easy, but doable [07:23:55] Question [07:24:11] but we can stop its masters [07:24:18] whick will simplify everthing [07:24:28] shoot [07:24:30] Is there a way to tell mydumper not to include DROP TABLE IF EXISTS? I know you'll have the replication filters in place and they should work fine, but just in case…? [07:24:46] is it worth researching that? [07:24:57] I don't think it does, does it? [07:25:09] why are you worried- I only import one database at a time [07:25:16] one that shouldn't exist [07:25:40] I am worried in case we see a database being dropped from labs :) [07:26:01] I havne't checked if it includes DROP TABLE, just thinking out loud [07:26:03] from labs or from sanitarium [07:26:23] because for sanitarium they are on different instances [07:26:29] yeah, I am worried about labs [07:27:21] let me check [07:28:05] Sorry to make you work more! [07:28:31] I think it shoudl complain, not drop, but I am going to check [07:29:27] yeah, to drop it should do -o [07:29:31] and we don't [07:29:36] great! [07:29:37] but I am going to check on the test host [07:29:40] just in case [07:31:16] So after that point, the last two, move replication to codfw s3 master and rename tables: looks good [07:31:16] It doesn't really matter if we leave it replicating from s3 eqiad either [07:32:43] well, it does if we want to rename/delete the dbs [07:32:43] but I need to start them locally [07:32:43] because it is safer for codfw [07:32:43] and I have only the local coordinates to start replicating [07:32:43] I am going to test importing s1 on db1118 [07:32:54] and see if it fails as I think it should [07:32:54] good [07:33:39] I cannot test because authentication issues [07:33:42] :-) [07:33:45] hahaha [07:33:45] let me try to fix those [07:37:26] so actually, the tables are overwritten [07:38:15] any suggestion beyond the replication filters? [07:39:15] To prevent that? [07:39:21] the drops [07:39:29] what you mention [07:40:00] * marostegui trying to think [07:40:21] remove the "--overwrite tables option" maybe [07:40:31] does that exist? [07:42:15] it overwrites equally without it [07:45:05] There is not much we can do apart from the filters then I think [07:45:59] the tables may not be dropped, but they are written equally [07:47:34] Yeah, but the replication filters is the only way to prevent that then [07:47:40] we'll need to trust them :) [07:47:48] note we know they don't create dbs [07:47:58] Maybe start with the smallest DB to check everything goes fine with the filters? [07:48:06] because private filters [07:48:09] so it should be ok [07:48:16] sure [07:48:31] I will start with mgwiktionary [07:48:34] Sorry to be so painful [07:52:41] it is ok [07:52:46] you can check my filters [07:52:51] while I prepare the import [07:53:16] sure! [07:53:51] Replicate_Wild_Ignore_Table: enwikivoyage.%,cebwiki.%,shwiki.%,srwiki.%,mgwiktionary.% [07:54:12] that looks good to me [07:55:35] droping the tables on db1110 is taking a non-negible amount of time [07:56:08] although it is not affecting so far lag on s5 [07:56:40] well, not a lot 0-1 seconds of lag [07:57:14] 2 minutes to drop the database [08:08:24] marostegui: so ready to start the mgwiktionary when you are [08:08:52] let's go! [08:08:55] into db1070 [08:09:02] did you downtime it? [08:09:06] like, s5 eqiad [08:09:55] nope [08:09:58] doing it now [08:10:05] only lag or lag and sql? [08:10:14] I would say lag [08:10:17] yeah [08:10:24] better know if something breaks [08:10:28] it should only alert via irc anyways [08:11:06] we are sure nothing from codfw replicates from db1070, right? [08:11:40] let's double check [08:11:57] https://phabricator.wikimedia.org/P7619 [08:12:01] all those are eqiad ids [08:12:18] (that is show slave hosts) from db1070 [08:13:55] 10DBA, 10Datasets-General-or-Unknown, 10Patch-For-Review, 10Release-Engineering-Team (Watching / External), and 2 others: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and slaves - https://phabricator.wikimedia.org/T104459 (10Marostegui) [08:20:25] myloader is running [08:20:25] let's check s5 hosts [08:20:25] is it not replicating or is it done on a single transaction? [08:21:18] I do see stuff on the slaves [08:21:27] 879978595 | system user | | mgwiktionary | Connect | 0 | init | INSERT INTO `cu_changes` VALUES [08:21:29] yeah, it was just the initial stuff [08:21:35] which goes slower [08:21:43] so I was seeing only s5 events [08:22:40] issues on dbstore1002 [08:23:00] and I am going to guess on labsdbs [08:23:33] labsdb is fine so far [08:23:47] I guess dbstore1002 has issues because of this [08:23:51] ? [08:23:53] Replicate_Wild_Do_Table: %wik%.%,heartbeat.% [08:23:53] Replicate_Wild_Ignore_Table: enwikivoyage.%,cebwiki.%,shwiki.%,srwiki.%,mgwiktionary.% [08:23:57] oh [08:23:59] :( [08:24:00] fail [08:24:02] yep [08:24:08] we can drop the tables [08:24:13] honestly [08:24:15] Yeah [08:24:19] I think so [08:24:39] I knew that could happen [08:24:44] but I only checked on labsdbs [08:25:10] I didn't realise those special filters on dbstore [08:25:16] I will drop the tables and the filters [08:25:37] +1 [08:27:15] I can drop them just before starting each import [08:27:15] thre is no s5 lag really, right? [08:27:32] which means the import maybe quite slow [08:27:34] yes on the slaves [08:27:40] is there? [08:27:43] there is lag at least on db1100 [08:27:59] We finished with Lali, 30 minutes and I at home [08:28:22] ok [08:28:24] I see it now [08:28:27] that is kida good [08:29:33] dbstore1002 is replicating again [08:29:41] \o/ [08:30:06] but of course now s3 fails [08:30:23] should I put an ignore there, but it will not work with the do db [08:30:37] Yeah, that is the thing [08:30:49] Maybe stop replication on s3? [08:30:55] ok [08:31:21] let's stop s3 replication and once the import has finished we can check how to deal with that [08:31:25] damn DO replication filters [08:31:31] we can remove the Replicate_Wild_Do_Table from s3 [08:31:39] as long as no new wikis are created [08:31:43] it does nothing [08:31:48] There are two tickets pending [08:31:51] For wiki creation [08:32:01] I can tell them not to create them [08:32:06] Until we give greenlight [08:32:08] yes, but non will go through before switch back [08:32:26] Who knows :) [08:32:28] I will comment [08:32:29] Just in case [08:32:43] I will do that [08:32:49] I have the tickets opened [08:32:50] I will do it [08:32:52] Don't worry [08:34:11] 10DBA, 10Cloud-Services, 10User-Banyek, 10User-Urbanecm: Prepare and check storage layer for yuewiktionary - https://phabricator.wikimedia.org/T205714 (10Marostegui) 05Open>03stalled a:03Marostegui We are doing some maintenance on s3, moving wikis around - please ask #dba before creating this wikis (... [08:34:24] 10DBA, 10Cloud-Services, 10User-Banyek, 10User-Urbanecm: Prepare and check storage layer for liwikinews - https://phabricator.wikimedia.org/T205713 (10Marostegui) 05Open>03stalled a:03Marostegui We are doing some maintenance on s3, moving wikis around - please ask #dba before creating this wikis (if... [08:37:07] mgwiktionary is not far from finishing [08:37:28] no other issues so far [08:37:42] labsdb1009 looking good [08:37:44] and db1100 delayed [08:37:51] although it is the smalles one [08:38:37] yeah, it was the hierarchy of filters, that is non-obvious [08:38:46] we had the issue when working with sanitarium [08:39:02] I really don't care that much about dbstore1002 [08:39:09] as it should be replaced soon [08:39:25] and in fact, the reload should create a better data than usual [08:39:47] indeed [08:40:02] we shouldn't spend much time on dbstore1002, the data is really bad there anyways [08:40:06] lots of drifts anyways [08:42:04] ther is only 2.7T avaiable on db1070 [08:42:17] but all imports should only take 500GB [08:42:21] dbstore1002 is feeling that the end is coming marostegui [08:42:25] haha [08:43:17] elukey: I was thinking about dbstore1002 [08:43:28] jynus: There are 250G on a tmp directory there, probably a left over [08:43:31] I will check and clean up [08:43:50] and I think the replacements maybe should be below sanitarium (but without the labs views) [08:44:11] It is a left over from : https://phabricator.wikimedia.org/T178313#3689690 [08:44:12] or at least with some kind of filters [08:44:14] I will get this cleaned up [08:46:42] jynus: just to understand, you mean that it should import sanitized data? [08:47:36] not fully sanitized, but I dont see a reason to let researchers have access to password hashes [08:47:48] it might be possible but I know for sure that my team needs some special tables like cu_changes for some mediawiki-history-reconstruction manual jobs (private data, not the public one that we'll put into the labs data lake) [08:47:56] yes [08:47:58] that I get [08:48:54] I am super ignorant about what it is sanitized by sanitarium, but if it is mostly passwords and critical info I agree 100% with you that should be sanitized [08:49:03] there is 2 passes [08:49:15] one is physical, for very sentistive stuff [08:49:24] and one is logical, for public stuff [08:49:58] I don't care about your team or anyone else accessing those [08:50:18] but exteranal people that actually want to check other stuff [08:50:29] shouldn't need very sensitive access [08:51:11] e.g. the only reason people should have access to password hashes is for security audit [08:51:20] and that should be done on proper production [08:52:03] ask around about having some filters in place [08:52:23] details can be discussed on a ticket [08:52:39] "I need X but I don't need Y" [08:59:37] cpu usage starting to go down: https://grafana.wikimedia.org/dashboard/db/prometheus-machine-stats?orgId=1&from=now-3h&to=now-1m&var-server=db1070&var-datasource=eqiad%20prometheus%2Fops [09:00:12] and I don't think because of iops only (it start importing on memory only), but because importing threads wind down [09:05:07] Are you now importing more than one DB at the time? [09:05:22] finished importing real 45m58.357s [09:05:28] \o/ [09:06:10] binlogs and server size only had a 5-10 minute overhead [09:06:50] I removed that old 250G data [09:08:30] we can wait a bit, but what next, the large one or the next one in size? [09:10:16] Let's do the larger? [09:10:19] the perf of replication is a bit bad, peaks of only 90K rows/s [09:10:49] maybe we should reduce temp. the consistency on the replicas [09:11:22] yeah, good idea [09:11:27] the peak on master is 253K rows/s [09:11:39] I would not do that if it was active [09:12:01] but we have to have really bad lack to have a full 20 server failure at the same time [09:12:30] lets do syn binlog=0 and flush=0 [09:12:58] yeah, and even if a server crashes, it is only 400GB to be copied [09:13:21] and the master will be kept fully consistent at all times [09:13:59] I am going to drop cebwiki from dbstore1002 [09:14:06] can you start the config changes? [09:14:06] good [09:14:17] or are you busy with the schema change? [09:14:39] nope [09:14:41] go for it [09:14:48] only touching enwiki [09:15:10] no, I am asking you to do the s5 changes, but I can do them later [09:15:31] no no, s5 is done :) [09:15:38] and s3 [09:16:18] you don't get me [09:16:22] but it is my fault [09:16:30] just don't touch s5 now, ok? [09:16:59] BTW, on that note, any schema change done through replication [09:17:01] No, I am not touching s5 and won't touch it until we are back in eqiad :) [09:17:14] will appear on the moved wikis [09:17:23] but any change done out of band [09:17:23] yeah, that is why I did s3 and s5 before the movement [09:17:45] will need to be redone if done after the backup time (last night) [09:17:57] probably worth chaking the 5 wikis just in case [09:18:04] after they all catch up [09:18:09] Yep, will do [09:18:22] dbstore1002 broke [09:18:25] for cebwiki [09:20:46] So we need to re-import x1 for those wikis on dbstore1002 [09:20:58] oh [09:21:00] true [09:21:23] let me set an extra ignore [09:21:39] I have dbstore1002 [09:21:41] *hate [09:21:47] yeah, it is a pain [09:44:55] I've started the cebwiki, mgwiki was almost finishing on the replicas [09:45:24] this one may take a loooong time [09:47:44] good news is that s8 compression finished and wb_terms is now less than 1/3d of the total size [09:47:54] 275G [09:49:18] vs 946G uncompressed on the master [09:49:25] ^ addshore [09:49:41] that's a lot [09:49:59] banyek: tell wikidata team! [09:50:08] :-) [09:50:47] it is also an underestimation, as it gets edits, it will grow a bit [09:50:54] BIG DATA BIG TIME [09:50:59] normally we end up with a 50% compression ratio [09:51:28] BTW, db1118 is totally trashed, will need a reimport [09:51:41] apropos compression, I check if es2001 finished with backup and resume it on dbstore2002 [09:51:48] there's 2 tables left in s2 [09:52:28] as I said, not a huge priority [09:52:30] no it still happens [09:52:40] to be done when there is a gap [09:53:05] Is there a better way (I bet there's one) to check the running backups not with ps? [09:53:21] yes! [09:53:28] zarcillo! [09:53:45] ongoing vs finished vs failed [09:53:49] banyek: for a quick glance also tendril activity can help [09:54:25] we could create a dashboard so we export some information on a web [09:55:38] it's not worth to add it to tendril, because it will be replaced, right? [09:56:04] no, one to zarcillo [09:56:10] which doesn't exist right now [09:56:16] :D ^ [09:56:19] but it could be the firs new dashboard [09:56:31] banyek: thanks for offering as volunteer to work on that! [09:56:40] :-D [09:56:43] Much appreciated [09:56:49] Actually that was I just wanted to say out loud :) [09:57:17] so the data is there, but I am alergic to frontend and js [09:57:43] There is: https://icinga.wikimedia.org/cgi-bin/icinga/status.cgi?search_string=Backup+of [09:57:50] banyek: I am planning to start giving you some "easy" schema changes soon, so you get your hands dirty with that too [09:58:34] marostegui: great, I already wanted to ask about those [10:00:01] to be fair, a django dashboard is something that many people could help us with [10:00:10] unlike the actual backend work [10:03:14] I am thinking of putting some 'backup state query tool' into wmfmariadbpy [10:04:10] We could do some sort of ./backups_state [10:04:11] backup state query tool <-- that already esists [10:04:15] Exactly [10:04:18] Using that [10:04:29] it is called check_mariadb_backups.py [10:04:53] you can expand it to do more than just icinga and make it work from other hosts [10:05:12] true! [10:05:18] but honestly, I would not work on that when we have other more priority things [10:05:40] not urgent I was just asking [10:06:32] for example, yesterday you left dbstore2002 without a firewall [10:06:57] that could have been dangerous security wise [10:07:03] ? [10:07:22] on restart, for some reason, the firewall failed to start correctly [10:07:30] I've seen that there was an error with that, and restarted the service, so ensure nagios will green up [10:07:32] and it did [10:07:33] and it marked that on icinga [10:07:44] I did restart it and logged it [10:07:58] only then it got fixed [10:08:07] I checked it iptables -L [10:08:11] no rules loaded [10:08:52] no problem with that, it was late [10:09:00] but wanted to point out for future cases [10:09:34] hello [10:09:40] I actually do rembember I did that [10:10:04] it's in my bash history [10:10:15] sorry - i haven't been able to pay attention - do we have a writeup of yesterday's discussion? [10:10:45] mark: I am working on it [10:11:00] ok, great [10:11:25] i'm also unable to attend tomorrow's meeting tomorrow :( [10:11:33] hospital visit [10:11:37] do we want to move it? [10:11:40] sorry [10:11:56] mark: you are the one with the busy schedule, just let us know what is easier for you [10:12:36] i don't know how urgent things we want to discuss are though [10:12:39] what do you have in mind? [10:13:14] (so it could become either friday or next week :) [10:14:54] I wanted to give you a heads up on the parsercache procurement just in case you are not aware of the latest news, and then report on the eqiad maintenance (which is mostly finished), then I thought maybe we should discuss backups purchases (or what's the plan with that) [10:15:01] That was roughly what I had written down [10:15:10] On my notes [10:15:37] jynus banyek anything from your side? [10:16:21] lets schedule for next week I say [10:16:51] sounds good from my side [10:17:38] next week is good to me too [10:18:24] I ve updated the even and see if that works [10:18:53]