[05:38:37] 07Blocked-on-schema-change, 10DBA: Convert unique keys into primary keys for some wiki tables on s1, s2, s4, s5 and s7 (eqiad) - https://phabricator.wikimedia.org/T164185#3224848 (10Marostegui) Great job! We are one step further now!! [06:32:27] 10DBA, 06Operations: Create less overhead on bacula jobs when dumping production databases - https://phabricator.wikimedia.org/T162789#3230503 (10Marostegui) Looks like db1015 ran out of space in the middle of the night: check emails from icinga and: https://grafana.wikimedia.org/dashboard/file/server-board.js... [06:33:41] 07Blocked-on-schema-change, 10DBA: Convert unique keys into primary keys for some wiki tables on s3-eqiad - https://phabricator.wikimedia.org/T163912#3230509 (10Marostegui) Great news!!! Save the scripts for codfw! :-p [06:42:52] 10DBA, 06Operations, 10ops-eqiad: Reset db1070 idrac - https://phabricator.wikimedia.org/T160392#3230537 (10Marostegui) @Cmjohnson this looks similar as the same issue with lots of servers, including dbstore1001 on this task: T158893#3186029 if we get Dell to fix this or advise on that ticket, probably we ca... [07:31:15] 10DBA, 06Operations, 10ops-codfw: es2019 crashed again - https://phabricator.wikimedia.org/T149526#3230560 (10Marostegui) 05Open>03Resolved Let's close this for now. [07:32:32] 10DBA, 06DC-Ops, 06Operations: db1063 thermal issues (was: db1063 io (s5 master eqiad) performance is bad) - https://phabricator.wikimedia.org/T164107#3230562 (10Marostegui) db1063 is definitely in good shape to keep being the master for the switchover: ``` root@db1063:~# megacli -AdpBbuCmd -a0 | grep Tem... [08:06:30] this is new [08:06:47] 4:31:44 [ERROR] mysqld: The table 'text' is full [08:06:52] (that is normal) [08:06:55] and them [08:07:06] FAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAILFAIL [08:07:20] (and it follows more) [08:07:43] is that db1015? [08:10:42] yes [08:11:04] well, at least it is clear it failed [08:11:30] spectacularly [08:11:38] s3 is filling up again [08:11:44] not sure if the new wikis [08:11:48] or something else [08:12:25] I saw db1028, but it still has 100G in its lv, so I didn't give it too much importance [08:12:25] but 15, 36 and 28 are running low on spave [08:12:28] But I didn't check the others [08:12:51] it grew from 10->5 in just 1-3 days [08:13:06] that is why I was reimporting it [08:13:44] oh, that is a pretty significative growth :| [08:14:48] https://grafana.wikimedia.org/dashboard/file/server-board.json?refresh=1m&orgId=1&var-server=db1015&var-network=eth0&from=now-20d&to=now-1d&panelId=17&fullscreen [08:15:08] it is probably the alters [08:15:21] I was checking the master: https://grafana.wikimedia.org/dashboard/file/server-board.json?refresh=1m&orgId=1&var-server=db1075&var-network=eth0&from=now-20d&to=now-1d&panelId=17&fullscreen [08:15:25] adding 300 GB [08:15:57] well, apparently it decreased space used there :-) [08:16:26] note that we were already borrowing time by heavyly compressing those s3 slaves [08:16:47] oh, those are compressed? [08:17:02] I think it is time to keep only new hosts for s3 [08:17:08] +1 [08:17:14] we also have new hosts ready in case we need them [08:17:21] but do we want to do that before the switch? [08:17:28] (keeping only the new hosts) [08:17:32] maybe we should do it next week [08:17:38] to add less variables to the switch back [08:17:48] we can wait [08:18:04] but not much time, 1 server down and 2 at 10% [08:18:10] yeah [08:18:17] db1028 still has room with lv [08:18:20] 100G or so [08:18:21] we need to run pt-table-checksum [08:18:33] yes [08:18:35] and I can recover db1015 data [08:19:17] dbstore1001:/srv/tmp/export-20170502-102514 [08:19:30] very happy with mysqldump otherwise [08:19:33] *mydumper [08:20:18] which options did you use? [08:21:07] that is what I was testing [08:21:34] mydumper -c -h db1015.eqiad.wmnet -t 8 -u root -p $pass -r 100000000 [08:21:55] myloader -h db1015.eqiad.wmnet -t 8 -u root -p $pass -d export-20170502-102514 [08:22:10] 1 hours per shard for export is very nice for backups [08:23:02] that is really good indead [08:23:04] indeed [08:23:09] the import is slow as expected, but probably 10 times faster than a single thread mysqldump [08:24:08] It creates 154416 files for s3 [08:24:19] 2 per table [08:24:47] so no need to start cuting mysqldump in pieces to recover a single table [08:25:53] we could probably tar them by database on s3 [08:26:24] FWIW I've been happy with mydumper too in other projects [08:26:44] godog, I was doubtful we could use the old version [08:26:58] it has some important limitations to me [08:27:06] strech has the newest version already [08:27:55] nice, didn't notice that, but yeah when it was still on launchpad I had a bug/patch to add --defaults-file support and it took forever to merge [08:28:14] the plan, godog is to use it to generate local dumps [08:28:34] to unblock bacula [08:29:32] however, we probably want a physical backup too, for instant recovery [08:30:55] makes sense, IIRC bpipe from bacula can stream out of the box and encrypted, should support block devices too [08:31:44] oh, I was just thinking files, not volumes or snapshots [08:31:59] compressed only take 200G per shard [08:32:10] double the logical backups [08:32:32] but instead of taking 15 hours to recover, they are as fast as thet can be copied to disk [08:34:32] 10DBA, 06DC-Ops, 06Operations: db1063 thermal issues (was: db1063 io (s5 master eqiad) performance is bad) - https://phabricator.wikimedia.org/T164107#3230684 (10jcrespo) Actually, temperatures have apparently shifted side: ``` $ cat /sys/class/thermal/thermal_zone*/temp 67000 53000 ``` [08:34:38] ah ok, in that case mysql needs some magic to ask innodb to flush things to disk before copying the files or similar things? [08:35:07] easier than that, we just stop the server :-) [08:35:41] we can shnapshot and copy in a hot way, but it takes time to run recovery [08:36:12] or we can use xtrabackup, but it doesn't work for s3 [08:36:56] having the 2 backup methods would take 1TB extra, so I am not sure we can afford it [08:38:41] marostegui, I would be ready to change master for s5 s4, s6 and s7, do not trust them [08:38:55] really? [08:39:13] we need to reenable replication btw [08:39:17] I mean, I trust them enough to leave them as masters [08:39:21] but not to not fail [08:39:38] well, at least they have done the alters without issues... [08:39:43] so I would like to have a plan B sketeched [08:39:44] that is something else apart from the repl thread [08:40:07] The easiest thing would be to go back to the previous masters if needed [08:40:19] like, if master fails, this is the candidate, and have it ready and decided [08:40:52] e.g. db1097 I think would be the candidate for s4 [08:41:15] let's add a comment on db-eqiad.php if you want [08:41:20] no need [08:41:25] this is just an exercice [08:41:31] let's add it to an etherpad [08:41:37] hopefuly we do not need it [08:41:50] also, the "old master" I added is precisely marking that [08:41:53] :-) [08:42:04] for me it would be: s4 -> db1097, s5 -> db1049, s6 -> db1050, s7 -> db1041 [08:42:06] but we need to check if still using STATEMENT [08:42:09] basically old masters [08:42:10] ^ [08:42:13] plus db1097 [08:42:14] true [08:42:43] so we need to: enable replication, check binlog for those.. [08:42:55] enable? [08:43:00] what do you mean? [08:43:10] sorry, restore replication codfw -> eqiad [08:43:17] ah, yes [08:43:30] that was on schedule [08:43:33] see calendar [08:43:39] yes yes [08:44:04] regarding semisync [08:44:19] I was thinking of disabling semisync for large servers [08:44:35] unlike on codfw, we have fast and slow hosts [08:44:42] and how we have faster masters [08:44:48] faster than some slaves [08:45:08] so I was thinking on not having the newest hosts on the semisync pool [08:45:15] to avoid lag [08:45:19] what do you think? [08:45:40] so, you mean leaving it only on slow hosts?? [08:46:04] I am not sure I am following your point [08:46:20] the alternative is that once 1 fast host receives the event, it follows [08:46:43] and with faster master and mixed slaves [08:46:49] that would be problematic [08:47:00] ah right [08:47:06] unless [08:47:23] there is a way to increse minimum acks [08:47:30] which I am not sure in this version [08:47:32] No, I get your point now [08:47:47] there is a chance of fast hosts to get delayed [08:47:51] but let's be honest [08:47:58] that is unlikely [08:48:01] (i think) [08:48:05] unless we have a massive issue [08:48:07] I did an alter on s3, newer servers almos had no lag [08:48:17] other took almost a week to catch up [08:48:24] to show the difference [08:48:42] yeah, we only had issues with large servers when we have massive issues with queries and stuff [08:48:44] now with db106X masters [08:48:45] otherwise... [08:48:49] that will be an issue [08:49:09] yep, indeed [08:49:13] good point [08:49:43] I will check if we can have acks = # new servers + 1 [08:51:23] my eyes [08:51:25] https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html [08:51:53] you want to do that also before the switch? [08:52:01] yes [08:52:15] part of the "checking semi sync" steps [08:52:41] youhave the etherpad handy?? [08:53:05] rpl_semi_sync_master_wait_for_slave_count introduced on 5.7.3 [08:53:08] :-( [08:53:15] :_( [08:54:12] I am searching the right one [08:58:37] no, we don't have one [09:06:03] so here is the thing [09:06:25] this needs careful checking because we are in the middle of moving things [09:06:35] and we have servers with statement, row and mixed [09:07:35] and we have to do statement for labs old, but we prefer row for labs-new (but statement works ok) [09:08:02] and mixed is an unknown [09:08:37] also this is why I wanted db1097 unpooled, to be able to switch quickly to master [09:08:44] *depooled [09:09:28] there is one extra thing we didn't do [09:10:08] and that is switchover db1052 [09:10:25] there is no need to swithcover db1052 [09:10:26] I thought about that yesterday, but I decided not to do it [09:10:31] for the first phase of depooling [09:10:33] there is [09:10:37] in the long term [09:10:46] as it will be decommed next [09:10:53] probably next year [09:10:54] next meaning 2018? [09:10:56] plus [09:11:04] next fiscal [09:11:04] I guess we will have another switchover before that no? [09:11:18] but I convinced myself not to do it [09:11:24] due to stability reasons [09:11:33] that was a good good good decision [09:11:39] db1052 has served as master for 3.4 years [09:12:44] plus the fact that we already have to deal with lots of new masters already [09:12:56] so leaving enwiki for its own change is a good decision I believe [10:15:22] 10DBA, 10Tool-Labs-tools-Other: Tired of APIError: readonly - https://phabricator.wikimedia.org/T164191#3230997 (10MarcoAurelio) @zhuyifei1999 Something like: ``` jstart -N jobname -l release=trusty -mem 3G /mnt/nfs/labstore-secondary-tools-project/mabot/.pywikibot/jobs/tarea.sh ``` ? [10:17:27] 10DBA, 10Tool-Labs-tools-Other: Tired of APIError: readonly - https://phabricator.wikimedia.org/T164191#3231001 (10zhuyifei1999) I meant add `export PYTHONPATH=/data/project/shared/pywikipedia/core:/data/project/shared/pywikipedia/core/externals/httplib2:/shared/pywikipedia/core/scripts` to `/mnt/nfs/labstore-... [14:41:37] s2 complaining a bit about replication [14:42:05] lag? [14:42:10] yes [14:42:21] Server db1073 has 1.4478878974915 seconds of lag (>= 1.204628944397) [14:42:44] Server db1036 has 14.533014059067 seconds of lag (>= 6) [14:43:01] db1036 is s2 watchlist [14:43:05] and db1073 is s1 api [14:43:25] how is enwiki api doing? [14:43:34] it was overloaded last time [14:43:55] https://grafana.wikimedia.org/dashboard/db/mysql?orgId=1&var-dc=eqiad%20prometheus%2Fops&var-server=db1073&from=now-1h&to=now [14:44:43] https://grafana.wikimedia.org/dashboard/db/mysql-replication-lag?orgId=1&var-dc=eqiad%20prometheus%2Fops [14:44:55] I think only one host on s5 is complaining [14:45:01] and was complaining before [14:45:10] db1026- checking [14:45:22] dbstore2001 alert are expected right? delayed slave [14:45:31] yep [14:45:31] alert? [14:45:33] let me double check [14:46:00] is it behind? [14:47:13] Seconds_Behind_Master: 86562 [14:47:16] so that is fine [14:47:27] 24 hours is 86400 [14:47:32] so it is fine [14:54:14] 10DBA, 10MediaWiki-Database, 13Patch-For-Review, 07PostgreSQL, 07Schema-change: Some tables lack unique or primary keys, may allow confusing duplicate data - https://phabricator.wikimedia.org/T17441#2684619 (10Catrope) `ls_field_val` is used in a `FORCE INDEX`, which is causing errors like: ``` [Exceptio... [15:14:20] marostegui: ... quick question... on icinga dbstore2001 alarms says 766382.26... 10 days??? [15:14:30] yeah, I am checking that [15:14:34] because it doesn't make much sense [15:14:43] it has 0 lag with its master and its master has 0 lag with eqiad master [15:15:00] is not delayed? [15:19:06] volans: we restored replication on codfw masters to eqiad (it was removed due to maintenance on eqiad masters), so maybe that is playing a role here with heartbeat [15:19:28] could be, need help to investigate? [15:20:00] 10 days is roughly when we removed replication, so I guess in 24 hours it will receive the heartbeat and will notice that it is just 1 day behind as usually? [15:20:17] 10DBA, 10MediaWiki-Database: Evaluate the need for FORCE INDEX (ls_field_val) [now IGNORE INDEX (ls_log_id)], delete the index hint if not needed anymore - https://phabricator.wikimedia.org/T164382#3231740 (10jcrespo) [15:21:14] root@DBSTORE[heartbeat]> select ts from heartbeat where shard = "s1"; [15:21:17] +----------------------------+ [15:21:19] | ts | [15:21:22] +----------------------------+ [15:21:24] | 2017-04-24T18:18:58.001210 | [15:21:27] | 2017-01-26T08:16:47.000790 | [15:21:29] | 2017-05-02T15:21:00.001010 | [15:21:32] +----------------------------+ [15:21:34] 3 rows in set (0.00 sec) [15:23:36] when did you re-activate replication? [15:23:38] yesterday? [15:23:44] today [15:24:21] ah ok, then we'll get it 1d after the re-activation [15:24:32] yes, that is what I think will happen [15:24:33] and the alarm will fix himself I guess [15:24:39] I will silence the alert so it doesn't show up there [15:24:41] wanna ack them? [15:24:43] for 20 hours [15:24:43] yeah [15:24:51] great, thanks [15:25:26] thank you! [15:34:21] what is the summary, I am not in the loop [15:34:45] 10DBA, 10MediaWiki-Database: Evaluate the need for FORCE INDEX (ls_field_val) [now IGNORE INDEX (ls_log_id)], delete the index hint if not needed anymore - https://phabricator.wikimedia.org/T164382#3231857 (10jcrespo) [15:35:53] oh, I see [15:35:56] jynus: all seems good, the heartbeat from eqiad masters are not yet in dbstore2001, because you restarted replication today and it's delayed by 24h, so it will arrive tomorrow and clear the alarm [15:35:57] alerting issues [15:36:10] yeah- I see [15:36:13] that's our understanding [15:36:22] replication deply is controled by show slave status [15:36:27] but check is done with heartbeat [15:36:40] which leads to some weirdness under maintenance [15:37:03] specially disconnecting eqiad and codfw [15:37:11] which is not a normal state [15:37:13] yeah I believe tomorrow we should be good [15:37:17] yes [15:37:44] and on switch, puppet has started to compare from eqiad all of a shudden [15:37:55] because it is the canonical host [15:38:09] yep [15:38:24] so the mental note is to not do a switch without 24 of replication to the lagged slaves [15:40:43] yeah, that's should be normal [15:41:17] 10DBA, 10MediaWiki-Database: Evaluate the need for FORCE INDEX (ls_field_val) [now IGNORE INDEX (ls_log_id)], delete the index hint if not needed anymore - https://phabricator.wikimedia.org/T164382#3231898 (10jcrespo) Very relevant: log_search was renamed from primary to ls_field_val some time ago: https://git... [16:22:44] jynus: I am going to drop off now unless you need me [16:23:15] no, have some rest [16:23:23] you too eh [16:23:32] see you tomorrow! [16:23:33] yes, just finishing this [16:23:35] bye [16:40:52] 10DBA, 06Operations, 10hardware-requests, 10ops-codfw: codfw: (1) spare pool system for temp allocation as database failover - https://phabricator.wikimedia.org/T161712#3232098 (10jcrespo) tempdb2001 is not going to be used anymore, but before returning to the pool of spares, we need to retire if from pupp... [16:43:38] 10DBA, 07Epic, 13Patch-For-Review, 05codfw-rollout: Database maintenance scheduled while eqiad datacenter is non primary (after the DC switchover) - https://phabricator.wikimedia.org/T155099#3232135 (10jcrespo) 05Open>03Resolved a:03Marostegui Now done, the tasks that stay open are the ones related t... [16:44:00] 10DBA, 06Operations, 10hardware-requests, 10ops-codfw: codfw: (1) spare pool system for temp allocation as database failover - https://phabricator.wikimedia.org/T161712#3232139 (10Marostegui) >>! In T161712#3232098, @jcrespo wrote: > tempdb2001 is not going to be used anymore, but before returning to the p... [16:46:01] 10DBA: Convert unique keys into primary keys for some wiki tables on all s* shards (codfw) - https://phabricator.wikimedia.org/T164399#3232175 (10jcrespo) [16:46:19] 10DBA, 10MediaWiki-Database, 13Patch-For-Review, 07PostgreSQL, 07Schema-change: Some tables lack unique or primary keys, may allow confusing duplicate data - https://phabricator.wikimedia.org/T17441#3232192 (10jcrespo) [16:46:21] 10DBA: Convert unique keys into primary keys for some wiki tables on all s* shards (codfw) - https://phabricator.wikimedia.org/T164399#3232191 (10jcrespo) [16:47:15] 10DBA, 07Epic, 13Patch-For-Review, 05codfw-rollout: Database maintenance scheduled while eqiad datacenter is non primary (after the DC switchover) - https://phabricator.wikimedia.org/T155099#3232210 (10Marostegui) a:05Marostegui>03None We BOTH have worked al lot this, so not fair to assign it to me! Th... [17:10:44] 10DBA, 10MediaWiki-Database: Evaluate the need for FORCE INDEX (ls_field_val) [now IGNORE INDEX (ls_log_id)], delete the index hint if not needed anymore - https://phabricator.wikimedia.org/T164382#3232361 (10Anomie) Looks like the forcing was present when it was added in {rSVN50567}. Maybe @aaron remembers why. [17:27:50] 10DBA, 06Collaboration-Team-Triage, 10Flow: Something weird going on with Flow in nowiki? - https://phabricator.wikimedia.org/T164406#3232389 (10Jdforrester-WMF) p:05Triage>03High [17:33:58] 10DBA, 06Collaboration-Team-Triage, 10Flow: Something weird going on with Flow in nowiki? - https://phabricator.wikimedia.org/T164406#3232423 (10jcrespo) [17:37:05] 10DBA, 06Collaboration-Team-Triage, 10Flow: Something weird going on with Flow in nowiki? - https://phabricator.wikimedia.org/T164406#3232431 (10jeblad) I would say the chance is pretty high for an user error on the initial deletion, probably the user (me) was tired, but the second error was real enough. I h... [17:38:35] 10DBA, 06Collaboration-Team-Triage, 10Flow: Something weird going on with Flow in nowiki? - https://phabricator.wikimedia.org/T164406#3232340 (10jcrespo) jeblad your error was real, and it was caused by the incident at T164407, which has been since mitigated. You should not have any further issue from now on... [17:41:35] 10DBA, 06Collaboration-Team-Triage, 10Flow: Something weird going on with Flow in nowiki? - https://phabricator.wikimedia.org/T164406#3232438 (10Catrope) >>! In T164406#3232431, @jeblad wrote: > I would say the chance is pretty high for an user error on the initial deletion, probably the user (me) was tired,... [18:39:11] 10DBA, 06Operations, 10ops-eqiad, 13Patch-For-Review: Decommission db1057 - https://phabricator.wikimedia.org/T162135#3232627 (10Cmjohnson) 05Open>03Resolved Removed from rack, removed switch cfg, removed from dns. disk were removed and wiped on a different server [18:48:16] 10DBA, 06Operations, 10ops-codfw, 13Patch-For-Review: codfw rack/setup 22 DB servers - https://phabricator.wikimedia.org/T162159#3232661 (10Papaul) I talked to @ayounsi, db2084 was in the Wrong VLAN so the installation is complete now running puppet and salt on the server [18:59:21] 10DBA, 06Operations, 10ops-codfw, 13Patch-For-Review: codfw rack/setup 22 DB servers - https://phabricator.wikimedia.org/T162159#3232696 (10Papaul) [19:00:21] 10DBA, 06Operations, 10ops-codfw, 13Patch-For-Review: codfw rack/setup 22 DB servers - https://phabricator.wikimedia.org/T162159#3154083 (10Papaul) a:05Papaul>03Marostegui This complete. @Marostegui you can take over from here. Thanks. [19:33:55] 10DBA, 06Operations, 06Performance-Team, 10Traffic: Cache invalidations coming from the JobQueue are causing slowdown on masters and lag on several wikis, and impact on varnish - https://phabricator.wikimedia.org/T164173#3232826 (10Gilles) p:05Triage>03Low [19:34:17] 10DBA, 06Operations, 06Performance-Team, 10Traffic: Cache invalidations coming from the JobQueue are causing slowdown on masters and lag on several wikis, and impact on varnish - https://phabricator.wikimedia.org/T164173#3224448 (10Gilles) a:03aaron [19:37:51] 10DBA, 06Operations, 06Performance-Team, 05Multiple-active-datacenters: Apache <=> mariadb SSL/TLS for cross-datacenter writes - https://phabricator.wikimedia.org/T134809#3232857 (10Krinkle) [19:46:24] 10DBA, 06Operations, 05DC-Switchover-Prep-Q3-2016-17, 05Multiple-active-datacenters, 13Patch-For-Review: Decouple Mariadb semi-sync replication from $::mw_primary - https://phabricator.wikimedia.org/T161007#3232910 (10Krinkle) [19:57:54] 10DBA, 06Operations, 07Availability (Multiple-active-datacenters), 05DC-Switchover-Prep-Q3-2016-17, 13Patch-For-Review: Decouple Mariadb semi-sync replication from $::mw_primary - https://phabricator.wikimedia.org/T161007#3232994 (10Krinkle) [19:58:39] 10DBA, 06Operations, 06Performance-Team, 07Availability (Multiple-active-datacenters): Apache <=> mariadb SSL/TLS for cross-datacenter writes - https://phabricator.wikimedia.org/T134809#3233013 (10Krinkle)