MySQL Bugs: #4369: MySQL 4.1 regression in Alter table/tmp table from hash

Bug #4369	MySQL 4.1 regression in Alter table/tmp table from hash
Submitted:	2 Jul 2004 6:01	Modified:	9 Aug 2004 23:16
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
I've spotted this bug working with customer who had very large table (hundred of millions of rows) with very short rows (5 bytes)

create table my(i int not null);

Populate table with  good amount of rows.

Run ALTER TABLE my TYPE=MYISAM;

Run "strace -p <pid>"  to see what is happening. 

In  MySQL 4.0 you will see writes  (pwrite) are done in   read_buffer blocks while MySQL
4.1.3 will write in 5 byte blocks. 

In the case in question regression in MySQL 4.1 was over 10 times. 

This can also be observed as very low user CPU usage, low IO and high system CPU usage
during "copying to tmp table" phase on alter table.

Other cases which seems to be affected (not 100% sure):

-  CREATE TABLE X SELECT * FROM Y;

-  Converting of HEAP temporary table to MyISAM

How to repeat:
see above

Fixed a typo in "start_bulk_insert" which prevented the activation of the write buffer.
This fixes several cases, but not the "Converting of HEAP temporary table to MyISAM",
as this function has not yet been called from there. This issue is fixed with bug#4497.

I separated the fix for bug#4497 from this one. The mentioned problems, except of "Converting of HEAP temporary table to MyISAM", appear in 4.1 only. The exception happens in 4.0 too, so I fixed this bug there, from where it will be merged into the 4.1 tree soon.

fixed in 4.1.4