Migrating very large databases

Currently, tweetburner.com has one table which is really big. We’re busy optimizing our database to make it more scalable and run faster on big calculations. For this, we need to set up multiple replication servers.
 
Normally you would just dump all tables into a sql or text file to copy them over to another database server. However, with a table of over 21 million (yes!) rows the SELECT query will lock the table for updates and therefore other reads so our service becomes unreachable.
 
By migrating your database into chunks you can make this process less painful.
 
We noticed that selecting 100,000 rows from a table it takes up to 0.8 second to run the query. If you select a million rows it can take up to 5 seconds. Apperantly the number grows exponentially. Keep in mind what selecting 21 million rows will take.
 
With a little scripting we can copy this table in about 22 times. 22 * 0.8 with 22 times a table lock of up to 1s this is less painful for your service and thus your users.
 
Conclusion: When migrating or backing up large tables, split them into acceptable chunks depending on row and table size so you won’t deadlock your service. Do this my selecting each 100,000 rows, put them in a dump file and copy them to your backup location. You can merge them after that.

Posted via email from Shaping Clouds Notes