StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The simple answer is that there is no simple answer to these sort of problems, the only way to find out what works for your scenario is to invest R&D time into it. </p> <p>The question is hard to answer because the performance requirements aren't spelled out by the OP. It appears to be 75M/year records over a number of customers with a write rate of num_customers*1minute (which is low), but I don't have figures for the required read / query performance. </p> <p>Effectively you have already a <a href="http://en.wikipedia.org/wiki/Shard_%28database_architecture%29" rel="nofollow">sharded</a> database using <a href="http://en.wikipedia.org/wiki/Partition_%28database%29" rel="nofollow">horizontal partitioning</a> because you're storing each customer in a seperate table. This is good and will increase performance. However you haven't yet established that you have a performance problem, so this needs to be measured and the problem size assessed before you can fix it.</p> <p>A NoSQL database is indeed a good way of fixing performance problems with traditional RDBMS, but it will not provide automatic scalabity and is not a general solution. You need to find your performance problem fix and then design the (nosqL) data model to provide the solution.</p> <p>Depending on what you're trying to achieve I'd look at <a href="http://www.mongodb.org/" rel="nofollow">MongoDB</a>, <a href="http://cassandra.apache.org/" rel="nofollow">Apache Cassandra</a>, <a href="http://hbase.apache.org/" rel="nofollow">Apache HBase</a> or <a href="http://hibari.sourceforge.net/" rel="nofollow">Hibari</a>.</p> <p>Remember that NoSQL is a vague term typically encompassing</p> <ul> <li>Applications that are either performance intensive in read or write. Often sacrificing read or write performance at the expense of the other.</li> <li>Distribution and scalability</li> <li>Different methods of persistency (RAM/Disk)</li> <li>A more structured/defined access pattern making ad-hoc queries harder.</li> </ul> <p>So, in the first instance I'd see if a traditional RDBMS can achieve the required performance, using all available techniques, get a copy of <a href="http://rads.stackoverflow.com/amzn/click/0596101716" rel="nofollow">High Performance MySQL</a> and read <a href="http://www.mysqlperformanceblog.com/" rel="nofollow">MySQL Performance Blog</a>.</p> <h2>Rev1:</h2> <p>In light of your comments I think it is fair to say that you could achieve what you want with one of the above NOSQL engines. </p> <p>My primary recommendation would be to get your data model designed and implemented, what you're using at the moment isn't really right.</p> <p>So look at <a href="http://en.wikipedia.org/wiki/Entity-attribute-value_model" rel="nofollow">Entity-attribute-value model</a> as I think it is exactly right for what you need. </p> <p>You need to get your data model right before you can consider which technology to use, being honest modifying schemas dynamically isn't a datamodel.</p> <p>I'd use a traditional SQL database to validate and test the new datamodel as the management tools are better and it's generally easier to work with the schemas as you refine the datamodel.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload