ALTER TABLE slower post 5.1.56
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Incomplete
|
Low
|
Unassigned | ||
5.1 |
Invalid
|
Low
|
Unassigned | ||
5.5 |
Incomplete
|
Low
|
Unassigned |
Bug Description
The new "fast index creation" feature for all ALTER TABLE and OPTIMIZE table is much slower post 5.1.55. This may be related to upstream bug: http://
Fast index creation does not appear to work on UTF-8 columns and forces entire table copy.
I suspect the entire copy happens multiple times with the new ALTER TABLE functionality.
Here is a sample schema and ALTER TABLE statements. ALTER TABLE is an order of magnitude slower on 5.1.56 or later compared with 5.1.55.
-- 2011-07-13 13:07:28.585391
CREATE TABLE `Comments` (
`CommentID` int(11) NOT NULL AUTO_INCREMENT,
`Comment_
`Comment_
`Comment_
`Comment_Text` text NOT NULL,
`Comment_Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Comment_
`Comment_IP` varchar(45) NOT NULL DEFAULT '',
`Comment_
`Comment_
PRIMARY KEY (`CommentID`),
KEY `comment_
KEY `wiki_user_id` (`Comment_
KEY `wiki_user_name` (`Comment_
KEY `pluscontidx` (`Comment_
KEY `miuscountidx` (`Comment_
KEY `comment_date` (`Comment_
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 2011-07-13 13:07:28.585401
--^ table needs to have some data for the following ALTERS to take a significant amount of time, but I don't have sample data
-- for the table
ALTER TABLE comments
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin,
MODIFY Comment_Username varbinary(200) NOT NULL DEFAULT '',
MODIFY Comment_Text blob NOT NULL,
MODIFY Comment_IP varbinary(45) NOT NULL DEFAULT '';
-- 2011-07-13 13:07:28.585408
ALTER TABLE comments
MODIFY Comment_Username varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
MODIFY Comment_Text text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
MODIFY Comment_IP varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
Changed in percona-server: | |
assignee: | nobody → Valentine Gostev (longbow) |
Changed in percona-server: | |
importance: | Undecided → Low |
Changed in percona-server: | |
assignee: | Valentine Gostev (longbow) → nobody |
Tested with PS 5.5
Also, the test case linked in http:// bugs.mysql. com/bug. php?id= 33650 is http:// lists.mysql. com/commits/ 136982 . It also passes.
CREATE TABLE `t1` ( 31746 DEFAULT CHARSET=utf8
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=
is the table used. (~ 10M).
To further confirm I enabled tracing with mysqld-debug --debug and checked with between_ tables with OFF and between_ tables when ON.
fast index creation OFF / ON, and checked copy_data_
without copy_data_