Medium 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!
