Description:
I've been experiencing alter statements taking a very long time. The one in question was a change to the primary key and setting row_format = compressed. The table was empty and it took over 10 minutes. The table had 1024 subpartitions in a 64x16 scheme. Looking at the file system I could see that the new table files were created quickly but then it just sat there on 100% CPU usage with a status of "rename result table". It eventually completed OK. The table is InnoDB of course. innodb_file_per_table=1.
Other alters that were just a change to the PK on a similarly structured table also took forever. So the question is, what is MySQL doing when it appears there's nothing left to do?
Thanks.
How to repeat:
13 column table with integers only. Primary key covers 8 columns.
PARTITION BY LIST (`col1` mod 64)
SUBPARTITION BY HASH (`col2`)
SUBPARTITIONS 16
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB, ......)
ALTER TABLE `table`
DROP PRIMARY KEY,
ADD PRIMARY KEY (all 13 columns...), row_format=compressed;
