Executing Queries on a Sharded Database

Determining a data storage solution as your web application scales can be the most difficult part of web development, and takes time away from developing application features. MongoDB, Redis, Postgres, Riak, Cassandra, Voldemort, NoSQL, MySQL, NewSQL — the options are overwhelming, and all claim to be elastic, fault-tolerant, durable, and give great performance for both reads and writes. In the first portion of this talk I’ll discuss these different storage solutions and explain what is really important when choosing a datastore — your application data schema and feature requirements.

No matter what datastore you choose, you will eventually have to consider sharding your data store to support growing traffic. Two key challenges arise: (1) web workloads often do not have one clear partitioning and (2) it is challenging to determine how to efficiently execute queries over partitioned tables.

To address these challenges I present Dixie, a SQL query planner, optimizer, and executor for databases horizontally partitioned over multiple servers. Dixie shows that we shouldn’t give up on SQL databases just yet. Dixie automates the exploitation of tables with multiple copies partitioned in different ways, in order to increase throughput by expanding the set of queries that need not be sent to all servers. Central to Dixie’s design are a cost model and plan generator that are mindful of queries small enough that query overhead may dominate the cost. For a large class of joins, which traditional wisdom suggests require tables partitioned on the join keys, Dixie can find higher-performance plans using other partitionings.

Presented at Strange Loop, St. Louis, MO.

Transcript

