0

Our DB is hosted in AWS MySQL RDS. The data is such that each company that signs up gets its own set of tables which then receive enormous amount of data in them.

Currently we want to move a chunk of rows from existing company table (having 200+million rows) to a new company table. We need to be careful of the auto increment ID in the new table and make sure all data is moved else rollback will be required.

0

If you can stop writes long enough (minutes? hours?) to do the copy (and delete?), then there is no issue. Simply copy the current ids.

If the two databases need to somehow not duplicate ids, that is another problem; so state.

If ... -- Please update your question.

Data Archiving

Assuming you want to move "old" data to somewhere else, while leaving the "recent" data intact, PARTITIONing is very efficient -- once it is set up.

The setup requires a huge ALTER TABLE to add partitioning. However, with a 'small' downtime, that can (probably) be done with pt-online-schema-change or gh-ost.

The partitioning would be BY RANGE and each partition would correspond to the chunk you want to 'archive'. However, I recommend no more than about 50 partitions (for other reasons).

With the partitioning in place, you can use "transportable tablespaces" to peel off the oldest partition, turning it into a table. This is fast. Then you can do whatever you like with that table without impacting the 'real' table.

This would require adding a new partition at the same time (essentially zero cost).

Some further discussion.

  • Since the data is constantly coming through API calls, how do you suggest to stop the writes? If a there's a way, we can stop for minutes but not hours. By the way this activity is more of data archiving which will happen more often so you can suggest some other solution too. – Asad M Apr 24 '17 at 5:57
  • What do you do when there is a power failure? Network failure? Lose data? Stall the clients of the API? Another tack... Do you have Replication setup on RDS? – Rick James Apr 28 '17 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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