Description
TokuDB/RocksDB don't support inplace alter operations, but there are some alter table operations which should not touch data in that specific table, eg. making an index invisible/visible or adding a comment to already existing field in the table.
So in 8.0 InnoDB and MyISAM do instant index visible/invisible modifications, but for TokuDB/RocksDB this is copying table data to temp table which takes a long time and it doesn't make sense since no change to data is done.
So this ticket is to investigate if this can somehow be fixed/improved since that would be IMHO a huge thing for TokuDB/RocksDB.
Here's how it looks:
# starts with innodb 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 VISIBLE; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 8.0.12>alter table sbtest1 engine=tokudb; Query OK, 3000000 rows affected (30.45 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 3000000 rows affected (31.52 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 VISIBLE; Query OK, 3000000 rows affected (31.02 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>show processlist; +----+-----------------+-----------+--------+---------+------+------------------------------------------------------------------------------------+-----------------------------------------------+-----------+--- ------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Ro ws_examined | +----+-----------------+-----------+--------+---------+------+------------------------------------------------------------------------------------+-----------------------------------------------+-----------+--- ------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 9295 | Waiting on empty queue | NULL | 0 | 0 | | 9 | root | localhost | sbtest | Query | 14 | Queried about 2293001 rows, Fetched about 2293000 rows, loading data still remains | ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE | 0 | 0 | | 14 | root | localhost | sbtest | Query | 0 | starting | show processlist | 0 | 0 | +----+-----------------+-----------+--------+---------+------+------------------------------------------------------------------------------------+-----------------------------------------------+-----------+--- ------------+ 3 rows in set (0.00 sec) 8.0.12>show processlist; +----+-----------------+-----------+--------+---------+------+---------------------------------------------------------+-----------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------------+-----------+--------+---------+------+---------------------------------------------------------+-----------------------------------------------+-----------+---------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 9305 | Waiting on empty queue | NULL | 0 | 0 | | 9 | root | localhost | sbtest | Query | 24 | Loading of data t ./sbtest/#sql-4eef_9 about 21.7% done | ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE | 0 | 0 | | 14 | root | localhost | sbtest | Query | 0 | starting | show processlist | 0 | 0 | +----+-----------------+-----------+--------+---------+------+---------------------------------------------------------+-----------------------------------------------+-----------+---------------+ 3 rows in set (0.00 sec) 8.0.12>alter table sbtest1 engine=rocksdb; Query OK, 3000000 rows affected (1 min 10.89 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 3000000 rows affected (59.92 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 VISIBLE; Query OK, 3000000 rows affected (59.58 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>show processlist; +----+-----------------+-----------+--------+---------+-------+------------------------+-----------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------------+-----------+--------+---------+-------+------------------------+-----------------------------------------------+-----------+---------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 11988 | Waiting on empty queue | NULL | 0 | 0 | | 9 | root | localhost | sbtest | Query | 13 | copy to tmp table | ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE | 0 | 0 | | 14 | root | localhost | sbtest | Query | 0 | starting | show processlist | 0 | 0 | +----+-----------------+-----------+--------+---------+-------+------------------------+-----------------------------------------------+-----------+---------------+ 3 rows in set (0.00 sec) 8.0.12>alter table sbtest1 engine=myisam; Query OK, 3000000 rows affected (26.11 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 VISIBLE; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
What's more if you repeatedly execute this even if the index is already invisible it will happily execute this expensive operation:
8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 3000000 rows affected (30.84 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 3000000 rows affected (30.54 sec) Records: 3000000 Duplicates: 0 Warnings: 0 8.0.12>ALTER TABLE sbtest1 ALTER INDEX k_1 INVISIBLE; Query OK, 3000000 rows affected (30.66 sec) Records: 3000000 Duplicates: 0 Warnings: 0
But for example for adding a comment to a field in a table doesn't execute a table copy (there was a bug fix for this: https://jira.percona.com/browse/PS-4280):
# TokuDB 8.0.12>ALTER TABLE sbtest1 MODIFY COLUMN `c` char(120) NOT NULL DEFAULT '' COMMENT 'test comment'; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 8.0.12>show create table sbtest1; +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sbtest1 | CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '' COMMENT 'test comment', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) /*!80000 INVISIBLE */ ) ENGINE=TokuDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
Many thanks to george.lorch for feedback and ideas!