Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the back-of-the-envelope calculations you all used

  • Why do you need to shard, query performance or some other reason? What is the average size of your metadata? May 29, 2011 at 14:00
  • Just size. We expect to have 10B rows. May 30, 2011 at 19:11
  • 10B doesn't sound so large that you will be forced to shard - unless you are storing large amounts of data for each row. The cost of adding a great deal of complexity would usually outweigh the cost of setting up one database this size. Databases are fantastic at storing large numbers of rows - 10B rows still probably would take no more than 5 block scans to find the one you are looking for with a typical btree index. May 31, 2011 at 7:39
  • Thanks Jack. Yep, we want to keep complexity low, unless warranted. Jun 1, 2011 at 6:27
  • Hello Krishna Sankar ,Could you please let me know how is your data management , because I see you have 10 billion rows in database ( in a single table ) , I am wondering how you are managing . Thanks - Kiran
    – Bujji
    Sep 8, 2012 at 14:28

3 Answers 3


These URLs will tell you about the data pointer option in MyISAM tables.



According to these URLs, a MyISAM table can theoretically be

256 TB (for myisam_data_pointer_size = 6 (default)
65536 TB (for myisam_data_pointer_size = 7 (maximum)

The largest InnoDB tablespace is 64TB.

The largest individual table can be 64TB if you enable innodb_file_per_table.

This URL will give you more numbers on largest BLOB, TEXT, VARCHAR, VARBINARY, LONGTEXT and BLOBs for InnoDB : http://dev.mysql.com/doc/refman/5.0/en/innodb-file-space.html

Click here for Maximum Number of Columns Per Table

As far as the maximum number of tables per database, that is OS dependent. If there is limit to the number of files in a folder in a given OS (let's call it OSFILEMAX), MAXTABLES is OSFILEMAX divided by 3 (.frm, .MYD, .MYI) for MyISAM tables, MAXTABLES is OSFILEMAX by 2 (.frm, .ibd) if using InnoDB with innodb_file_per_table enabled, and MAXTABLES is OSFILEMAX (.frm) if using InnoDB with innodb_file_per_table disabled.

I hope these URLs and other answers provided help you !!!


Sharding of individual items is not too difficult -- you just need a layer to figure out what machine to go to to get the one item.

Sharding of something that you need to scan or search can be messy. You would have to hand off the query to each possible machine, let them do the scan/search, get back the results, and stitch together them. GROUP BY, ORDER BY, and LIMIT all have hiccups. You end up doing some things both in the sharded machines and in the central aggregator.

You mentioned storing 10B metadata rows. That sounds like a few TB. That is easily handled in a single machine these days. The table could be PARTITIONed, but do not rush into that until you understand what PARTITION does and does not give you.

Meanwhile, your "objects" could be scattered around dozens, even thousands, of machines. Now you get two more problems -- knowing where to look, and dealing with crashes.

Knowing where to look -- a hash is simple, until you need more shards. Having a "lookup" adds another column to the metadata, and perhaps an index. I prefer a compromise between the two.

Crashes -- with a thousand machines, you should plan on one dying every week. Where's its backup? Or do you have another copy online? Is two copies safe enough? Are all thousand machines in the same location, thereby at risk of a single tsunami, tornado, or whatever?

Oh, back to the back of the envelope -- 10B INSERTs, one row at a time, could take a year to finish. Please consider LOAD DATA and/or batched INSERTs.

INDEXing the monster metadata table versus caching. Let's say you have 5 indexes other than AUTO_INCREMENT or CURRENT_TIMESTAMP -- that will be 5 random disk hits to update the indexes. With terabytes of stuff, there is no way to cache it effectively. Oops, you are now limited to about 20 rows inserted per second, no matter how you do the INSERTs. With about 30M seconds/year, that leads a decade to insert your 10B rows. Watch out for indexing! Adding RAID striping helps my a small factor. SSDs ($$$!) helps by maybe a factor of maybe 5. (And it would be hard to get much past 1TB of SSDs on a single machine.)

  • Rick, Thanks. Good helpful insights. I don't have enough reputation to vote up ;o( Jun 1, 2011 at 6:28
  • On the hash vs lookup, we are leaning towards lookup and embedding the shard id as a smart key with the object. Not an elegant solution, but looks like it will work. Jun 1, 2011 at 6:33

If you're interested in Sharding, check out http://www.scalebase.com - they provide a transparent sharding solution, and can also recommend the correct sharding policy for you

  • 1
    Hi Liran, consider this a brief warning. I encourage you to read this meta.stackexchange.com/questions/88642/… and then to peruse our FAQ May I promote products or websites I am affiliated with here? where it states "May I promote products or websites I am affiliated with here? Be careful, because the community frowns on overt self-promotion and tends to vote it down and flag it as spam." ~ Bear in mind we absolutely value your expert opinion, and really do want you to contribute. Help us help you!
    – jcolebrand
    Jun 12, 2011 at 17:06
  • 1
    What this means to you in general is a) if you're going to overtly advertise for the company you're affiliated with, please give full disclosure, and b) we're not against you advertising your own company's services, but the community values members who contribute, and will give more weight to someone who gives a little of themselves in addition to the asking people to purchase your product. Welcome to dba.se, and we hope to see you around more.
    – jcolebrand
    Jun 12, 2011 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.