List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 10 2009 3:54pm
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
Hi Michael,

On Mon, Feb 9, 2009 at 6:03 PM, Michael Addyman
<michael.addyman@stripped> 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).
>
> 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?
>
> Many thanks,
>
> Michael
>

This is not an easy question to answer without knowing a lot about
your application's workload, which I suspect you will not be able to
provide information on until you actually get some load.

When you get a lot of InnoDB tables, one thing I can tell you is that
the stock InnoDB will chew up a lot of memory for the data dictionary.
 We've recently patched InnoDB to alleviate this:
http://www.percona.com/docs/wiki/patches:innodb_dict_size_limit

If I were you I'd just go with your best educated guess, and when you
get enough load to measure (not enough that you think you're going to
be in trouble soon -- don't wait that long), call for expert help to
find the most expensive parts of the app, and decide which are going
to be hard to scale.  It's usually difficult to predict in advance,
but if you get some non-trivial load, you can then measure and have
plenty of time to do something about what you find out.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
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