Mongo Boulder 2011: Data Modeling for Performance

Transcript

1. Data Modeling for Performance Mongo Boulder January 21, 2010 Michael Dwan Snapjoy
2. i’m michael dwan @michaeldwan on the twitter
3. i’m michael dwan @michaeldwan on the twitter
4. the project Company X
5. application spec • find business details (web + api) • search by category/keyword + geo (web + api) • update (api)
6. why is this interesting?
7. why is this interesting? 15,000,000 businesses 30,000 partners 100,000 geo areas 2,300 categories 2,000,000 requests daily 24,000,000 urls in sitemap 100,000,000 tags
8. updates • infrequent changes • monthly updates w/ 12M monthly changes • “zero downtime”
9. the problem mo’ data, mo’ problems
10. complexity
11. businesses phone_numbers businesses _phone_numbers cities states zips neighborhoods businesses_neighborhoods tags taggings assets users categories categorizations providers mappings
12. architecture
13. architecture
14. architecture x
15. architecture x x
16. architecture x x x
17. architecture x x x x
18. read performance
19. solr
20. solr downtime
21. solr getting fussy
22. migrations
23. migrations downtime
24. the solution
25. > gem install acts_as_web_scale
28. a business...
29. a business... { "_id" : ObjectId("4ce838ef4a882579960001b9"), "name" : "Acme Glass Co", "tagline" : "Your trusty glass hole", "description" : "Glass repair...", "hours" : "Mon Fri 8 5", "url" : "http://acmeglasshole.biz", }
30. a business... has many phone numbers {
 "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "tagline" : "Your trusty glass hole",
 "description" : "Glass repair...",
 "hours" : "Mon Fri 8 5",
 "url" : "http://acmeglasshole.biz",
 }

31. a business... has many phone numbers {
 "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "tagline" : "Your trusty glass hole",
 "description" : "Glass repair...",
 "hours" : "Mon Fri 8 5",
 "url" : "http://acmeglasshole.biz",
 "phone_numbers" : [
 "5035550091",
 "8005555456"
 ]
 }

32. a business... has coordinates "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "tagline" : "Your trusty glass hole",
 "description" : "Glass repair...",
 "hours" : "Mon Fri 8 5",
 "url" : "http://acmeglasshole.biz",
 "phone_numbers" : [
 "5035550091",
 "8005555456"
 ]
 }

33. a business... has coordinates "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "tagline" : "Your trusty glass hole",
 "description" : "Glass repair...",
 "hours" : "Mon Fri 8 5",
 "url" : "http://acmeglasshole.biz",
 "phone_numbers" : [
 "5035550091",
 "8005555456"
 ],
 "coordinates" : [
 45.559294,
 -122.644053
 ]
 }

34. a business... has many tags "url" : "http://acmeglasshole.biz",
 "phone_numbers" : [
 "5035550091",
 "8005555456"
 ],
 "coordinates" : [
 45.559294,
 -122.644053
 ]
 }

35. a business... has many tags "url" : "http://acmeglasshole.biz",
 "phone_numbers" : [
 "5035550091",
 "8005555456"
 ],
 "coordinates" : [
 45.559294,
 -122.644053
 ],
 "tags" : [
 "glass",
 "mirrors",
 "flat glass"
 ]
 }

36. a business... has an address "coordinates" : [
 45.559294,
 -122.644053
 ],
 "tags" : [
 "glass",
 "mirrors",
 "flat glass"
 ]
 }

37. a business... has an address "coordinates" : [
 45.559294,
 -122.644053
 ],
 "tags" : [
 "glass",
 "mirrors",
 "flat glass"
 ],
 "location" : {
 "street_address" : "2035 NE Alberta St"
 }
 }
 
 

38. belongs to?
39. a state {
 "_id" : ObjectId("4ce82937961552247900000f"),
 "name" : "Illinois",
 "slug" : "il",
 ...
 }

