[06:26:35] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) From my chat with @Banyek the approach now is to create tables populated with the following queries... [06:47:47] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) >>! In T210693#4798691, @Milimetric wrote: > And to follow up on my first bullet from before: > > 1... [09:43:27] 10DBA, 10MediaWiki-API, 10MediaWiki-Database: prop=revisions API timing out for a specific user and pages they edited - https://phabricator.wikimedia.org/T197486 (10Marostegui) Looks like this only happens on enwiki (I guess because of the size of the revision table there). I have upgraded db1080 to 10.1.37:... [10:32:28] 10DBA, 10MediaWiki-API, 10MediaWiki-Database: prop=revisions API timing out for a specific user and pages they edited - https://phabricator.wikimedia.org/T197486 (10Marostegui) Also happens on eswiki (s7 - the biggest revision table of all those wikis on s7, 44GB) (db1079 - 10.1.33): ` root@db1079.eqiad.wmne... [10:52:58] 10DBA, 10MediaWiki-API, 10MediaWiki-Database: prop=revisions API timing out for a specific user and pages they edited - https://phabricator.wikimedia.org/T197486 (10Marostegui) And after upgrading db1090:3317, it gets fixed: ` root@db1090.eqiad.wmnet[eswiki]> select @@hostname; select version(); +-----------... [11:39:53] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) For testing I created the view as `comment_view_temp` with the query @Bstorm wrote in T210693#4798638 an... [11:43:49] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) On which host and on which database? [11:46:08] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) Ah, I see, labsdb1010 but why is the view `comment_view_temp` on `enwiki` and not on `enwiki_p` wher... [11:52:23] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) No, there is no any real reson, but as this is a test only I wanted to keep this 'clean' and enwiki feel... [13:21:07] 10DBA, 10Analytics, 10Analytics-Kanban, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Banyek) What I am not sure is what to do with the 'extra' databases (I see there are no previous clusters having them, but if we move them t... [13:24:01] 10DBA, 10Analytics, 10Analytics-Kanban, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Banyek) About the `staging` db: Analytics team uses it, but I am not sure if they need all the data in it (is there any data or just tables... [13:42:15] 10DBA, 10Analytics, 10Analytics-Kanban, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Banyek) maybe I can summon here @Milimetric about the `staging` db? [13:43:02] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) Thanks for the clarification - I just wanted to know if there was some specific reason for it that I... [13:49:09] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) >>! In T210693#4800137, @Marostegui wrote: > Thanks for the clarification - I just wanted to know if the... [13:50:57] marostegui: is it possible to see how much space the indexes on the table take up too? im pretty sure i saw that somewhere! [13:51:16] yep [13:51:20] for wb_terms? [13:52:08] yup [13:52:11] let me see [13:52:31] Index_length: 612300046336 [13:52:36] keep in mind that that is approximate [13:53:06] bytes? [13:53:11] if we need exact values I could run an analyze table but that can take quite long [13:54:09] addshore: bytes yep [13:54:12] ty [13:55:02] yw! [13:58:21] 10DBA, 10Analytics, 10Analytics-Kanban, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10elukey) This is a very good point, I'll bring it up to my team's standup today and I'll let you know. It has been used, as far as I know, fo... [13:59:24] 10DBA, 10Analytics, 10Analytics-Kanban, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Banyek) >>! In T210478#4800170, @elukey wrote: > This is a very good point, I'll bring it up to my team's standup today and I'll let you kno... [14:34:02] I go and do the T85757 schema change on s5 codfw [14:34:03] T85757: Dropping user.user_options on wmf databases - https://phabricator.wikimedia.org/T85757 [14:34:16] tomorrow I'll keep doing it on s5 eqiad [14:35:26] ok [15:10:48] 10Blocked-on-schema-change, 10DBA, 10Patch-For-Review, 10Schema-change, 10User-Banyek: Dropping user.user_options on wmf databases - https://phabricator.wikimedia.org/T85757 (10Banyek) [15:11:43] 10Blocked-on-schema-change, 10DBA, 10Patch-For-Review, 10Schema-change, 10User-Banyek: Dropping user.user_options on wmf databases - https://phabricator.wikimedia.org/T85757 (10Banyek) [15:12:07] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Marostegui) [15:31:56] 10DBA, 10Operations, 10decommission, 10ops-codfw: Decommission parsercache hosts: pc2004 pc2005 pc2006 (Dec 2018 lease return) - https://phabricator.wikimedia.org/T209858 (10Papaul) [15:35:47] 10DBA, 10Operations, 10decommission, 10ops-codfw: Decommission parsercache hosts: pc2004 pc2005 pc2006 (Dec 2018 lease return) - https://phabricator.wikimedia.org/T209858 (10Papaul) @RobH any reason why we have to add the servers that we are returning to the decommission tracking Google sheet since that sh... [15:45:54] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Bstorm) > Basically, we are importing actor and comment from production replicas, and these include comment text... [15:46:56] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Bstorm) That's relying on a very particular special case, but I think it is actually true. 😁 [15:53:59] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) We don't sanitize anything on `actor` or `comment` on a triggers level on sanitarium if that is what... [16:25:07] 10DBA, 10Operations, 10ops-eqiad: rack/setup/install pc1007-pc1010 - https://phabricator.wikimedia.org/T207258 (10Cmjohnson) Waiting on a technician to swap out the motherboard. Our request was approved. [16:29:30] 10DBA, 10Operations, 10ops-eqiad: rack/setup/install pc1007-pc1010 - https://phabricator.wikimedia.org/T207258 (10Marostegui) Awesome! Thank you @Cmjohnson! :) If you get it online today, reminder: RAID5 with 256 stripe! (Reminding it because it is not the usual config) Thanks a lot [16:30:49] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Milimetric) Following up on a good problem that @Bstorm raised with my approach. I would love @Anomie to take a... [16:47:51] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Milimetric) Following up on a good problem that @Bstorm raised with my approach. I would love @Anomie to take a... [16:57:13] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Anomie) >>! In T210693#4800694, @Milimetric wrote: > According to the `actor` view definition: https://gerrit.wi... [17:01:02] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Milimetric) >>! In T210693#4800781, @Anomie wrote: >>>! In T210693#4800694, @Milimetric wrote: >> According to t... [17:14:10] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Anomie) You said "any reference is sanitized → not present". The contrapositive of that would be "present → not... [17:14:36] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Bstorm) @Anomie I think you actually answered the question in that. As it currently stands, the logic there alr... [17:16:51] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Bstorm) To be clear, I'm deliberately trying to find problems with this because it sounds correct to me. I want... [17:22:42] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Banyek) I don't find the corresponding event in the master binary log: The output of `SHOW SLAVE STATUS` on labsdb1004 is the following: `MariaDB [(none)]> show slave status\G **********************... [17:23:14] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Banyek) p:05Triage>03High [17:23:52] I have to leave to a doctor now, I'll continue this when I am back [17:24:05] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Anomie) >>! In T210693#4800871, @Bstorm wrote: > @Milimetric would like to try using the sanitized views of the... [17:45:02] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Milimetric) Thanks very much @Anomie, I understand my misunderstanding, and your third answer is what I was asking. [18:09:32] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Marostegui) From what I can see it is part of a huge transaction - just ignore that table on the replication filters, let it replicate until it is in sync again and then reimport the table from the m... [19:22:46] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Banyek) I ignore the table with `SET SQL_LOG_BIN=0; SET GLOBAL Replicate_Wild_Ignore_Table='s51412\_\_data.%,s51071\_\_templatetiger\_p.%,s52721\_\_pagecount\_stats\_p.%,s51290\_\_dpl\_p.%,s51230\_... [19:28:36] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Banyek) replication is catching up [19:28:55] 10DBA: labsdb1004 replication broken for linkwatcher_linklog table - https://phabricator.wikimedia.org/T211210 (10Banyek) p:05High>03Normal [19:50:28] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) The 'materialized view' for comments is completed. I moved it into `enwiki_p` with name `comment_mat`. I... [20:01:20] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) The actor view was empty, and the empty `actor_mat` too. [20:07:22] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Anomie) If you want to fake up an `actor_mat` for a size estimate, I think something like this would do it. Obvi... [20:09:29] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) >>! In T210693#4801411, @Anomie wrote: > If you want to fake up an `actor_mat` for a size estimate, I th... [20:09:44] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Banyek) also we need to create indices for comment_mat [20:15:16] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Marostegui) I am not sure it makes sense to continue that approach. We have more than 900 wikis, even if it take... [20:23:25] labsdb1010 is repooled [20:23:56] labsdb1004 didn't catched up yet, I'll finish that tomorrow and I leave for today [22:17:51] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Anomie) Defining all the triggers might be a pain, but https://dev.mysql.com/doc/refman/5.5/en/replication-featu... [22:36:31] 10DBA, 10Analytics, 10Analytics-Kanban, 10Data-Services, and 3 others: Create materialized views on Wiki Replica hosts for better query performance - https://phabricator.wikimedia.org/T210693 (10Milimetric) On the issue of storage, the average per wiki would definitely not come out to 5GB. If you plot all...