[06:50:12] <_joe_> jynus, volans I prepared reverts for all changes we made during the switchover [06:50:26] <_joe_> the only one I could not revert is https://gerrit.wikimedia.org/r/#/c/284144/ [06:50:39] <_joe_> for which your scrutiny is probably needed [06:50:55] <_joe_> writing to ops@ [07:03:32] _joe_: that doesn't need to be reverted, we will merge https://gerrit.wikimedia.org/r/#/c/283771/ today and then for tomorrow we just need to swap the $master true=>false and false=>true, we are failovering all old eqiad masters today [07:08:14] I hate aNag for android when it get stuck refreshing in the middle of the night, seems to not have a timeout :( [07:08:28] I'll check/open a bug for them... [07:09:03] * volans getting a shower, be back soon ready for the failovering [07:11:19] I answered on the mail,_joe_, I didn't see the message here first [07:12:44] I've answered there too :) [07:12:55] didn't saw your reply when hit send [07:14:06] jynus: for T133122 ok for the insert ignore, but given the ID is an auto_increment, they are not misaligned there due to the missing rows of tonight? Following inserted rows have IDs that are in your dump [07:14:06] T133122: Backfill data into db1065 and db1066 - https://phabricator.wikimedia.org/T133122 [07:14:26] I can do a quick check getting the IDs from the dump and checking [07:14:45] ids should be correct as they are sent on STATEMENT based replication [07:15:03] if they were not, we have a really bad problem [07:17:00] how being an insert...select? [07:17:14] well, the insert-select was not sent [07:17:22] subequent insert were [07:17:45] mysql -BN -h db1052.eqiad.wmnet enwiki -e "SELECT max(rc_id) FROM recentchanges"; mysql -BN -h db1052.eqiad.wmnet enwiki -e "SELECT max(rc_id) FROM recentchanges"; [07:17:45] 817480596 [07:17:45] 817480596 [07:17:48] true, you mean all the others from usual softwar are properly inserted [07:18:12] change the second 65 for 52 [07:18:16] same results [07:18:27] that doesn't mean that they are not dangerous [07:18:56] 2 consecuitive insert selects with different data, and we are screwed [07:19:13] yes [07:19:24] specially if we have different locking patterns [07:20:10] I had problems in the past with that and using pt-table-sync/pt-online-schema change [07:20:37] I will review and deploy the change, ok? [07:20:45] The certs one [07:20:59] ok, I didn't had time to review the puppet compiler diffs, link in the CR [07:21:17] I'll get a shower and be back [07:24:23] I'm ready to merge the patches to enable base::firewall on the eqiad mariadb::core's whenever works for you, just give a "go" so that I don't interfere with the other maintenance work you're doing [07:26:23] actually, mortiz, we should merge it with the patches we need to do or apply them now and we rebase [07:26:47] probably the second will be easier to apply and debug [07:26:59] can we do it really now? [07:33:55] sure, let's do it now [07:34:01] any particular order you'd like? [07:35:05] same I reviewed [07:35:12] first one is already rebase [07:35:31] lets go to ops- [07:36:43] ok [08:04:56] jynus: for s2, do you want to failover db1018 to db1024 too? If you do want I need to add few lines on puppet [08:07:53] I do not know [08:08:06] whatever it works [08:08:46] whatever is your goal for this week, if you want db1024 as master I'll make it a master :) [08:09:29] I do not want db1024 as a master, the goal is have new TLS certs deployed [08:09:42] if that requires db1024 as the master, so be it [08:10:03] whatever is less dangerous [08:10:30] if we restart db1018 after merging puppet we'll have the new ones [08:12:40] then that is ok [09:07:56] hey jynus ! Just wondering if you could take a look at https://phabricator.wikimedia.org/T130067#2133384 and reply to it? :) [09:08:17] I'm not sure if them ebing together is possible / would even make sense [09:08:56] actually, this may be one of the changes to potentially do now [09:09:27] but it probably will not fit, and will have to wait until next failover [09:09:33] that is why I said 6 month [09:09:52] :D [09:09:58] changing a primary key is not easy, once it has a primary key, all other changes will not get blocked [09:10:04] and do not need a faiover [09:10:19] it is the PK what block everything [09:10:29] you can paste this there if you want [09:10:30] yup :) thought so! [09:10:33] (hope it is clear) [09:10:33] awesome, will do! [09:13:51] also regarding clearing a users entries from the watchlist table, even with the wl_id in place deleting in batches of 1000 would still be prefered? [09:16:01] yes, although the PK will make the change faster/less troublesome for replication [09:16:35] if we had a proper framework, we should implement a "decaying time" window [09:17:34] run 1000 updates on PK- if it takes way less than 1 second, double it; if it takes more, half it [09:18:19] okay! [09:18:23] also, once we have mariadb 10 slaves, index alter will be transparent [09:18:38] *masters, I mean [09:18:57] and in general, things like alters will be faster [09:19:19] I just cannor promise to be done this time (it will probably won't) [09:19:29] (the specific alter) [09:21:54] yup, okay! :) [09:22:56] volans, you are going to laugh [09:23:12] but now we have 2 more rows on api servers than on the other hosts [09:23:13] tell me [09:23:24] rotfl! told you :-P [09:23:26] the import went smothly [09:23:37] the count is ok: 2914 [09:23:50] but the other servers now have 2912 [09:23:57] so they have deleted 2 rows [09:24:29] compared to before [09:24:37] AFAIK those are deleted after like 2~3 months, but maybe you can "undo" a recent change? [09:24:53] I do not think so [09:25:01] rcs I think are only there for a month [09:25:52] I mean that those of yesterday should not get deleted from what I know [09:25:55] but it's very little [09:26:20] (my knowledge of the underlying application logic involved_ [09:29:34] <_joe_> hey, I just put down https://etherpad.wikimedia.org/p/eqiad-switchback, whenever you have time, phase 6 needs heavy editing by you guys [09:30:10] yes, we know [09:30:22] <_joe_> you knew I did that? [09:30:24] <_joe_> :P [09:30:37] 817302324 817301655 are missing [09:30:48] I will delete before something else breaks [09:30:58] <_joe_> oh you weren't respondig to me but to volans, ok :) [09:31:22] _joe_: I would add in parentheses who has to do it a given command to, thanks, I'll take a look at it [09:31:26] jynus: go ahead [09:31:39] we are in the middle of something not very important, _joe_ just breaking eqiad database servers [09:31:47] *masters [09:31:54] <_joe_> jynus: yeah I didn't need an answer [09:32:03] <_joe_> that was just a notification [09:32:59] :-P [09:33:20] so those could be hidden edits [09:33:31] e.g. deleted articles, or made them hidden [09:33:48] in which case they could have or not broken data consistency [09:34:19] at this point we should consider those tainted, given that mediawiki tends to do unsafe statements [09:35:51] if there are unsafe statements all can be tainted... [09:36:08] yesterday script was a one-off to fix the issue during the switchover [09:37:35] so if the dump of lines in that timeframe is the same between 65/66 and the other slaves/master I'll tend to assume they are tainted as they were before :) [09:38:25] my assumption is that we don't have other INSERT ... SELECT from recentchanges into other tables [09:38:39] if we have... than they have wrong data [09:40:54] https://phabricator.wikimedia.org/T133122#2222196 [09:43:25] doing a diff only one row different [09:43:59] a field 0 and the other 1 [09:44:02] id 817303249 [09:45:22] no, wait... [09:45:56] yes [09:46:09] yes rc_patrolled [09:49:25] so summary: kill the query? good call [09:49:46] but better not skipping- creating the index or whatever [09:49:57] if you didn't have the time, is ok, leave it lagging [09:51:46] agree, was 1am and probably not my best call, I was worried that would have lagged too much, blocked us today for maintenance stuff and don't recover by tomorrow for the switchback [09:52:03] and yes creating the index is probably the best thing [09:52:09] it was eqiad == not real traffic [09:52:17] hoping it will not affect existing query plans [09:52:28] it eqiad was primary, yes, you did well [09:52:37] as it would have impacted availability [09:53:00] the actual summary is that we need to fix schema drifts [09:53:06] and we need help for that [09:53:15] I am reviewing the change [09:53:36] ok thanks [09:54:21] no touching yet the 5.5 masters, right? [09:54:32] <_joe_> btw, a few people on VP:T are complaining about db issues https://en.wikipedia.org/wiki/Wikipedia:Village_pump_%28technical%29#More_than_usually_buggy [09:54:36] not at all [09:55:10] _joe_, I saw those [09:55:23] the problem is that mediawiki db errors are usually not db-related [09:55:23] <_joe_> ok, I wasn't sure [09:55:28] <_joe_> ehe [09:55:56] <_joe_> when in doubt, blame the db [09:56:35] I mean, if a row gets locked, it is a mediawiki logic- of course I can help, but the fix would be on mediawiki queries [09:57:06] it just need long-term profiling of mediawiki [10:01:08] ok let's do it [10:02:26] ok [10:03:23] running puppet on db1057 to test it [10:03:28] I mean, verify it [10:03:40] not merged yet [10:03:44] wanted you to be around [10:03:57] right... I was ahead :) [10:04:05] done now [10:04:12] db changes are safe [10:04:20] because they only write to the file [10:04:25] mostly [10:04:36] I just want to avoid the spam in the channel if something is wrong [10:05:43] db1057 and db1064 (different type of changes) run smoothly [10:07:18] I trust the merges [10:07:37] it is the replication channels that could fail on restart/change master [10:08:51] so for the large wikis (s1, s4, s5), I am thinking of preserving at least an API node if they have the old (.15/.16) query rewrite plugin in use [10:08:52] binlog_format is MIXED on all designated masters [10:09:21] I can change it to STATEMENT now on all [10:10:18] e.g. https://phabricator.wikimedia.org/P2934 [10:10:49] yes [10:10:53] we could have any issue with delayed dbstore1001? do we plan to change it tomorrow continuing to replicate from old masters for the next 24h? [10:11:03] now or before topology change, whatever you prefer [10:11:15] but before puting labs as a slave [10:11:30] I would pause that for now [10:11:42] as in, leave it in third tier [10:11:53] we can mange it after the failover [10:12:05] ok [10:12:12] or if we have the time [10:12:47] so what are you starting with? [10:14:02] s7? [10:14:38] ok, I will start with s2 [10:14:57] jynus: I tested requests to enwiki (s1), itwiki (s2) commons (s4), dewiki (s5), frwiki (s6) and rowiki (s7) from an eqiad appserver (mw1150), went all well. no idea how to test the es1 shard, though [10:15:08] and dawiki for s3 [10:15:39] jynus: s2 you need to do a manual CHANGE MASTER TO to empty the old certs [10:16:05] moritzm, I think that is more than enough [10:16:09] ok [10:16:11] on db1018 only of course [10:16:12] thank you very much for your help [10:16:25] yes, I saw you changed the designated master [10:16:37] ? [10:16:52] I left db1018 that was already a 10 master [10:16:54] sure, let me know if I can help with anything else during today's window [10:17:13] * jynus says, this younglings, I was killing eqiad servers before you were born! [10:17:57] * jynus you know, all this parameters, master, ssl, etc. I created those, there were not parameters at all before! [10:18:07] * jynus those were good times! [10:19:05] lol [10:19:15] it needs restart anyway for the "internal" cert [10:19:24] for its slaves [10:20:10] I think I will do that and silence all replication alerts [10:20:45] db2017 -> db1018 replica is using old certs, db2017 has the special CA cert that accept both old and new, see show slave status on db1018 [10:21:27] and db2017, that is using the old cert for connecting to db1018 but was restarted with the new ones [10:21:31] yes, but we want to restart the master if possible to use only the new one, and so its slaves [10:21:54] yes, you need to do a stop slave on db2017 too and change master [10:22:04] the slaves will have to use the old one unless db1018 is restarted [10:22:23] to reset the SSL parameters set manually to the path of old cert [10:22:50] for replication that works, but that requires master restart- and we did not restart db1018 [10:23:01] you did it on a diferent host [10:23:34] I think we are saying two different things :) [10:23:39] no [10:23:51] I know what you are saying [10:24:07] but that will work on all shards execept s2 [10:24:24] no, I'm saying that s2 has special steps, exactly for that [10:24:57] including a restart of db1018 [10:25:12] of course [10:25:18] ok :-) [10:25:19] see in the etherpad: # Ensure there are not custom certs on [10:25:26] then we agree! [10:25:28] :-) [10:25:31] :) [10:25:44] if we have the time, we can restart the others to get rid of the dual cert [10:25:50] but only if we have the time [10:25:57] not now [10:26:12] each one to its task! [10:26:14] and I suggest to stop also the replica from db2017 before restarting given that you have to change master there too [10:26:19] ok [10:26:37] I didn't changed the binlog_format to all, so let's do one by one [10:47:26] jynus: so full upgrade or just mariadb package? [10:52:10] if you are restarting mysql, or need to upgrade, do it of all packages, it worked well for me on other host, and there are some pending upgrades we could not do [10:52:31] so restart the host too... [10:52:47] for the kernel? [10:52:50] again, only on those where a simple change master is not enough [10:53:20] I think it is ok to restart, just do it one by one because there is a change they won't come back [10:53:25] *Chance [10:53:48] on eqiad slaves we need to restart mysql on all if you want them to work out of the box, otherwise just a stop slave change master to to use the new cert; start slave will work too [10:54:10] I thought we want to restart all of them to upgrade them too, but maybe I get it wrong [10:54:13] what I would do is do that first [10:54:26] (only the change master) [10:54:38] then, if there is time, the full package [10:54:42] ok [10:54:50] but TLS is the priority [10:55:12] the question is that older ones will require restart [10:55:23] (of mysql), for upgrade [10:55:38] or SSL will not work at all [10:56:05] of course [10:57:51] BTW, that is expected but even if the default cert is changed, replication still uses the old one [10:58:08] openssl support is from >= 10.0.16 or >= 10.0.22? [10:58:15] 22 and 23 only [10:58:27] what do you mean? (line above) [10:58:35] >= 10.0.22 [10:58:40] no the one before :) [10:58:47] no [10:58:52] in fact [10:59:01] it may be 10.0.22-2 [10:59:10] and not in 10.0.22-1 [10:59:18] due to a compilation problem [10:59:21] ok, I'll check that [10:59:32] there should not be 22-1s [10:59:41] but just in case [10:59:44] my what do you mean was for "that is expected but even if the default cert is changed, replication still uses the old one" [10:59:57] sorry [11:00:22] I have to run CHANGE MASTER on db1019, for example [11:00:29] *db1018, sorry [11:00:47] even if the global configuration is now the right one [11:00:57] to force the new certs [11:01:10] just to remove the old set value for the CA [11:01:25] yes, on the master.info / SHOW SLAVE STATUS [11:01:43] it was a heads up to check it [11:01:51] but it may be s2-only [11:02:17] just MASTER_SSL = 1 on the others should work well [11:02:29] it shouldn't be needed even on s2 [11:02:59] you should set to empty Master_SSL_CA_File, Master_SSL_Cert and Master_SSL_Key and if was restarted it will get the new ones [11:03:05] from my.cnf [11:03:07] no [11:03:14] ah [11:03:15] yes [11:03:17] mm [11:03:19] not sure [11:03:22] I think yes [11:03:35] but I have force it, just to be sure [11:04:19] I think in some cases, CHANGE MASTER may use the old values instead of the default ones [11:04:37] if they were not set manually should not [11:04:38] better safe than sorry, and we can run in again at any time [11:04:58] ^ [11:05:24] it is the codfw -> eqiad replication after all, we can change it later at any time [11:06:15] I am going to not touch the s2 slaves yet (as this topology should be already right) [11:06:21] and going for another master [11:06:54] ok, the designated master on the other shards should not need any action beside binlog_format STATEMENT [11:07:03] only the slaves needs work [11:07:06] yes [11:07:14] as in you are right [11:09:01] error reconnecting to master 'repl@db1018.eqiad.wmnet:3306' - retry-time: 60 retries: 86400 message: SSL connection error: error:14094418:SSL routines:SSL3_READ_BYTES:tlsv1 alert unknown ca on db2017, not sure if expected or something wrong [11:09:25] expected [11:09:30] it is using the old certs [11:09:31] where? db1018 or db2018? [11:09:34] db207? [11:09:36] 2017 [11:09:41] ^that one [11:10:05] so executing change master there too [11:10:24] yes is expected you didn't change master to remove the old certs values [11:10:31] Master_SSL_Cert and Master_SSL_Key [11:10:42] set them to '' [11:10:50] should I try now wihout- exactly [11:11:02] doing that [11:11:23] that's what I was trying to say before, probably I didn't explain myself :) [11:13:58] CHANGE MASTER TO MASTER_SSL_CERT='', MASTER_SSL_KEY=''; -- to be precise [11:14:20] I confirm that works: MASTER_SSL = 1, MASTER_SSL_CA='', MASTER_SSL_CERT='', MASTER_SSL_KEY=''; [11:14:37] with the 1 to force SSL [11:14:43] was alredy 1 [11:15:19] I cannot remember, but it is one of those things I prefer to set always, just to be sure [11:15:36] I just look at Master_SSL_Allowed :) [11:19:03] I will be taking a break now and continue with another shard later [11:19:27] ok [11:19:58] s2 is done except within-datacenter traffic [11:27:28] * volans lunch [12:05:16] * volans back [12:25:16] I will start with s3 now [12:26:00] ok jynus, a 5.5 slave of a 10 will work? [12:26:23] "yes" [12:26:46] "ok" :) [12:26:52] I've done it for us, and we are actively doing it, for example on x1 [12:27:12] or, you know, on every single s* shard [12:29:37] true since yesterday :) [12:32:39] nice (s7)! https://tendril.wikimedia.org/tree [12:33:02] s7 completed just now [12:33:34] and db1033 is running :-) [12:33:52] eheheh [12:34:00] I've put the commands executed on the etherpad [12:34:08] I added the last 2 at the bottom [12:34:12] to complete the switch [12:36:22] I'll go with s6 then [12:41:51] did you change codfw's master? [12:42:07] or are they now a circle of 3? [12:43:44] codfw masters were already replicating from designated master [12:43:51] I'm stupid [12:44:12] * volans too, went to check ... [12:44:12] sorry [12:45:03] this is one of the thigs that unders normal circusntances would be easy [12:45:13] but with so much legacy, it gets confusing [12:45:27] yeah [12:45:27] I am glad I have you here [12:48:06] thanks, I'm glad to be of some help [12:50:46] s6 done [12:54:31] doing s5 [12:59:06] s5 done [12:59:51] doing s4 [13:03:57] s3 done [13:04:13] I suppose that leave s1 [13:04:17] *leaves [13:04:24] yep, last one [13:05:02] and last one with MIXED [13:08:22] s4 done [13:10:46] jynus: are you doing s1 or I do it? [13:11:05] I am [13:11:16] ok [13:11:22] next, I would, either check es/x1 [13:11:27] or do restarts [13:11:40] whatever you think will me more urgent [13:12:25] es/x1 do not have yet "designated" [13:12:58] so they would need puppet + s2-like treatment [13:13:14] no STATEMENT, though [13:14:46] I would like to take care of db1070/71/65 for the heating with chris, given we need to shutdown I'll also do the upgrade there [13:14:54] sure [13:15:04] let me do s1 quicky, then [13:15:42] as those will be down, upgrade them when I finish [13:16:31] sure I need to find Chris before :) [13:20:51] T105135 can probably be resolved now :) [13:20:52] T105135: Implement mariadb 10.0 masters - https://phabricator.wikimedia.org/T105135 [13:21:20] :-) [13:21:30] I predict around 20 tickets [13:21:47] will be closed or now closed very quickly [13:21:59] you are very lucky to see an actual change [13:22:14] usually this kind of changes take months to prepare [13:22:25] well, literally it took years to upgrade to 10.0 [13:22:26] yeah [13:22:42] I upgraded a good chunk of those from 5.5 [13:22:52] slaves used to be on 5.5 tooª [13:22:54] ! [13:24:23] one s1 slave is lagging, blocking the change [13:24:30] well, several, one is lagging more [13:25:09] all 0 now [13:25:17] no, it is a lie [13:25:27] check db1047's graph [13:26:00] tendril does not yet use pt-heartbeat, and the lag it shows when it shows 1 number is a bit of random or something [13:26:02] db1047 is always a bit on the edge [13:26:13] it is not a production db [13:26:22] it is in reality an analytics-slave [13:26:22] multisource [13:26:25] yep [13:26:36] plus you remember hardware issues, locking,etc [13:26:41] *ay [13:27:33] I may leave it there [13:27:36] for now [13:29:19] ok [13:30:53] do we have slaves to restart on eqiad that we cannot do when active due to weight/specific role? [13:30:59] so we can give priority to them [13:32:10] in general no [13:32:22] as in, there should not be single slaves that are SPOF [13:32:23] but [13:32:31] there are some more difficult than others [13:33:16] in particular, the ones with more load (db107[23] on s1) and the ones with role rc,logger that are only 1 [13:33:55] such as db1019 [13:34:07] new ones (70s) I guess are already updated recently [13:34:14] and db1026 [13:34:36] not really, as they were more loaded, it was more difficult to depool them [13:34:45] use the version for that [13:34:59] if it is 22/23 do not touch them [13:35:24] but if you mean >db1073, yes, those are already in jessie [13:35:33] not sure about the cert, though [13:35:52] for s3 they have it [13:36:02] but the new one? [13:36:19] oh, yeah [13:36:24] 21:04 logmsgbot: volans@tin Synchronized wmf-config/db-eqiad.php: Repool new db1075,1077,1078 after TLS upgrade on s3 - T111654 (duration: 00m 36s) [13:36:24] T111654: Set up TLS for MariaDB replication - https://phabricator.wikimedia.org/T111654 [13:36:25] we can change it dynamically [13:36:27] from SAL [13:36:46] great [13:37:23] I would focus on what I said before: large weight and old servers [13:37:28] both things [13:37:33] at the same time [13:37:43] (old versions) [13:38:11] db1072 and db1073 [13:39:41] do you want to check those while I give a look at x1 [13:39:43] ? [13:40:48] remember the dump_now/dump_at_shutdown-preciselly the older ones may be the ones where newer configuration may not have taken effect [13:41:05] yes of course [13:41:09] ok I'll start with them [13:41:19]