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

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (65.4 > 60.0) in mysql: ``` #62865

Open vorpal-buildbot opened 10 months ago

vorpal-buildbot commented 10 months ago

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438ef66bef91c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

vorpal-buildbot commented 10 months ago

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

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438ef76dd4a1c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 10 months ago

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

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438ef7c9a3a1c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 10 months ago

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

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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, 68.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438ef783ed01c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 10 months ago

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

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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, 68.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438ef7dbb491c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite

vorpal-buildbot commented 10 months ago

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

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        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
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (233400, 233393) 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: 8e6d427b2aa9af85b7921657b9cfab56c2756428

Request Data

``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '233394'), ('session', '.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40'), ('views', '192')]) Endpoint: post_archetypes View Args: {} Person: 343361125699813376 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '233402', 'archetype_id': '512'} Content-Type: application/x-www-form-urlencoded Content-Length: 184 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 178.85.111.123 Cf-Ray: 8438efb73edd1c94-AMS X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Not_A Brand";v="8", "Chromium";v="120", "Brave";v="120" 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/120.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.5 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Cookie: hide_intro=True; page_size=20; deck_id=233394; session=.eJx1UdFOwjAU_Zc-T9KtY6y8CRrUBKMYYGrMUra7Ura10HZBQvh3uxngwfjWc8-599x7ekTpFnTNJEiLhlY34CGW10KiYcEq41AOtahVfiZzYTKl81S4CiIhIZHvB_2I0tgnZBChq6JSGavAqUDeTEaO-L9FyJQ3orqYVIpzyNvNjJKdVRz51EO15SptDGjJ6naywwWTPqXtEMUauw5SY5ltuWA5fdrFi-1dffuuCUn4nD6zaSI5T0Dgq96qEty1R8SyDIw5Y_RgF-NSzfdveLZnmxV9oXix4cs5nswex77rh--t0GBS5oLzB7jfJ8HAj3ohDeIwuNJtlBEOY4w9pKFwpfXFQ4Ulvf8oQ7KWCi93h2mZlK8xTfRgNVZr5-GS3LpjPlEXj-lCdD8lioN7_tZ6G-UsvjzUDU3toW1AI2AaNDp56E-IlmkObmfZVNXpB7OLpEY.ZZ8xyA.JS_tct5n14_srBzn3HgSbVBNG40; views=192 Cf-Connecting-Ip: 178.85.111.123 Cdn-Loop: cloudflare Cf-Ipcountry: NL ```

Labels: decksite