40. a business... belongs to a state "coordinates" : [
 45.559294,
 -122.644053
 ],
 "tags" : [
 "glass",
 "mirrors",
 "flat glass"
 ],
 "location" : {
 "street_address" : "2035 NE Alberta St"
 }
 }
 
 

41. a business... belongs to a state "glass",
 "mirrors",
 "flat glass"
 ],
 "location" : {
 "street_address" : "2035 NE Alberta St"
 }
 }
 
 

42. a business... belongs to a state "glass",
 "mirrors",
 "flat glass"
 ],
 "location" : {
 "street_address" : "2035 NE Alberta St",
 "state" : {
 "_id" : ObjectId("4ce829379615522479000026"),
 "meta" : {
 "slug" : "or"
 },
 "display_name" : "Oregon"
 }
 }
 }

43. a business... belongs to a city "state" : {
 "_id" : ObjectId("4ce829379615522479000026"),
 "meta" : {
 "slug" : "or"
 },
 "display_name" : "Oregon"
 }
 }
 }

44. a business... belongs to a city "state" : {
 "_id" : ObjectId("4ce829379615522479000026"),
 "meta" : {
 "slug" : "or"
 },
 "display_name" : "Oregon"
 },
 "city" : {
 "_id" : ObjectId("4ce82abdd3dfaa10f8006faa"),
 "meta" : {
 "slug" : "portland",
 },
 "display_name" : "Portland, OR"
 },
 }
 }

45. a business... belongs to a zip code },
 "display_name" : "Oregon"
 },
 "city" : {
 "_id" : ObjectId("4ce82abdd3dfaa10f8006faa"),
 "meta" : {
 "slug" : "portland",
 },
 "display_name" : "Portland, OR"
 },
 }
 }

46. a business... belongs to a zip code },
 "display_name" : "Oregon"
 },
 "city" : {
 "_id" : ObjectId("4ce82abdd3dfaa10f8006faa"),
 "meta" : {
 "slug" : "portland",
 },
 "display_name" : "Portland, OR"
 },
 "zip" : {
 "_id" : ObjectId("4ce82c29d3dfaa116b006dfa"),
 "display_name" : "97211"
 }
 }
 }

47. many-to-many?
48. a category {
 "_id" : ObjectId("4ce82e64d3dfaa16360014eb"),
 "name" : "Auto Glass",
 "slug" : "3063-auto-glass",
 "tags" : [
 "windshields"
 ],
 ...
 }

49. a business... belongs to a zip code "meta" : {
 "slug" : "or"
 },
 "display_name" : "Oregon"
 },
 "city" : {
 "_id" : ObjectId("4ce82abdd3dfaa10f8006faa"),
 "meta" : {
 "slug" : "portland",
 },
 "display_name" : "Portland, OR"
 },
 "zip" : {
 "_id" : ObjectId("4ce82c29d3dfaa116b006dfa"),
 "display_name" : "97211"
 }
 }
 }

50. a business... belongs to many categories }
 }
 }

51. a business... belongs to many categories }
 },
 "categories" : [
 {
 "_id" : ObjectId("4ce82e50d3dfaa16360004f2"),
 "meta" : {
 "slug" : "282-glass",
 "tags" : [ "windows" ],
 },
 "display_name" : "Glass"
 },
 {
 "_id" : ObjectId("4ce82e64d3dfaa16360014eb"),
 "meta" : {
 "slug" : "3063-auto-glass",
 "tags" : [ "windshields" ],
 },
 "display_name" : "Auto Glass"
 }
 ]
 }

52. queries & indexes know what you want
53. #1 find a business I want *that* one
54. find a business // single business
 db.businesses.findOne({
 _id: ObjectId("4ce838ef4a882579960001b9")
 })
55. #2 find by location Businesses in San Francisco, CA
56. find businesses by state/city/zip // find all within state
 db.businesses.find({
 "location.state._id": ObjectId("4ce82937961552247900000f")
 })
 ! ! ! ! ! ! ! ! 

