MySQL Forums
Forum List  »  Partitioning

Re: Should I partition?
Posted by: NOT_FOUND NOT_FOUND
Date: April 03, 2013 10:08AM

I'm currently at the "architecture" or "pie in the sky" point.

I found/edited a few procedures for automatically creating new partitions and dropping old ones (based on date, since that's what I'll be rotating around). They seem to work just great.

MyISAM isn't an option since MySQL doesn't support compressing individual partitions. That leaves InnoDB's row level compress or ARCHIVE as the only options. I'm not even sure if I'll be saving space at this point since I'm trying to rely on INET_ATON and storing as much as I can as the smallest possible integer value. I read a few forums which basically stated that if you do this, compression might take more space (ung).

All of my fields are either one form of INT or date. I currently have no string/char fields. I'm looking at having to add a single string field, but that might be a future enhancement to the record.

The only downside of INET_ATON that I fear is if someone wants to do a "like" search for "10.0.0.%". I worry that it would have to convert the entire database (or rather, the rest of the searching fields) to find matches.

The full record is needed for the entire duration of the database (20-30 days).

I think I'm going to have to end up doing some experiments, as you suggested. Once I get the basics completed, the process done, and more records to play with I can try different configurations. For example, I don't know what SELECTS will *really* look like since we're talking theory-craft. Once you give users a chance, they will surprise you with some wonky query.

I was playing with the idea of subpartitioning based on the sending device (limited list) to help searching with an ARCHIVE database type but from your webpage you don't seem to think fondly of subpartitioning ;)

Options: ReplyQuote


Subject
Views
Written By
Posted
2760
March 19, 2013 02:57PM
1594
March 21, 2013 08:49PM
Re: Should I partition?
1507
April 03, 2013 10:08AM
1430
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.