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 Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime?

In 100GB table the records are increasing for each and every seconds.How can I do partition with out affecting the incoming records?

Using Percona-Mysql 5.5.30.Innodb-file-per-table was enabled.

Please tell me the steps.

share|improve this question

2 Answers 2

You may use one of the online-alter-table tools: these are specialized scripts which simulate an ALTER TABLE operation, using TRIGGERs and shadow tables, and which allow for non-interruptive process. Thus, they will copy your table in small chunks into a shadow table even while you are modifying (inserting, deleting, updating) your table, synching the two.

They end with a very brief (up to a few seconds, typically sub second) lock, where your original table is thrown away and the "shadow" table kicks in to replace it.

Two such tools are:

  1. oak-online-alter-table, as part of the openark-kit (disclosure: I'm author of this tool). Been around since 2009 and the first of its kind.
  2. pt-online-schema-change, as part of the percona-toolkit; later arrival but more sophisticated and more tested.
share|improve this answer

I looked at the two suggested tools above but had trouble installing them on my system. (I have access to My DB not the whole server.)

If the updates to your table is only additions, no changes in history, the process to partition (or add indexes) to the table is a lot easier and reasonable to hack yourself without external tools. My solution was to make a short script that:

  • Create a new table with partitions and new indexes.
  • Create and execute a temporary stored procedure that copies batches of records, oldest first to the new table. (Limiting the batch size and sleeping a short while between batches limit the impact on the application.)
  • Create and execute an other temporary stored procedure that:
    • copies the records added while the previous one was running
    • notes the last copied record
    • sets any autoinc values of the new table high enough
    • uses "RENAME TABLE" to switch the two tables
    • copies the few records that came in during this operation from the old table to the new one.

Splitting the procedures above into two made them easier to test.

share|improve this answer

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.