57. find businesses by state/city/zip // find all within state
 db.businesses.find({
 "location.state._id": ObjectId("4ce82937961552247900000f")
 })
 
 // find all within city
 db.businesses.find({
 "location.city._id": ObjectId("4ce82aa0d3dfaa10f8004a95")
 })
 ! ! ! 

58. find businesses by state/city/zip // find all within state
 db.businesses.find({
 "location.state._id": ObjectId("4ce82937961552247900000f")
 })
 
 // find all within city
 db.businesses.find({
 "location.city._id": ObjectId("4ce82aa0d3dfaa10f8004a95")
 })
 
 // find all within zip
 db.businesses.find({
 "location.zip._id": ObjectId("4ce82b5ed3dfaa116b0026f0")
 })

59. indexes // the indexes
 db.businesses.ensureIndex({"location.city._id": 1})
 db.businesses.ensureIndex({"location.zip._id": 1})

60. indexes // the indexes
 db.businesses.ensureIndex({"location.city._id": 1})
 db.businesses.ensureIndex({"location.zip._id": 1})
 1.5GB each
61. indexes // the indexes
 db.businesses.ensureIndex({"location.city._id": 1})
 db.businesses.ensureIndex({"location.zip._id": 1})
 skip “location.state._id” -- only 51 possibilities 1.5GB each
62. #3 find by category Businesses in the Auto Repair category
63. businesses by category // find by category id
 db.businesses.find({
 "categories._id": ObjectId("4ce82e50d3dfaa16360004f2")
 })

64. businesses by category // find by category id
 db.businesses.find({
 "categories._id": ObjectId("4ce82e50d3dfaa16360004f2")
 })
 // the index
 db.businesses.ensureIndex({
 "categories._id":1
 })
65. #4 - find by category + location Businesses in the Plumbing category in Chicago, IL
66. businesses by category + city // find by city id and category id
 db.businesses.find({
 "location.city._id": ObjectId("4ce82aa0d3dfaa10f8004a95"),
 "categories._id": ObjectId("4ce82e50d3dfaa16360004f2")
 })
67. which index should we use?
68. which index should we use? // city id
 {"location.city._id":1}
 // category id
 {"categories._id":1} ~ or ~
69. which index should we use? // city id
 {"location.city._id":1}
 // category id
 {"categories._id":1} ~ or ~ we need a compound index answer: both suck
70. which order? db.businesses.ensureIndex({
 "location.city._id" : 1, "categories._id" : 1
 })
 db.businesses.ensureIndex({
 "categories._id" : 1, "location.city._id" : 1
 }) ~ or ~
71. which order? db.businesses.ensureIndex({
 "location.city._id" : 1, "categories._id" : 1
 })
 db.businesses.ensureIndex({
 "categories._id" : 1, "location.city._id" : 1
 }) ~ or ~ 35,000 cities & 2,500 categories
72. which order? db.businesses.ensureIndex({
 "location.city._id" : 1, "categories._id" : 1
 })
 db.businesses.ensureIndex({
 "categories._id" : 1, "location.city._id" : 1
 }) ~ or ~ answer: cities → categories 35,000 cities & 2,500 categories create one for zip codes and categories too!
73. don’t we have 2 indexes on city id?
74. don’t we have 2 indexes on city id? {"location.city._id" : 1} {"location.city._id" : 1, "categories._id" : 1}
75. don’t we have 2 indexes on city id? answer: yes {"location.city._id" : 1} {"location.city._id" : 1, "categories._id" : 1}
76. don’t we have 2 indexes on city id? answer: yes {"location.city._id" : 1} {"location.city._id" : 1, "categories._id" : 1} db.businesses.dropIndex("location.city._id_1")
77. #5 - find by keyword “something awesome” in Boulder, CO
78. find businesses in city by keyword
79. find businesses in city by keyword {
 "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "keywords" : [
 "glass",
 "repair",
 "acme",
 ...
 ]
 }

