List:General Discussion« Previous MessageNext Message »
From:Michael Addyman Date:February 10 2009 10:30am
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
Thanks for your comments Mike.

The largest table contains 48 columns (objects), the second largest 20
columns (users) and all the rest are less than 10 columns. The instance
sizes range from 10MB to 1GB.

Transactions and row locking are required. Most queries are updates,
followed by writes, then reads (application mostly uses memcached and other
forms of caching for reads).

I have since thought of having 1 table type per database, resulting in
'only' ~30 databases; this would be 'easier' to maintain, and each database
(containing 1 table type) could be optimised for its ratio of reading :
writing : updating.

However, this approach would require a LOT of work to re-write the
application's database layer.

What approach would be best?

Thanks again,

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