List:General Discussion« Previous MessageNext Message »
From:Michael Addyman Date:February 10 2009 4:34pm
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
I'll take that on board.

Thanks for your advice, mysql-master-master, Maatkit, mysqlperformanceblog,
your patches and community support!

On Tue, Feb 10, 2009 at 3:54 PM, Baron Schwartz <baron@stripped> wrote:

> 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