ALTER TABLE slower post 5.1.56

[Swany]

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://bugs.mysql.com/bug.php?id=33650

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_Page_ID` int(11) NOT NULL DEFAULT '0',
   `Comment_user_id` int(11) NOT NULL DEFAULT '0',
   `Comment_Username` varchar(200) NOT NULL DEFAULT '',
   `Comment_Text` text NOT NULL,
   `Comment_Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `Comment_Parent_ID` int(11) NOT NULL DEFAULT '0',
   `Comment_IP` varchar(45) NOT NULL DEFAULT '',
   `Comment_Plus_Count` int(11) NOT NULL DEFAULT '0',
   `Comment_Minus_Count` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`CommentID`),
   KEY `comment_page_id_index` (`Comment_Page_ID`),
   KEY `wiki_user_id` (`Comment_user_id`),
   KEY `wiki_user_name` (`Comment_Username`),
   KEY `pluscontidx` (`Comment_user_id`),
   KEY `miuscountidx` (`Comment_Plus_Count`),
   KEY `comment_date` (`Comment_Minus_Count`)
 ) 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 '';

[Raghavendra D Prabhu (raghavendra-prabhu)]

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` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=31746 DEFAULT CHARSET=utf8

is the table used. (~ 10M).

To further confirm I enabled tracing with mysqld-debug --debug and checked with
fast index creation OFF / ON, and checked copy_data_between_tables with OFF and
without copy_data_between_tables when ON.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1881