From: Martin Gainty Date: December 24 2012 1:14pm Subject: RE: Animation studio asset management List-Archive: http://lists.mysql.com/mysql/228845 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_dbdb690d-7ef0-40f5-8604-780813e1813d_" --_dbdb690d-7ef0-40f5-8604-780813e1813d_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable To add to Admiral Nelsons observations As your database grows your options are to either scale up (boost Memory an= d Disks) or scale out (add more MySQL nodes) If you want to add MySQL additional nodes (without incurring resultant) dow= ntime you may wish to consider MySQL Clustering http://books.google.com/books?id=3D_3nERYD9xqcC&pg=3DPA2&lpg=3DPA2&dq=3DMYS= QL+and+%22Reasons+to+Cluster%22&source=3Dbl&ots=3DL_i0E9iBZe&sig=3DZUqEGux6= YLa7lrtfQ_rU5pIOd14&hl=3Den&sa=3DX&ei=3DIVPYUJXbAbDJ0AHiq4H4BA&ved=3D0CDIQ6= AEwAA#v=3Donepage&q=3DMYSQL%20and%20%22Reasons%20to%20Cluster%22&f=3Dfalse Boxing Day is December 26thMartin Gainty=20 ______________________________________________=20 Please do not alter or otherwise disrupt this transmission. > Date: Sun=2C 23 Dec 2012 22:39:01 -0800 > Subject: Re: Animation studio asset management > From: chadvernon@stripped > To: michael@stripped > CC: mysql@stripped >=20 > Thank you for the responses. >=20 > The database would only be accessed internally by about 30-60 users max. > An average show could add anywhere from 5=2C000-20=2C000 records over a p= eriod > of a few months. We would do maybe 3-4 shows a year. > Maybe a few dozen records would be inserted daily=2C updates would not be= as > common. > About 20-30 users would occasionally be running queries simultaneously=2C > worst case would be every employee (maybe 50-75) which would be highly > unlikely. > We currently have no dedicated database developers or design experts=2C > although if we grew to 50-75 we would probably consider hiring one (or tw= o). > There are not really any security requirements as the server is local to > our network and won't contain sensitive information. >=20 > My gut feeling is to go with the simplest solution and use one database= =2C > however since I'm not a database design expert=2C I want to make sure I'm= not > missing anything important. If and when we get over 100k records in a > single table=2C would we need to worry about performance then? I'm not > really familiar with what the scale of a large database is. If Dan's > statement of millions of rows is correct=2C then I probably don't need to > consider size as one of our performance considerations as it would take u= s > quite a long time to get that many entries. Also=2C many of these record= s > would be related to older shows and wouldn't be accessed as much. >=20 > Thanks again for your expertise. >=20 > Chad >=20 >=20 > On Sun=2C Dec 23=2C 2012 at 9:14 PM=2C Michael Anderson > wrote: >=20 > > Database performance (like system performance) is always driven by a gi= ve > > and take process=2C a trade-off between resources (this includes design > > expertise) available=2C and the required results. > > > > How many records are you dealing with? > > How often are records are being added and/or updated? > > How many users will be running queries simultaneously=2C number of read= only=2C > > and number of updates and inserts? > > > > If only a handful of users=2C and less than 100=2C000 records=2C that r= arely > > change=2C then you'll probably never have performance problems. > > > > However=2C it you have 100=2C000 users running queries simultaneously= =2C and > > changing records regularly=2C then you may have some issues to address. > > Their are literally hundreds of solutions to performance problems=2C an= d the > > right solutions will depend on your particular requirements. > > > > Security=2C is much the same=2C dependent on the security requirements. > > Example: > > Security auditors claimed that the marketing product database was not > > secure. > > They said that it needed to be more secure. > > The CEO of the company asked a simple question=2C he said: > > "Whats the worst that could happen if an outsider gains access to this > > database?" > > Answer: "They might buy one of your products!" > > Lesson learned: Don't waste your time securing data that doesn't need t= o > > be secured. > > > > So=2C what are your requirements? > > > > > > > > > > > > On 12/23/2012 09:11 PM=2C Chad Vernon wrote: > > > >> Hi there=2C > >> I'm designing an animation studio database to track assets across mult= iple > >> shows. My original design was to have a "show" table to track which a= sset > >> belongs to which show with a show_id column. A couple coworkers sugge= sted > >> having a database per show because they are worried about performance = and > >> reliability vs a single database as the number of shows grows. I don'= t > >> think reliability would be different because it's all on the same MySQ= L > >> server anyways. Is that assumption correct? I've read a few articles > >> about multi-tenant database design=2C however the motivation for multi= ple > >> databases seems to be security which doesn't apply to us. Am I correc= t in > >> trying to push a single database design for this? If it helps=2C we h= ave no > >> dedicated dba and currently a single mysql server machine. > >> > >> Thanks=2C > >> Chad > >> > >> > > = --_dbdb690d-7ef0-40f5-8604-780813e1813d_--