Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Schema Design at Scale

rick446
September 14, 2012

Schema Design at Scale

This deck is an overview of the process that 10gen went through to scale their MongoDB monitoring service MMS on a single unsharded replica set.

rick446

September 14, 2012
Tweet

More Decks by rick446

Other Decks in Technology

Transcript

  1. Who am I? • Now a consultant/trainer, but formerly... •

    Software engineer at SourceForge • Author of Essential SQLAlchemy • Author of MongoDB with Python and Ming • Primarily code Python Friday, September 14, 12
  2. The Inspiration • MongoDB monitoring service (MMS) • Free to

    all MongoDB users • Minute-by-minute stats on all your servers • Hardware cost is important, use it efficiently (remember it’s a free service!) Friday, September 14, 12
  3. Our Experiment • Similar to MMS but not identical •

    Collection of 100 metrics, each with per- minute values • “Simulation time” is 300x real time • Run on 2x AWS small instance • one MongoDB server (2.0.2) • one “load generator” Friday, September 14, 12
  4. Load Generator • Increment each metric as many times as

    possible during the course of a simulated minute • Record number of updates per second • Occasionally call getLastError to prevent disconnects Friday, September 14, 12
  5. Schema v1 • One document per metric (per server) per

    day • Per hour/minute statistics stored as documents { _id: "20101010/metric-1", metadata: { date: ISODate("2000-10-10T00:00:00Z"), metric: "metric-1" }, daily: 5468426, hourly: { "00": 227850, "01": 210231, ... "23": 20457 }, minute: { "0000": 3612, "0001": 3241, ... "1439": 2819 } } Friday, September 14, 12
  6. Update v1 • Use $inc to update fields in- place

    • Use upsert to create document if it’s missing • Easy, correct, seems like a good idea.... increment = { daily: 1 } increment['hourly.' + hour] = 1 increment['minute.' + minute] = 1 db.stats.update( { _id: id, metadata: metadata }, { $inc: update }, true) // upsert Friday, September 14, 12
  7. Problems with v1 • The document movement problem • The

    midnight problem • The end-of-the-day problem • The historical query problem Friday, September 14, 12
  8. Document movement problem • MongoDB in-place updates are fast •

    ... except when they’re not in place • MongoDB adaptively pads documents • ... but it’s better to know your doc size ahead of time Friday, September 14, 12
  9. Midnight problem • Upserts are convenient, but what’s our key?

    • date/metric • At midnight, you get a huge spike in inserts Friday, September 14, 12
  10. Fixing the document movement problem • Preallocate documents with zeros

    • Crontab (?) • NO! (makes the midnight problem even worse) db.stats.update( { _id: id, metadata: metadata }, { $inc: { daily: 0, hourly.0: 0, hourly.1: 0, ... minute.0: 0, minute.1: 0, ... } true) // upsert Friday, September 14, 12
  11. Fixing the midnight problem • Could schedule preallocation for different

    metrics, staggered through the day Friday, September 14, 12
  12. Fixing the midnight problem • Could schedule preallocation for different

    metrics, staggered through the day • Observation: Preallocation isn’t required for correct operation Friday, September 14, 12
  13. Fixing the midnight problem • Could schedule preallocation for different

    metrics, staggered through the day • Observation: Preallocation isn’t required for correct operation • Let’s just preallocate tomorrow’s docs randomly as new stats are inserted (with low probability). Friday, September 14, 12
  14. Performance with Preallocation • Well, it’s better • Still have

    decreasing performance through the day... WTF? Experiment startup Friday, September 14, 12
  15. Performance with Preallocation • Well, it’s better • Still have

    decreasing performance through the day... WTF? Experiment startup Friday, September 14, 12
  16. Problems with v1 • The document movement problem • The

    midnight problem • The end-of-the-day problem • The historical query problem Friday, September 14, 12
  17. End-of-day problem • Bson stores documents as an association list

    • MongoDB must check each key for a match • Load increases significantly at the end of the day (MongoDB must scan 1439 keys to find the right minute!) “1439” Value “0000” Value “0001” Value Friday, September 14, 12
  18. Fixing the end-of-day problem • Split up our ‘minute’ property

    by hour • Better worst-case keys scanned: • Old: 1439 • New: 82 { _id: "20101010/metric-1", metadata: { date: ISODate("2000-10-10T00:00:00Z"), metric: "metric-1" }, daily: 5468426, hourly: { "0": 227850, "1": 210231, ... "23": 20457 }, minute: { "00": { "0000": 3612, "0100": 3241, ... }, ..., "23": { ..., "1439": 2819 } } Friday, September 14, 12
  19. Historical Query Problem • Intra-day queries are great • What

    about “performance year to date”? • Now you’re hitting a lot of “cold” documents and causing page faults Friday, September 14, 12
  20. Fixing the historical query problem • Store multiple levels of

    granularity in different collections • 2 updates rather than 1, but historical queries much faster • Preallocate along with daily docs (only infrequently upserted) { _id: "201010/metric-1", metadata: { date: ISODate("2000-10-01T00:00:00Z"), metric: "metric-1" }, daily: { "0": 5468426, "1": ..., ... "31": ... }, } Friday, September 14, 12
  21. Queries • Updates are by _id, so no index needed

    there • Chart queries are by metadata • Your range/sort should be last in the compound index db.stats.daily.find( { "metadata.date": { $gte: dt1, $lte: dt2 }, "metadata.metric": "metric-1"}, { "metadata.date": 1, "hourly": 1 } }, sort=[("metadata.date", 1)]) db.stats.daily.ensureIndex({ 'metadata.metric': 1, 'metadata.date': 1 }) Friday, September 14, 12
  22. Conclusion • Monitor your performance. Watch out for spikes. •

    Preallocate to prevent document copying • Pay attention to the number of keys in your documents (hierarchy can help) • Make sure your index is optimized for your sorts Friday, September 14, 12