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

PS 8.0 - TokuDB/RocksDB rebuilds a table when altering an index as invisible

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 8.0.12-2rc1
    • 8.0.13-3
    • MyRocks, TokuDB
    • None

    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!

      Attachments

        Issue Links

          Activity

            People

              george.lorch George Lorch (Inactive)
              tomislav.plavcic@percona.com Tomislav Plavcic
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 3 hours, 4 minutes
                  3h 4m

                  Smart Checklist