Billion Records from SQL to Cassandra, lessons learned

Billion Records from SQL to Cassandra, lessons learned
DuyHai Doan Brice Dutheil

View Slide

#CassandraSummit @doanduyhai @BriceDutheil
Who are we ?
Brice Dutheil
Mockito
Java Track Lead @ Devoxx France
Independant contractor @ Libon
(Orange-Vallée)
DuyHai Doan
Achilles
Cassandra Technical Advocate
Former Java Developer @ Libon
2

View Slide

Agenda
•  Libon context
•  Migration strategy
•  Business code migration
•  Data Modeling
•  Take Away
3

View Slide

Libon Context

View Slide

What is Libon ?
•  Messaging app
•  VOIP (out)
•  Custom voicemail & greetings
•  SMS/chat/ﬁle transfer
•  Contacts matching
5

View Slide

Contact Matching
6
Libon User

View Slide

Contact Matching
7
Libon User Friend

View Slide

Contact Matching
8
Libon User Friend
Contact matching

View Slide

Contact Matching
9
Libon User Friend
Accept link

View Slide

Project Context
•  Application grew over the years
10

View Slide

Project Context
•  Application grew over the years
•  Already using Cassandra to handle events
•  messaging / ﬁle sharing / SMS / notiﬁcations
•  Cassandra R/W latencies ≈ 0,4 ms
•  server response time under 10 ms
11

View Slide

Project Context
•  About contacts …
12

View Slide

Project Context
•  stored as relational model in RDBMS (Oracle)
13

View Slide

Project Context
•  1 user ≈ 300 contacts
14

View Slide

Project Context
•  with millions users ‛ billions of contacts to handle
15

View Slide

Project Context
•  with millions users ‛ billions of contacts to handle
•  query latency unpredictable
16

View Slide

17

View Slide

Fixing the problem
•  Tune the RDBMS
18

View Slide

Fixing the problem
•  indices
19

View Slide

Fixing the problem
•  indices
•  partitioning
20

View Slide

Fixing the problem
•  indices
•  partitioning
•  less joins, simpliﬁed relational model
21

View Slide

Fixing the problem
•  indices
•  partitioning
•  hardware capacity increased
22

View Slide

Fixing the problem
•  indices
•  partitioning
That worked
23

View Slide

Fixing the problem
•  indices
•  partitioning
That worked
but …
24

View Slide

Back-end application
RDBMS Cassandra
25

View Slide

Next Challenges
•  High Availability (DB failure, site failure …)
26

View Slide

Next Challenges
•  Predictable performance at scale
27

View Slide

Next Challenges
•  Predictable performance at scale
•  Going to multi data-centers
28

View Slide

Going for Cassandra
•  Denormalize (if possible …)
29

View Slide

Going for Cassandra
•  Know your business ‛ know your queries
30

View Slide

Going for Cassandra
•  Linear scaling out
31

View Slide

Going for Cassandra
•  Linear scaling out
•  Consistent performance
32

View Slide

Data Migration Strategy

View Slide

Objectives
•  No downtime
34

View Slide

Objectives
•  No downtime
•  No concurrency corner-cases
35

View Slide

Objectives
•  No downtime
•  Safe rollback possible
36

View Slide

Objectives
•  No downtime
•  Safe rollback possible
•  Replay-ability & resume-ability
37

View Slide

Strategy
•  3 phases
38

View Slide

Strategy
•  3 phases
•  Write contacts to both data stores
39

View Slide

Strategy
•  3 phases
•  Old contacts migration
40

View Slide

Strategy
•  3 phases
•  Old contacts migration
•  Switch to Cassandra …
•  … and deprecate SQL
41

View Slide

Migration Phase 1
Back end server
·
·
·
SQL
SQL
SQL
C*
C*
C*
C*
C*
Write
contactUUID
42
contactId … contactUUID
129363 123e4567-
e89b-12d3…
834849
contacId(long) + contactUUID

View Slide

Migration Phase 1
Back end server
·
·
·
SQL
SQL
SQL
C*
C*
C*
C*
C*
Read
43

View Slide

