Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27380

After minor upgrade to 10.5.13 queries with large IN clauses have performance issues

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.5.9, 10.5.13, 10.5, 10.6, 10.7(EOL)
    • 10.5.17, 10.6.9, 10.7.5, 10.8.4
    • Optimizer
    • None
    • RHEL 8

    Description

      Since upgrading from 10.2 to 10.5.13 queries using IN clauses with many values have performance issues.

      This is the same issue reported under MDEV-24117 whichi s fixed in 10.5.8. Even though it's fixed in 10.5.8, I can reproduce the same issue in 10.5.9 and 10.5.13 with the attached mariadbbug.sql in the MDEV-24117.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Thanks for the report!
            I could repeat the performance regression on 10.5-10.7 (0.1 in 10.4 vs 3.8 in 10.5), there is some problem with large IN (varchars)

            alice Alice Sherepa added a comment - Thanks for the report! I could repeat the performance regression on 10.5-10.7 (0.1 in 10.4 vs 3.8 in 10.5), there is some problem with large IN (varchars)

            caused by

            commit c36720388d5
            Author: Sergei Petrunia <psergey@askmonty.org>
            Date:   Thu Jan 28 21:43:55 2021 +0300
             
                MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ...
                
                (Variant #5, full patch, for 10.5)
                
                Do not produce SEL_ARG graphs that would yield huge numbers of ranges.
                Introduce a concept of SEL_ARG graph's "weight". If we are about to
                produce a graph whose "weight" exceeds the limit, remove the parts
                of SEL_ARG graph that represent the biggest key parts. Do so until
                the graph's is within the limit.
                
                Includes
                - debug code to verify SEL_ARG graph weight
                - A user-visible @@optimizer_max_sel_arg_weight to control the optimization
                - Logging the optimization into the optimizer trace.
            

            serg Sergei Golubchik added a comment - caused by commit c36720388d5 Author: Sergei Petrunia <psergey@askmonty.org> Date: Thu Jan 28 21:43:55 2021 +0300   MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ... (Variant #5, full patch, for 10.5) Do not produce SEL_ARG graphs that would yield huge numbers of ranges. Introduce a concept of SEL_ARG graph's "weight". If we are about to produce a graph whose "weight" exceeds the limit, remove the parts of SEL_ARG graph that represent the biggest key parts. Do so until the graph's is within the limit. Includes - debug code to verify SEL_ARG graph weight - A user-visible @@optimizer_max_sel_arg_weight to control the optimization - Logging the optimization into the optimizer trace.
            thilanka@orangehrm.us.com Thilanka added a comment -

            Can I get a rough idea, when will this get prioritized to fix?

            thilanka@orangehrm.us.com Thilanka added a comment - Can I get a rough idea, when will this get prioritized to fix?

            it's likely that it won't be fixed in 10.5.14, unfortunately

            serg Sergei Golubchik added a comment - it's likely that it won't be fixed in 10.5.14, unfortunately
            thilanka@orangehrm.us.com Thilanka added a comment -

            Thanks for the update.

            thilanka@orangehrm.us.com Thilanka added a comment - Thanks for the update.

            Testcase:

            CREATE TABLE IF NOT EXISTS `mariadb_bug` (
              `SubID` int(7) NOT NULL,
              `Token` bigint(20) unsigned NOT NULL,
              `Date` datetime NOT NULL DEFAULT current_timestamp(),
              `SteamID` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
              `IpAddress` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
              UNIQUE KEY `SubID` (`SubID`,`Token`,`SteamID`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
            

            SELECT `SubID`, `Token` 
            FROM `mariadb_bug` 
            WHERE `SubID` IN (<13800 constants in quotes>);
            

            There seem to be no multi-part SEL_ARG trees...

            psergei Sergei Petrunia added a comment - Testcase: CREATE TABLE IF NOT EXISTS `mariadb_bug` ( `SubID` int (7) NOT NULL , `Token` bigint (20) unsigned NOT NULL , ` Date ` datetime NOT NULL DEFAULT current_timestamp (), `SteamID` varchar (20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '' , `IpAddress` varchar (40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL , UNIQUE KEY `SubID` (`SubID`,`Token`,`SteamID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_bin; SELECT `SubID`, `Token` FROM `mariadb_bug` WHERE `SubID` IN (<13800 constants in quotes>); There seem to be no multi-part SEL_ARG trees...

            Fixed by fix for MDEV-25020.

            psergei Sergei Petrunia added a comment - Fixed by fix for MDEV-25020 .
            mreibert Mark Reibert added a comment - - edited

            Does this apply to, and was it fixed in, the 10.4 line? I ask because once upon a time I was running 10.4.14 and experienced OOM failures we traced to MDEV-24117. I am now running 10.4.22 and my OOMs have returned. Note I have been running 10.4.22 for some time, and the OOMs only recently returned. I do not know what confluence of events is triggering the memory increase now leading to the OOM, but since this is apparently a regression of MDEV-24117 I am wondering if it could be impacting 10.4.22.

            mreibert Mark Reibert added a comment - - edited Does this apply to, and was it fixed in, the 10.4 line? I ask because once upon a time I was running 10.4.14 and experienced OOM failures we traced to MDEV-24117 . I am now running 10.4.22 and my OOMs have returned. Note I have been running 10.4.22 for some time, and the OOMs only recently returned. I do not know what confluence of events is triggering the memory increase now leading to the OOM, but since this is apparently a regression of MDEV-24117 I am wondering if it could be impacting 10.4.22.

            People

              psergei Sergei Petrunia
              thilanka@orangehrm.us.com Thilanka
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.