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

ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows in the table

    XMLWordPrintable

    Details

      Description

      When EITS is enabled, ALTER TABLE ... ANALYZE PARTITION reads (and locks) all rows in the InnoDB table, even for a tiny partition. As a result, for huge tables it may work for hours and days and does not server the purpose. This does NOT happen without engine-independent persistent statistics enabled.

      Consider this table:

      MariaDB [test]> show create table tp\G
      *************************** 1. row ***************************
             Table: tp
      Create Table: CREATE TABLE `tp` (
        `id` int(11) DEFAULT NULL,
        `c1` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       PARTITION BY RANGE (`id`)
      (PARTITION `p0` VALUES LESS THAN (10) ENGINE = InnoDB,
       PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
      1 row in set (0,00 sec)
       
      MariaDB [test]> show table status like 'tp'\G
      *************************** 1. row ***************************
                 Name: tp
               Engine: InnoDB
              Version: 10
           Row_format: Dynamic
                 Rows: 9731209
       Avg_row_length: 32
          Data_length: 318619648
      Max_data_length: 0
         Index_length: 0
            Data_free: 5242880
       Auto_increment: NULL
          Create_time: 2020-01-10 17:45:50
          Update_time: 2020-01-14 10:39:57
           Check_time: NULL
            Collation: latin1_swedish_ci
             Checksum: NULL
       Create_options: partitioned
              Comment:
      1 row in set (0,02 sec)
      

      This is how long it takes to get statistics for the partition with just 9 rows:

      MariaDB [test]> set session use_stat_tables='preferably';
      Query OK, 0 rows affected (0,00 sec)
       
      MariaDB [test]> alter table tp analyze partition p0;
      +---------+---------+----------+-----------------------------------------+
      | Table   | Op      | Msg_type | Msg_text                                |
      +---------+---------+----------+-----------------------------------------+
      | test.tp | analyze | status   | Engine-independent statistics collected |
      | test.tp | analyze | status   | OK                                      |
      +---------+---------+----------+-----------------------------------------+
      2 rows in set (17,33 sec)
       
      MariaDB [test]> select count(*) from tp;
      +----------+
      | count(*) |
      +----------+
      | 10000000 |
      +----------+
      1 row in set (5,89 sec)
       
      MariaDB [test]> select count(*) from tp partition(p0);
      +----------+
      | count(*) |
      +----------+
      |        9 |
      +----------+
      1 row in set (0,00 sec)
      

      In the INNODB STATUS output taken when this query runs we see:

      ...
      ---TRANSACTION 421521529651464, ACTIVE 10 sec fetching rows
      mysql tables in use 2, locked 2
      12335 lock struct(s), heap size 2105552, 6374971 row lock(s)
      MySQL thread id 10, OS thread handle 140046871947008, query id 59 localhost root
      alter table tp analyze partition p0
      ...
      

      So, the statement that should access single partition with just 9 rows reads and locks all rows in the table. As a result it is totally useless, One can just run ANALYZE for the entire table with the same "costs".

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: