Page MenuHomePhabricator
Create Task
Maniphest T195836

Cannot reliably get the EXPLAIN for a query on analytics wiki replica cluster
Closed, DeclinedPublic
Actions

Description

Quarry uses the SHOW EXPLAIN FOR <connection id> method to get the EXPLAINs of queries (ping T50875 T146483)

This method requires the SHOW EXPLAIN FOR being run on the very same host as the one the query is executed, but the analytics cluster is two hosts with a proxy at the front doing round-robin, without a way to force a routing. Therefore SHOW EXPLAIN FOR on a second connection is not very reliable.

What I would like is a reliable method that, given a certain SQL SELECT statement (or preferably allow multiple statements as well, since Quarry allows that), it returns the typical table that EXPLAIN SELECT ... gives.

Related Objects

Event Timeline

zhuyifei1999 created this task.May 29 2018, 12:58 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 29 2018, 12:58 PM
MusikAnimal subscribed.May 29 2018, 5:23 PM
jcrespo triaged this task as Low priority.Jun 15 2018, 11:46 AM
jcrespo moved this task from Triage to Backlog on the DBA board.

So the workaround for now is to make sure one is connected to the same server by doing:

SELECT @@GLOBAL.hostname;

This is not an ideal scenario, but it is the only option until we can setup an alternative method that doesn't threaten the High Availability and automatic load balancing architecture. I am open for suggestions, but I don't see an easy option right now.

• Vvjjkkii renamed this task from Cannot reliably get the EXPLAIN for a query on analytics wiki replica cluster to f3baaaaaaa.Jul 1 2018, 1:07 AM
• Vvjjkkii raised the priority of this task from Low to High.
• Vvjjkkii added projects: CheckUser, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), Tamil-Sites, Gamepress, Hashtags, Jade, KartoEditor, Language-2018-Apr-June, New-Editor-Experiences, Mail, TCB-Team (now WMDE-TechWish).
• Vvjjkkii updated the task description. (Show Details)
• Vvjjkkii removed a subscriber: Aklapper.
zhuyifei1999 mentioned this in T196525: Allow /query/new to accept sql parameter.Jul 2 2018, 12:18 AM
Marostegui renamed this task from f3baaaaaaa to Cannot reliably get the EXPLAIN for a query on analytics wiki replica cluster.Jul 2 2018, 5:18 AM
Marostegui lowered the priority of this task from High to Low.
Marostegui removed projects: TCB-Team (now WMDE-TechWish), Mail, New-Editor-Experiences, Language-2018-Apr-June, KartoEditor, Jade, Hashtags, Gamepress, Tamil-Sites, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), CheckUser.
Marostegui updated the task description. (Show Details)
Marostegui closed this task as Declined.Sep 18 2018, 6:34 AM
Marostegui subscribed.

I am going to close this as there is nothing else we can do at the moment as per T195836#4291244
If someone feels this needs to remain open, please reopen.

MusikAnimal added a comment.Sep 18 2018, 6:13 PM

Also note https://tools.wmflabs.org/sql-optimizer is a thing, which uses some hacky workarounds. Some queries still can't be explained, though, such as those with UNIONs.

Framawiki mentioned this in T205214: EXPLAIN is broken because new analytics wiki replica cluster contains multiple servers.Sep 23 2018, 10:15 AM
AntiCompositeNumber mentioned this in T265430: SHOW EXPLAIN FOR via Quarry or sql-optimizer fails often due to load balancing.Oct 14 2020, 12:48 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits