MySQL Bugs: #21109: Reorder tables using ALTER TABLE ... ORDER BY creates huge temptables

Bug #21109	Reorder tables using ALTER TABLE ... ORDER BY creates huge temptables
Submitted:	18 Jul 2006 9:03	Modified:	18 Aug 2006 15:24
Reporter:	Apachez	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.18-nt	OS:	Windows (Win2k)
Assigned to:		CPU Architecture:	Any

Description:
In order to boost performance of a table, one method for a table which will be mostly read-only, is to reorder the contents according to how the queries will be performed.

Such as:

ALTER TABLE Tbl_Search_Vector ORDER BY SearchWordId, SearchChunkId;

and then followed by:

OPTIMIZE TABLE Tbl_Search_Vector;

During the ALTER TABLE process a temptable/tempfile will be created in the temp directory on the disk. I have noticed that when a BLOB column is included in this ALTER TABLE the temptable created in the temp directory will be more than 15 GB large even thou the original table is not more than 252 MB in MYD and 73 MB in MYI.

Why does MySQL do this?

Shouldn't the temptable be not more than the original size if needed?

If I leave out the BLOB column (as in the above example) then the temptable created in the temp directory is roughly 30 MB. Once ALTER TABLE completes the temptable is removed.

I find this serious due to the extreme growth of the temptable which might end up with out of diskspace.

How to repeat:
1) Create a table containing a BLOB column:

CREATE TABLE Tbl_Search_Vector (
`SearchWordId` mediumint UNSIGNED NOT NULL DEFAULT '0',
`SearchChunkId` mediumint UNSIGNED NOT NULL DEFAULT '0',
`SearchVector` blob NOT NULL,
PRIMARY KEY (`SearchWordId`, `SearchChunkId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2) Fill the table with data (in my case the MYD is 252 MB containing 7548817 rows)...

3) Issue a reorder of the table using ALTER TABLE including the BLOB column:

ALTER TABLE Tbl_Search_Vector ORDER BY SearchWord, SearchWordId, SearchVector;

4) Take a look at the temp directory in the filesystem and notice how the temptable which MySQL creates grows and grows and grows. Specially that it will become way larger than the original file which is being altered.

5) Issue a reorder of the table using ALTER TABLE this time WITHOUT the BLOB column:

ALTER TABLE Tbl_Search_Vector ORDER BY SearchWordId, SearchChunkId;

6) Notice how the temptable created by MySQL is basically smaller than the original filesize, at least it is not larger than the original files.

Suggested fix:
A workaround is to leave out the BLOB column from the ALTER TABLE ... ORDER BY query.

Other than that I have no idea how this can be fixed internally by MySQL or if this even is a bug, but due to how the temptable grows I find this to be a bug or at least a "non expected behaviour".

I changed the size of the BLOB to TINYBLOB in order to see how this affect the tempfiles which are being created by MySQL. The MYD is now 248 MB while the MYI is still 73.7 MB.

The tempfiles are smaller but still way larger than the original files.

MySQL creates two files in the c:\temp directory. One named MY2 and the other MY3 - both grows to a size of 7 805 476 778 bytes (7.26 GB). Which gives that MySQL occupies in total 15 610 953 556 bytes (14.53 GB) of tempfiles just to reorder a 248 MB table !?

Thank you for a bug report. Sorry, but I was not able to repeat the behaviour described with 4.1.22-BK on Linux. I had created similar table and put some random data (without large blobs) to make it of some reasonable size:

...
mysql> insert into `Tbl_Search_Vector` select rand()*1000000, rand()*2000000, '
adcdefghijklmnop' from Tbl_Search_Vector;
Query OK, 524288 rows affected (7.87 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> show table status like 'Tbl%'\G
*************************** 1. row ***************************
           Name: Tbl_Search_Vector
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 1048576
 Avg_row_length: 28
    Data_length: 29360128
Max_data_length: 4294967295
   Index_length: 12539904
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-07-18 16:20:30
    Update_time: 2006-07-18 16:21:44
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ALTER TABLE Tbl_Search_Vector ORDER BY SearchWordId, SearchChunkId;
Query OK, 1048576 rows affected (21.79 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.22    |
+-----------+
1 row in set (0.00 sec)

While that ALTER TABLE were running, I checked from the other window:

openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users        0 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users     1024 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users  3145728 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users     1024 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
...
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users 24510464 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users  7414784 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users 25690112 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users  7417856 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users 27000832 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users  7418880 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users 28180480 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYD
-rw-rw----  1 openxs users  7418880 2006-07-18 16:22 dbs/4.1/var/bug21109/#sql-1
fe7_1.MYI
-rw-rw----  1 openxs users 29360128 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 12539904 2006-07-18 16:21 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI
openxs@suse:~> ls -l dbs/4.1/var/bug21109/*.M*
-rw-rw----  1 openxs users 29360128 2006-07-18 16:22 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYD
-rw-rw----  1 openxs users 10747904 2006-07-18 16:22 dbs/4.1/var/bug21109/Tbl_Se
arch_Vector.MYI

So, temporary table was large, but not larger than original one. Please, try to repeat with a newer version, 4.1.20 on Windows, with a test case similar to mine, and inform about the results.

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".