search

PennyDreadfulMTG / perf-reports

2 stars 7 forks source link

Exceeded slow_query limit (117.4 > 60.0) in mysql: ``` #62858

Open vorpal-buildbot opened 7 months ago

vorpal-buildbot commented 7 months ago 
    SELECT

    d.id,
    d.finish,
    d.decklist_hash,
    cache.active_date,
    cache.wins,
    cache.losses,
    cache.draws,
    cache.color_sort,
    ct.name AS competition_type_name

    FROM
        deck AS d

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN
        deck_cache AS cache ON d.id = cache.deck_id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Yorion, Sky Nomad', 'Sword of Light and Shadow', 'Jace, Architect of Thought', 'Magistrate''s Scepter', 'Fae of Wishes', 'Force of Rage', 'Guardian of Ghirapur', 'Loxodon Smiter', 'Hordeling Outburst', 'Zurgo Bellstriker', 'Greater Gargadon', 'Out of Time', 'Joint Exploration', 'Veinfire Borderpost', 'Isamaru, Hound of Konda', 'Trostani, Selesnya''s Voice', 'Obelisk of Urd', 'Sunpetal Grove', 'Coalition Relic', 'Sweltering Suns', 'Goro-Goro, Disciple of Ryusei', 'Circuit Mender', 'Ib Halfheart, Goblin Tactician', 'Damn', 'Palinchron', 'Lurrus of the Dream-Den', 'Clay-Fired Bricks', 'Ranger of Eos', 'Touch the Spirit Realm', 'Vraan, Executioner Thane', 'Benevolent Bodyguard', 'Temple of Mystery', 'Radiant Grove', 'Mistvein Borderpost', 'Banehound', 'Graven Cairns', 'Glacial Fortress', 'Wildfire', 'Erebos''s Intervention', 'Fervent Champion', 'Candy Grapple', 'Karn''s Bastion', 'Hinterland Harbor', 'Astral Cornucopia', 'Reality Heist', 'Mana Leak', 'Caves of Koilos', 'Drowner of Hope', 'Student of Warfare', 'Venser, Shaper Savant', 'Everflowing Chalice', 'Battlefield Forge', 'Spring-Loaded Sawblades', 'Devour Flesh', 'Champion of the Parish', 'Fetid Pools', 'Secluded Courtyard', 'Glimpse the Core', 'Blood Baron of Vizkopa', 'Drizzt Do''Urden', 'Spirited Companion', 'Restoration Angel', 'Languish', 'Flicker of Fate', 'Firewild Borderpost', 'The Antiquities War', 'Clifftop Retreat', 'Dictate of Karametra', 'Candy Trail', 'Vampire Nocturnus', 'Ondu Inversion', 'Cultivate', 'Hagra Mauling', 'Surge Node', 'Yavimaya Coast', 'Sunlance', 'Emancipation Angel', 'King Darien XLVIII', 'Pashalik Mons', 'Rally the Ranks', 'Soldier of the Pantheon', 'Confounding Riddle', 'Prophetic Prism', 'Wrath of God', 'Primal Might', 'Nimbus Maze', 'Nullpriest of Oblivion', 'Mephitic Draught', 'As Foretold', 'Oliphaunt', 'Selesnya Charm', 'Vindicate', 'Hero of Precinct One', 'Temple of Deceit', 'Horned Loch-Whale', 'Lochmere Serpent', 'Mouth // Feed', 'Deafening Clarion', 'Goblin Charbelcher', 'Necropotence', 'Valentin, Dean of the Vein', 'Metalwork Colossus', 'Dragon Fodder', 'Temple of Silence', 'Executioner''s Capsule', 'Yahenni, Undying Partisan', 'Agonizing Remorse', 'Bloodhusk Ritualist', 'Lashwrithe', 'Eldrazi Displacer', 'Shigeki, Jukai Visionary', 'Restore Balance', 'Thief of Sanity', 'Tithing Blade', 'Gatekeeper of Malakir', 'Impulse', 'Naturalize', 'Krenko, Tin Street Kingpin', 'Timesifter', 'Benalish Marshal', 'Wilt-Leaf Liege', 'Memory Lapse', 'Crawling Barrens', 'Ratchet Bomb', 'Sphere of the Suns', 'Oath of Kaya', 'The Irencrag', 'Stirring Wildwood', 'Academy Ruins', 'Torens, Fist of the Angels', 'Sword of Body and Mind', 'Brass''s Tunnel-Grinder', 'Makindi Stampede', 'Idyllic Beachfront', 'Staunch Crewmate', 'Throne of Geth', 'Drowned Catacomb', 'Realm Razer', 'Renegade Map', 'Colossal Skyturtle', 'Isolated Chapel', 'Thoughts of Ruin', 'Krenko, Mob Boss', 'Shadows'' Verdict', 'Graypelt Refuge', 'Tyrant of Discord', 'Decadent Dragon', 'Mass Hysteria', 'Sea Gate Wreckage', 'Blue Sun''s Zenith', 'Shock', 'Deep-Cavern Bat', 'Tooth and Nail', 'Akal Pakal, First Among Equals', 'Acclaimed Contender', 'River of Tears', 'Bronzehide Lion', 'Fleecemane Lion', 'Fieldmist Borderpost', 'Shambling Vent', 'Kaya, Ghost Assassin', 'Verdant Mastery', 'Cut Down', 'Arixmethes, Slumbering Isle', 'Demonic Bargain', 'Corridor Monitor', 'Vampire Nighthawk', 'Electrodominance', 'Zof Consumption', 'Fortified Village', 'Silent Arbiter', 'Eldrazi Temple', 'Far // Away'))) AND (TRUE)
    GROUP BY 
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.

    HAVING
        TRUE
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 117.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 06044623423d2e3b53b4912d6c15c5fda59366a1

vorpal-buildbot commented 6 months ago

Exceeded slow_query limit (61.7 > 60.0) in mysql: ```

    SELECT

    d.id,
    d.finish,
    d.decklist_hash,
    cache.active_date,
    cache.wins,
    cache.losses,
    cache.draws,
    cache.color_sort,
    ct.name AS competition_type_name

    FROM
        deck AS d

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN
        deck_cache AS cache ON d.id = cache.deck_id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Pashalik Mons', 'Eater of Virtue', 'Confounding Riddle', 'Secluded Courtyard', 'Altar of Dementia', 'Hada Freeblade', 'Force of Rage', 'Caves of Koilos', 'Seal of Cleansing', 'Kjeldoran Outpost', 'Rally the Ranks', 'Yahenni, Undying Partisan', 'Decadent Dragon', 'Doomed Traveler', 'Kumano Faces Kakkazan', 'Riptide Laboratory', 'Sleeper Dart', 'Flourishing Fox', 'Skirge Familiar', 'Deafening Clarion', 'Semblance Anvil', 'Grave Titan', 'Nimbus Maze', 'Vivid Marsh', 'Thalia''s Lancers', 'Electrostatic Infantry', 'Spyglass Siren', 'Cruel Ultimatum', 'Siege Rhino', 'Migloz, Maze Crusher', 'Meria, Scholar of Antiquity', 'Wandering Fumarole', 'Dragon Egg', 'Gavony Township', 'Tocatli Honor Guard', 'Fires of Invention', 'Sulfur Falls', 'Thassa''s Intervention', 'Scroll of Griselbrand', 'Goblin Chieftain', 'Eye of Vecna', 'Cut Down', 'Shinka, the Bloodsoaked Keep', 'Smoldering Marsh', 'Benalish Marshal', 'Banehound', 'Yarok''s Fenlurker', 'Alchemist''s Vial', 'Timeless Dragon', 'Metalspinner''s Puzzleknot', 'Sheltered Thicket', 'Phoenix Chick', 'Nullpriest of Oblivion', 'Starving Revenant', 'Game Trail', 'Search for Azcanta', 'Canyon Slough', 'Horrifying Revelation', 'Eiganjo Castle', 'Virus Beetle', 'Tibalt''s Trickery', 'Massacre Wurm', 'Rites of Initiation', 'Noxious Gearhulk', 'Kemba, Kha Enduring', 'Choked Estuary', 'Kari Zev, Skyship Raider', 'Sunpetal Grove', 'Honor of the Pure', 'Drannith Stinger', 'Cheeky House-Mouse', 'The Shire', 'Borborygmos Enraged', 'Monstrous Rage', 'Gifts Ungiven', 'Llanowar Wastes', 'Reality Smasher', 'Drowned Catacomb', 'Faerie Dreamthief', 'Birds of Paradise', 'Heartless Summoning', 'Hatching Plans', 'Crumbling Vestige', 'Callous Sell-Sword', 'Edgewall Inn', 'Candy Trail', 'Circuit Mender', 'Murmuring Bosk', 'Silent Arbiter', 'Impulse', 'Mortarpod', 'Nine Lives', 'Iname as One', 'Farsight Ritual', 'Rootbound Crag', 'Hour of Devastation', 'Renegade Tactics', 'Shizo, Death''s Storehouse', 'Mogg War Marshal', 'Rugged Prairie', 'Brave the Elements', 'Jor Kadeen, First Goldwarden', 'Anointer Priest', 'Crystal Grotto', 'Laboratory Maniac', 'Nicol Bolas, Planeswalker', 'Sea-Dasher Octopus', 'Drannith Healer', 'Horror of the Broken Lands', 'Dark Intimations', 'Bad River', 'Draconic Roar', 'Rocky Tar Pit', 'Mogg Hollows', 'Tyrant''s Scorn', 'Faith''s Shield', 'Bringer of the Last Gift', 'Spell Pierce', 'Greater Auramancy', 'Tainted Remedy', 'Authority of the Consuls', 'Frantic Search', 'Isamaru, Hound of Konda', 'Fervent Champion', 'Lavaclaw Reaches', 'Despise', 'Enchantress''s Presence', 'Faith''s Reward', 'Deep-Cavern Bat', 'Nivmagus Elemental', 'Akroan Crusader', 'Imposing Vantasaur', 'Hope of Ghirapur', 'Boros Elite', 'Fetid Pools', 'Temple of Silence', 'Chainer, Nightmare Adept', 'Valiant Veteran', 'Worship', 'Elspeth''s Nightmare', 'Gallia of the Endless Dance', 'River of Tears', 'Hidden Cataract', 'Smuggler''s Copter', 'Scrap Trawler', 'Stormblood Berserker', 'Rabbit Battery', 'Matter Reshaper', 'Monstrous Carabid', 'Guardian of Ghirapur', 'Anger of the Gods', 'Quick Study', 'Soldier of the Pantheon', 'Trace of Abundance', 'Eldrazi Skyspawner', 'Primal Surge', 'Touch the Spirit Realm', 'Helping Hand', 'Nicol Bolas, God-Pharaoh', 'Promising Vein', 'Hokori, Dust Drinker', 'Cry of Contrition', 'Sleight of Hand', 'Stormbreath Dragon', 'Rampaging Raptor', 'Out of Time', 'Wasteland Strangler', 'Treacherous Blessing', 'Restless Bivouac', 'Roar of Jukai', 'Thunderbreak Regent', 'Isolated Chapel', 'Elvish Mystic', 'Hoarding Broodlord', 'Prophetic Prism', 'Forgotten Monument', 'Mastermind''s Acquisition', 'Nimble Obstructionist', 'Jinnie Fay, Jetmir''s Second', 'See the Truth', 'Dance of the Manse', 'Spring-Loaded Sawblades', 'Firja''s Retribution', 'Scattered Groves', 'Krenko, Tin Street Kingpin', 'Ultimate Price', 'Nourishing Shoal', 'Graven Cairns', 'Lizard Blades', 'Minas Tirith', 'Errant and Giada', 'Sickening Shoal', 'Moria Marauder', 'Flood Plain', 'Battlefield Forge', 'Eldrazi Mimic', 'Leonin Lightscribe', 'Phyrexian Unlife', 'Harried Spearguard', 'Student of Warfare', 'Nezumi Shortfang', 'Dragonskull Summit', 'Venser, Shaper Savant', 'Kazandu Blademaster', 'Kolaghan, the Storm''s Fury', 'Decree of Justice', 'Dreadhorde Invasion', 'Lightning Rift', 'Hidden Necropolis', 'Magister of Worth', 'Wrath of God', 'Consume the Meek', 'Clifftop Retreat', 'Ashen Rider', 'Goblin Warchief', 'Phyrexian Missionary', 'Mirrorshell Crab', 'Captain''s Claws', 'Kathari Remnant', 'Tithing Blade', 'Vampire Nighthawk', 'Metalwork Colossus', 'Restoration Angel', 'Dire Flail', 'Kaleidostone', 'Seasons Past', 'Slumbering Dragon', 'Parallel Lives', 'Dig Up', 'Frodo, Sauron''s Bane', 'Market Gnome', 'Burst Lightning', 'Mana Leak', 'Clay-Fired Bricks', 'Agonizing Remorse', 'Legion Warboss', 'Necropotence', 'Dimir Machinations', 'Crowd''s Favor', 'Frenzied Goblin', 'Reckless Charge', 'Dragon''s Hoard', 'Kessig Wolf Run', 'Hagra Mauling', 'Oblivion Ring', 'Shambling Vent', 'Resolute Reinforcements', 'Elspeth Conquers Death', 'Dragon Tempest', 'Priest of Fell Rites', 'Progenitor Exarch', 'Ice Out', 'Executioner''s Capsule', 'Contested War Zone', 'Secluded Steppe', 'Bag of Holding', 'Baird, Argivian Recruiter', 'Professional Face-Breaker', 'Glacial Fortress', 'Crypt of Agadeem', 'Birthday Escape', 'Sanctum of Ugin', 'Izzet Boilerworks', 'Cabal Therapist', 'Pull from Tomorrow', 'Mindslaver', 'Hanweir Battlements', 'Dread Presence', 'Thragtusk', 'Charming Prince', 'Light of Hope', 'Blade Splicer', 'Molten Tributary', 'Inverter of Truth', 'Celestial Colonnade', 'Lake of the Dead', 'Torch Courier', 'Erebos''s Intervention', 'Contaminated Aquifer', 'Fierce Empath', 'Swans of Bryn Argoll', 'Elder Deep-Fiend', 'Lashwrithe', 'Yorion, Sky Nomad', 'Zenith Flare', 'Ranger of Eos', 'Sylvan Library', 'Phoenix of Ash', 'Eldrazi Temple', 'Painful Truths', 'Vindicate', 'Lurching Rotbeast', 'Flowering of the White Tree', 'Gyruda, Doom of Depths', 'Languish', 'Arch of Orazca', 'Cavernous Maw', 'Syncopate', 'Demonic Bargain', 'Stromkirk Noble', 'Deathrite Shaman', 'Ash, Party Crasher', 'Haazda Marshal', 'Thousand-Faced Shadow', 'Volatile Fault', 'Sacred Peaks', 'Zur the Enchanter', 'Detention Sphere', 'Invasion of Ixalan', 'Scavenging Ooze', 'Asmodeus the Archfiend', 'Bomat Courier', 'Precinct Captain', 'Valiant Rescuer', 'Fallen Shinobi', 'Temple of Enlightenment', 'Westvale Abbey', 'Moment of Craving', 'Mogg Fanatic', 'The Cruelty of Gix', 'Vraan, Executioner Thane', 'Sword of Light and Shadow', 'Spirited Companion', 'Soul-Guide Lantern', 'The Raven''s Warning', 'Twinshot Sniper', 'Skirk Prospector', 'Shivan Reef', 'Dreadbore', 'Kaya, Ghost Assassin', 'Words of Wilding', 'Doom Foretold', 'Slaughter Pact', 'Phalanx Leader', 'Vault of the Archangel', 'Valentin, Dean of the Vein', 'Ratchet Bomb', 'Earthshaker Khenra', 'Blazing Archon', 'Autochthon Wurm', 'Champion of the Parish', 'Samwise Gamgee', 'Salvage Titan', 'Call of the Ring', 'Moon-Circuit Hacker', 'Oath of Kaya', 'Sweltering Suns', 'Oliphaunt', 'Spawning Pool', 'Specter''s Shriek', 'Declaration in Stone', 'Candy Grapple', 'Goblin Piledriver', 'Goddric, Cloaked Reveler', 'Bloodhusk Ritualist', 'Viashino Pyromancer', 'Ulvenwald Oddity', 'Memory Lapse', 'Gatekeeper of Malakir', 'Canopy Vista', 'Commune with Spirits', 'Horned Loch-Whale', 'Spark Spray', 'Archangel Avacyn', 'Brain in a Jar', 'Saheeli''s Lattice', 'Skullcap Snail', 'Anointed Procession', 'Archfiend of Ifnir', 'Torch the Tower', 'Moorland Haunt', 'Solemn Simulacrum', 'Eldrazi Displacer', 'Mesmeric Fiend', 'Gideon Jura', 'Divine Visitation', 'Hidden Volcano', 'Woodland Cemetery', 'Fortified Village', 'Stirring Wildwood', 'Necrotic Ooze', 'Gideon''s Lawkeeper', 'Zurgo Bellstriker', 'Merry, Esquire of Rohan', 'Azorius Charm', 'Damn', 'Crux of Fate', 'Lurrus of the Dream-Den', 'Jukai Naturalist', 'Incinerate', 'Second Sunrise', 'Oversold Cemetery', 'Benevolent Bodyguard', 'Footfall Crater', 'Greater Gargadon', 'Deathcap Marionette', 'Hopeless Nightmare', 'Chain of Plasma', 'Garruk Relentless', 'Curse of Silence', 'Growing Ranks', 'Idyllic Tutor', 'Ground Rift', 'Solemnity'))) AND (TRUE)
    GROUP BY 
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.

    HAVING
        TRUE
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 61.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 06044623423d2e3b53b4912d6c15c5fda59366a1

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (114.4 > 60.0) in mysql: ```

    SELECT

    d.id,
    d.finish,
    d.decklist_hash,
    cache.active_date,
    cache.wins,
    cache.losses,
    cache.draws,
    cache.color_sort,
    ct.name AS competition_type_name

    FROM
        deck AS d

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN
        deck_cache AS cache ON d.id = cache.deck_id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('The Eternal Wanderer', 'Priest of Fell Rites', 'Evolving Wilds', 'Siege Veteran', 'Fetid Pools', 'Goblin Dark-Dwellers', 'Defiler of Vigor', 'Mana Leak', 'Fetid Heath', 'Tainted Indulgence', 'Burst Lightning', 'Condemn', 'Escape Tunnel', 'Leatherback Baloth', 'Iona, Shield of Emeria', 'Fire Prophecy', 'Brave the Elements', 'Deduce', 'Repeal', 'Tireless Tracker', 'Kher Keep', 'Soldier of the Pantheon', 'Cloudgoat Ranger', 'Devouring Sugarmaw', 'Furnace Host Charger', 'Magister of Worth', 'Precinct Captain', 'Urabrask the Hidden', 'Abrade', 'Elite Vanguard', 'Aurelia, the Warleader', 'Thalia''s Lieutenant', 'Mystic Gate', 'Timeless Dragon', 'Sheltered Thicket', 'Liliana, Death''s Majesty', 'Courser of Kruphix', 'Isolated Chapel', 'Mistcutter Hydra', 'Kessig Wolf Run', 'Vindicate', 'Glacial Fortress', 'Sea Gate Oracle', 'Electrolyze', 'Polukranos, World Eater', 'Nevinyrral''s Disk', 'Hurricane', 'Shambling Vent', 'Surrak, the Hunt Caller', 'Kiki-Jiki, Mirror Breaker', 'Sulfur Falls', 'Crackling Drake', 'Kaya''s Guile', 'Titania, Protector of Argoth', 'Boromir, Warden of the Tower', 'Tribute to the World Tree', 'Spell Pierce', 'Power Sink', 'Lonely Sandbar', 'Secluded Steppe', 'Elvish Mystic', 'Declaration in Stone', 'Bow of Nylea', 'Sylvan Library', 'Thragtusk', 'Goreclaw, Terror of Qal Sisma', 'Drowned Catacomb', 'Phyrexian Arena', 'Caves of Koilos', 'Xenagos, the Reveler', 'Oath of Kaya', 'Flame-Kin Zealot', 'Boros Elite', 'Glimpse of Tomorrow', 'Shivan Reef', 'Etali''s Favor', 'Geological Appraiser', 'Bloodbraid Elf', 'Venser, Shaper Savant', 'Mindslaver', 'Rofellos, Llanowar Emissary', 'Mazemind Tome', 'Sweltering Suns', 'Ancestral Vision', 'Kaya, Orzhov Usurper', 'Malcolm, Alluring Scoundrel', 'Yorvo, Lord of Garenbrig', 'Academy Ruins', 'Steel Leaf Champion', 'Kaya, Ghost Assassin', 'Cloudpost', 'Grave Titan', 'Pestermite', 'Wrath of God', 'Karplusan Forest', 'Inscription of Abundance', 'Platinum Angel', 'Gifts Ungiven', 'Memory Lapse', 'Sorin, Grim Nemesis', 'Valiant Veteran', 'Birds of Paradise', 'Nylea, God of the Hunt', 'Opt', 'Bitter Triumph', 'Champion of the Parish', 'Doom Blade', 'Cascade Bluffs', 'Glimmerpost', 'Horned Loch-Whale', 'Battlefield Forge', 'Dig Through Time', 'Day of Judgment', 'Vivien Reid', 'Clifftop Retreat', 'Thalia''s Lancers', 'Sauron''s Ransom', 'Orcish Lumberjack', 'Huntmaster of the Fells', 'Chancellor of the Forge', 'Polukranos Reborn'))) AND (TRUE)
    GROUP BY 
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.

    HAVING
        TRUE
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 114.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 06044623423d2e3b53b4912d6c15c5fda59366a1

Labels: decksite