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

open_tables count is increasing with >=mariadb:10.1.36 to 10.4.10

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.1.36, 10.4.10, 10.2(EOL), 10.3(EOL)
    • N/A
    • Server
    • Docker hub image MariaDB 10.1.36 to 10.4.10
      RAM: 58 GB
      CPU(s): 8
      Model name: Intel(R) Xeon(R) Platinum 8167M CPU @ 2.00GHz
      CPU MHz:1995.312
      Number of Databases: 100

    Description

      When I am trying to upgrade >=10.1.36 to 10.4.10, following issue is coming.
      Count of open_tables increasing beyond 100K, if load increases it is going beyond 200K.
      Due to this queries execution becomes slow, number of slow queries are increasing and after some time mariaDB is unable to handle requests.

      This issue is not occurring with 10.1.35 and count of open_tables not crossing default limit(400),working perfectly with 10.1.35.

      Suspicious commit:https://github.com/MariaDB/server/commit/64a23c1c8a826a6f58f8a415f60a0e3cc0e0375f
      After commenting out the if-statement-block in sql/sql_base.cc from above commit issue is resolved and working properly.
      if-statement : if (table_list->table->file->referenced_by_foreign_key())

      Attachments

        Issue Links

          Activity

            Thanks for the report.

            MTR test case which demonstrates the problem is below.
            It is for debugging purposes only, do not put it into the regression suite.

            --source include/have_innodb.inc
             
            CREATE TABLE t0 (
              `id` int(10) unsigned NOT NULL,
              a int,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB;
             
            --let $num= 500
            while ($num)
            {
                --eval CREATE DATABASE db$num
                --eval USE db$num
                CREATE TABLE t (
                    x int(10) unsigned NOT NULL,
                    CONSTRAINT fk FOREIGN KEY (x) REFERENCES test.t0 (id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
                --dec $num
            }
             
            --let $num= 32
            while ($num)
            {
                --connect (con$num,localhost,root,,)
                --delimiter $
                --send
                    LOOP
                        UPDATE test.t0 SET a = 1 WHERE id = 1;
                    END LOOP$
                --delimiter ;
                --dec $num
            }
             
            --connection default
            show variables like 'table_open_cache';
            --let $num= 10
            while ($num)
            {
                SHOW GLOBAL STATUS LIKE 'open_tables';
                --sleep 1
                --dec $num
            }
            

            10.1.36

            show variables like 'table_open_cache';
            Variable_name	Value
            table_open_cache	421
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	5868
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	7899
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	7442
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	8460
            ...
            

            10.1.35

            show variables like 'table_open_cache';
            Variable_name	Value
            table_open_cache	421
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	52
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	54
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	54
            SHOW GLOBAL STATUS LIKE 'open_tables';
            Variable_name	Value
            Open_tables	54
            ...
            

            elenst Elena Stepanova added a comment - Thanks for the report. MTR test case which demonstrates the problem is below. It is for debugging purposes only, do not put it into the regression suite . --source include/have_innodb.inc   CREATE TABLE t0 ( `id` int (10) unsigned NOT NULL , a int , PRIMARY KEY (`id`) ) ENGINE=InnoDB;   --let $num= 500 while ($num) { --eval CREATE DATABASE db$num --eval USE db$num CREATE TABLE t ( x int (10) unsigned NOT NULL , CONSTRAINT fk FOREIGN KEY (x) REFERENCES test.t0 (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --dec $num }   --let $num= 32 while ($num) { --connect (con$num,localhost,root,,) --delimiter $ --send LOOP UPDATE test.t0 SET a = 1 WHERE id = 1; END LOOP$ --delimiter ; --dec $num }   --connection default show variables like 'table_open_cache' ; --let $num= 10 while ($num) { SHOW GLOBAL STATUS LIKE 'open_tables' ; --sleep 1 --dec $num } 10.1.36 show variables like 'table_open_cache'; Variable_name Value table_open_cache 421 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 5868 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 7899 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 7442 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 8460 ... 10.1.35 show variables like 'table_open_cache'; Variable_name Value table_open_cache 421 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 52 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 54 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 54 SHOW GLOBAL STATUS LIKE 'open_tables'; Variable_name Value Open_tables 54 ...

            Please, see MDEV-22180 and the discussion therein

            serg Sergei Golubchik added a comment - Please, see MDEV-22180 and the discussion therein

            People

              serg Sergei Golubchik
              umagmrit UmaShankar Avagadda
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.