patternsqlMinor
What are the sharding heuristics for MySQL - number of records per table & number of tables per instance?
Viewed 0 times
tablesthenumberwhatheuristicsareperrecordsinstancemysql
Problem
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
Solution
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.)
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.)
Context
StackExchange Database Administrators Q#3011, answer score: 3
Revisions (0)
No revisions yet.