Hooray! http://code.google.com/p/mysql-master-master/
Am I crazy to be considering replicating 500+ databases? I think so... On Tue, Feb 10, 2009 at 12:11 PM, Michael Addyman < michael.addy...@googlemail.com> wrote: > Walter, this is exactly why we went for separate application instances > initially - it is the most flexible solution for scaling. > > However, we have since discovered that it's actually a lot more work to > manage than we anticipated! > > We would love to continue using separate application instances (allowing us > to move instances around depending on load), but setting up and maintaining > replication is very time consuming. > > Does anyone know of any scripts / tools to ease replication / clustering > setup / administration / maintenance? > > Thanks again, > > Michael > > > On Tue, Feb 10, 2009 at 11:48 AM, Walter Heck <li...@olindata.com> wrote: > >> I think it would be good to think about scaling a bit more. What if >> your requirements change from 500 application instances to 5000 >> instances? It is good to go with a solution now that can easily scale >> over to multiple servers. Also, it would probably be good if you could >> move databases over to other database servers when they require more >> (or less) peformance. >> >> From these two requirements I would at least put every instance in >> it's own database. That way, if you develop an easy way to move a >> database over to another server easily and automated, you have an >> extremely scalable approach. Then, you could have each database server >> tuned the same way and just kind of "load balance" them by moving >> databases from one server to another. If you don't want to change the >> application's configuration every time a database moves to another >> server, you could look at a simple MySQL proxy installation to hide >> the server a database is on from the client application. >> >> These are my 2 cents :) >> >> Walter >> >> OlinData: Professional services for MySQL >> Support * Consulting * Administration >> http://www.olindata.com >> >> >> >> On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman >> <michael.addy...@googlemail.com> wrote: >> > Thanks for your comments Mike. >> > >> > 1. The largest table has 48 columns, the second largest 20 columns, and >> the >> > remainder less than 10 columns. >> > >> > 2. Each application instance (~30 tables) is between 50MB and 1GB. >> > >> > 3. Application instances are separate for many reasons including >> > infrastructure/scaling flexibility and security. >> > >> > 4. Transactions and row locking are required. >> > >> > 5. Mostly writes, closely followed by updates, then reads >> (out-of-database >> > caching handles most reads) >> > >> > I have now thought of having 1 table type per database (i.e. ~30 >> databases). >> > This would be easier and cheaper to manage than hundreds of databases, >> and >> > would also allow databases to be finely tuned to the table type, size, >> > workload and writes : updates : reads ratio. >> > >> > However, re-developing the database layer to achieve this looks >> incredibly >> > difficult. >> > >> > An easy solution would be to have ~100 instances per database, resulting >> in >> > ~3000 tables per database, and ~5 database clusters. >> > >> > I think my final suggestion is the most suitable. >> > >> > What would your recommendations be? >> > >> > Many thanks >> > >> > Michael. >> > >> > On Tue, Feb 10, 2009 at 6:01 AM, mos <mo...@fastmail.fm> wrote: >> > >> >> At 05:03 PM 2/9/2009, Michael Addyman wrote: >> >> >> >>> Dear Geniuses, >> >>> >> >>> I have an application requiring ~30 InnoDB tables, which needs to >> scale up >> >>> to at least 500 application instances (500 instances * ~30 tables = >> 15,000 >> >>> tables). >> >>> >> >> >> >> Some of the questions people are going to ask are: >> >> How large are each of the 30 tables? # of rows and physical size? >> >> What's the reason for having 500 separate application instances? >> Security? >> >> You're using InnoDb because you need transactions? Row locking? >> >> What percentage of the queries will be updates compared to reads? >> >> >> >> Discussions in the archives suggest I would be better off having >> >>> independent >> >>> databases for each of the application instances (i.e. 500 databases). >> >>> >> >>> However, it seems this would be much more difficult/expensive to >> >>> manage/replicate/cluster than a single large database containing >> 15,000 >> >>> tables. >> >>> >> >>> Storing all the data from all the application instances in ~30 large >> >>> tables >> >>> is not possible. >> >>> >> >>> Please could you give me your recommendations and experience? >> >>> >> >> >> >> By creating 500 separate instances you are of course creating 500x the >> >> amount of work. >> >> >> >> Mike >> >> >> >> -- >> >> MySQL General Mailing List >> >> For list archives: http://lists.mysql.com/mysql >> >> To unsubscribe: >> >> http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com >> >> >> >> >> > >> > >
