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:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1 OS:Linux (linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[2 Jul 2004 6:01] Peter Zaitsev
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 "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):


-  Converting of HEAP temporary table to MyISAM

How to repeat:
see above
[4 Aug 2004 19:41] Ingo Strüwing
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.
[9 Aug 2004 16:10] Ingo Strüwing
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.
[9 Aug 2004 23:16] Sergei Golubchik
fixed in 4.1.4