Migration Phase 2
SQL
SQL
SQL
C*
C*
C*
C*
C*
For each batch of users
SELECT * FROM contacts
WHERE user_id = …
AND contact_uuid IS NULL
•  On live production, migrate old contacts
44
Old contacts created
before phase 1

View Slide

Migration Phase 2
SQL
SQL
SQL
C*
C*
C*
C*
C*
For each batch of users
SELECT * FROM contacts
WHERE user_id = …
AND contact_uuid IS NULL
Logged batches of
INSERT INTO contacts(..)
VALUES(…)
USING TIMESTAMP
now() - 1 week
•  On live production, migrate old contacts
45
Old contacts created
before phase 1

View Slide

Migration Phase 2
USING TIMESTAMP now() - 1 week

46

View Slide

Migration Phase 2
•  During data migration …
47

View Slide

Migration Phase 2
•  … concurrent writes from the migration batch …
48

View Slide

Migration Phase 2
•  … concurrent writes from the migration batch …
•  … and updates from production for the same contact
49

View Slide

Migration Phase 2
contact_uuid
name (now -1 week)
… name (now)
…
Johny … Johnny …
Insert from batch
(to the past)
Update from production
50

View Slide

Migration Phase 2
contact_uuid
name (now -1 week)
… name (now)
…
Johny … Johnny …
Future reads pick the most up-to-date value
51

View Slide

Migration Phase 2
"Write to the Past…
to save the Future"
Libon – 2014/10/08
52

View Slide

Migration Phase 3
Back end server
·
·
·
SQL
SQL
SQL
C*
C*
C*
C*
C*
Write
❌
53

View Slide

Business Code Refactoring

View Slide

Code Inventory
•  Written for RDBMS
55

View Slide

Code Inventory
•  Lots of joins (no surprise)
56

View Slide

Code Inventory
•  Designed around transactions
57

View Slide

Code Inventory
•  Designed around transactions
•  Spring @Transactional everywhere
58

View Slide

Code Inventory cont.
•  Entities go through Services & Repositories
59
Repositories
Services
ContactEntity

View Slide

•  Hibernate is auto-magic
60

View Slide

•  Hibernate is auto-magic
•  lazy loading
•  1st level cache
•  N+1 select
61
Repositories
Services
ContactEntity

View Slide

Which options ?
•  Throw existing code …
•  … and re-design from scratch for Cassandra
62

View Slide

Which options ?
•  Throw existing code …
•  … and re-design from scratch for Cassandra
No way !
63

View Slide

Code Quality
•  Existing business code has…
•  … ≈ 3500 unit tests
64

View Slide

Code Quality
•  Existing business code has…
•  … ≈ 3500 unit tests
•  and ≈600+ integration tests
65

View Slide

Code Quality
•  We are TDD aﬁcionados …
66

View Slide

Code Quality
•  We are TDD aﬁcionados …
•  … and we love our code coverage
67

View Slide

Code Quality
"The code coverage
is one of your most
valuable technical asset"
Libon – since beginning
68

View Slide

Repositories
Services
Refactoring Strategy
ContactMatchingService
ContactService
ContactSync
ContactEntity
n
1
n n
69

View Slide

Repositories
Services
ContactService
ContactNoSQLEntity
ContactSync
ContactEntity
n
1
n n
70
Proxy

View Slide

Repositories
Services
ContactService
ContactNoSQLEntity
ContactSync
ContactEntity
n
1
n n
Denorm2
… DenormN
Denorm1
71
Proxy

View Slide

•  Use CQRS
•  ContactReadRepository
•  ContactWriteRepository
•  ContactUpdateRepository
•  ContactDeleteRepository
72

View Slide

•  ContactReadRepository
•  direct sequential read
•  no joins
•  1 read ≈ 1 SELECT
73

View Slide

•  ContactWriteRepository
•  write to all denormalized tables
•  using CQL logged batches
•  use TTLs
74

View Slide

•  ContactUpdateRepository
•  read-before-write most of the time
•  rare updates ‛ acceptable perf penalty
75

View Slide

•  ContactDeleteRepository
•  delete
•  update contact modiﬁcation date
76

View Slide

Outcome
•  5 months of 2 men work
77

View Slide

