[04:43:15] 10DBA, 10Parsoid, 10Parsoid-Tests: testreduce_vd database in m5 still in use? - https://phabricator.wikimedia.org/T245408 (10ssastry) [06:04:57] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) p:05Triage→03Medium [06:22:40] 10DBA, 10Parsoid, 10Parsoid-Tests: testreduce_vd database in m5 still in use? - https://phabricator.wikimedia.org/T245408 (10Marostegui) >>! In T245408#5993222, @ssastry wrote: >>>! In T245408#5991238, @Marostegui wrote: >> @ssastry any follow up on this task? > > Sorry .. got distracted by everything else.... [08:27:28] 10DBA: Drop wb_terms in production from s4 (commonswiki, testcommonswiki), s3 (testwikidatawiki), s8 (wikidatawiki) - https://phabricator.wikimedia.org/T248086 (10Marostegui) Table renamed on db1104 (main and api traffic) ` root@db1104.eqiad.wmnet[wikidatawiki]> set session sql_log_bin=0; rename table wb_terms t... [08:27:46] 10DBA: Drop wb_terms in production from s4 (commonswiki, testcommonswiki), s3 (testwikidatawiki), s8 (wikidatawiki) - https://phabricator.wikimedia.org/T248086 (10Marostegui) [08:44:20] 10DBA, 10cloud-services-team (Kanban): Drop nova and nova_api databases from m5 - https://phabricator.wikimedia.org/T248313 (10Marostegui) `nova` database renamed: ` root@cumin1001:/home/marostegui# mysql.py -hdb1133 nova -e "show tables" +----------------------------------------------------+ | Tables_in_nova... [09:31:00] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) [09:32:06] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) a:03Marostegui [09:40:52] o/ [09:41:23] addshore: o/ [09:41:23] I have a query that I am trying to optimize for someone for the new terms storage and the labs replicas and I was wondering if one of you could just glance at what I created quickly to see if there is anything else I could change! [09:41:54] currently got https://phabricator.wikimedia.org/P10754 which takes 4.5 mins to complete [09:42:31] running it for the largest language only, takes 1min 18 seconds, and one of the smaller langusges it takes ~ 15 seconds [09:42:59] I guess there isn't any more optimization? :D [09:43:04] let's see [09:43:20] keep one thing in mind, which is that the replicas are kinda overloaded, specially quarry's [09:43:34] so maybe that's the fastest it can actually run :) [09:43:35] let me check [09:43:53] yeah, well, it was just over 4 mins on the analytics replica, trying on quarry now [09:44:14] | 2 | DERIVED | wbt_text_in_lang | range | PRIMARY,wbt_text_in_lang_language | wbt_text_in_lang_language | 22 | NULL | 29397024 | Using where; Using index; Using temporary; Using filesort | [09:44:15] uff [09:44:17] yeah [09:44:34] doing a query per language makes that ref be a "const" [09:45:44] Let me see what the optimizer trace shows [09:45:57] There's no way that query can be split? [09:47:14] It can easily be split by language, no need for it to have all those languages in there [09:47:20] I guess that would be the final optimization [09:47:51] Then you get: [09:47:54] https://www.irccloud.com/pastebin/zeEDMQAl/ [09:48:13] for the largest version of the query [09:49:13] that is a lot better indeed [09:49:22] still quite a bunch, but definitely better [09:49:33] I am checking the previous query (still running) [09:59:49] I guess the labs tables are also compressed? [09:59:59] so probably got some overhead for the first people that start querying these tables [10:07:23] addshore: Yeah,I believe so, we compressed everything recently [10:07:24] I can check [10:07:29] 73 rows in set (9 min 15.84 sec) [10:08:13] https://phabricator.wikimedia.org/P10755 [10:08:14] :( [10:09:27] I think that one runs on 4:15 once the tables are a bit in memroy [10:09:50] yeah, but that is not a safe assumption, specially on labs [10:09:54] "ranges": [ "(as) <= (wbxl_language) <= (as)", [10:09:55] hah [10:09:57] with so many different queries running all the time [10:10:24] I wrote an evil query that UNIONS a bunch of stuff (no idea if it would make it faster) and it also returns in the same time [10:10:46] Right now still waiting for the query for 1 language to return on quarry though :/ [10:11:21] 10DBA, 10cloud-services-team (Kanban): Drop nova and nova_api databases from m5 - https://phabricator.wikimedia.org/T248313 (10aborrero) LGTM, please get a +1 from @Andrew too. [10:12:17] yeah, quarry is not performing well these days, we have some tasks and some actions plans [10:12:42] Executed in 787.56 seconds as of Tue Mar 24 2020. [10:12:52] 10 mins for 1 language, not tooooooo shabby, :P [10:13:27] let me try for "bn" which should look at the most rows [10:15:59] I think the right answer to this is that these numbers should be calculated in the analytics realm and then publish somewhere [10:16:13] which they were! until that broke :D [10:16:24] https://phabricator.wikimedia.org/T154601 [10:43:09] 10DBA, 10cloud-services-team (Kanban): Drop nova and nova_api databases from m5 - https://phabricator.wikimedia.org/T248313 (10Marostegui) Thanks, I will wait for @Andrew [10:45:05] # bn, Executed in 1809.17 seconds as of Tue Mar 24 2020. [10:45:07] slow, but it works [10:47:16] that's quarry? [10:47:25] yup [10:47:30] for a single language [10:47:41] Hopefully that'll improve sometime soon [10:47:50] We are seeing what we can do to improve quarry [10:47:58] We have two tasks about it being slow :( [10:48:00] Yeah, i'm running it a second time now to see how long it takes once in some caches [10:48:04] https://www.irccloud.com/pastebin/zY1GHWzY/ [10:48:07] ^^ that was the query [10:48:40] that still scans 55M rows [10:49:33] I mean, the resulting count is 32+ million, so the # of rows probably makes sense [10:50:40] 47 seconds for a first run on the analytics machine, and 29 seconds for a second run [10:53:49] nice, but that host is not used at all [10:54:00] it is not the jungle like quarry's [11:07:41] please see: https://gerrit.wikimedia.org/r/c/operations/puppet/+/583049 [11:08:05] I've disabled alerting on s3 source backups hosts, db2098 and db1095 [11:08:37] checking [11:09:30] jynus: have you disabled the alert manually or going to disable notifications on those hosts? [11:11:06] manually, I still want to be alerted if replication breaks or otherwise [11:11:34] there is not yet a way to do it per-instance on puppet, sadly [11:12:17] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) I will start with s6, and first, host by host in codfw, to make sure nothing unexpected happens - also note that this schema change will require all the masters t... [11:14:24] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) How the tables look like after the alter @TK-999 please confirm this looks good: ` root@cumin1001:/home/marostegui/T248333# for i in frwiki jawiki ruwiki; do echo... [11:14:47] +1ed [11:25:45] thanks [11:34:28] I've run out of tendril or db automation small tasks to suggest to new contributors [11:35:13] if you have some very small nitpick on current scripts, let me know so I can create a ticket and send it to volunteers [11:36:26] the ones on "Backlog (help welcome)" are not really that small [11:39:12] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10TK-999) It is looking good, thank you! [11:46:25] jynus: ah cool, will take a look [11:46:35] all the ones you suggested are under the main tracking one, no? [11:59:09] marostegui: FYI I added to our WMCS meeting notes the thing about the nova databases in m5 to make sure we discuss it tomorrow at the latest in our team meeting [12:01:09] arturo: thank you! :) [12:06:22] \o/ [13:23:03] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Anomie) >>! In T248333#5994497, @Marostegui wrote: > as it is changing the column data type It shouldn't be changing the type, only changing from NOT NULL to NULL. [[https:/... [13:32:23] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) >>! In T248333#5994717, @Anomie wrote: >>>! In T248333#5994497, @Marostegui wrote: >> as it is changing the column data type > > It shouldn't be changing the typ... [13:52:21] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) I have confirmed that changing the `DEFAULT ' '` doesn't produce any locking - this is how db2114 looks like ` root@cumin1001:/home/marostegui/T248333# for i in... [14:04:14] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Anomie) >>! In T248333#5994749, @Marostegui wrote: > So for those with varbinary we can just: > ` > alter table page change page_restrictions page_restrictions varbinary(255)... [14:14:00] 10Blocked-on-schema-change, 10DBA: Schema change: Make page.page_restrictions column NULL - https://phabricator.wikimedia.org/T248333 (10Marostegui) >>! In T248333#5994839, @Anomie wrote: >>>! In T248333#5994749, @Marostegui wrote: >> So for those with varbinary we can just: >> ` >> alter table page change pag... [15:29:15] I had suggested the following microtasks: [15:29:22] T218189 - there is a patch for it already [15:29:23] T218189: wmfmariadbpy/CuminExecution must capture Exception cumin.transports.WorkerError - https://phabricator.wikimedia.org/T218189 [15:29:36] T200035 - too large [15:29:37] T200035: DB backup restore skip empty databases - https://phabricator.wikimedia.org/T200035 [15:30:00] T96499 - jquery part already solved [15:30:00] T96499: dbtree loads third party resources (from google.com/jsapi) - https://phabricator.wikimedia.org/T96499 [15:30:17] T200306 - too large [15:30:17] T200306: Improve database master switchover script - https://phabricator.wikimedia.org/T200306 [15:30:49] T204110 already assigned [15:30:50] T204110: Add favicon to icinga and tendril - https://phabricator.wikimedia.org/T204110