1. Execu&ng  Queries  on  a  Sharded   Database   Neha  Narula   September  25,  2012  
2. Choosing  and  Scaling  Your   Datastore   Neha  Narula   September  25,  2012  
3. Who  Am  I?   Froogle   Blobstore   Na&ve  Client  
4. In  This  Talk   •  What  to  think  about  when  choosing  a   datastore  for  a  web  applica&on   •  Myths  and  legends   •  Execu&ng  distributed  queries   •  My  research:    a  consistent  cache  
5. Every  so  oPen…   Friends  ask  me  for  advice  when  they  are   building  a  new  applica&on  
6. Friend  
7. “Hi  Neha!    I  am  making  a  new  applica&on.      
8. I  have  heard  MySQL  sucks  and  I  should  use  NoSQL.      
9. I  am  going  to  be  itera&ng  on  my  app  a  lot  
10. I  don't  have  any  customers  yet  
11. and  my  current  data  set  could  fit  on  a  thumb  drive   from  2004,  but…  
12.  Can  you  tell  me  which  NoSQL  database  I   should  use?  
13. And  how  to  shard  it?"    
14. Neha  
15. h[p://knowyourmeme.com/memes/facepalm  
18. What  to  Think  about  When  You’re   Choosing  a  Datastore   Hint:  Not  Scaling  
19. Development  Cycle   •  Prototyping   – Ease  of  use   •  Developing   – Flexibility,  mul&ple  developers   •  Running  a  real  produc&on  system   – Reliability   •  SUCCESS!    Sharding  and  specialized   datastorage       – We  should  only  be  so  lucky  
20. Gecng  Started   First  five  minutes  –  what’s  it  like?   Idea  credit:  Adam  Marcus,  The  NoSQL  Ecosystem,  HPTS  2011     via  Jus&n  Sheehy  from  Basho    
21. First  Five  Minutes:  Redis   h[p://simonwillison.net/2009/Oct/22/redis/  
22. First  Five  Minutes:  MySQL  
23. Developing   •  Mul&ple  people  working  on  the  same  code   •  Tes&ng  new  features  =>  new  access  pa[erns   •  New  person  comes  along…  
24. Redis   Time  to  go  get   lunch  
25. MySQL  
26. Ques&ons   •  What  is  your  mix  of  reads  and  writes?   •  How  much  data  do  you  have?   •  Do  you  need  transac&ons?   •  What  are  your  access  pa[erns?   •  What  will  grow  and  change?   •  What  do  you  already  know?  
27. Reads  and  Writes   •  Write  op&mized  vs.  Read  op&mized   •  MongoDB  has  a  global  write  lock  per  process   •  No  concurrent  writes!  
28. Size  of  Data   •  Does  it  fit  in  memory?   •  Disk-­‐based  solu&ons  will  be  slower   •  Worst  case,  Redis  needs  2X  the  memory  of   your  data!   – It  forks  with  copy-­‐on-­‐write   sudo echo 1 > /proc/sys/vm/overcommit_memory!  
29. Requirements   •  Performance   – Latency  tolerance   •  Durability   – Data  loss  tolerance   •  Freshness   – Staleness  tolerance   •  Up&me   – Down&me  tolerance  
30. Performance   •  Rela&onal  databases  are  considered  slow   •  But  they  are  doing  a  lot  of  work!   •  Some&mes  you  need  that  work,  some&mes   you  don’t.  
31. Simplest  Scenario   •  Responding  to  user  requests  in  real  &me   •  Frequently  read  data  fits  in  memory   •  High  read  rate   •  No  need  to  go  to  disk  or  scan  lots  of  data   Datastore  CPU  is  the  bo[leneck  
32. Cost  of  Execu&ng  a  Primary  Key  Lookup   Query   Cost   •  Receiving  message   •  Instan&a&ng  thread   •  Parsing  query   •  Op&mizing   •  Take  out  read  locks   •  (Some&mes)  lookup  in   an  index  btree   •  Responding  to  the  client   Actually  retrieving  data  
33. Op&ons  to  Make  This  Fast   •  Query  cache   •  Prepared  statements   •  Handler  Socket   – 750K  lookups/sec  on  a  single  server   Lesson:    Primary  key  lookups  can  be  fast  no   ma[er  what  datastore  you  use  
34. Flexibility  vs.  Performance   •  We  might  want  to  pay  the  overhead  for  query   flexibility   •  In  a  primary  key  datastore,  we  can  only  ask   queries  on  primary  key   •  SQL  gives  us  flexibility  to  change  our  queries  
35. Durability   •  Persistent  datastores   –  Client:    write   –  Server:  flush  to  disk,  then  send  “I  completed  your   write”   –  CRASH   –  Recover:  See  the  write   •  By  default,  MongoDB  does  not  fsync()  before   returning  to  client  on  write   –  Need  j:true   •  By  default,  MySQL  uses  MyISAM  instead  of   InnoDB  
36. Specializa&on   •  You  know  your     – query  access  pa[erns  and  traffic   – consistency  requirements   •  Design  specialized  lookups   – Transac&onal  datastore  for  consistent  data   – Memcached  for  sta&c,  mostly  unchanging  content   – Redis  for  a  data  processing  pipeline   •  Know  what  tradeoffs  to  make  
37. Ways  to  Scale   •  Reads   – Cache   – Replicate   – Par&&on   •  Writes   – Par&&on  data  amongst  mul&ple  servers    
38. Lots  of  Folklore  
39. Myths  of  Sharded  Datastores   •  NoSQL  scales  be[er  than  a  rela&onal  database   •  You  can’t  do  a  JOIN  on  a  sharded  datastore  
40. MYTH:    NoSQL  scales  be[er  than  a   rela&onal  database   •  Scaling  isn’t  about  the  datastore,  it’s  about   the  applica&on   – Examples:  FriendFeed,  Quora,  Facebook   •  Complex  queries  that  go  to  all  shards  don’t   scale   •  Simple  queries  that  par&&on  well  and  use  only   one  shard  do  scale    
41. Problem:    Applica&ons  Look  Up   Data  in  Different  Ways  
42. Post  Page   SELECT * ! FROM comments! WHERE post_id = 100! !   zrange comments:100 0 -1!   HA!  
43. Example  Par&&oned   Database   Database   Database   Database   comments table post_id! 100-199! 0-99! 200-199! Webservers  
44. MySQL MySQL MySQL Query  Goes  to  One   Par&&on   MySQL MySQL MySQL Comments   on  post  100   100-199! 0-99! 200-299!
45. MySQL MySQL MySQL Many  Concurrent   Queries   MySQL MySQL MySQL Comments   on  post  100   100-199! 0-99! 200-299! Comments   on  post  52   Comments   on  post  289  
46. User  Comments  Page   Fetch  all  of  a  user's  comments:   SELECT * FROM comments WHERE user = 'sam'!
47. Query  Goes  to  All   Par&&ons   MySQL MySQL MySQL Query  goes  to  all  servers   100-199! 0-99! 200-299! Sam’s   comments  
48. Costs  for  Query  Go  Up   When  Adding  a  New   Server   MySQL 0-99! Sam’s   comments   MySQL 100-199! MySQL 200-299! MySQL 300-399!
49. CPU  Cost  on  Server  of  Retrieving  One  Row   Two  costs:    Query  overhead  and  row  retrieval   MySQL Query   Overhead   97%  
50. Idea:  Mul&ple  Par&&onings   Par&&on  comments  table  on  post_id  and  user.     Reads  can  choose  appropriate  copy  so  queries  go  to   only  one  server.     Writes  go  to  all  copies.  
51. Mul&ple  Par&&onings   Database   Database   Database   comments table   post_id! 100-199! 0-99! 200-199! user! K-R! A-J! S-Z!
52. All  Read  Queries  Go  To  Only   One  Par&&on   MySQL MySQL MySQL 100-199! 0-99! S-Z! Sam’s   comments   Comments  on   post  100  
53. Writes  Go  to  Both  Table   Copies   MySQL MySQL MySQL 100-199! 0-99! S-Z! Write  a  new   comment  by   Vicki  on  post   100  
54. Scaling   •  Reads  scale  by  N  –  the  number  of  servers   •  Writes  are  slowed  down  by  T  –  the  number  of   table  copies   •  Big  win  if  N  >>  T   •  Table  copies  use  more  memory   – OPen  don’t  have  to  copy  large  tables  –  usually   metadata   •  How  to  execute  these  queries?  
55. Dixie   •  Is  a  query  planner  which  executes  SQL  queries  over  a   par&&oned  database     •  Op&mizes  read  queries  to  minimize  query  overhead  and   data  retrieval  costs   •  Uses  a  novel  cost  formula  which  takes  advantage  of  data   par&&oned  in  different  ways  
56. 0 5000 10000 15000 20000 25000 1 2 5 10 QPS Number of Partitions One copy of page table Dixie + page.title copy Wikipedia  Workload  with  Dixie   3.2 X Each   query   going  to  1   server   Many   queries   going  to  all   servers   Wikipedia   •  Database  dump  from  2008   •  Real  HTTP  traces   •  Sharded  across  1,  2,  5,  and  10  servers   •  Compare  by  adding  a  copy  of  the  page   table  (<  100  MB),  sharded  another  way    
57. Myths  of  Sharded  Datastores   •  NoSQL  scales  be[er  than  a  rela&onal  database   •  You  can’t  do  a  JOIN  on  a  sharded  datastore  
58. MYTH:    You  can’t  execute  a  JOIN  on  a   sharded  database   •  What  are  the  JOIN  keys?    What  are  your   par&&on  keys?   •  Bad  to  move  lots  of  data   •  Not  bad  to  lookup  a  few  pieces  of  data   •  Index  lookup  JOINs  expressed  as  primary  key   lookups  are  fast  
59. Join  Query   Fetch  all  of  Alice's  comments  on  Max's  posts.   3 Alice First post! comments table post_id user text 6 7 22 Alice Alice Alice Like. You think? Nice work! 1 Max http://… posts table id author link 3 22 37 Max Max Max www. Ask Hacker http://…
60. Join  Query   Fetch  all  of  Alice's  comments  on  Max's  posts.   3 Alice First post! comments table post_id user text 6 7 22 Alice Alice Alice Like. You think? Nice work! 1 Max http://… posts table id author link 3 22 37 Max Max Max www. Ask Hacker http://…
61. Distributed  Query  Planning   •  R*   •  Shore   •  The  State  of  the  Art  in  Distributed  Query   Processing,  by  Donald  Kossman   •  Gizzard  
62. Conven&onal  Wisdom   Par&&on  on  JOIN  keys,  Send  query   as  is  to  each  server.   0-99! 0-99! 100- 199! 100- 199! 200- 299! 200- 299! SELECT comments.text ! FROM posts, comments! WHERE comments.post_id = posts.id! AND posts.author = 'Max'! AND comments.user = 'Alice'!
63. … Conven&onal  Plan  Scaling   MySQL Alice’s   comments  on   Max’s  posts   MySQL MySQL
64. Index  Lookup  Join   Par&&on  on  filter  keys,  retrieve  Max's   posts,  then  retrieve  Alice's  comments  on   Max's  posts.   0-99! 100- 199! 200- 299! J-R! S-Z! 100- 199! 200- 299! J-R! S-Z! A-I! 0-99! A-I! SELECT posts.id ! FROM posts! WHERE posts.author = 'Max’!
65. 0-99! 100- 199! 200- 299! J-R! S-Z! 100- 199! 200- 299! J-R! S-Z! A-I! 0-99! A-I! ids of Max’s posts Index  Lookup  Join     Par&&on  on  filter  keys,  retrieve  Max's   posts,  then  retrieve  Alice's  comments  on   Max's  posts.  
66. 0-99! 100- 199! 200- 299! J-R! S-Z! 100- 199! 200- 299! A-I! J-R! S-Z! 0-99! A-I! SELECT comments.text ! FROM comments! WHERE comments.post_id IN […]! AND comments.user = 'Alice'! Index  Lookup  Join   Par&&on  on  filter  keys,  retrieve  Max's   posts,  then  retrieve  Alice's  comments  on   Max's  posts.  
67. 0-99! 100- 199! 200- 299! J-R! S-Z! 100- 199! 200- 299! A-I! J-R! S-Z! 0-99! A-I! Alice’s  comments  on   Max’s  posts   Index  Lookup  Join   Par&&on  on  filter  keys,  retrieve  Max's   posts,  then  retrieve  Alice's  comments  on   Max's  posts.  
68. … MySQL Alice’s comments on Max’s posts MySQL MySQL Index  Lookup  Join  Plan  Scaling    
69. Comparison   Conven&onal  Plan   Index  Lookup  Plan   Intermediate   data,  Max’s   posts  Alice   DIDN’T   comment  on  
70. Comparison   Conven&onal  Plan   Index  Lookup  Plan   Intermediate   data,  Max’s   posts  Alice   DIDN’T   comment  on  
71. Dixie   •  Cost  model  and  query  op&mizer  which   predicts  the  costs  of  the  two  plans   •  Query  executor  which  executes  the  original   query,  designed  for  a  single  database,  on  a   par&&oned  database  with  table  copies  
72. Dixie's  Predic&ons  for  the  Two  Plans   0 2000 4000 6000 8000 10000 0 50 100 150 Queries/Sec Posts/Author 2-Step Join Estimate Pushdown Join Estimate •  Varying  the  size  of  the  intermediate  data   •  Max’s  posts,  Alice  didn’t  comment  on   •  Fixing  the  number  of  results  returned   •  Par&&oned  over  10  servers     Conven&onal   Plan,  each   query  going   to  all  servers   Index  Lookup   Plan,  each   query  going  to   two  servers  
73. Performance  of  the  Two  Plans   0 2000 4000 6000 8000 10000 0 50 100 150 Queries/Sec Posts/Author 2-Step Join Pushdown Join 2-Step Join Estimate Pushdown Join Estimate Blue  beats  yellow  aPer  this.   Dixie  predicts  it!  
74. JOINs  Are  Not  Evil   •  When  only  transferring  small  amounts  data   •  And  with  carefully  par&&oned  data  
75. Lessons  Learned   •  Don’t  worry  at  the  beginning:  Use  what  you   know   •  Not  about  SQL  vs.  NoSQL  systems  –  you  can   scale  any  system   •  More  about  complex  vs.  simple  queries   •  When  you  do  scale,  try  to  make  every  query   use  one  (or  a  few)  shards  
76. Research:    Caching   •  Applica&ons  use  a  cache  to  store  results   computed  from  a  webserver  or  database  rows   •  Expira&on  or  invalida&on?   •  Annoying  and  difficult  for  app  developers  to   invalidate  cache  items  correctly   Work  in  progress  with  Bryan  Kate,  Eddie  Kohler,  Yandong  Mao,  and  Robert  Morris   Harvard  and  MIT  
77. Solu&on:  Dependency  Tracking  Cache   •  Applica&on  indicates  what  data  went  into  a   cached  result   •  The  system  will  take  care  of  invalida&ons   •  Don’t  need  to  recalculate  expired  data  if  it  has   not  changed   •  Always  get  the  freshest  data  when  data  has   changed   •  Track  ranges,  even  if  empty!  
78. Example  Applica&on:  Twi[er   •  Cache  a  user’s  view  of  their  twi[er  homepage   (&meline)   •  Only  invalidate  when  a  followed  user  tweets   •  Even  if  many  followed  users  tweet,  only   recalculate  the  &meline  once  when  read   •  No  need  to  recalculate  on  read  if  no  one  has   tweeted  
79. Challenges   •  Invalida&on  +  expira&on  &mes   – Reading  stale  data   •  Distributed  caching   •  Cache  coherence  on  the  original  database   rows  
80. Benefits   •  Applica&on  gets  all  the  benefits  of  caching   – Saves  on  computa&on   – Less  traffic  to  the  backend  database   •  Doesn’t  have  to  worry  about  freshness  
81. Summary   •  Choosing  a  datastore   •  Dixie   •  Dependency  tracking  cache  
82. Thanks!     narula@mit.edu   h[p://nehanaru.la   @neha