Outcome
•  Many iterations to ﬁx bugs (thanks to IT)
78

View Slide

Outcome
•  Lots of performance benchmarks using Gatling
79

View Slide

Gatling Output
80

View Slide

Outcome
‛ data model & code validation
81

View Slide

Outcome
‛ data model & code validation
•  … we are almost there for production
82

View Slide

Data Model

View Slide

Denormalization, the good
•  Support fast reads
•  1 read ≈ 1 SELECT
•  Worthy because mostly read, few updates
84

View Slide

Denormalization, the bad
•  Updating mutable data can be nightmare
•  Data model bound by existing client-facing API
•  Update paths very error-prone without tests
85

View Slide

Data model in detail
Contacts_by_id
Contacts_by_identifiers
Contacts_in_profiles
Contacts_by_modification_date
Contacts_by_firstname_lastname
Contacts_linked_user
86

View Slide

Contacts_by_id
87
user_id always component
of partition key

View Slide

Scalable design
88
n1
n2
n3
n4
n5
n6
n7
n8
A
B
C
D
E
F
G
H
user_id1
user_id2
user_id3
user_id4
user_id5

View Slide

Scalable design
89
n1
n2
n3
n4
n5
n6
n7
n8
A
B
C
D
E
F
G
H
user_id1
user_id2
user_id3
user_id4
user_id5

View Slide

Bloom filters in action
90
•  For some tables, partition key = (user_id, contact_id)
‛ fast look-up, leverages Bloom ﬁlters
‛ touches 1 SSTable most of the time

View Slide

Contacts_by_id
91
Wide partition
Bucketed

View Slide

A "queue" story
92
•  contacts_by_modiﬁcation_date
•  queue-like pattern

View Slide

A "queue" story
93
•  contacts_by_modiﬁcation_date
•  queue-like pattern
‛ buckets to the rescue
user_id:2014-12
date35
date12
… … date47
… … … …
user_id:2014-11
date11
date12
… … date34
… … … …

View Slide

Data model summary
•  7 tables for denormalization
94

View Slide

Data model summary
•  Normalize some tables because rare access
95

View Slide

Data model summary
•  Normalize some tables because rare access
•  Read-before write in most update scenarios
96

View Slide

Notes on contact_id
•  In SQL, auto-generated long using sequence
•  In Cassandra, auto-generated timeuuid
97

View Slide

Notes on contact_id
•  How to store both types ?
98

View Slide

Notes on contact_id
•  As text ? ‛ easy solution …
99

View Slide

Notes on contact_id
•  As text ? ‛ easy solution …
•  … but waste of space !
•  because encoded as UTF-8 or ASCII in Cassandra
100

View Slide

Notes on contact_id
•  Long ‛ 8 bytes
•  Long as text(UTF-8: 1 byte)
‛ "digits count" bytes
101

View Slide

Notes on contact_id
•  UUID ‛ 16 bytes
•  32 hex chars + 4 hyphens = 36 chars
•  UUID as text(UTF-8: 1 byte)
‛ 36 bytes
•  Bytes overhead = 36 – 16 = 20 bytes
102

View Slide

Notes on contact_id
•  20 bytes wasted per contact uuid
103

View Slide

Notes on contact_id
•  × 7 denormalizations = 140 bytes per contact uuid
104

View Slide

Notes on contact_id
•  × 7 denormalizations = 140 bytes per contact uuid
•  × 109 contacts = 140 GB wasted

105
not even counting replication factor …

View Slide

Notes on contact_id
•  ‛ just save contact id as byte[ ]
106

View Slide

Notes on contact_id
•  Achilles @TypeTransformer for automatic conversion
(see later)
107

View Slide

Notes on contact_id
•  Achilles @TypeTransformer for automatic conversion
(see later)
•  Use blobAsBigInt( ) or blobAsUUID( ) to view data
108

View Slide

Achilles
•  Advanced "object mapper"
•  Fluent API
•  Tons of features
•  TDD friendly
109

View Slide

Achilles
•  Dirty checking, why is it important ?
110

View Slide

Achilles
•  1 contact ≈ 8 mutable ﬁelds
111

View Slide

