Search code examples
mysqldatabasesharding

database sharding strategy

For an online marketplace product under construction, I have a situation which requires implementing a database sharding solution. I am new to sharding and after reading the posts in this forum I feel a directory based sharding strategy using business entities will be suitable. But I am still not clear about the denormalization and data synchronization best practices to adopt with such a sharded solution. There will be 3 core entities, supplier, customer and order. I am planning to shard the database based on the supplier id as most of the processing on the order data will be carried out by the supplier admins. This will ensure that the orders for a supplier are fetched from a single db instance eliminating cross db fetches. However, in this case, when the customers view their order information that data will be residing in multiple db instances and will require multi database fetching. What is typically done when such scenarios come up in a sharded solution.

Solution

  • I think there is a 99.9% chance that you do not need sharding.

    You need sharding if:

    • Your database insert /update rate is close to, or is exceeding, the capacity of the highest spec server you can cost-effectively buy AND
    • You are already farming out most of your read queries, reporting, backups etc on to read-only replicated slaves
    • You have done functional partitioning to move any nonessential or unrelated update-heavy workloads off your master server

    If you cannot definitely say "yes" to all three of the above, you do not need to shard.

    Read

    http://www.mysqlperformanceblog.com/2009/08/06/why-you-dont-want-to-shard/