search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (60.7 > 60.0) in mysql: ``` #63557

Open vorpal-buildbot opened 3 months ago

vorpal-buildbot commented 3 months ago 
        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 60.7, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa8088e39663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```
vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 65.0, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Cf-Ray: 8b4aa818f8a3663f X-Forwarded-For: 37.114.90.118 Host: pennydreadfulmagic.com Cf-Connecting-Ip: 37.114.90.118 Accept-Encoding: gzip, br Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 X-Forwarded-Proto: https Cdn-Loop: cloudflare Priority: u=0, i Cf-Visitor: {"scheme":"https"} Cf-Ipcountry: NL Referer: https://pennydreadfulmagic.com/admin/archetypes/ Sec-Fetch-Dest: document Sec-Fetch-User: ?1 Sec-Fetch-Mode: navigate Connection: Keep-Alive Sec-Fetch-Site: same-origin Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 65.8, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa8205f93663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 67.5, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa8233a2c663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 67.8, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa8483ac4663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 67.4, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa84e498f663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 67.3, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa84f8b0c663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 62.4, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa851bdd9663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 3 months ago

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

        SELECT
            deck_id,
            GROUP_CONCAT(rule_id) AS rule_ids,
            GROUP_CONCAT(archetype_id) AS archetype_ids,
            GROUP_CONCAT(archetype_name) AS archetype_names
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            inclusions.sideboard = deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (248076, 248073) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) >= 1
    ```

[] (slow_query, 62.5, mysql)

Reported on decksite by mysql-perf

Location Hash: cb0f9808607fd376c62b7a49896dcabdf02f8567

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '247887'), ('session', '.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0'), ('views', '1375')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '248075', 'archetype_id': '41'} Content-Type: application/x-www-form-urlencoded Content-Length: 120 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip, br X-Forwarded-For: 37.114.90.118 Cf-Ray: 8b4aa850acae663f-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not)A;Brand";v="99", "Brave";v="127", "Chromium";v="127" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8 Sec-Gpc: 1 Accept-Language: en-US,en;q=0.8 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Priority: u=0, i Cookie: hide_intro=True; page_size=20; deck_id=247887; session=.eJx1UV1TwjAQ_C95rkzTQCG8UUVFGPEDgdFxOrE9SjBNMEmhheG_m-IgD45vudu93bvNHsVr0DmTIC3qWl2Ah1iac4m6CyaMq1LIea7SE5hykyidxtx1EGkSEmIctEJKO5iQdojODKESJsCxQF7cRA74f4TLOCu4-DURKssgrTczSh6tOiGmHsptpuLCgJYsr5VdvWASU1qLKFbYZRAby2yN3T-Xl8MPcTe7XrJd-TDEu2AUvfafeoNma_t15lv1Ce7aPWJJAsacarTtt2m1gdEcV6qVTKNkAtPbUWt2NeaT5djNQ7nmGkzMXHC4HZCA4tBvNwhtYhKc4TrK0G92fN9DGhautfz1yHuM2V4ldgN_MydlGhXrl5CnVf5IpyvuPFySa3fMGzrGY1zn59FYKadbR-r-jS8q9O6ho2hsq3oARcA0aHTw0J8QLdMZuJ1lIcThG07rpVU.ZsDACA.Q0pVyBEJWKmlWMx6yXRwSgFVIa0; views=1375 Cf-Connecting-Ip: 37.114.90.118 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite