I have a MySQL 5.6 on Amazon RDS that I'm using for testing some data archiving scripts. I'm removing oldest data based on a "updated_date" column and index. Curiously, after removing a few million rows, my script gets stuck on the initial query it does for determining data bounds.
I run a query like this:
SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;
explain command on this query shows:
'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'
So, it's supposed to hit the index and run almost instantly, and it did when the testing started, but now, after millions of rows were removed, it gets stuck in the 'optimizing' state for several minutes.
The script is the only thing running on the database.
Any ideas on what's wrong with it? Am I supposed to do something when removing lots of rows like that? Do I have to run
optimize table, even though I'm not using
The result from
show table status like 'order':
Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment order,InnoDB,10,Compact,568037197,280,159252496384,0,180806041600,37692112896,4052226884,"2015-01-26 17:27:20",NULL,NULL,utf8_general_ci,NULL,,
The result from
select count(*) from order is 618376777 rows.
Unfortunately, I can't post the whole schema here, but where it bears on the issue, the result from
show create table order is:
CREATE TABLE `order` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, // 31 data columns here `updated_date` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_order_updated_date` (`updated_date`), // 9 indexes here ) ENGINE=InnoDB AUTO_INCREMENT=4052226884 DEFAULT CHARSET=utf8;
By separating the min() and max() calls in two queries, I noticed only the min() query is affected. The max() returns almost immediately, so it looks like the min() is traversing the index for all index entries that existed but are now empty. Is there any way to prevent that from happening other than rebuilding the index?
RickJames nailed the problem with the hint about change buffering, but disabling it entirely compromises performance for all inserts, deletes and updates. Eventually, I figured out the time it took to flush the changing buffer was reasonable with the production server, so problem solved for me, but if you run into the same issue with a low-end server with magnetic storage, good luck with it.