For InnoDB table maintenance, one of the really important my.cnf parameters is innodb_file_per_table. This will create a [tablename].ibd file for each of your InnoDB tables instead of putting it all in one really large ibdataX file. This way, you can optimize the table to recover space without dumping and reimporting. Hope that helps!

I’ll also second the partitioning comment – it sounds like you guys can get a lot of benefit from it. Likewise, I’d look at ARCHIVE engine.

Great post!

The primary difference on the EC2 instance was that I had 15GB of InnoDB buffer to play with, vs the 2GB InnoDB buffer on our our normal DB server. I’m not quite sure of how much time was saved overall, since I was never able to complete the table restructuring on our test server before MySQL crashed due to lack of memory.

1. Actually the ability to merge indexes came out with MySQL 5.0, so it is now possible to use more than one index at a time within a single query.

2. It is possible that running the optimize table alone could have had a positive affect on performance, but it was not the major factor here. InnoDB tables are a lot less prone to row fragmentation due to the implied clustered index. Deletes out of this particular table that I use as an example are very rare.

3. Partitioning, oh yeah – that is one of the near term plans for our overall database structure. With MySQL 5.1’s introduction of partitions, this is something I want to take advantage soon (once I get around to upgrading to 5.1)

1. you are starting from a bad setup; only index at a time can be used, so there is not need to keep all the indexes there were.

2. you optimized the table, which can make quite a difference, especially if there is fragmentation.
I’m not sure, but it’s possible that this can reduce also the size of the table, reducing the unused space in the blocks.

Seems like that the table you are talking about is quite static, have you thought about partitioning? It can improve the performance even more.

Cheers, saverio

Actually I “cheated” a bit on the table rebuilds. The first thing I did was to setup a slave replicated instance on an EC2 xlarge instance (15GB RAM/4 Processors). When I brought the system down for the re-index I did all of the work on the EC2 instance, which had a lot more power than our primary DB server.

I then dropped all of the indexes on the summary tables – which decreased the overall size of the table to less than half. Once the indexes were gone, I dropped the ID column and added the new composite primary key – this actually ran quite quickly. After the new primary key was there, I ran an optimize on the table.

The sql I used was:

select now();

alter table duration_summaries
drop index user_id,
drop index durationable_id,
drop index summary_date,
drop index week,
drop index month,
drop index year,
drop index user_week_year,
drop index user_month_year,
drop index date_user_dur_idx,
modify column user_id int(11) first,
modify column durationable_id int(11) after summary_date;

select now();

alter table duration_summaries
drop column id,
drop primary key,
add primary key (user_id, summary_date, durationable_id);

select now();

optimize table duration_summaries;

select now();

In my limited experience, it takes a lot longer. I must be doing something wrong.

Can you share precisely how you rebuilt the innodb table?