Bug #20038 | ALTER TABLE copies original data even when exclusive lock is issued | ||
---|---|---|---|
Submitted: | 24 May 2006 9:34 | Modified: | 27 Sep 2008 11:09 |
Reporter: | Francisco Reinaldo | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
Version: | 4.0.22 | OS: | Any (All) |
Assigned to: | CPU Architecture: | Any |
[24 May 2006 9:34]
Francisco Reinaldo
[1 Jun 2007 13:35]
Francisco Reinaldo
I believe this bug should be fixed in 4.0 because it is not a trivial problem and I don't think is a good idea to force users to update to 4.1 or 5.0 since the database formats are not compatible. This defect of creating a copy of the files is not only a performance issue but also creates unstable code since a lengthy process can fail at the end, that is, at the index creation because lack of disk space or any other I/O issue. It happened to us in several occasions that the data and index disappeared without even notifying the connected client which was performing the ALTER INDEX operation that something went wrong. Modifying the code so the index is created before inserting the data is not an option since it impacts performance. In own tests, it impacted performance 2 or 4 times. This issue also affects people importing huge external files with millions of records. For a 1Gb file for instance, it can create a 3GB index and therefore you will need an addition 1Gb of additional space only to copy the data and cross your finger that the process that not crash when executing the ALTER INDEX instruction. I hope my comments serve to reconsider mysql's strategy about when the bug should be fixed.
[4 Jun 2007 15:37]
Konstantin Osipov
Thank you for a reasonable feature request.
[27 Sep 2008 11:09]
Konstantin Osipov
This is fixed in 5.1 with fast add/drop index.