MySQL Bugs: #48359: ALTER TABLE xx COMMENT

Bug #48359	ALTER TABLE xx COMMENT - locks large tables
Submitted:	27 Oct 2009 19:24	Modified:	24 Jun 2010 8:01
Reporter:	Colin Mutter	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.1	OS:	Linux (RHEL 4)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	5.1.44-ndb-7.1.3, alter, ALTER TABLE, comment, comments, lock table, performance, slow

Description:
When changing the comment on a MyISAM table, or commenting its constituent fields, the engine performs (as expected) a full ALTER.  That's perfectly fine on small tables, but when dealing with very large MyISAM tables we end up locking them for the full amount of time to run the ALTER...just to change a comment!

This feature request is mainly to suggest that a more efficient mechanism for managing comments in the engine be created.  We all want, and encourage better documentation - but locking production tables just to add them seems rather necessary and somewhat of a hindrance to proper documentation.

How to repeat:
Add table and field comments on a very large (10+GB table).

Suggested fix:
Introduce a shortcut in the interpretation of ALTER so that if the only request is to add/change comment, just do that (not a full alter/rebuild).

Introduce new syntax, such as 
ANNOTATE {Table} WITH 'My Comment Here'  

or 

ANNOTATE {Table.Column} WITH 'Hello, another comment'

I think this problem is actually solved in MySQL 5.1.x. Read http://dev.mysql.com/doc/refman/5.1/en/alter-table.html and check the following my test:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tbig(c1 int, c2 char(255), c3 char(255));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into tbig values(1,'a','b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbig select * from tbig;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbig select * from tbig;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tbig select * from tbig;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

...

mysql> insert into tbig select * from tbig;
Query OK, 524288 rows affected (9.96 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> alter table tbig engine=InnoDB;
Query OK, 1048576 rows affected (1 min 35.09 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> alter table tbig comment 'InnoDB one';
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tbig engine=InnoDB;
Query OK, 1048576 rows affected (1 min 40.65 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> alter table tbig engine=MyISAM;
Query OK, 1048576 rows affected (13.29 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> alter table tbig comment 'MyISAM one';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tbig engine=MyISAM;
Query OK, 1048576 rows affected (10.08 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> alter table tbig comment 'MyISAM again';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

So, both for InnoDB and MyISAM tables adding comment is really fast comparing to other ALTER statements that make complete copy of table. Please, check with 5.1.40 yourself if in doubts. 

For MySQL 5.0.x this solution backporting or implementation of new feature like you suggest is very unlikely.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This seems to be fixed in mainline 5.1 but not in MySQL Cluster (tested on ndb-7.1.3 which is baed on mainline 5.1.44)

Changing the comment on a 500K row myisam table in e.g. 5.1.39 takes next 
to no time, when doing the same on a myisam table in 5.1.44 the time taken 
grows more or less in a linear way with the number of rows stored in the table,
e.g. taking about 7s for 250K rows, about 13s for 500k ...

reopening and changing category to MySQL Cluster