Achilles
•  × 7 denormalizations = 56 update combinations …
112

View Slide

Achilles
•  × 7 denormalizations = 56 update combinations …
•  and not even counting multiple ﬁelds updates …
113

View Slide

Achilles
•  Are you going to manually generate 56+ prepared
statements for all possible updates ?
114

View Slide

Achilles
•  Are you going to manually generate 56+ prepared
statements for all possible updates ?
•  Or just use dynamic plain string statements and get
some perf penalty ?
115

View Slide

Achilles
•  Dirty check in action
//No read-before-write
ContactEntity proxy = manager.forUpdate(ContactEntity.class, contactId);
proxy.setFirstName(…);
proxy.setLastName(…); //type-safe updates
proxy.setAddress(…);
manager.update(proxy);
116

View Slide

Achilles
117
Empty
Entity
DirtyMap
Proxy Setters interception
PrimaryKey

View Slide

Achilles
•  Dynamic statements generation
UPDATE contacts SET firstname=?, lastname=?,address=?
WHERE contact_id=?
118
prepared statements are cached, of course

View Slide

Achilles
•  Insert strategy, what is it ?
119

View Slide

Achilles
•  Simple INSERT prepared statement
INSERT INTO
contacts(contact_id,name,age,address,gender,avatar,…)
VALUES(?, ?, ?, ? … ?);
120

View Slide

Achilles
•  Runtime values binding
•  some columns are optional
preparedStatement.bind(49374,’John DOE’,33, null, null, …, null);
121

View Slide

Achilles
Wait … are you saying inserting null in CQL???

122

View Slide

Achilles
Inserting null 㲇 creating tombstones
123

View Slide

Achilles
× 7 denormalizations
124

View Slide

Achilles
× 7 denormalizations
× billions of contacts created

125
not even counting replication factor …

View Slide

Achilles
@Entity(table = "contacts_by_id »)
@Strategy(insert = InsertStrategy.NOT_NULL_FIELDS)
public class ContactById {
}
126
•  Simple annotation

View Slide

Achilles
•  Runtime dynamic INSERT statement
INSERT INTO
contacts(contact_id, name, age, address,)
VALUES(:contact_id, :name, :age, :address);
127
prepared statements are cached, of course

View Slide

Achilles
@PartitionKey
@Column(name = "contact_id")
@TypeTransformer(valueCodecClass = ContactIdToBytes.class)
private ContactId contactId;
128
•  Remember the contactId ⁶ byte[ ] conversion ?
BYOC ‛ Bring Your Own Codec

View Slide

Achilles
public interface Codec {
Class sourceType();
Class targetType();
TO encode(FROM fromJava)
FROM decode(TO fromCassandra);
}
129

View Slide

Achilles
130
2014-12-01 14:25:20,554 Bound statement : [INSERT INTO
contacts.contacts_by_modification_date(user_id,month_bucket,modification_date,...) VALUES
(:user_id,:month_bucket,:modification_date,...) USING TTL :ttl;] with CONSISTENCY LEVEL [LOCAL_QUORUM]
2014-12-01 14:25:20,554 bound values : [222130151, 2014-12, e13d0d50-7965-11e4-af38-90b11c2549e0, ...]
2014-12-01 14:25:20,701 Bound statement : [SELECT birthday,middlename,avatar_size,... FROM
contacts.contacts_by_modification_date WHERE user_id=:user_id AND month_bucket=:month_bucket AND
(modification_date)>=(:modification_date) ORDER BY modification_date ASC;] with CONSISTENCY LEVEL
[LOCAL_QUORUM]
2014-12-01 14:25:20,701 bound values : [222130151, 2014-10, be6bc010-6109-11e4-b385-000038377ead]
•  Dynamic logging in action

View Slide

Achilles
131
•  Dynamic logging
•  runtime activation
•  no need to recompile/re-deploy
•  save us hours of debugging
•  TRACE log level ‛ query tracing

View Slide

Billion Records from SQL to Cassandra, lessons learned

Billion Records from SQL to Cassandra, lessons learned

Brice Dutheil

More Decks by Brice Dutheil

Other Decks in Programming

Featured

Transcript