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 <mo...@fastmail.fm> 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=michael.addy...@gmail.com > >
