the etsy shard architecture: starts with s and ends with hard
DESCRIPTION
Overview of the etsy shard architectureTRANSCRIPT
![Page 2: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/2.jpg)
![Page 3: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/3.jpg)
1.5B page views / mo.525MM sales in 201140MM unique visitors/mo.800K shops / 150 countries
![Page 4: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/4.jpg)
![Page 5: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/5.jpg)
![Page 6: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/6.jpg)
25K+ queries/sec avg3TB InnoDB buffer pool15TB+ data stored99.99% queries under 1ms
![Page 7: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/7.jpg)
50+ MySQL servers
Server SpecHP DL 380 G7
96GB RAM16 spindles / 1TB RAID 10
24 Core
![Page 8: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/8.jpg)
![Page 9: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/9.jpg)
Ross SnyderScaling Etsy - What Went Wrong, What Went Right
http://bit.ly/rpcxtP
Matt GrahamMigrating From PG to MySQL Without Downtime
http://bit.ly/rQpqZG
![Page 10: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/10.jpg)
Architecture
![Page 11: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/11.jpg)
Redundancy
![Page 12: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/12.jpg)
Master - Master
![Page 13: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/13.jpg)
Master - Master
R/W R/W
![Page 14: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/14.jpg)
Master - Master
R/W R/W
Side A Side B
![Page 15: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/15.jpg)
Scalability
![Page 16: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/16.jpg)
shard 1 shard 2 shard N
. . .
![Page 17: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/17.jpg)
shard 1 shard 2 shard N
shard N + 1
. . .
![Page 18: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/18.jpg)
shard 1 shard 2 shard N
shard N + 1
. . .
Migrate Migrate Migrate
![Page 19: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/19.jpg)
Bird’s-Eye View
![Page 20: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/20.jpg)
tickets index
shard 1 shard 2 shard N
![Page 21: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/21.jpg)
tickets index
shard 1 shard 2 shard N
Unique IDs
![Page 22: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/22.jpg)
tickets index
shard 1 shard 2 shard N
Shard Lookup
![Page 23: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/23.jpg)
tickets index
shard 1 shard 2 shard N
Store/Retrieve Data
![Page 24: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/24.jpg)
Basics
![Page 25: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/25.jpg)
users_groups
user_id group_id
1 A
1 B
2 A
2 C
3 A
3 B
3 C
![Page 26: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/26.jpg)
users_groups
user_id group_id
1 A
1 B
2 A
2 C
3 A
3 B
3 C
![Page 27: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/27.jpg)
users_groups
user_id group_id
1 A
1 B
2 A
2 C
user_id group_id
3 A
3 B
3 C
3 A
3 B
3 C
![Page 28: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/28.jpg)
users_groups
user_id group_id
1 A
1 B
2 A
2 C
user_id group_id
3 A
3 B
3 C
shard 1
shard 2
![Page 29: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/29.jpg)
Index Servers
![Page 30: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/30.jpg)
Shards NOT Determined bykey hashing
range partitionspartitioning by function
![Page 31: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/31.jpg)
Look-Up Data
![Page 32: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/32.jpg)
index
shard 1 shard 2 shard N
![Page 33: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/33.jpg)
index
shard 1 shard 2 shard N
select shard_id from user_index where user_id = X
![Page 34: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/34.jpg)
index
shard 1 shard 2 shard N
select shard_id from user_index where user_id = X
returns 1
![Page 35: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/35.jpg)
index
shard 1 shard 2 shard N
select join_date from users where user_id = X
![Page 36: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/36.jpg)
index
shard 1 shard 2 shard N
select join_date from users where user_id = X
returns 2012-02-05
![Page 37: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/37.jpg)
Ticket Servers
![Page 38: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/38.jpg)
Globally Unique ID
![Page 39: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/39.jpg)
CREATE TABLE `tickets` ( `id` bigint(20) unsigned NOT NULL auto_increment, `stub` char(1) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `stub` (`stub`)) ENGINE=MyISAM
![Page 40: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/40.jpg)
REPLACE INTO tickets (stub) VALUES ('a');SELECT LAST_INSERT_ID();
Ticket Generation
![Page 41: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/41.jpg)
REPLACE INTO tickets (stub) VALUES ('a');SELECT LAST_INSERT_ID();
SELECT * FROM tickets;
Ticket Generation
id stub
4589294 a
![Page 42: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/42.jpg)
auto-increment-increment = 2auto-increment-offset = 1
auto-increment-increment = 2auto-increment-offset = 2
tickets A
tickets B
![Page 43: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/43.jpg)
auto-increment-increment = 2auto-increment-offset = 1
auto-increment-increment = 2auto-increment-offset = 2
tickets A
tickets B
NOT master-master
![Page 44: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/44.jpg)
Shards
![Page 45: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/45.jpg)
Object Hashing
![Page 46: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/46.jpg)
user_id : 500
A B
![Page 47: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/47.jpg)
user_id : 500 % (# active replicants)
A B
![Page 48: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/48.jpg)
user_id : 500 % (# active replicants)
'etsy_index_A' => 'mysql:host=dbindex01.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_index_B' => 'mysql:host=dbindex02.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_shard_001_A' => 'mysql:host=dbshard01.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_001_B' => 'mysql:host=dbshard02.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_A' => 'mysql:host=dbshard03.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_B' => 'mysql:host=dbshard04.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_A' => 'mysql:host=dbshard05.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_B' => 'mysql:host=dbshard06.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw',
A B
![Page 49: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/49.jpg)
user_id : 500 % (# active replicants)
'etsy_index_A' => 'mysql:host=dbindex01.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_index_B' => 'mysql:host=dbindex02.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_shard_001_A' => 'mysql:host=dbshard01.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_001_B' => 'mysql:host=dbshard02.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_A' => 'mysql:host=dbshard03.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_B' => 'mysql:host=dbshard04.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_A' => 'mysql:host=dbshard05.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_B' => 'mysql:host=dbshard06.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw',
A B
![Page 50: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/50.jpg)
user_id : 500 % (2)
A B
![Page 51: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/51.jpg)
user_id : 500 % (2) == 0
A B
![Page 52: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/52.jpg)
user_id : 500 % (2) == 0 select ... insert ...update ...
A B
![Page 53: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/53.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
A B
![Page 54: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/54.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
select ... insert ...update ...
500select ... insert ...update ...
501A B
![Page 55: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/55.jpg)
Failure
![Page 56: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/56.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
A B
![Page 57: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/57.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
A B
![Page 58: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/58.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
A B
![Page 59: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/59.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
'etsy_index_A' => 'mysql:host=dbindex01.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_index_B' => 'mysql:host=dbindex02.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_shard_001_A' => 'mysql:host=dbshard01.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_001_B' => 'mysql:host=dbshard02.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_A' => 'mysql:host=dbshard03.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_B' => 'mysql:host=dbshard04.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_A' => 'mysql:host=dbshard05.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_B' => 'mysql:host=dbshard06.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw',
A B
![Page 60: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/60.jpg)
user_id : 500 % (2) == 0user_id : 501 % (2) == 1
'etsy_index_A' => 'mysql:host=dbindex01.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_index_B' => 'mysql:host=dbindex02.ny4.etsy.com;port=3306;dbname=etsy_index;user=etsy_rw', 'etsy_shard_001_A' => 'mysql:host=dbshard01.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_001_B' => 'mysql:host=dbshard02.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_A' => 'mysql:host=dbshard03.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_002_B' => 'mysql:host=dbshard04.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_A' => 'mysql:host=dbshard05.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw', 'etsy_shard_003_B' => 'mysql:host=dbshard06.ny4.etsy.com;port=3306;dbname=etsy_shard;user=etsy_rw',
A B
![Page 61: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/61.jpg)
user_id : 500 % (1) == 0user_id : 501 % (1) == 0
A B
![Page 62: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/62.jpg)
ORM
![Page 63: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/63.jpg)
connection handlingshard lookup
replicant selection
![Page 64: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/64.jpg)
CRUDcache handlingdata validation
data abstraction
![Page 65: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/65.jpg)
Shard Selection
![Page 66: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/66.jpg)
$config["non_writable_shards"] = array(1, 2, 3, 4);
public static function getKnownWritableShards(){ return array_values( array_diff( self::getKnownShards(), self::getNonwritableShards() )); }
Non-Writable Shards
![Page 67: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/67.jpg)
$shards = EtsyORM::getKnownWritableShards();
$user_shard = $shards[rand(0, count($shards) - 1)];
user_id shard_id
500
Initial Selection
![Page 68: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/68.jpg)
user_id shard_id
500 2
Initial Selection
$shards = EtsyORM::getKnownWritableShards();
$user_shard = $shards[rand(0, count($shards) - 1)];
![Page 69: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/69.jpg)
index
shard 1 shard 2 shard N
select shard_id from user_index where user_id = X
Later....
![Page 70: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/70.jpg)
Variants
![Page 71: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/71.jpg)
shard 1
user_id group_id
1 A
1 B
2 A
2 C
shard 2
user_id group_id
3 A
3 B
4 A
5 C
SELECT user_id FROM users_groups WHERE group_id = ‘A’
![Page 72: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/72.jpg)
shard 1
user_id group_id
1 A
1 B
2 A
2 C
shard 2
user_id group_id
3 A
3 B
4 A
5 C
SELECT user_id FROM users_groups WHERE group_id = ‘A’Broken!
![Page 73: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/73.jpg)
shard 1
user_id group_id
1 A
1 B
2 A
2 C
shard 2
user_id group_id
3 A
3 B
4 A
5 C
SELECT user_id FROM users_groups WHERE group_id = ‘A’Broken!
JOIN?
![Page 74: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/74.jpg)
shard 1
user_id group_id
1 A
1 B
2 A
2 C
shard 2
user_id group_id
3 A
3 B
4 A
5 C
SELECT user_id FROM users_groups WHERE group_id = ‘A’Broken!
JOIN?
![Page 75: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/75.jpg)
users_groupsuser_id group_id
1 A
1 B
2 A
2 C
groups_usersgroup_id user_id
A 1
A 3
A 2
3 A
3 B
3 C
B 3
B 1
C 2
C 3
![Page 76: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/76.jpg)
indexuser_id shard_id
1 1
2 1
3 2
4 3
group_id shard_id
A 1
B 2
C 2
D 3
users_groups_index groups_users_index
separate indexes for different slices of data
![Page 77: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/77.jpg)
indexuser_id shard_id
1 1
2 1
3 2
4 3
group_id shard_id
A 1
B 2
C 2
D 3
users_groups_index groups_users_index
shard 3user_id group_id
4 A
4 B
4 C
4 D
![Page 78: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/78.jpg)
Schema Changes
![Page 79: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/79.jpg)
shard 1 shard 2 shard N
![Page 80: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/80.jpg)
shard 1 shard 2 shard N
![Page 81: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/81.jpg)
Schemanator
![Page 82: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/82.jpg)
![Page 83: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/83.jpg)
![Page 84: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/84.jpg)
shard 1 shard 2 shard N
![Page 85: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/85.jpg)
shard 1 shard 2 shard N
SET SQL_LOG_BIN = 0; ALTER TABLE user ....
![Page 86: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/86.jpg)
shard migration
![Page 87: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/87.jpg)
Why?
![Page 88: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/88.jpg)
Prevent disk from filling
![Page 89: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/89.jpg)
Prevent disk from fillingHigh traffic objects (shops, users)
![Page 90: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/90.jpg)
Prevent disk from fillingHigh traffic objects (shops, users)Shard rebalancing
![Page 91: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/91.jpg)
When?
![Page 92: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/92.jpg)
![Page 93: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/93.jpg)
Balance
![Page 94: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/94.jpg)
Added Shards
![Page 95: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/95.jpg)
# migrate_object User 5307827 2
per object migration <object type> <object id> <shard>
![Page 96: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/96.jpg)
# migrate_pct User 25 3 6
percentage migration <object type> <percent> <old shard> <new shard>
![Page 97: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/97.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 1 0 0
![Page 98: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/98.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 1 1 0
•Lock
![Page 99: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/99.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 1 1 0
•Lock•Migrate
![Page 100: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/100.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 1 1 0
•Lock•Migrate•Checksum
![Page 101: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/101.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 1 1 0
•Lock•Migrate•Checksum
![Page 102: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/102.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 2 0 1
•Lock•Migrate•Checksum•Unlock
![Page 103: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/103.jpg)
index
shard 1 shard 2 shard N
user_id shard_id migration_lock old_shard_id
1 2 0 1
•Lock•Migrate•Checksum•Unlock•Delete (from old shard)
![Page 104: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/104.jpg)
Usage Patterns
![Page 105: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/105.jpg)
Arbitrary Key Hash
![Page 106: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/106.jpg)
tag1 tag2 co_occurrence _count
“red” “cloth” 666
![Page 107: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/107.jpg)
hash_bucket shard_id
1 2
2 3
3 1
4 2
5 3
tag1 tag2 shard_id
“red” “cloth” 1
“vintage” “doll” 3
“antique” “radio” 5
“gift” “vinyl” 2
“toy” “car” 1
“wool” “felt” 2
“floral” “wreath” 5
“wood” “table” 8
“box” “wood” 4
“doll” “happy” 5
“smile” “clown” 3
“radio” “vintage” 10
“blue” “luggage” 8
“shoes” “green” 12
... ... ...
OR
![Page 108: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/108.jpg)
1. provide some key
![Page 109: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/109.jpg)
1. provide some key2. compute corresponding hash bucket
![Page 110: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/110.jpg)
1. provide some key2. compute corresponding hash bucket3. lookup hash bucket on index to find shard
![Page 111: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/111.jpg)
1,000,000 'buckets' each with a row in arbitrary_key_index which points to a shard
hash_bucket shard_id
1 2
2 3
3 1
4 2
5 3
hash_bucket == hash(‘red’, ‘cloth’) % BUCKETS
![Page 112: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/112.jpg)
1,000,000 'buckets' each with a row in arbitrary_key_index which points to a shard
hash_bucket shard_id
1 2
2 3
3 1
4 2
5 3
hash_bucket == hash(‘red’, ‘cloth’) % BUCKETS
![Page 113: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/113.jpg)
1,000,000 'buckets' each with a row in arbitrary_key_index which points to a shard
hash_bucket shard_id
1 2
2 3
3 1
4 2
5 3
hash_bucket == hash(‘red’, ‘cloth’) % BUCKETS
![Page 114: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/114.jpg)
1,000,000 'buckets' each with a row in arbitrary_key_index which points to a shard
hash_bucket shard_id
1 2
2 3
3 1
4 2
5 3
hash_bucket == hash(‘red’, ‘cloth’) % BUCKETS
![Page 115: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/115.jpg)
Partitions
![Page 116: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/116.jpg)
PARTITION BY RANGE (reference_timestamp)( PARTITION P5 VALUES LESS THAN (1317441600), PARTITION P6 VALUES LESS THAN (1320120000), PARTITION P7 VALUES LESS THAN (1322715600), PARTITION P8 VALUES LESS THAN (1325394000));
![Page 117: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/117.jpg)
Deleting a large partition: few hours, tons of disk IO
![Page 118: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/118.jpg)
Deleting a large partition: few hours, tons of disk IO
Dropping a 2G partition with 2M rows :
![Page 119: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/119.jpg)
Deleting a large partition: few hours, tons of disk IO
Dropping a 2G partition with 2M rows :
< 1s
![Page 120: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/120.jpg)
# file= "shop_stats_syndication_hourly#P#P1345867200.ibd"# ln $file $file.remove"
![Page 121: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/121.jpg)
# file= "shop_stats_syndication_hourly#P#P1345867200.ibd"# ln $file $file.remove"
# stat "shop_stats_syndication_hourly#P#P1345867200.ibd" File: `shop_stats_syndication_hourly#P#P1345867200.ibd' Size: 65536 Blocks: 136 IO Block: 4096 regular fileDevice: 6804h/26628d Inode: 41321163 Links: 2Access: (0660/-rw-rw----) Uid: ( 104/ mysql) Gid: ( 106/ mysql)
![Page 122: The Etsy Shard Architecture: Starts With S and Ends With Hard](https://reader034.vdocuments.us/reader034/viewer/2022051411/545435d3af79597c338b4b7f/html5/thumbnails/122.jpg)
tickets index
shard 1 shard 2 shard N