Re: Multiple Tables Split By Date
Posted by: Rick James
Date: February 23, 2013 12:10PM

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

Options: ReplyQuote


Subject
Written By
Posted
February 20, 2013 05:40AM
February 21, 2013 11:23PM
Re: Multiple Tables Split By Date
February 23, 2013 12:10PM
February 25, 2013 11:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.