Split database content without outage

Split database content without outage

Post by igor.shm.. » Thu, 31 May 2012 04:32:06



Can you please help with advice?
I need to design a solution for a database which will grow and will require a horizontal split at some moment.
Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database.
My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users.
What are the ways to achieve it?

Thank you in advance,
-igorS

 
 
 

Split database content without outage

Post by Robert Klemm » Thu, 31 May 2012 05:47:33



Quote:> I need to design a solution for a database which will grow and will require a horizontal split at some moment.

Require the split because of what?

Quote:> Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database.
> My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users.

This cannot work since at least at one point you have to reconfigure
clients to access a second (third, fourth...) database.  Why do you
think you need to split the DB?  Did you consider using table
partitioning instead?  Or did you consider deleting old data?

Kind regards

        robert

 
 
 

Split database content without outage

Post by Mladen Gogal » Wed, 06 Jun 2012 08:17:50



> Can you please help with advice?
> I need to design a solution for a database which will grow and will
> require a horizontal split at some moment.
> Here is how I am planning to do it: Every record in every table has a
> shard number. After the database becomes too large (gets too many
> requests), the tables need to be horizontally split. It means that for
> every table all records with some specific shard numbers need to be
> moved to a new database.
> My plan is to (1) create a copy of the database on a new server
> (backup/restore?), (2) synchronize the databases (how?), and then (3)
> forward all requests for the moved shards to the new database. (I will
> get rid of the unneeded shards later). An important requirement: there
> should be no outage for the users.
> What are the ways to achieve it?

Use Oracle.

--
http://mgogala.byethost5.com

 
 
 

1. To split or not to split columns

I have an index char column in the format
aaaa-bbbbbb-cc-dd

where a,b,c and d are integers

Most of the time, the whole column will be searched for. But it will
sometimes be necessary to search for the substring in b or d (or a
combination of both) Thus if I split them up, they will need to be
indexed as well.

My question is: in terms of read and write performance,  is it better to
split them up into 4 indexed columns of integer instead or to keep them
in one indexed column of char?

If they should be kept in one column, what is the quickest way to
search? To use a regular expression?

  - Mathias

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

2. Launching application on desktop from Pocket Pc?

3. database reverted to 18hr old state after power outage

4. difference between TRANSPOSING and PITCH SHIFTING????

5. AT&T Database Glitch Caused '800' Phone Outage

6. ACPI or standardPC?

7. 4.0.8 doesn't compile with --without-innodb and --without-query-cache options

8. IE4 : So much hype for so little...

9. Split a Database into multiple databases

10. Splitting textarea values to put into a database

11. split database

12. raid vs splitting the database

13. how to split database and index