While trying to add an index online to a TokuDB table, we noticed a weird behaviour where the hot index creation seems to be stuck in a loop, reaching 100% (as shown by processlist) then back to zero. Wash, rinse, repeat.
In this specific case we are trying to add an index on a part of the primary key, with the intention of dropping the primary key afterwards. Table is partitioned.
CREATE TABLE `mytable` (
`HASH_ID` char(64) NOT NULL,
`RAW_DATA` mediumblob NOT NULL,
`LAST_UPDATE` datetime NOT NULL,
`EXPIRE_DATE` datetime NOT NULL,
PRIMARY KEY (`HASH_ID`,`EXPIRE_DATE`),
KEY `EXPIRE_DATE_IX` (`EXPIRE_DATE`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_ZLIB
/*!50100 PARTITION BY RANGE (TO_DAYS(EXPIRE_DATE))
Create index statement: create index HASH on mytable(HASH_ID);
Show processlist shows:
Adding of indexes about 100.0% done
after some time, it restarts from zero.
This repeats in a loop, the create index never completes.
I can see the I/O impact of the index creation in the graphs, which repeats accordingly.
Server version: 5.6.34-79.1-log Percona Server (GPL), Release 79.1, Revision 1c589f9