Sharding is hard. - Speaker Deck

Slide 1

Slide 1 text

SHARDING server #1 server # server #

Slide 2

Slide 2 text

Database is still main problem of web app

Slide 3

Slide 3 text

SCALABILITY !

Slide 4

Slide 4 text

ALTER TABLE ADD INDEX

Slide 5

Slide 5 text

JOIN is evil

Slide 6

Slide 6 text

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 [email protected]

Slide 7

Slide 7 text

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 [email protected] 100. 000 denormalize

Slide 8

Slide 8 text

memcached alternative PHP cache xcache redis

Slide 9

Slide 9 text

sphinx search lucene ! solr elastic search

Slide 10

Slide 10 text

Replication master slave slave SELECT UPDATE  DELETE INSERT SELECT SELECT

Slide 11

Slide 11 text

Replication master master master SELECT UPDATE  DELETE INSERT SELECT UPDATE  DELETE INSERT SELECT UPDATE  DELETE INSERT

Slide 12

Slide 12 text

Replication is harder than you think!

Slide 13

Slide 13 text

know your tools

Slide 14

Slide 14 text

Rafał Malinowski @afterdesign

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

No content

Slide 20

Slide 20 text

SHARDING

Slide 21

Slide 21 text

Sharding is a type of database partitioning that separates very large databases into smaller parts called data shards The Internet

Slide 22

Slide 22 text

user_id link 1 phpers.pl 1 meetphp.pl 1 php3city.pl

Slide 23

Slide 23 text

user_id link 1 phpers.pl user_id link 1 meetphp.pl user_id link 1 php3city.pl

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

How to shard ?

Slide 26

Slide 26 text

modulo

Slide 27

Slide 27 text

key % shards count = server id

Slide 28

Slide 28 text

sh.st/1

Slide 29

Slide 29 text

crc32('1') % 3 = 2

Slide 30

Slide 30 text

Sharding is hard. Resharding is harder !

Slide 31

Slide 31 text

crc32('1') % 3 = 2 crc32('1') % 4 = 3

Slide 32

Slide 32 text

db0 db1 4 5 6 7 0 1 2 3 8 9 db0 db2 db1

Slide 33

Slide 33 text

db0 db1 4 5 6 7 0 1 2 3 8 9 db0 db2 db1 db2 db3

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

key map

Slide 36

Slide 36 text

shard_X url_id goto date

Slide 37

Slide 37 text

shards_counters shard_id count 1 0 2 0 shard_X url_id goto date

Slide 38

Slide 38 text

shard_X url_id goto date shards_counters shard_id count 1 0 2 0 sharding_map url_id shard_id

Slide 39

Slide 39 text

meetphp.pl

Slide 40

Slide 40 text

shards_counters shard_id count 1 0 2 0 SELECT `shard_id` FROM `shards_counters` ORDER BY `count` LIMIT 1;

Slide 41

Slide 41 text

shards_counters shard_id count 1 1 2 0 meetphp.pl sharding_map url_id shard_id 1 1 sh.st/1

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

SELECT * FROM `urls` WHERE `url_id` = 1; shard_1.urls url_id goto date 1 meetphp.pl 2014-01-01

Slide 45

Slide 45 text

phpers.pl

Slide 46

Slide 46 text

shards_counters shard_id count 1 1 2 0 SELECT `shard_id` FROM `shards_counters` ORDER BY `count` LIMIT 1;

Slide 47

Slide 47 text

shards_counters shard_id count 1 1 2 1 sharding_map url_id shard_id 1 1 2 2 phpers.pl sh.st/2

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

SELECT * FROM `shard_2.urls ` WHERE `url_id` = 2; shard_2.urls url_id goto date 2 phpers.pl 2014-01-01

Slide 51

Slide 51 text

Key is unique throughout system

Slide 52

Slide 52 text

Adding shards ?

Slide 53

Slide 53 text

consistent hashing

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

0 max(int) meetphp.pl crc32('127.0.0.1') = 3619153832 crc32('127.0.0.2') = 1321121298 crc32('127.0.0.3') = 968459908

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

sh.st/3254572804 meetphp.pl crc32('meetphp.pl') = 3254572804

Slide 58

Slide 58 text

shard_3.urls url_id goto date 3254572804 meetphp.pl 2014-01-01 crc32('127.0.0.1') = 3619153832

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

sh.st/42311941 phpers.pl crc32('phpers.pl') = 42311941

Slide 61

Slide 61 text

shard_1.urls url_id goto date 42311941 phpers.pl 2014-01-01 crc32('127.0.0.3') = 968459908

Slide 62

Slide 62 text

Searching is expensive

Slide 63

Slide 63 text

ketama

Slide 64

Slide 64 text

0 max(int) crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3')

Slide 65

Slide 65 text

buckets = {}

Slide 66

Slide 66 text

$bucketsCount = 1024; ! for ($i = 0; $i < $bucketsCount; $i++) { buckets[i] = max(int) / $bucketsCount * i }

Slide 67

Slide 67 text

crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') 0 max(int) buckets[0] = 127.0.0.2

Slide 68

Slide 68 text

0 max(int) crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') buckets[261] = 127.0.0.1

Slide 69

Slide 69 text

crc32('127.0.0.1') crc32('127.0.0.2') crc32('127.0.0.3') 0 max(int) buckets[772] = 127.0.0.1

Slide 70

Slide 70 text

meetphp.pl crc32('meetphp.pl') = 3254572804 3254572804 % 1024 = 772

Slide 71

Slide 71 text

buckets[772] = shard 3

Slide 72

Slide 72 text

phpers.pl crc32('phpers.pl') = 42311941 42311941 % 1024 = 261

Slide 73

Slide 73 text

buckets[261] = shard 3

Slide 74

Slide 74 text

shard_3.urls url_id goto date 1 meetphp.pl 2014-01-01 2 phpers.pl 2014-01-01

Slide 75

Slide 75 text

resharding ?

Slide 76

Slide 76 text

1 6

Slide 77

Slide 77 text

1 6 2

Slide 78

Slide 78 text

1 6 2 3

Slide 79

Slide 79 text

1 6 2 3

Slide 80

Slide 80 text

data distribution

Slide 81

Slide 81 text

oversharding

Slide 82

Slide 82 text

problems sharding implementation ? which tables shard ? how big are our tables ?