[00:13:01] volans: I'm not sure what I'm doing wrong, but I keep getting a syntax error when I try to run a query. [00:13:10] SELECT * FROM externallinks_global A OUTER APPLY (SELECT TOP 1 * [00:13:10] FROM externallinks_userlog B [00:13:10] WHERE B.log_object = A.url_id AND B.log_action = 'changearchive' [00:13:12] ORDER BY B.log_timestamp DESC) as B; [00:13:26] It doesn't seem to like OUTER APPLY [00:24:03] I don't think outer apply is valid mysql sql at all [00:24:31] Really. It seems to be used in some documentation. [00:25:04] It might be in newer mysql versions... [00:25:04] https://forums.mysql.com/read.php?10,625014,625014#msg-625014 [00:25:20] But CROSS APPLY seems to be mssql specific [00:26:18] Google suggests cross apply is effectively an inner join [00:26:41] I want OUTER APPLY, not cross [00:27:31] LEFT OUTER JOIN then [00:28:23] What I want to do is fetch * from externallinks_global left joining externallinks_userlog and only fetching 1 row, carrying the latest timestamp in log_timestamp, in this one to many pair of tables. [00:29:29] Which is why I tried the OUTER APPLY method which selects the newest row when multiple records exist for a given entry inn the left table. [00:29:32] Reedy: ^ [00:30:01] also, SELECT TOP 1 won't work on mysql either [00:30:09] DERP [00:30:33] I pulled this from stack overflow [00:31:42] Reedy: any good way to make sure the left table rows aren't being duplicated when the right table has multiple matches? [00:32:18] And that the single entry joined from the right tables is the newest entry? [00:33:24] if you're ordering the other table, and putting a limit on it... [00:34:23] The other table would be ordered by log_timestamp, likely DESC and being LIMITed to 1. But how do I convey that limit without limiting or sorting the entire resultset. [00:34:45] I would ideally just like to apply those limits to right table as the join to the left. [00:35:32] Without blowing up the server, since somehow I've been writing queries that completely ignore the indexes lately. :/ [00:36:48] Reedy: ^ [02:20:50] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, 10Wikimedia-log-errors: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3353741 (10zhuyifei1999) [05:10:34] 10DBA, 10Labs, 10User-bd808, 10cloud-services-team (Kanban): Prepare and check storage layer for atjwiki - https://phabricator.wikimedia.org/T167715#3353839 (10Marostegui) Similar to: T168021#3353382 I did: ``` GRANT SELECT, SHOW VIEW ON `atjwiki\_p`.* to labsdbuser; ``` And then ran the script: `sudo /us... [05:11:15] 10DBA, 10Labs, 10User-bd808, 10cloud-services-team (Kanban): setup dewiki and wikidatawiki on the labsdb1009, 1010 and 1011 - https://phabricator.wikimedia.org/T168021#3353312 (10Marostegui) Adding the grant did work on: T167715#3353839 [05:12:25] 10DBA, 10Operations, 10ops-codfw: Degraded RAID on db2070 - https://phabricator.wikimedia.org/T167667#3353844 (10Marostegui) 05Open>03Resolved The rebuilt finished correctly: ``` logicaldrive 1 (3.3 TB, RAID 1+0, OK) physicaldrive 1I:1:1 (port 1I:box 1:bay 1, SAS, 600 GB, OK) physicald... [05:24:32] 10DBA, 10Labs, 10User-bd808, 10cloud-services-team (Kanban): Prepare and check storage layer for atjwiki - https://phabricator.wikimedia.org/T167715#3353848 (10Marostegui) 05Open>03Resolved a:05bd808>03Marostegui And views created on labsdb1010 and 1011 after adding the grant. Also created on labsd... [06:41:21] marostegui: I am fixing dbstore2001 MariaDB Slave SQL: s3 [06:46:45] jynus: thanks, I just got online, what happened? [06:47:32] x1 and s3 conflicted on database creation [06:47:38] ah [06:47:45] the usual problem :_( [06:47:54] do not know why, supposedly x1 is created with if not exists [06:48:16] supposedly... [07:39:00] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, 10Wikimedia-log-errors: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3353926 (10Marostegui) p:05Triage>03Unbreak! This is indeed broken - did an... [07:41:43] 10Blocked-on-schema-change, 10DBA, 10Patch-For-Review: Convert unique keys into primary keys for some wiki tables on s4 - https://phabricator.wikimedia.org/T166206#3353930 (10Marostegui) [07:55:49] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, 10Wikimedia-log-errors: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3352925 (10jcrespo) It is **not** using the rc_timestamp index, which creates t... [07:59:18] 10DBA, 10Epic, 10Tracking: Database tables to be dropped on Wikimedia wikis and other WMF databases (tracking) - https://phabricator.wikimedia.org/T54921#3353952 (10Marostegui) [08:03:20] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, 10Wikimedia-log-errors: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3353962 (10Marostegui) Thanks Jaime for putting that clearly - I re-read my mes... [08:04:18] 10DBA: Truncate 'searchindex' table on all wikis - https://phabricator.wikimedia.org/T167978#3351824 (10jcrespo) Yes, answering the question- all tables in core should exist on our installation. If they are not used on the WMF, they should exist empty (TRUNCATE). [08:05:52] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, 10Wikimedia-log-errors: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3353969 (10jcrespo) Yes, I will depool one server and try that or other things,... [09:52:58] 10DBA, 10Patch-For-Review: Migrate parsercache hosts to file per table - https://phabricator.wikimedia.org/T167567#3354099 (10jcrespo) pc1004,5,6 have been upgraded, restarted and they are catching up replication from the currently pooled servers db1096,db1099 and db1101. I will keep the new hosts pooled for s... [10:34:43] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354156 (10jcrespo) This is the actual explain running: ``` db1091.eqiad.wmnet[commonswiki]... [10:38:15] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354161 (10Marostegui) Would it be worth to force the index on code too? Even if it gets fi... [10:40:45] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354169 (10jcrespo) Refreshing the stats didn't help: ``` db1091[commonswiki]> ANALYZE TABL... [11:03:53] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354247 (10jcrespo) We should try to avoid it, look at this new queries- they break when th... [11:17:32] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354301 (10jcrespo) @Marostegui where did you get that query? I cannot see any reference to... [11:23:30] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354331 (10jcrespo) I think all this analysis is valid, but for a completely different quer... [11:24:32] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354337 (10Marostegui) >>! In T168010#3354301, @jcrespo wrote: > @Marostegui where did you... [11:26:12] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354340 (10jcrespo) I am 100% sure that the bad query here is: ``` function: ShortPagesPag... [11:28:40] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354347 (10jcrespo) ``` db1091[commonswiki]> EXPLAIN SELECT page_namespace AS `namespace`,... [11:30:25] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354352 (10Marostegui) Check this: https://tendril.wikimedia.org/report/slow_queries?host=%... [11:30:40] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354354 (10jcrespo) works much better without the force index: ``` db1091.eqiad.wmnet[comm... [11:36:14] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354362 (10jcrespo) The special thing about commons is that it adds 6 to the list of conten... [11:52:19] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354390 (10jcrespo) This is my proposal, https://gerrit.wikimedia.org/r/359404 I will test... [11:55:17] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354409 (10Marostegui) I was wondering why do you think the issue is: ``` SELECT page_name... [12:05:43] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354473 (10Marostegui) >>! In T168010#3353926, @Marostegui wrote: > **IGNORE this comment -... [13:11:13] 10DBA, 10Operations, 10ops-codfw: Degraded RAID on db2070 - https://phabricator.wikimedia.org/T167667#3354715 (10Papaul) Return label information {F8467699} [13:30:28] 10DBA, 10Patch-For-Review: Convert unique keys into primary keys for some wiki tables on s2 - https://phabricator.wikimedia.org/T166205#3354754 (10Marostegui) [13:45:43] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354811 (10jcrespo) On large wikis with extra Content namespaces like eswiki, the original... [13:56:21] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354866 (10jcrespo) However, with the patch, dewiki and wikidata are several times slower. [14:17:16] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354926 (10Anomie) Looking at the history, it seems the index was added for {T28393}. The... [14:22:30] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354934 (10jcrespo) ha, we reached the same conclusion independently! ``` db1091[commonsw... [14:28:05] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3354973 (10jcrespo) That is one hell of a query- however, we do not need to do it on SQL, w... [14:32:04] 10DBA, 10Patch-For-Review: Convert unique keys into primary keys for some wiki tables on s2 - https://phabricator.wikimedia.org/T166205#3355001 (10Marostegui) [14:34:19] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3355014 (10Anomie) What's the other option besides the union query we both came up with? [14:37:43] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3355030 (10jcrespo) > What's the other option besides the union query we both came up with?... [14:44:10] 10DBA, 10Scoring-platform-team, 10articlequality-modeling, 10artificial-intelligence: [Discuss] Hosting the monthly article quality dataset on labsDB - https://phabricator.wikimedia.org/T146718#3355072 (10Halfak) Again a connection failure. So I'm going to try splitting the file into parts and loading it... [15:02:34] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3355106 (10Anomie) I'm a little wary of making multiple queries and filtering the results i... [15:09:26] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 2 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3355134 (10jcrespo) I agree with what you say, one thing though: > What are the chances we... [15:17:57] 10DBA, 10Community-Tech, 10MediaWiki-User-management: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#3355174 (10jcrespo) > If the schema looks OK Looks ok to me, this won't be hopefully a high-rate request point. Can I delet... [15:23:36] 10DBA, 10Community-Tech, 10MediaWiki-User-management: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#3355186 (10MusikAnimal) >>! In T156318#3355174, @jcrespo wrote: > Looks ok to me, this won't be hopefully a high-rate reques... [15:28:59] 10DBA, 10Community-Tech, 10MediaWiki-User-management: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#3355205 (10jcrespo) Thanks, feel free to resolve this and we can continue being in contact (only if necessary) on the implem... [15:47:19] 10DBA, 10MediaWiki-User-management, 10Community-Tech-Sprint: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#3355227 (10MusikAnimal) [15:47:31] 10DBA, 10MediaWiki-User-management, 10Community-Tech-Sprint: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#2980871 (10MusikAnimal) 05Open>03Resolved [15:50:04] 10DBA, 10MediaWiki-User-management, 10Community-Tech-Sprint: Do test queries for range contributions to gauge performance of using different tables - https://phabricator.wikimedia.org/T156318#3355235 (10MusikAnimal) a:05kaldari>03MusikAnimal [15:54:41] 10DBA, 10Commons, 10MediaWiki-Special-pages, 10Wikimedia-General-or-Unknown, and 3 others: Special:ShortPages does not load in Wikimedia Commons: "Read timeout is reached" - https://phabricator.wikimedia.org/T168010#3355238 (10Aklapper) [19:17:44] 10DBA, 10MediaWiki-API, 10Patch-For-Review, 10Performance: Certain ApiQueryRecentChanges::run api query is too slow, slowing down dewiki - https://phabricator.wikimedia.org/T149077#2741216 (10Anomie) a:03Anomie [23:54:39] 10DBA, 10Collaboration-Team-Triage, 10Notifications, 10Schema-change: Review new Echo table - https://phabricator.wikimedia.org/T168107#3356374 (10MaxSem)