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
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?
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
> 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
>> 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
>> Storing all the data from all the application instances in ~30 large
>> 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.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: