Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very large table approx 28GB and that is continually increasing. I am thinking about partitioning my table.

The table is InnoDB and The File Per Table is enabled

I have a field name created which is an integer field and stores the timestamp so I was thinking about creating the table like this:

ALTER TABLE TABLE_NAME PARTITION BY RANGE (created)
(
PARTITION p0 VALUES LESS THAN (1325356200) ENGINE = InnoDB, # Data before 2012
PARTITION p1 VALUES LESS THAN (1333218600) ENGINE = InnoDB, # Data for JAN,FEB,MARCH YEAR 2012
PARTITION p2 VALUES LESS THAN (1341081000) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p3 VALUES LESS THAN (1349029800) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p4 VALUES LESS THAN (1356978600) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p5 VALUES LESS THAN MAXVALUE              # DATA for Next years   
) ;

I have partitioned the year 2012 in Quarters(As it is our requirement) and will also do the same for year 2013 but not done here.

Questions:

  1. Does partitioning boost INSERTs and SELECTs Performance
  2. How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?
  3. Do I need to use the field created in the queries so that the optimizer can use the partition?
  4. Are there any limitation's or risks by partioning this table?
  5. Is there any way to ALTER the table without downtime?
share|improve this question
add comment

2 Answers

yes you can partition is very useful in read and write table.i had try it in my 15 GB table and select operation become 50-60 % fast .
for ALTER better way:

  1. make copy of your current table structure(only structure not data ).
  2. rename original table to tablename_old.
  3. rename new blank table to original table name.
  4. now you can alter table with out any down time. mean time data is not accessible . after completion of process make this process reverse order .
share|improve this answer
add comment

Does partitioning boost INSERTs and SELECTs Performance

It depends. See the PARTITION section of http://mysql.rjweb.org/doc.php/ricksrots

How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?

ALTER (usually) takes a time proportional to the data size. Hour(s) for 28GB. OTOH, ALTER PARTITION may be much faster. DROP PARTITION is virtually instantaneous, hence useful for purging 'old' data. Be more specific about your ALTER question.

Do I need to use the field created in the queries so that the optimizer can use the partition?

The rules on indexes may seem screwball. Let's see the CREATE TABLE and the SELECT(s).

Are there any limitation's or risks by partioning this table?

No more than 1024 partitions, but 100 is 'too many', in my opinion. The biggest risk is that you will do all the work to do PARTITIONing, only to get no performance boost.

Is there any way to ALTER the table without downtime?

It depends -- be specific about the ALTER.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.