[00:43:14] 10DBA, 10Phabricator, 10serviceops, 10Release-Engineering-Team (Backlog), and 2 others: Improve privilege separation for phabricator's config files and mysql credentials - https://phabricator.wikimedia.org/T146055 (10greg) [06:10:22] 10DBA, 10Growth-Team, 10Notifications, 10Schema-change: Remove etp_user from echo_target_page in production - https://phabricator.wikimedia.org/T217453 (10Marostegui) Thank you! :) [06:10:41] 10DBA, 10Growth-Team, 10Notifications, 10Schema-change: Remove etp_user from echo_target_page in production - https://phabricator.wikimedia.org/T217453 (10Marostegui) p:05Triage→03Normal [06:49:32] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) [07:32:16] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) I defragmented dbstore1005, just to see the figures and the wikidatawiki one is spectacular :) : BEFORE: ` -rw-r----- 1 mysql mysql 9.4G Mar 5 06:49 enwiki/echo_event.ibd -rw-r----- 1 mysql mysql 23G Mar 5 06:4... [07:32:26] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) a:03Marostegui [08:31:32] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) [08:49:07] jynus: I don't understand your last comment, can you elaborate a bit more? what do you mean with "changes are not deployed in 3"? [09:00:47] redact_sanitarium.sh is not run [09:01:31] but until we don't run redact_sanitarium the view will not include those two new columns [09:01:37] then the view tries to be created and fail [09:01:55] not sure what you mean [09:02:04] so in my mind the workflow would be [09:02:14] 1) add the columns in production and they get replicated to labs [09:02:17] "the view will not include those two new columns" that may be now, but on the next wiki created? [09:02:18] 2) we run redact_sanitarium [09:02:35] 3) we give green light to cloud to recreate the views including those new columns [09:02:38] yes, next wiki created will have the column [09:02:44] yes, but not the view [09:02:51] which is run almost automatically [09:03:07] no, we tell cloud when we have sanitized everything [09:03:09] I prefer it to fail not based on "we greenlight", as cloud lately [09:03:16] has been doing things on its own [09:03:33] but on "things don't work technically" [09:03:45] so, to see if I understand you correctly, you are afraid of this when new wikis are created? [09:03:51] yes [09:04:00] first deploy no issue in anyway [09:04:04] but think longterm [09:04:26] that worries me, when everybody casually creates wikis without telling us [09:04:29] But for new wikis, we always do it that way, we do the sanitization and only after that we handle it to cloud [09:04:34] or views without telling us [09:04:52] what we do != what others do without telling us [09:04:54] can I sum up this conversation on the task? [09:05:00] so people get the context? [09:05:15] let me just ask you a question [09:05:30] how many times wikis have been created without us being told [09:05:43] and views without us greenlighting it [09:06:02] Not sure I remember, I recall the last testcommons one as the last one [09:06:07] and yes, anomie will not do that [09:06:10] and many others [09:06:24] but it only takes one person doing that to leak private data [09:06:26] But let me sum this up on the task so everyone get the context as our last comments might have gotten people lost, then we can discuss there :) [09:06:40] I prefer a technical barrier rather than a social one [09:06:47] so people are aware of what we are talking [09:07:28] when/if we go to blacklisting, I wouldn't care much [09:16:13] mmm [09:16:15] but [09:16:36] there is no difference between 1 and 3 in regards of the new wikis creation [09:16:46] well, we would need to create those columns manually for each wiki [09:16:48] on sanitariums [09:26:47] manually? if only we had a script we could run after each wiki creation! [09:27:04] you know what I mean [09:27:06] with manually [09:27:40] what I mean is that, assuming triggers are involved, let's put them all on the same place [09:27:52] I don't disagree with that at all [09:27:59] I don't like triggers, but apparently all 3 involve those [09:28:10] THey should go to sanitariums, I never questioned that :) [09:28:37] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) [09:29:07] also note that asside from the initial deployment, which would be messy no matter what [09:29:32] the alters on new wikis should be on very small tables (2-3 records) [09:29:47] yes [09:29:55] but think also this- do you prefer to do 300 alters on sanitarium, or on all of production? [09:30:00] :-D [09:30:03] No, I agree [09:30:16] and yes, that will be more messy [09:30:23] but messy on cloud only [09:30:28] My concerns are more in the way that we'll have different columns on the replicas AND the big IF of row based replication on tables with different columns [09:30:41] oh, I tested that, saw a previous comment [09:30:48] assuming it is also the last column [09:30:48] so you trust it? [09:31:00] it is likely to break on comment alter table [09:31:02] but what if we add new columns in the future? [09:31:10] but it will only break cloud [09:31:22] > breaking production [09:31:26] yep, but I mean, that column needs to always be the last one? [09:31:51] yes [09:32:21] there can be others, but row accepts aplying to the first columns [09:32:32] It is not a big deal, but it will bite us for sure (like it has happened with for example columns that are part of triggers) [09:32:46] cause we will always have to remember to put BEFORE bla bla on the alter [09:32:58] oh, I agree with that [09:33:24] I gave more context to the discussion on the ticket, let's see what others (specially cloud) think [09:33:26] but again, I always think "it will affect cloud only" [09:34:00] but even if we decide to go for option 3), we will still have the same issue with the new wikis [09:34:05] as in: the "procedure" can fail [09:34:17] yes, and views will fail to create [09:34:32] vs. silently failing [09:34:51] on new wikis, which should not be of high priority [09:35:12] yes, but the new columns will not be exposed if the views fail to create [09:35:26] exactly [09:35:33] but that also happens with option 1 [09:37:09] no? [09:37:19] until the views are created those two new columns aren't exposed [09:37:26] ? [09:37:32] the views won't fail [09:37:47] you won't be able to see those columns until the view is recreated [09:37:49] because the column exists automatically [09:38:05] but not on the views [09:38:19] yes, but the views can be created without being redacted, for the first time exposing private data [09:38:31] but that can happen now too [09:38:40] at the moment, if a view is created without redaction, nothing is exposed [09:39:12] so this is a new risk [09:39:16] what prevents that? [09:39:31] view referencing a non-existing column that is not created automatically [09:39:50] so failing technically, not based on procedure [09:40:22] I am ok with you disagreeing, but I suspect I didn't got understood correctly, sorry about taht [09:40:34] No, I am the one who is not understanding :) [09:40:46] yes, so it is my fault for not communicating properly [09:40:57] So, let's see option 1 goes [09:41:02] the new columns are added [09:41:05] BUT NOT sanitized [09:41:12] no one will be able to select on them [09:41:17] as the view is not recreated [09:41:20] remember they are added automatically [09:41:27] the views or the columns? [09:41:31] the columns [09:41:39] yes, but the view isn't referencing to it [09:41:41] and of course, nobody tell us [09:41:45] the view doesn't exist [09:41:51] because it is a new wiki [09:42:16] so cloud decides to be proactive (or set it automatically) detects a new wiki and creates the views [09:42:24] right, so what I described at: https://phabricator.wikimedia.org/T215445#5000717 [09:42:27] right? [09:42:34] I think there are times where they run process for all wikis [09:42:45] where they delete views for all wikis and recreate them [09:42:49] if that happens then yes, option 1 is bad [09:42:53] let's see what they say about it [09:42:54] so it is not out of the ordenary [09:43:06] let me ask them there [09:43:13] so 3, would hard fail, based on code rather than procedure [09:43:26] you understood my position, even if may not share it? [09:43:57] yep, now I got it [09:44:16] And option 3 will fix it because we have to create them manually on the sanitariums (already redacted) right? [09:44:20] and yes, I prefered to have it in production at first if you see my comment [09:44:34] on previous weeks [09:44:40] but this is out of the 3 options [09:44:51] there is also one bad thing about 1 [09:45:04] I edited the comment to ask them [09:45:07] which is analytics coming to "sqoop" production [09:45:13] One sec [09:45:30] and seeing this "is_visible" column on production and saying [09:45:33] Option 3 "fixes" this as we have to create the columns (and sanitized them at the same time) on sanitariums, right? [09:45:54] "we applied the is_visible rules we found on production!" [09:46:42] but seeing that anomie doesn't see a possiblity to maintain it on production, out of the 3, I prefer to be technically involved, responding your questino [09:47:09] add it to redact_sanitarium, alter only cloud infra [09:47:27] so, is my understanding of option 3 "way of fixing it" the thing you are thinking of? [09:47:33] yep [09:47:38] ok, got it fully now [09:48:00] so my initial option was a hardcore 1 (fully mantained on production) [09:48:13] but as it is a soft 1, I prefer a hardcore 3 :-P [09:48:25] (fully maintained on sanitarium) [09:49:05] my only concern with option 3 is the drift (we should maybe maintain a file with the columns, like with do with indexes) and the future alter tables, that will need to include the BEFORE [09:49:09] I still don't know if it will work, though, because tigger performance [09:49:27] yeah, let's see what people think after my comment summing up things [09:49:37] marostegui: not worried for one reason- if it fails, it fails hard [09:50:17] that is my whole phylosophy- monitoring is nice, but hard failing preferered to check problems [09:50:41] we would still have check_private_data additions to check for wrong trigger behaviour [09:51:19] "neither option #3 (nor option #1) fixes the current issue with exposing new wikis once they get created." [09:51:24] what do you mean? [09:51:30] that if a new wiki is created [09:51:33] and no one tells us [09:51:34] option 1 doesn't fix it [09:51:36] the views can be created it [09:51:37] 3 does [09:51:42] and you are exposing all the tables [09:51:45] view fail automatically [09:51:45] without sanitizing [09:51:52] so no exposure [09:51:58] what do you mean? [09:52:02] that is my whole point :-) [09:52:12] a view on the user table might not fail [09:52:34] CREATE VIEW AS SELECT is_visible: column not found [09:52:38] no no [09:52:43] I mean on the _whole_ wiki [09:52:52] if you mean something like blacklisting [09:52:53] I agree [09:52:59] but that is irrelevant to this conversation [09:53:00] Yeah, that was the point of the comment [09:53:06] because that is also true now [09:53:15] Yep, but people might not be aware [09:53:21] So I wanted to be clear that the current problem exists [09:53:21] if any, #3 minimizes it [09:53:33] That is what I mentioned, that it minimizes it [09:53:44] because it will physically fail, and it would be bad faith then if cloud view maintainer doesn't ping us at that moment [09:53:53] ok [09:54:01] This is what I wrote [09:54:01] Option #3 minimizes the risk of exposing the two new columns as those would need to be created specifically on the sanitarium hosts (most likely), however, neither option #3 (nor option #1) fixes the current issue with exposing new wikis once they get created. [09:54:06] maybe I should rephrase it? [09:54:10] it is ok [09:54:23] I basically wanted to say that this problem already exists with new wikis [09:54:39] so we should fix that [09:54:42] new wikis with all the tables, not specific to this table [09:54:46] but that is a parallel conversation [09:54:57] although it is true that if that is fixed [09:55:10] the differences between options are minimized [09:55:28] maybe still the "confusing on production" and "less alter tables" option [09:55:29] I changed it to fixes the current issue with exposing new wikis (not only for these tables) once they get created. [09:55:35] so I added "not only for these tables") [09:58:12] so my worry here was having mutual understanding, I don't mind and even prefer if you disagree [09:58:56] yeah, I was not understanding [09:58:58] now it is clear [09:59:00] my problem is I am always thinking 3 movements ahead (probably wrongly), and you usually are more grounded [09:59:03] and I am not sure which one I prefer! [09:59:21] I think the 3 are bad [09:59:33] they both have pros and cons [09:59:38] but I don't have alternatives here and now [09:59:56] 2 I don't like for sure [10:00:15] I like both 1 and 3, but not sure which one is less bad :) [10:00:26] let's see what others say [10:00:34] we gave food for thought :) [10:37:08] I've created https://wikitech.wikimedia.org/wiki/MariaDB/Sanitarium_and_Labsdbs#Private_data_checks you can double check it [10:37:19] will do thanks .) [10:37:21] :) [11:11:48] 10DBA: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) [11:28:28] marostegui: any idea what date the data you imported for https://phabricator.wikimedia.org/T211338 was from? [11:29:02] Currently trying to estimate the growth rate of the table again and find out the points in time that are "scary" [11:29:33] you mean from which host? [11:31:51] well, more, what point in time was the data from [11:32:11] Was it from a backup a month before that ticket was done? or was it actually the data at that point in time? [11:32:55] addshore: I think I took an already existing backup [11:33:23] so probably (again, probably) I took either that week's or the previous' week backup [11:33:44] let me see if there is someway where I can find that [11:35:10] BUt I think I deleted the files after the dump was finished [11:37:27] marostegui: I think knowing it was within 2 weeks is pretty good, thanks! [11:37:38] addshore: something like that most likely [11:38:32] we keep 3 backups, so 3 weeks tops [11:39:47] cool! [11:40:30] So, 1 year or so ago, we got an estimate of when DBAs thought we could run with the wb_terms table, ( cant actually find those estimations documented now), ut do you have any up to date estimations ? [11:40:42] sorry if this is sounding repetitive :/ [11:40:52] I'm starting to feel like a broken record [11:40:55] haha [11:41:33] I don't recall where that could be, maybe Amir1 does? [11:41:38] Could be a ticket somewhere [11:42:38] Maybe it is in the main incident ticket *looks* [11:42:51] I have collected so many documents about this topic now, its hard to actually find anything [11:42:55] the tmp1 index one? [11:42:59] yup [11:43:06] maybe, not sure [11:43:11] maybe that one links to others [11:43:18] I recall there was a normalization one or something like that [11:43:21] Like a meta ticket for wb_terms [11:43:58] I did have a document with some notes between you guys and amir regarding normalization from last year at some point, let me look in that [11:44:27] Size of table enormous: 900 GB in early April 2018, of which roughly 70% is index data [11:45:00] -rw-rw---- 1 mysql mysql 946G Mar 5 11:44 wb_terms.ibd -> this is the master [11:45:26] thanks [11:45:56] its hard from where I am to figure out which of these sizes matter, as the size reported by mysql is much smaller than that :P [11:46:11] that is the disk size [11:46:34] the table could be fragmented so an optimize would reduce it, however, we don't have anough disk space to do so anymore \o/ [11:47:07] hahahaa [11:47:21] noted that in the doc [11:47:29] yeah, an alter table right now is impossible [11:47:44] we don't have enough disk space on the current master to be able to alter the table [11:47:50] and how far away from filling the disk on one of the hosts are we? [11:48:03] the master has 897G available [11:48:18] the master is old hardware though [11:48:27] the other hosts are around 1.2T free [11:48:37] which is still quite narrow considering how big that table is [11:48:58] Normally you'd need to plan that the alter table will fully copy the table [11:49:25] So almost 1TB could be reached (could be more or could be less) and we only have 1.2T available [11:49:37] So I wouldn't really feel very confident running an alter there [11:49:39] as of today [11:49:42] yupp [11:50:12] so, another concern was / is indexes being too big etc to be loaded into memory / ending up using disk more for queries, any thoughts on that topic? [11:51:27] Don't know, that table is really massive to fit in memory anyways, it needs to go away! [11:51:47] yup :P [11:51:54] so far with the SSDs we have generally no much issues, but that will not be the case forever [11:52:26] and we have the query killer anyways, so… [11:52:31] and predicting when things might become a bigger issue is, well, hard, until we get there [11:52:42] could be 6 months? could be 6 years? (in theroy) [11:54:25] yeah, that is pretty hard, you probably would know better than us in the sense of you know if there are more things using that table or less [11:54:42] but the fact that the table is impossible to alter right now, it is _very_ worrying [11:55:48] ack [11:55:57] when do we stop being able to alter other tables? [11:57:04] what do you mean? [11:57:23] addshore: there is some writing about that already [11:57:40] jynus: do you know where the doc or etherpad or phab ticket is? [11:57:42] https://wikitech.wikimedia.org/wiki/Schema_changes#Dangers_of_schema_changes [11:57:55] addshore: revision was at risk for example,but with all the stuff that has been written to offload it, we are now kinda in a better shape [11:58:53] marostegui: not yet there, eventually [11:59:02] jynus: -rw-rw---- 1 mysql mysql 383G Mar 5 11:57 revision.ibd [11:59:06] I more meant, how big on disk is the next biggest table, that is wb_terms kept filling disk, it would not only be wb_terms that you could not alter [11:59:06] I meant it is alterable again [11:59:16] aaah, gotcha, thats exactly what I was going for :) [12:00:10] going for lunch [12:00:10] o/ [12:01:45] marostegui: I mean not yet there for the "better shape", eventually those will shrink [12:02:15] right now there is a lot of duplication still on MCR, comment and actor [12:14:38] jynus: does https://tendril.wikimedia.org/report/index_find work? or an I just not using it correctly? :) [12:14:53] probably both [12:16:13] if I search on db1067, it find revision on enwikiquote [12:17:46] I would suggest not using it [12:18:04] ack [12:18:22] Is it possible to get the sizes of each index on wb_terms then? (I thought that report on tendril might do that for me) [12:18:42] afaik it would be frome mysql.innodb_index_stats, but I can't see that table [12:19:34] there are no index size statistics, that I know of [12:19:42] there are "total index size" [12:19:46] *indexes [12:19:58] okay, if a breakdown is not possible then that is fine [12:20:08] innodb_index_stats is not that [12:20:41] it is for cardinality, for the query optimizer, it is not stats about indexes [12:20:56] okay [12:21:33] there could be a way to find out, but it would need spetialized tools we don't have analysing internal index structure [12:22:21] you can get the total size on show table status [12:22:26] (logical size) [12:22:50] and more or less get an idea of the proportion base on the size of columns + size of primary key [12:22:54] *based [12:27:29] I guess size property may be useful, but I would not trust that to make conclusions [12:27:54] and that measures pages which may be missleading, etc. [12:28:30] ack, thanks for the explanation [12:30:15] you should have access to those anyway through information_schema.INNODB_SYS_INDEXES [12:39:03] Example 14.4 of https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-system-tables.html looks interesting [12:39:40] but still, nothing per index, thanks for your help as always :) [12:39:59] there can be approximations [12:40:33] the size will be for the most part proportional to the secondary column storage + PK [12:40:49] alternatively, one can remove all indexes [12:41:03] and see how much the index size increases :-) [12:41:16] *all the indexes but the PK [12:41:32] increases on each "add index" [12:41:42] that should work for your test hosts [12:54:49] jynus: oooh, i could indeed to that on the test hosts :) [12:55:11] jynus: I was just taking another look at https://phabricator.wikimedia.org/T195792 is the migration to MariaDB 10.1 complete now? [12:55:52] yes [12:56:01] amazing, I'll add a comment to the ticket [12:56:22] we are not migrating to next server and os [12:56:24] *now [13:03:33] 10DBA, 10Operations, 10ops-eqiad, 10Patch-For-Review: db1114 crashed (HW memory issues) - https://phabricator.wikimedia.org/T214720 (10jcrespo) Hi, @Cmjohnson The remote IPMI password was out of sync. Just mentioning to add it on the to do list for motherboard changes (this and reviewing the boot order, w... [13:04:37] 10DBA, 10Operations, 10Patch-For-Review, 10User-fgiunchedi: Upgrade mysqld_exporter in production - https://phabricator.wikimedia.org/T161296 (10ops-monitoring-bot) Script wmf-auto-reimage was launched by jynus on cumin1001.eqiad.wmnet for hosts: ` ['db1114.eqiad.wmnet'] ` The log can be found in `/var/log... [14:08:16] 10DBA, 10Patch-For-Review: Defragment echo_event tables on x1 - https://phabricator.wikimedia.org/T217591 (10Marostegui) [14:10:41] 10DBA, 10Operations, 10Patch-For-Review, 10User-fgiunchedi: Upgrade mysqld_exporter in production - https://phabricator.wikimedia.org/T161296 (10ops-monitoring-bot) Completed auto-reimage of hosts: ` ['db1114.eqiad.wmnet'] ` Of which those **FAILED**: ` ['db1114.eqiad.wmnet'] ` [14:49:06] quick question about centralauth - I can see that it is in s7, but I don't find it in the s7.dblist in mediawiki-config.. [14:49:09] is it on purpose? [14:50:42] elukey: I think it is cause it is not a wiki per se [14:56:43] marostegui: ah ok so I'll add a special case in analytics-myql [14:56:47] *mysql [14:57:01] I thought that the dblists contained all the databases [14:57:09] I am not going to parse db-eqiad.php :P [15:02:36] dblist if of wikis [15:02:39] *is [15:02:55] each wiki is a database, but not each database is a wiki [15:03:28] let me see where I can find where is the location of centrauth defined [15:04:02] I grepped on mediawiki-config and didn't see it [15:04:20] (not mentioning config files of course) [15:31:32] I found it in db-eqiad.php [15:32:05] http://code.openark.org/blog/mysql/un-split-brain-mysql-via-gh-mysql-rewind [15:32:22] ^marostegui interesting because it could be even easier for mariadb [15:33:28] elukey: remember I warned dblists is not a canonical place to look for config [15:34:10] jynus: I didn't recall that (my bad) but for the moment centralauth seems the only corner case, the rest works really great [15:34:18] we had special cases where dblists and db-{}.php differed, normally not a huge issue [15:34:30] for example, during a wiki movement [15:34:43] or a new wiki creation [15:36:50] elukey: https://phabricator.wikimedia.org/T212386#4951229 [15:37:32] that may help creating an icinga alert if needed in the future about "dns out of sync" [15:39:22] sure [15:44:12] parsing php with python is not really something that I'd look for, but I'll switch to db-eqiad.php if more corner case will arise [15:44:14] :( [15:44:18] he he [15:44:54] technically, I am not parsing php with python, I just run the php, and convert it to json, then import it into python :-) [15:46:56] :D [16:09:50] so I have some bug at https://gerrit.wikimedia.org/r/494236 for multisource hosts: https://puppet-compiler.wmflabs.org/compiler1002/14971/dbstore2001.codfw.wmnet/ but I cannot spot where [16:10:09] s/multisource/multiinstance/ [16:11:07] actually, I may not, I just have a removed space that is fixed on the others? [16:25:36] elukey: I don't know if you are reusinf prometheus exporter for your mysqls, If you do, this may affect you: https://gerrit.wikimedia.org/r/494236 [16:26:31] jynus: I do but I don't do anything particular, I guess it is ok, if it breaks I'll fix it :)