From: Martin Gainty Date: February 10 2009 5:17pm Subject: RE: InnoDB: Thousands of Tables or Hundreds of Databases? List-Archive: http://lists.mysql.com/mysql/216251 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_5846ab1f-e985-434d-bb1e-17c106b64dac_" --_5846ab1f-e985-434d-bb1e-17c106b64dac_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I vote for 1 table per TableType this will keep your DB schema consistent with Architecture Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > Date: Tue=2C 10 Feb 2009 11:03:46 -0600 > To: mysql@stripped > From: mos99@stripped > Subject: Re: InnoDB: Thousands of Tables or Hundreds of Databases? >=20 > At 04:30 AM 2/10/2009=2C you wrote: > >Thanks for your comments Mike. > > > >The largest table contains 48 columns (objects)=2C 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=2C > >followed by writes=2C then reads (application mostly uses memcached and = other > >forms of caching for reads). > > > >I have since thought of having 1 table type per database=2C resulting in > >'only' ~30 databases=3B this would be 'easier' to maintain=2C and each d= atabase > >(containing 1 table type) could be optimised for its ratio of reading : > >writing : updating. > > > >However=2C this approach would require a LOT of work to re-write the > >application's database layer. > > > >What approach would be best? >=20 > Michael=2C > Does the saying "between a rock and a hard place" sound=20 > familiar? :-) >=20 > I feel you're going to have to create a test suite to benchmark both=20 > solutions thoroughly before you start on the application code. You're goi= ng=20 > to find pro's and con's with both designs but after benchmarking you're=20 > going to know which one performs better both from a speed viewpoint and=20 > maintenance viewpoint. The more time you spend testing the design=2C the = more=20 > confidence you'll have that it works and the less chance of throwing it=20 > away and starting over later on down the road. Then you'll also be able t= o=20 > present to your client some hard facts about each design. >=20 > Mike >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ Windows Live=99: Keep your life in sync.=20 http://windowslive.com/howitworks?ocid=3DTXT_TAGLM_WL_t1_allup_howitworks_0= 22009= --_5846ab1f-e985-434d-bb1e-17c106b64dac_--