> meant that we ended up using more space etc
* Usually only a few percent more space is needed for summary tables. Meanwhile, you may be saving space by not having as many indexes on the 'Fact' table.
* Sure, more code. For that, you get a _lot_ of performance. (Rule of Thumb: 10x; extreme case: 1000x.)
> Does this (partitioning) work well with MASTER-MASTER replication
* Yes.
* I oppose writing to both masters, for a number of reasons.
* Dual-Master gives you some good options when it comes to upgrading OS, MySQL, PARTITIONing, pagination, etc. With it, many major changes can be done with only seconds of downtime. Without it, it could take hours or days of downtime. So, if downtime will be important, then Master-Master may be your first task.
> we change the table on one MASTER does it replicate the change to the SLAVE
* Yes. That is essentially the definition of MySQL's "replication".
* In a dual-master setup, each machine is both Master and Slave to the other.
> InnoDB uses a lot of disk space
* True. Rule of Thumb: 2x-3x over MyISAM.
* Recovery from a crash is much better.
* Indexes work radically differently; sometimes one needs to understand the diffs in order to keep from making performance worse:
http://localhost/rjweb/mysql/doc.php/myisam2innodb
> How else would you suggest doing the pagination? We are using PHP and SELECT SQL_FOUND_ROWS to get the total record count, with limits etc to calculate pagination.
* Pagination using OFFSET and LIMIT has been the downfall of many a big web site. The solution:
http://localhost/rjweb/mysql/doc.php/pagination
* "out of ..." is discussed there, too.
> the best way to move the current table to a partitioned one.
* Plan A: ALTER TABLE ADD PARTITION...
* Plan B: CREATE TABLE with partitions, then INSERT...SELECT from the old into the new.
* I prefer Plan B --
* You can test and develop the code for it before committing to PARTITIONing.
* You could have a LIMIT on the SELECT, so that the creation of the partitioned table is not huge. (Eventually, you would do the full conversion, with some downtime.)
> Lets say, the time span for the records is 5 years
* It is rare for a 'system' to survive longer than that -- obsolete hardware, obsolete OS, obsolete MySQL, requirements change, more data, etc, etc.
* Plan on at least one complete upgrade & rewrite between now and then. Maybe PARTITIONed InnoDB with 5.5 (or 5.6, since it is GA now) is your first experiment in such an overhaul.
If you will eventually purge 'old' data, then PARTITIONing has another bonus; see
http://localhost/rjweb/mysql/doc.php/partitionmaint
What you do is up to you. I am merely telling you what my experience says is likely to be best. I have thrown a lot at you, and it sound like it is overwhelming to you. You may wish to take on one task at a time. The tasks are mostly independent: Dual-Master, 5.x upgrade, bigger disk, InnoDB (or XtraDB, which I prefer), PARTITIONing, Pagination, summary tables, etc.
Think about what your main pain points are now, or when will they become pain points. (If you are only a few months into your 5-year plan, then some issues are yet to come.) A million rows can be restructured in minutes. 60M rows will take hours or days.