MySQL Forums
Forum List  »  Partitioning

Re: Should I partition?
Posted by: Rick James
Date: March 21, 2013 08:49PM

Should you partition? Yes, if for no reason other than the daily purge.

Circular list of partitions? No. Partition pruning will not work unless you always hit just one partition. Instead use RANGE; then DROP PARTITION + REORGANIZE PARTITION daily. Details:

Compress? InnoDB has a ROW_FORMAT=COMPRESS, but it may or may not help. 5.6 has significantly improved the utility of that.

Engine=ARCHIVE? No, unless you can live without indexes. (As you pointed out.)

MyISAM? It _might_ be smaller than InnoDB+COMPRESS.

Let's see the SHOW CREATE TABLE. There may be tips on how to shrink the data.

CDRs? IPv4? IPv6? (I may have tips there.)

Let's see the SELECTs that will hit the table.

INDEXes aren't free (in disk space) (except for the PRIMARY KEY in InnoDB).

30*20G = 600GB -- that's a medium sized disk these days. Under $100. (However, I would not want to put it on a drive smaller than twice that -- for maintenance issues, like ALTER.)

Do you need all the details? Or can you summarize things every night? Or every hour? That might shrink things down to 60GB.

Experiment! Take 3 hour's worth of data, build the PARTITIONs, etc, and look at the sizes (SHOW TABLE STATUS). Try the SELECTs (but realize that the full data could be much slower).

Options: ReplyQuote

Written By
March 19, 2013 02:57PM
Re: Should I partition?
March 21, 2013 08:49PM
April 03, 2013 10:08AM
April 04, 2013 11:35PM

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.