List:General Discussion« Previous MessageNext Message »
From:mos Date:February 10 2009 5:03pm
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
At 04:30 AM 2/10/2009, you wrote:
>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?

Michael,
             Does the saying "between a rock and a hard place" sound 
familiar? :-)

I feel you're going to have to create a test suite to benchmark both 
solutions thoroughly before you start on the application code. You're going 
to find pro's and con's with both designs but after benchmarking you're 
going to know which one performs better both from a speed viewpoint and 
maintenance viewpoint. The more time you spend testing the design, the more 
confidence you'll have that it works and the less chance of throwing it 
away and starting over later on down the road. Then you'll also be able to 
present to your client some hard facts about each design.

Mike

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