List:General Discussion« Previous MessageNext Message »
From:Michael Addyman Date:February 10 2009 1:57pm
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
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.addyman@stripped> 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 <lists@stripped> 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.addyman@stripped> 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 <mos99@stripped> 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=1
>> >>
>> >>
>> >
>>
>
>

Thread
InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
  • Re: InnoDB: Thousands of Tables or Hundreds of Databases?mos10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?mos10 Feb
        • RE: InnoDB: Thousands of Tables or Hundreds of Databases?Martin Gainty10 Feb
          • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Johan De Meersman10 Feb
        • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Walter Heck10 Feb
        • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
          • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
            • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Johan De Meersman10 Feb
              • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
  • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Baron Schwartz10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb