List:General Discussion« Previous MessageNext Message »
From:Walter Heck Date:February 10 2009 11:48am
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
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