TokuDB Hot Column Expansion Too Slow

[Timothy Kyalo]

I need to expand a varchar field length from 255 to 4000.

I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

I know TokuDB supports Hot Column operations but this is not working for me ( number of rows ~ 210 million)

Show variables file: https://drive.google.com/file/d/0B5noFLrbjDjzSW9wdnVjb095Q0U/view?usp=sharing

Alter command

alter table test_table modify test_column varchar(4000);

Show processlist:

mysql> show processlist;

+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+

| Id | User | Host      | db            | Command | Time | State                                                   | Info                                                       | Rows_sent | Rows_examined |

+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+

|  6 | root | localhost | NULL          | Query   |    0 | init                                                    | show processlist                                           |         0 |             0 |

|  7 | root | localhost | test | Query   |  461 | Queried about 2445001 rows, Inserted about 2445000 rows | alter table test_table modify test_column varchar(4000) |         0 |             0 |

+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+

2 rows in set (0.00 sec)

Table type: ENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8

Any idea which options I might need to set because it's currently processing at ~ 6k per second(which might take me ~10 hours)

P.S. I've also tested doing a `ALTER TABLE test_table  MODIFY test_column  MEDIUMTEXT,ALGORITHM=COPY; ` from another server ( with more memory but still getting same rates)

Please advice which options I need to set to enable the schema change.

[George Lorch]

'Hot' does not mean instant. It means non-blocking. So you should still be able to access the table while this is running. It takes as long as it is going to take depending on the kind of change you are making, how many messages are 'in-flight' within the indices, how much 'garbage' is in the tree and how many rows are present. It requires a full left to right scan of every index impacted on the table.

It seems your TokuDB cache size is only 4G, that seems pretty small for any kind of use of TokuDB, it will work, but it will not be particularly fast with default node sizes.

You are also using the default checkpoint time of 60 seconds. You might want to extend that to 15 minutes with the trade off of a longer recovery time in the event of a crash.

--
George O. Lorch III
Software Engineer, Percona
US/Arizona (GMT -7)