TokuDB "drop partition" didn't work for pruning data.
Description
Environment
Smart Checklist
Activity
Jira Bot August 31, 2020 at 6:56 PM
Hello @Heewon Lee,
It's been 52 days since this issue went into Incomplete and we haven't heard
from you on this.
At this point, our policy is to Close this issue, to keep things from getting
too cluttered. If you have more information about this issue and wish to
reopen it, please reply with a comment containing "jira-bot=reopen".
Jira Bot August 23, 2020 at 6:56 PM
Hello @Heewon Lee,
It's jira-bot again. Your bug report is important to us, but we haven't heard
from you since the previous notification. If we don't hear from you on
this in 7 days, the issue will be automatically closed.
Jira Bot August 8, 2020 at 5:56 PM
Hello @Heewon Lee,
I'm jira-bot, Percona's automated helper script. Your bug report is important
to us but we've been unable to reproduce it, and asked you for more
information. If we haven't heard from you on this in 3 more weeks, the issue
will be automatically closed.
George Lorch July 10, 2020 at 5:08 PM
This is currently not a bug as 8.x TokuDB and MyRocks do not yet support atomic DDL in the 8.0 series, which is now a requirement for INPLACE algorithm partition management functions. So ALTER TABLE ADD/DROP partition now can only use the COPY algorithm on RANGE and LIST partitioning, which instead of dropping data within the dropped partition, reorganizes the data. The same case applies for ADD if a range or list is added in the middle of the table, the data will again be reorganized. See https://dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html for the differences between INPLACE and COPY algorithm behaviors.
Lalit Choudhary March 24, 2020 at 11:05 AM
Hi @Heewon Lee
Thank you for the report.
Verified as described with PS 8.0.18
Test:
mysql [localhost] {msandbox} (test) > CALL build_test_partition_table(1000);
Query OK, 1001 rows affected (9.30 sec)
mysql [localhost] {msandbox} (test) > SELECT t_date, count(1) FROM test_innodb GROUP BY t_date;
+------------+----------+
| t_date | count(1) |
+------------+----------+
| 2020-03-01 | 334 |
| 2020-03-02 | 334 |
| 2020-03-03 | 333 |
+------------+----------+
3 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT t_date, count(1) FROM test_tokudb GROUP BY t_date;
+------------+----------+
| t_date | count(1) |
+------------+----------+
| 2020-03-01 | 334 |
| 2020-03-02 | 334 |
| 2020-03-03 | 333 |
+------------+----------+
3 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE test_innodb DROP PARTITION p1;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > select t_date, count(1) FROM test_innodb GROUP BY t_date;
+------------+----------+
| t_date | count(1) |
+------------+----------+
| 2020-03-02 | 334 |
| 2020-03-03 | 333 |
+------------+----------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE test_tokudb DROP PARTITION p1;
Query OK, 1001 rows affected, 1 warning (0.47 sec)
Records: 1001 Duplicates: 0 Warnings: 1
mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 138 | Inplace partition altering is not supported |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT t_date, count(1) FROM test_tokudb GROUP BY t_date;
+------------+----------+
| t_date | count(1) |
+------------+----------+
| 2020-03-01 | 334 |
| 2020-03-02 | 334 |
| 2020-03-03 | 333 |
+------------+----------+
3 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='test_tokudb';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2 | 668 |
| p3 | 333 |
+----------------+------------+
2 rows in set (0.01 sec)
bug senario
build test tables for innodb and tokudb
check data distribution.
drop partition and check data and partition's status.
# build test tables for innodb and tokudb
DELIMITER $$ CREATE PROCEDURE `build_test_partition_table`(IN i_max INT) BEGIN DECLARE i INT DEFAULT 0; DROP TABLE IF EXISTS test_innodb; DROP TABLE IF EXISTS test_tokudb; CREATE TABLE IF NOT EXISTS test_innodb ( t_seq int NOT NULL AUTO_INCREMENT, t_date date NOT NULL DEFAULT '0000-00-00', t_text varchar(255) NOT NULL DEFAULT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua', PRIMARY KEY (t_seq, t_date) ) partition by range columns (t_date) ( partition p1 values less than ('2020-03-02'), partition p2 values less than ('2020-03-03'), partition p3 values less than ('2020-03-04') ); CREATE TABLE IF NOT exists test_tokudb LIKE test_innodb; ALTER TABLE test_tokudb ENGINE = tokudb; SET @sql = 'INSERT INTO test_innodb (t_date) VALUES (?)'; PREPARE stmt FROM @sql; WHILE i <= i_max DO BEGIN SET @t_date = CONCAT('2020-03-0', 1+(i%3)); EXECUTE stmt USING @t_date; SET i = i + 1; END; END WHILE; DEALLOCATE PREPARE stmt; INSERT INTO test_tokudb SELECT * FROM test_innodb; END$$ DELIMITER ;and
mysql> CALL build_test_partition_table(1000); Query OK, 1001 rows affected (1.11 sec)# check data distribution
mysql> SELECT t_date, count(1) FROM test_innodb GROUP BY t_date; +------------+----------+ | t_date | count(1) | +------------+----------+ | 2020-03-01 | 334 | | 2020-03-02 | 334 | | 2020-03-03 | 333 | +------------+----------+ 3 rows in set (0.00 sec) mysql> SELECT t_date, count(1) FROM test_tokudb GROUP BY t_date; +------------+----------+ | t_date | count(1) | +------------+----------+ | 2020-03-01 | 334 | | 2020-03-02 | 334 | | 2020-03-03 | 333 | +------------+----------+ 3 rows in set (0.00 sec)# drop partition and check data
1. innodb
mysql> ALTER TABLE test_innodb DROP PARTITION p1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select t_date, count(1) FROM test_innodb GROUP BY t_date; +------------+----------+ | t_date | count(1) | +------------+----------+ | 2020-03-02 | 334 | | 2020-03-03 | 333 | +------------+----------+ 2 rows in set (0.00 sec)2. tokudb
mysql> ALTER TABLE test_tokudb DROP PARTITION p1; Query OK, 1001 rows affected, 1 warning (0.09 sec) Records: 1001 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 138 | Inplace partition altering is not supported | +---------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT t_date, count(1) FROM test_tokudb GROUP BY t_date; +------------+----------+ | t_date | count(1) | +------------+----------+ | 2020-03-01 | 334 | | 2020-03-02 | 334 | | 2020-03-03 | 333 | +------------+----------+ 3 rows in set (0.00 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='test_tokudb'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p2 | 668 | | p3 | 333 | +----------------+------------+ 2 rows in set (0.00 sec)Please fix this issue for TokuDB users.
Best regards.