80. find businesses in city by keyword {
 "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "keywords" : [
 "glass",
 "repair",
 "acme",
 ...
 ]
 }
 db.businesses.ensureIndex({
 "location.city._id":1,
 "keywords":1
 })

81. find businesses in city by keyword {
 "_id" : ObjectId("4ce838ef4a882579960001b9"),
 "name" : "Acme Glass Co",
 "keywords" : [
 "glass",
 "repair",
 "acme",
 ...
 ]
 }
 db.businesses.ensureIndex({
 "location.city._id":1,
 "keywords":1
 })
 db.businesses.find({
 "location.city._id":ObjectId("4ce82aa0d3dfaa10f8004a95"),
 "keywords":/glass/i
 })

82. chat with Kyle Banker
83. chat with Kyle Banker me: we’re switching from postgres+solr to mongo
84. chat with Kyle Banker me: we’re switching from postgres+solr to mongo kyle: oh wow, you can replace solr with mongo?
85. chat with Kyle Banker me: we’re switching from postgres+solr to mongo kyle: oh wow, you can replace solr with mongo? me: with some creativity
86. chat with Kyle Banker me: we’re switching from postgres+solr to mongo kyle: oh wow, you can replace solr with mongo? me: with some creativity kyle: seems like it’d still be hard to get just right
87. chat with Kyle Banker me: we’re switching from postgres+solr to mongo kyle: oh wow, you can replace solr with mongo? me: with some creativity kyle: seems like it’d still be hard to get just right me: it works well
88. chat with Kyle Banker me: we’re switching from postgres+solr to mongo kyle: oh wow, you can replace solr with mongo? me: with some creativity kyle: seems like it’d still be hard to get just right me: it works well kyle: gotcha
89. i was wrong, kyle was right
90. i was wrong, kyle was right
91. i was wrong, kyle was right
92. i was wrong, kyle was right
93. I
94. I’ll never leave you again ...until MongoDB supports full text later this year :) I
95. aggregation map/reduce to the rescue
96. sitemaps big list of every url
97. sitemaps • xml files containing each unique url ~ 24M • 50,000 urls per file, about 500 files • urls are generated from live data • http://companyx.com/sitemaps/1.xml
98. partition by consistent hash >> "hello!".hash % 6 #=> 5 >> "/ny/new-york/c/apartments".hash % 6 #=> 5 returns an integer between 0 and the number specified
99. map/reduce
100. map/reduce 1.map each url in the site to a partition 2.reduce all partitions to a single document containing all urls in that partition 3.save to a permanent collection
101. map /il/chicago/c/pizza 4 /ny/new-york/c/apartments 1 nd/rugby/c/apartments 6 /14076500-bayside-marina 2 /13401000-comtrak-logistics-inc 3 /12347500-allstate-auto-insurance 1 il/downers-grove/c/computer-web-design 6 /1009500-heidelberg-lodges 5 mn/redwood-falls/c/food-service 4 /14077000-bank-of-america 5 mn/savage/c/audio-visual-equipment 1 ... 1 2 3 4 5 6
102. reduce {
 "total" : 2,
 "urls" : [
 "/12347500-allstate-auto-insurance",
 "/ny/new-york/c/apartments"
 ]
 }
 {
 "total" : 1,
 "urls" : [
 "/mn/savage/c/audio-visual-equipment"
 ]
 }
 {
 "_id" : 1,
 "value" : {
 "total" : 2,
 "urls" : [
 "/12347500-allstate-auto-insurance",
 "/mn/savage/c/audio-visual-equipment",
 "/ny/new-york/c/apartments"
 ]
 }
 }

103. usage db.sitemaps.findOne({_id:1}).value.urls [
 "/12347500-allstate-auto-insurance",
 "/mn/savage/c/audio-visual-equipment",
 "/ny/new-york/c/apartments"
 ]

104. wrap up
105. 2 months later 115ms average response times
106. thank you @michaeldwan