[MDEV-28972] Index statistics disappear from existing partitions when a new partition is added Created: 2022-06-14  Updated: 2023-12-11

Status: Stalled
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 1
Labels: None

Attachments: HTML File GLOBAL_VARIABLES    

 Description   

When adding a new partition the statistics disappear from existing partitions.

This is not specific for one table but it's general for all partitioned tables schemas.

SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='XXXX';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p2022_05_25    |          0 |
 
| p2022_05_26    |          0 |
 
| p2022_05_27    |          0 |
 
| p2022_05_28    |          0 |
 
| p2022_05_29    |          0 |
 
| p2022_05_30    |          0 |
 
| p2022_05_31    |          0 |
 
| p2022_06_01    |          0 |
 
| p2022_06_02    |          0 |
 
| p2022_06_03    |          0 |
 
| p2022_06_04    |          0 |
 
| p2022_06_05    |          0 |
 
| p2022_06_06    |          0 |
 
| p2022_06_07    |          0 |
 
| p2022_06_08    |         72 |
 
| p2022_06_09    |   33576782 |

An explicit ALTER ONLINE TABLE XXXX ANALYZE PARTITION p2022_05_26;

Restored the statistics for such partition.

> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='XXXX';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p2022_05_26 | 116803372 |

Attached global variables.

 Comments   
Comment by Alexey Botchkov [ 2022-10-05 ]

It is reproducible with the InnoDB engine.

drop table if exists p_t;
 
create table p_t(id int, name varchar(64)) engine=innodb
 
   partition by range (id)   partitions 2
 
   (     partition p1 values less than (3),  partition p2 values less than (100));
 
insert into p_t values (1, 'one');
 
insert into p_t values (2, 'two');
 
insert into p_t values (10, 'ten');
 
 
 
SELECT partition_name, table_rows FROM
 
  information_schema.partitions WHERE table_name ='p_t';
 
 
 
alter table p_t add partition (partition p3 values less than (1000));
 
 
 
SELECT partition_name, table_rows FROM
 
  information_schema.partitions WHERE table_name ='p_t';
 
 
 
ALTER ONLINE TABLE p_t ANALYZE PARTITION p2;
 
 
 
SELECT partition_name, table_rows FROM
 
  information_schema.partitions WHERE table_name ='p_t';
 
~

results in

partition_name  table_rows
 
p1      2
 
p2      1
 
partition_name  table_rows
 
p1      2
 
p2      0
 
p3      0
 
Table   Op      Msg_type        Msg_text
 
test.p_t        analyze status  OK
 
partition_name  table_rows
 
p1      2
 
p2      1
 
p3      0

Comment by Claudio Nanni [ 2023-10-10 ]

Another test,

First Statistics are accurate, 3 values in P8 and 97 values in P9, adding P10 makes them 2 and 19, ANALYZE brings them back ok.

 CREATE TABLE `t1` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `info` varchar(32) DEFAULT NULL,
 
  `info_added` timestamp NOT NULL DEFAULT current_timestamp(),
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=8368 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_spanish_ci
 
 PARTITION BY RANGE (`id`)
 
(PARTITION `p1` VALUES LESS THAN (1000) ENGINE = InnoDB,
 
 PARTITION `p2` VALUES LESS THAN (2000) ENGINE = InnoDB,
 
 PARTITION `p3` VALUES LESS THAN (3000) ENGINE = InnoDB,
 
 PARTITION `p4` VALUES LESS THAN (4000) ENGINE = InnoDB,
 
 PARTITION `p5` VALUES LESS THAN (5000) ENGINE = InnoDB,
 
 PARTITION `p6` VALUES LESS THAN (6000) ENGINE = InnoDB,
 
 PARTITION `p7` VALUES LESS THAN (7000) ENGINE = InnoDB,
 
 PARTITION `p8` VALUES LESS THAN (8000) ENGINE = InnoDB,
 
 PARTITION `p9` VALUES LESS THAN (9000) ENGINE = InnoDB,
 
 PARTITION `p10` VALUES LESS THAN (10000) ENGINE = InnoDB) 
 
 
MariaDB [CS6147]> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='t1' and table_schema='CS6147';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p1             |        352 |
 
