MySQL Bugs: #59925: Table optimize fails to rebuild index after a bulk delete for large tables

Bug #59925	Table optimize fails to rebuild index after a bulk delete for large tables
Submitted:	3 Feb 2011 17:50	Modified:	25 Apr 2012 15:38
Reporter:	Yuriy Demchenko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Linux (tested on fedora fc11 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	myisam_sort_buffer_size, optimize fails

Description:
Table optimize fails to rebuild index after a bulk delete for very large tables when myisam_sort_buffer_size is set to 4Gb or larger.

I have tested this for mysql-5.1.54-linux-x86_64-glibc23 and mysql-5.5.8-linux2.6-x86_64 on fedora core 11.

The errors produced are somewhat different between mysql 5.1 and 5.5, I will be talking about 5.5 from now on.

I have a MyIsam table with 3 indexes, 30Gb size, half of it is index. When I run a "DELETE" followed by "OPTIMIZE", the "OPTIMIZE" will fail with this message for non-partitioned table:

[Warning] Warning: Optimize table got errno 137 on sdv_log_collector.sdv_logs_4, retrying

The index will be corrupted at this point and table will be unusable.
Please note, this does not happen when deleting couple of rows, I've got consistent results when deleting 30-50% of the data.

This is the error I got when performing the same on a partitioned table:

110130 15:55:08 [Warning] Warning: Optimize table got errno 22 on log_collector.logs, retrying
110130 16:03:10 [ERROR] Key 1 - Found too many records; Can't continue
110130 16:03:10 [Warning] Warning: Enabling keys got errno 0 on log_collector.logs, retrying
110130 16:04:46 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records
110130 16:04:46 [ERROR] Run recovery again without -q

Even though the error is different, I believe the same thing causes it.

How to repeat:
Set myisam_sort_buffer_size to 4Gb or larger( I tried 4Gb and 26Gb).

Create a table like:

CREATE TABLE logs (
  date int(10) unsigned NOT NULL,
  device smallint(5) unsigned NOT NULL,
  action smallint(5) unsigned NOT NULL,
  st int(10) unsigned NOT NULL,
  rid int(10) unsigned NOT NULL,
  sg mediumint(8) unsigned NOT NULL,
  gda int(10) unsigned NOT NULL,
  sid smallint(5) unsigned NOT NULL,
  KEY `action` (`action`),
  KEY sg (sg),
  KEY st (st)
) ENGINE=MyISAM

Load the table with data so the approximate table size is 30Gb, 15Gb for keys and 15Gb for data. You will have approximately 600 million rows.

Issue a "DELETE FROM logs WHERE date < 'some value'". Aim to delete approximately 50% of the records.

Issue a "OPTIMIZE TABLE logs".

You should end up with a corrupted index now. "REPAIR TABLE" will fix the index

Suggested fix:
My guess is that myisam_sort_buffer_size set to 4Gb or larger is not used properly in some cases. I am not sure why this happens.

Can it be when myisam_sort_buffer_size is 4gb or larger, mysql can not evaluate properly if index is larger and it should use temporary files for sorting?

Thank you for the report.

> Can it be when myisam_sort_buffer_size is 4gb or larger, mysql can not evaluate properly
if index is larger and it should use temporary files for sorting?

Correct. See bug #45702 for details.

Can you confirm you don't have same problem with smaller myisam_sort_buffer_size?

Yes, I confirmed that it works fine for both 5.1 and 5.5 for buffer size < 4gb. I am running my databases with 3Gb stable.

One more thing, bug #45702 indicates that an improper sorting mechanism being used.
In this case, it looks like "repair with key cache" is used properly, but it fails to complete!

This bug hit us in a big way.  With myisam_sort_buffer_size over 4 GB, the MyISAM OPTIMIZE TABLE destroyed our indexes.

Worst of all, the tables didn't go offline due to the problem, they remained available but with empty indexes that made data inaccessible and allowed inserts that resulted in unique key collisions.

This is definitely a serious bug..

As that other potentially related bug is finally fixed in 5.5.22, please, check if 5.5.22 solves the problem for you.

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