Sharding is hard.

Transcript

1. SHARDING server #1 server # server #
2. Database is still main problem of web app
3. SCALABILITY !
4. ALTER TABLE ADD INDEX
5. JOIN is evil
6. denormalize users_links user_id link 1 phpers.pl 1 meetphp.pl 1 php3city.pl … … select count(*) from users_links where user_id=1; users user_id link 1 rafal@malinowski.be
7. users_links user_id link 1 phpers.pl 1 meetphp.pl 1 php3city.pl … … select links_count from users where user_id=1; users user_id link links_count 1 rafal@malinowski.be 100. 000 denormalize
8. memcached alternative PHP cache xcache redis
9. sphinx search lucene ! solr elastic search
10. Replication master slave slave SELECT UPDATE
 DELETE INSERT SELECT SELECT
11. Replication master master master SELECT UPDATE
 DELETE INSERT SELECT UPDATE
 DELETE INSERT SELECT UPDATE
 DELETE INSERT
12. Replication is harder than you think!
13. know your tools
14. Rafał Malinowski @afterdesign
20. SHARDING
21. Sharding is a type of database partitioning that separates very large databases into smaller parts called data shards The Internet
22. user_id link 1 phpers.pl 1 meetphp.pl 1 php3city.pl
23. user_id link 1 phpers.pl user_id link 1 meetphp.pl user_id link 1 php3city.pl
25. How to shard ?
26. modulo
27. key % shards count = server id
28. sh.st/1
29. crc32('1') % 3 = 2
30. Sharding is hard. Resharding is harder !
31. crc32('1') % 3 = 2 crc32('1') % 4 = 3
32. db0 db1 4 5 6 7 0 1 2 3 8 9 db0 db2 db1
33. db0 db1 4 5 6 7 0 1 2 3 8 9 db0 db2 db1 db2 db3
34. shards moved rows 2 → 3 667 402 2 → 4 500 000 2 → 5 800 468 3 → 4 750 357 3 → 5 800 060 4 → 5 800 247
35. key map
36. shard_X url_id goto date
37. shards_counters shard_id count 1 0 2 0 shard_X url_id goto date
38. shard_X url_id goto date shards_counters shard_id count 1 0 2 0 sharding_map url_id shard_id
39. meetphp.pl
40. shards_counters shard_id count 1 0 2 0 SELECT `shard_id` FROM `shards_counters` ORDER BY `count` LIMIT 1;
41. shards_counters shard_id count 1 1 2 0 meetphp.pl sharding_map url_id shard_id 1 1 sh.st/1
42. shards_counters shard_id count 1 1 2 0 shard_1.urls url_id goto date 1 meetphp.pl 2014-01-01 sharding_map url_id shard_id 1 1
43. shards_counters shard_id count 1 1 2 0 SELECT `shard_id` FROM `sharding_map` WHERE `url_id` = 1; sharding_map url_id shard_id 1 1
44. SELECT * FROM `urls` WHERE `url_id` = 1; shard_1.urls url_id goto date 1 meetphp.pl 2014-01-01
45. phpers.pl
46. shards_counters shard_id count 1 1 2 0 SELECT `shard_id` FROM `shards_counters` ORDER BY `count` LIMIT 1;
47. shards_counters shard_id count 1 1 2 1 sharding_map url_id shard_id 1 1 2 2 phpers.pl sh.st/2
48. shards_counters shard_id count 1 1 2 1 shard_2.urls url_id goto date 2 phpers.pl 2014-01-01 sharding_map url_id shard_id 1 1 2 2
49. shards_counters shard_id count 1 1 2 1 SELECT `shard_id` FROM `sharding_map` WHERE `url_id` = 2; sharding_map url_id shard_id 1 1 2 2
50. SELECT * FROM `shard_2.urls ` WHERE `url_id` = 2; shard_2.urls url_id goto date 2 phpers.pl 2014-01-01
51. Key is unique throughout system
52. Adding shards ?
53. consistent hashing
54. 0 max(int) shard_3 crc32('127.0.0.1') = 3619153832 shard_2 crc32('127.0.0.2') = 1321121298 shard_1 crc32('127.0.0.3') = 968459908
55. 0 max(int) meetphp.pl crc32('127.0.0.1') = 3619153832 crc32('127.0.0.2') = 1321121298 crc32('127.0.0.3') = 968459908
56. 0 max(int) crc32('meetphp.pl') = 3254572804 crc32('127.0.0.1') = 3619153832 crc32('127.0.0.2') = 1321121298 crc32('127.0.0.3') = 968459908 meetphp.pl
57. sh.st/3254572804 meetphp.pl crc32('meetphp.pl') = 3254572804
58. shard_3.urls url_id goto date 3254572804 meetphp.pl 2014-01-01 crc32('127.0.0.1') = 3619153832
59. 0 max(int) crc32('127.0.0.1') = 3619153832 crc32('127.0.0.2') = 1321121298 crc32('127.0.0.3') = 968459908 crc32('phpers.pl') = 42311941 phpers.pl
60. sh.st/42311941 phpers.pl crc32('phpers.pl') = 42311941
61. shard_1.urls url_id goto date 42311941 phpers.pl 2014-01-01 crc32('127.0.0.3') = 968459908
62. Searching is expensive
63. ketama
64. 0 max(int) crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3')
65. buckets = {}
66. $bucketsCount = 1024; ! for ($i = 0; $i < $bucketsCount; $i++) { buckets[i] = max(int) / $bucketsCount * i }
67. crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') 0 max(int) buckets[0] = 127.0.0.2
68. 0 max(int) crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') buckets[261] = 127.0.0.1
69. crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') 0 max(int) buckets[772] = 127.0.0.1
70. meetphp.pl crc32('meetphp.pl') = 3254572804 3254572804 % 1024 = 772
71. buckets[772] = shard 3
72. phpers.pl crc32('phpers.pl') = 42311941 42311941 % 1024 = 261
73. buckets[261] = shard 3
74. shard_3.urls url_id goto date 1 meetphp.pl 2014-01-01 2 phpers.pl 2014-01-01
75. resharding ?
76. 1 6
77. 1 6 2
78. 1 6 2 3
79. 1 6 2 3
80. data distribution
81. oversharding
82. problems sharding implementation ? which tables shard ? how big are our tables ?
83. problems data count pagers/lists
84. sharding count insert/select one of the last things to do
85. Rafał Malinowski @afterdesign