Bug #61621 ALTER TABLE very slow on empy partitioned table

Submitted: 23 Jun 2011 22:51 Modified: 24 Jun 2011 4:06
Reporter: Chris Wagner Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.5.13 OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any
Tags: alter table slow empty parititioned

[23 Jun 2011 22:51] Chris Wagner
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;
[24 Jun 2011 4:06] Valeriy Kravchuk
This is a duplicate of Bug #51325.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.