I'm using a recent release of MySql with sqlAlchemy as the ORM... but could move to a different sql if there's a better one if needed.
Since I never done this before, we started prototyping the database and the code side, to see how complex sharding would be. the first issue that came up is the ID, it's not unique, there are many Asset with ID 1 for example. So the way around that is to create a truly unique_id across all the databases. so the Asset ID is 1 but the uniqueID is: somethinglong001101 the next step would be the code side, how can we hide the complexity of the sharding from the tools and pipeline, we don't want them to "know" about it... I hope this prototype will show if it's worth it or not... worth it or not, I guess it comes down to is the sharding gives us flexible but at the same time the complexity is hidden from our day to day tools/code :) On Monday, 29 September 2014 18:47:56 UTC-7, Justin Israel wrote: > > You are referring to this generic wiki about partitioned data, but are you > specifically talking about MySQL as your target? The reason I ask is > because some databases have built in concepts of sharding and routing. And > also some databases may use row locking instead of table locking. Maybe it > is best to investigate the right database for your needs before looking at > trying to implement a manual mysql partitioning abstraction? > > I agree about some of the advantages, but I wonder if they outweigh the > disadvantages. It would mean that each time you create a new project, a new > database must be created before applications will be able to function. And > you have to maintain your own routing logic, which may or may not be a big > deal at all. > > Has it proven difficult to extract a single projects data set for backups, > just using queries? And is your project dataset so large that it needs to > be off-lined with each final project? > hey :) > > Question what you think/know about Horizontal Partitioning of each > project data into a "shard" database? > distributed project database in a sense that all the project have the same > scheme But they have separated database. > http://en.wikipedia.org/wiki/Partition_(database) > > for example projectA have tables (Shot, Asset) and projectB have the same > tables with the same fields, > but projectA and projectB are on different database or even servers. > > And using a common lookup project table, you can find which project points > to which database, for you code to work with > > Why we're thinking about it? > having each project individual database have some advantages: > - easier to backup and restore ( much smaller set of data ) > - easier to archive together with the entire project files ( cause that DB > is isolated from the rest of the projects data ) > - less table locking wait time, cause each project have different database > and tables > > badness coming from this? > - complexity ... each item needs a unique_id across all the tables, > something like ( projectID_ID ) > - complexity ... where do you store common tables ( Users for example ) > - you can't do "join" across databases... do get all artists Tasks across > shows become multiple queries, one per project > > any thoughts? > > -- > You received this message because you are subscribed to the Google Groups > "Python Programming for Autodesk Maya" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/python_inside_maya/6dbde745-cfdf-4609-ab09-714f99d37ba4%40googlegroups.com > > <https://groups.google.com/d/msgid/python_inside_maya/6dbde745-cfdf-4609-ab09-714f99d37ba4%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/python_inside_maya/f3a798ff-1d8f-41da-bd91-9017c522f4bf%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
