Bug #9659 SHOW PROCESSLIST 'State' is 'copying to tmp table' during keycache ALTER TABLE

• Submitted: 5 Apr 2005 18:32
• Modified: 5 Apr 2005 23:43
• Reporter: Timothy Smith
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: all
• OS: Any (all)
• CPU Architecture: Any

[5 Apr 2005 18:32] Timothy Smith

Description:

When one does ALTER TABLE on a large MYISAM table with keys, when myisam_max_{,extra_}sort_file_size are both set low enough that MySQL will use the 'keycache' method of repair, the SHOW PROCESSLIST State stays in 'copying to tmp table' for the entire operation.  If you increase *_file_size so that the 'sort' method is used, then State changes from 'copying to tmp table' to 'Repairing by sort'.

What would be really nice is to have it change to 'Repairing by (keycache|sort): key #3 - NNNNN', like myisamchk tells you which key it is working on and how far along it is.  When altering a very large MyISAM table, it can take hours or days.  It is important to have some way of knowing what is happening during that time.  Especially since the speed difference between the 'keycache' and 'sort' methods is so large, it is important to know which method is being used for which keys, to better understand if the speed can be improved.

How to repeat:
set global myisam_max_sort_file_size=500;
set global myisam_max_extra_sort_file_size=500;

create table a (a int, b int, c int, d int, e int) engine=myisam;

insert into a (a) values (0);
insert into a select * from a;
-- Hit <Up><Enter> until you have a million rows in the table

alter table a add key(a), add key(b), add key(c), add key(d), add key(e);

-- Look at SHOW PROCESSLIST in another session

Suggested fix:

Make the mysiam table handler use the same kind of reporting that myisamchk uses when doing CHECK, REPAIR and ALTER.

[5 Apr 2005 23:43] Timothy Smith

Hi!  I got more info on this; it turns out that the ALTER TABLE actually doesn't do a repair by keycache.  It works in a similar way (by inserting entries into the key one at a time), but does it as it is copying the data file (instead of in a separate step, as myisamchk does).

So the SHOW PROCESSLIST output of 'copying to tmp table' is correct - it's just that during that step, keys are also being built row-by-row (using the key cache).

Regards,

Timothy