To add to Admiral Nelsons observations
As your database grows your options are to either scale up (boost Memory and
Disks) or scale out (add more MySQL nodes)
If you want to add MySQL additional nodes (without incurring resultant)
downtime you may wish to consider MySQL Clustering
http://books.google.com/books?id=_3nERYD9xqcC&pg=PA2&lpg=PA2&dq=MYSQL+and+%22Reasons+to+Cluster%22&source=bl&ots=L_i0E9iBZe&sig=ZUqEGux6YLa7lrtfQ_rU5pIOd14&hl=en&sa=X&ei=IVPYUJXbAbDJ0AHiq4H4BA&ved=0CDIQ6AEwAA#v=onepage&q=MYSQL%20and%20%22Reasons%20to%20Cluster%22&f=false
Boxing Day is December 26thMartin Gainty
______________________________________________
Please do not alter or otherwise disrupt this transmission.
> Date: Sun, 23 Dec 2012 22:39:01 -0800
> Subject: Re: Animation studio asset management
> From: chadver...@gmail.com
> To: mich...@j3ksolutions.com
> CC: mysql@lists.mysql.com
>
> Thank you for the responses.
>
> The database would only be accessed internally by about 30-60 users max.
> An average show could add anywhere from 5,000-20,000 records over a period
> of a few months. We would do maybe 3-4 shows a year.
> Maybe a few dozen records would be inserted daily, updates would not be as
> common.
> About 20-30 users would occasionally be running queries simultaneously,
> worst case would be every employee (maybe 50-75) which would be highly
> unlikely.
> We currently have no dedicated database developers or design experts,
> although if we grew to 50-75 we would probably consider hiring one (or two).
> There are not really any security requirements as the server is local to
> our network and won't contain sensitive information.
>
> My gut feeling is to go with the simplest solution and use one database,
> however since I'm not a database design expert, I want to make sure I'm not
> missing anything important. If and when we get over 100k records in a
> single table, 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, then I probably don't need to
> consider size as one of our performance considerations as it would take us
> quite a long time to get that many entries. Also, many of these records
> would be related to older shows and wouldn't be accessed as much.
>
> Thanks again for your expertise.
>
> Chad
>
>
> On Sun, Dec 23, 2012 at 9:14 PM, Michael Anderson
> <mich...@j3ksolutions.com>wrote:
>
> > Database performance (like system performance) is always driven by a give
> > and take process, a trade-off between resources (this includes design
> > expertise) available, 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, number of readonly,
> > and number of updates and inserts?
> >
> > If only a handful of users, and less than 100,000 records, that rarely
> > change, then you'll probably never have performance problems.
> >
> > However, it you have 100,000 users running queries simultaneously, and
> > changing records regularly, then you may have some issues to address.
> > Their are literally hundreds of solutions to performance problems, and the
> > right solutions will depend on your particular requirements.
> >
> > Security, is much the same, 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, 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 to
> > be secured.
> >
> > So, what are your requirements?
> >
> >
> >
> >
> >
> > On 12/23/2012 09:11 PM, Chad Vernon wrote:
> >
> >> Hi there,
> >> I'm designing an animation studio database to track assets across multiple
> >> shows. My original design was to have a "show" table to track which asset
> >> belongs to which show with a show_id column. A couple coworkers suggested
> >> 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 MySQL
> >> server anyways. Is that assumption correct? I've read a few articles
> >> about multi-tenant database design, however the motivation for multiple
> >> databases seems to be security which doesn't apply to us. Am I correct in
> >> trying to push a single database design for this? If it helps, we have no
> >> dedicated dba and currently a single mysql server machine.
> >>
> >> Thanks,
> >> Chad
> >>
> >>
> >
