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
Tell me more
×
Database Administrators Stack Exchange is a question and answer site for
database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
|
|
These URLs will tell you about the data pointer option in MyISAM tables. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_myisam_data_pointer_size http://dev.mysql.com/doc/refman/5.0/en/full-table.html According to these URLs, a MyISAM table can theoretically be 256 TB (for myisam_data_pointer_size = 6 (default) 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.) |
|||||
|
|
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 |
|||||||||
|