| p2             |        352 |
 
| p3             |        486 |
 
| p4             |        218 |
 
| p5             |        463 |
 
| p6             |        500 |
 
| p7             |        445 |
 
| p8             |          3 |
 
| p9             |         97 |
 
+----------------+------------+
 
9 rows in set (0,001 sec)
 
 
 
MariaDB [CS6147]> alter table t1 add partition (partition p10 values less than (10000));
 
Query OK, 0 rows affected (0,014 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='t1' and table_schema='CS6147';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p1             |        352 |
 
| p2             |        352 |
 
| p3             |        486 |
 
| p4             |        218 |
 
| p5             |        463 |
 
| p6             |        500 |
 
| p7             |        445 |
 
| p8             |          2 |
 
| p9             |         19 |
 
| p10            |          0 |
 
+----------------+------------+
 
MariaDB [CS6147]> analyze table t1;
 
+-----------+---------+----------+----------+
 
| Table     | Op      | Msg_type | Msg_text |
 
+-----------+---------+----------+----------+
 
| CS6147.t1 | analyze | status   | OK       |
 
+-----------+---------+----------+----------+
 
1 row in set (0,009 sec)
 
 
 
MariaDB [CS6147]> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='t1' and table_schema='CS6147';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p1             |        352 |
 
| p2             |        352 |
 
| p3             |        486 |
 
| p4             |        218 |
 
| p5             |        463 |
 
| p6             |        500 |
 
| p7             |        445 |
 
| p8             |          3 |
 
| p9             |         97 |
 
| p10            |          0 |
 
+----------------+------------+

Comment by Claudio Nanni [ 2023-10-10 ]

Now adding a larger partition up to 100.000, you see stats are deleted for previous partition and messed up a bit for the few before it:

bin/mysql  Ver 15.1 Distrib 10.6.14-9-MariaDB, for Linux (x86_64) using readline 5.1
 
 
 
MariaDB [CS6147]> alter table t1 add partition (partition p11 values less than (100000));
 
Query OK, 0 rows affected (0,011 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> insert into t1 (info) select left(uuid(),8) from t1;
 
Query OK, 2916 rows affected (0,024 sec)
 
Records: 2916  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> insert into t1 (info) select left(uuid(),8) from t1;
 
Query OK, 5832 rows affected (0,043 sec)
 
Records: 5832  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> insert into t1 (info) select left(uuid(),8) from t1;
 
Query OK, 11664 rows affected (0,068 sec)
 
Records: 11664  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> insert into t1 (info) select left(uuid(),8) from t1;
 
Query OK, 23328 rows affected (0,102 sec)
 
Records: 23328  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> insert into t1 (info) select left(uuid(),8) from t1;
 
ERROR 1526 (HY000): Table has no partition for value 100000
 
(reached the limit)
 
 
 
MariaDB [CS6147]> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='t1' and table_schema='CS6147';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p1             |        352 |
 
| p2             |        352 |
 
| p3             |        486 |
 
| p4             |        218 |
 
| p5             |        463 |
 
| p6             |        500 |
 
| p7             |        445 |
 
| p8             |          3 |
 
| p9             |        413 |
 
| p10            |        500 |
 
| p11            |      42924 |
 
+----------------+------------+
 
11 rows in set (0,001 sec)
 
 
 
MariaDB [CS6147]> alter table t1 add partition (partition p12 values less than (200000));
 
Query OK, 0 rows affected (0,013 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [CS6147]> SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name ='t1' and table_schema='CS6147';
 
+----------------+------------+
 
| partition_name | table_rows |
 
+----------------+------------+
 
| p1             |        352 |
 
| p2             |        352 |
 
| p3             |        486 |
 
| p4             |        218 |
 
| p5             |        463 |
 
| p6             |        500 |
 
| p7             |        445 |
 
| p8             |          3 |
 
| p9             |        130 |
 
| p10            |         55 |
 
| p11            |          0 |
 
| p12            |          0 |
 
+----------------+------------+

Comment by Claudio Nanni [ 2023-10-10 ]

holyfoot Please check my latest comments, I could reproduce in the measure of resetting the stats of the most recent partition when adding a new one.

Generated at Thu Feb 08 10:04:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.