Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7076

Modify to not update Cardinality after setting tokudb_cardinality_scale_percent

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.28-31
    • Fix Version/s: 5.7.31-34, 8.0.21-12
    • Component/s: MyRocks, TokuDB
    • Labels:
      None

      Description

      Similar to https://jira.percona.com/browse/PS-4297, index cardinality is equal for all columns. In this case the table is partitioned and last partition is empty and using TokuDB.

       

      How to repeat:

       

      CREATE TABLE `tt` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a` int(11) DEFAULT NULL,
       `b` int(11) DEFAULT NULL,
       `c` varchar(10) DEFAULT NULL,
       `d` date NOT NULL,
       PRIMARY KEY (`id`,`d`),
       KEY `a` (`a`),
       KEY `b` (`b`),
       KEY `c` (`c`)
      ) ENGINE=TokuDB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (to_days(d))
      (PARTITION p20200427 VALUES LESS THAN (737908) ENGINE = TokuDB,
       PARTITION p20200428 VALUES LESS THAN (737909) ENGINE = TokuDB,
       PARTITION p20200429 VALUES LESS THAN (737910) ENGINE = TokuDB) */
      Populate with some data:
      insert into tt values(null, rand()*1000, rand()*10000, "hello", "2020-04-28");
      repeat until we have say 2049 rows:
      insert into tt select null,rand()*1000, rand()*10000, "hello", "2020-04-28" from tt;
       
      

      This has inserted 2049 rows to the middle partition (p20200428). Now index statistics are wrong:

       

      mysql [localhost:5728] {msandbox} (test) > show keys from tt;
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | tt    |          0 | PRIMARY  |            1 | id          | A         |        2050 |     NULL | NULL   |      | BTREE      |         |               |
      | tt    |          0 | PRIMARY  |            2 | d           | A         |        2050 |     NULL | NULL   |      | BTREE      |         |               |
      | tt    |          1 | a        |            1 | a           | A         |        2050 |     NULL | NULL   | YES  | BTREE      |         |               |
      | tt    |          1 | b        |            1 | b           | A         |        2050 |     NULL | NULL   | YES  | BTREE      |         |               |
      | tt    |          1 | c        |            1 | c           | A         |        2050 |     NULL | NULL   | YES  | BTREE      |         |               |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      5 rows in set (0.00 sec)
      
      

      Using tokudb_analyze_time=0; and ANALYZE TABLE tt does not fix it like it did in PS-4297.

      Neither does{{ ALTER TABLE tt ANALYZE PARTITION p20200428;}}

       

      Dropping the last (empty) partition gets proper index statistics:

      mysql [localhost:5728] {msandbox} (test) > alter table tt drop partition p20200429;
      Query OK, 0 rows affected, 1 warning (0.13 sec)
      Records: 0  Duplicates: 0  Warnings: 0mysql [localhost:5728] {msandbox} (test) > show keys from tt;
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | tt    |          0 | PRIMARY  |            1 | id          | A         |        2049 |     NULL | NULL   |      | BTREE      |         |               |
      | tt    |          0 | PRIMARY  |            2 | d           | A         |        2049 |     NULL | NULL   |      | BTREE      |         |               |
      | tt    |          1 | a        |            1 | a           | A         |        1025 |     NULL | NULL   | YES  | BTREE      |         |               |
      | tt    |          1 | b        |            1 | b           | A         |        2049 |     NULL | NULL   | YES  | BTREE      |         |               |
      | tt    |          1 | c        |            1 | c           | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      5 rows in set (0.02 sec)
      

      I believe biggest partition should be used, but last one is in fact being used.

       

       

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              george.lorch George Lorch
              Reporter:
              sami.ahlroos Sami Ahlroos
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 4 hours, 15 minutes
                  1d 4h 15m