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

All MyISAM partitions opened, despite optimizer's partition pruning

    XMLWordPrintable

Details

    Description

      This is an upstream bug:

      http://bugs.mysql.com/bug.php?id=75753

      Description

      In certain cases, the optimizer can eliminate partitions from a query plan, based on conditions in the WHERE clause. This is called partition pruning:

      http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html

      However, even when partition pruning is in use, the MYI and MYD files of all MyISAM partitions in a given table are opened, even if some of them won't be read.

      This causes problems for users who have tables with a lot of MyISAM partitions. A simply query targeting one partition of a table with 100 partitions can easily create thousands of file descriptors if there are a few concurrent users.

      How to repeat:

      I'll use one of the example tables from the partition pruning documentation:

      CREATE TABLE t2 (
          fname VARCHAR(50) NOT NULL,
          lname VARCHAR(50) NOT NULL,
          region_code TINYINT UNSIGNED NOT NULL,
          dob DATE NOT NULL
      ) ENGINE=MyISAM
      PARTITION BY RANGE( YEAR(dob) ) (
          PARTITION d0 VALUES LESS THAN (1970) ENGINE=MyISAM,
          PARTITION d1 VALUES LESS THAN (1975) ENGINE=MyISAM,
          PARTITION d2 VALUES LESS THAN (1980) ENGINE=MyISAM,
          PARTITION d3 VALUES LESS THAN (1985) ENGINE=MyISAM,
          PARTITION d4 VALUES LESS THAN (1990) ENGINE=MyISAM,
          PARTITION d5 VALUES LESS THAN (2000) ENGINE=MyISAM,
          PARTITION d6 VALUES LESS THAN (2005) ENGINE=MyISAM,
          PARTITION d7 VALUES LESS THAN MAXVALUE ENGINE=MyISAM
      );

      If we insert a row:

      INSERT INTO t2 VALUES ('John', 'Doe', 1, '1982-06-23');

      Now let's SELECT from the table. We know partition pruning will be used in this particular query:

      mysql> EXPLAIN PARTITIONS SELECT * FROM t2 WHERE dob = '1982-06-23';
      +----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
      | id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | Extra |
      +----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
      |  1 | SIMPLE      | t2    | d3         | system | NULL          | NULL | NULL    | NULL |    1 |       |
      +----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
      1 row in set (0.00 sec)

      The optimizer says the results will only be in partition "d3", but let's see what files MySQL actually opens when the query is executed.

      Shell 1

      mysql> FLUSH TABLES;
      Query OK, 0 rows affected (0.00 sec)

      Shell 2

      [gmontee@localhost ~]$ sudo strace -p `pidof mysqld` -ff -o `pidof mysqld`.out -e trace=open
      Process 4295 attached with 17 threads

      Shell 1

      mysql> SELECT * FROM t2 WHERE dob = '1982-06-23';
      +-------+-------+-------------+------------+
      | fname | lname | region_code | dob        |
      +-------+-------+-------------+------------+
      | John  | Doe   |           1 | 1982-06-23 |
      +-------+-------+-------------+------------+
      1 row in set (0.00 sec)

      Shell 2

      ^CProcess 4295 detached
      Process 4297 detached
      Process 4298 detached
      Process 4299 detached
      Process 4300 detached
      Process 4301 detached
      Process 4302 detached
      Process 4303 detached
      Process 4304 detached
      Process 4305 detached
      Process 4306 detached
      Process 4308 detached
      Process 4309 detached
      Process 4310 detached
      Process 4311 detached
      Process 4316 detached
      Process 4319 detached
      [gmontee@localhost ~]$ cat 4295.out.4319
      open("./tmp/t2.frm", O_RDONLY)          = 16
      open("./tmp/t2.par", O_RDONLY)          = 17
      open("./tmp/t2.par", O_RDONLY)          = 16
      open("/var/lib/mysql/tmp/t2#P#d0.MYI", O_RDWR) = 16
      open("./tmp/t2#P#d0.MYD", O_RDWR)       = 17
      open("/var/lib/mysql/tmp/t2#P#d1.MYI", O_RDWR) = 18
      open("./tmp/t2#P#d1.MYD", O_RDWR)       = 19
      open("/var/lib/mysql/tmp/t2#P#d2.MYI", O_RDWR) = 20
      open("./tmp/t2#P#d2.MYD", O_RDWR)       = 21
      open("/var/lib/mysql/tmp/t2#P#d3.MYI", O_RDWR) = 22
      open("./tmp/t2#P#d3.MYD", O_RDWR)       = 23
      open("/var/lib/mysql/tmp/t2#P#d4.MYI", O_RDWR) = 24
      open("./tmp/t2#P#d4.MYD", O_RDWR)       = 25
      open("/var/lib/mysql/tmp/t2#P#d5.MYI", O_RDWR) = 26
      open("./tmp/t2#P#d5.MYD", O_RDWR)       = 27
      open("/var/lib/mysql/tmp/t2#P#d6.MYI", O_RDWR) = 28
      open("./tmp/t2#P#d6.MYD", O_RDWR)       = 29
      open("/var/lib/mysql/tmp/t2#P#d7.MYI", O_RDWR) = 30
      open("./tmp/t2#P#d7.MYD", O_RDWR)       = 31

      Suggested fix

      If partition pruning is in use, it is a waste to open partitions that will not be checked.

      Attachments

        Activity

          People

            Unassigned Unassigned
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.