Google Cloud Spanner Deep Dive
GCPUG Admin
Google Developers Expert
Mercari / Merpay Solution Team
@sinmetal
深海まではたどり着けなかった・・・
https://gcpug.jp
What’s Google Cloud Spanner
2
https://gcpug.jp
Spannerの特徴
3
https://gcpug.jp
GCP Databaseの系譜 (sinmetalの主観)
4
Firebase Realtime DB, Datastore, Spannerのノウハウを融合し、よりGCPらしいDBへ
Bigtable
Datastore
Spanner
Realtime DB
Firestore
Datastoreよりパフォーマンスを改善し、大量のトラフィックを高速に捌けるようになった。
RDBのような柔軟なQueryにも対応
Bigtableをアプリケーションで使いやすいようにQueryやTxを追加し、Bigtableのインフラを共有することでコストを抑えた
https://gcpug.jp
Spanner Component
https://gcpug.jp
Spanner Component
6
Instance
DB
Table
Index
https://gcpug.jp
Nodeの数
7
https://gcpug.jp
Region or Multi-region
8
europe-west1, asia-east1は
Read Onlyにしかできない
https://gcpug.jp
Spanner Architecture
https://gcpug.jp
Spanner Architecture
10
Node
Split
Colossus
Split
Split
Split
Split
Split
Split
Node
Root Server
Insert, Update, Delete, Queryを処理するレイヤー
それぞれSpan Serverを管理する
1台のサーバというわけではなく、複数Zoneに存在している
Table, Indexが保存されているレイヤー
数は動的に変更される
https://gcpug.jp
Splitの決定方法
11
Split
Split
Split
Split
a ~ e
f ~ j
k ~ o
p ~ z
今、Splitが管理しているKeyの範囲を管理しておけば、指定されたKeyのRowを持っている可能性があるSplitが分かり、そのSplitを担当しているNodeが分かる
Node
Node
Root Server
https://gcpug.jp
Splitの分割
12
Split
Split
Split
Split
a
f ~ j
k ~ o
p ~ z
Node
Node
Root Server
Split
b ~ e
a のPKのデータばっかり増えたので、a ~ eを分割
https://gcpug.jp
Splitの再配置
13
Split
Split
Split
Split
a
f ~ j
k ~ o
p ~ z
Node
Node
Root Server
Split
b ~ e
Node
https://gcpug.jp
Splitのhotspot問題
14
Split
Split
Split
Split
aa ~ af
f ~ j
k ~ o
p ~ z
Node
Node
Root Server
Split
b ~ e
Split
ag ~ az
hot
spot
hot
spot
hot
spot
hotspotが発生しないようにSpannerはSplitの分割や、Splitの担当のNodeを動的に変更していくが、どうにもならん時もある
ひたすらaのPKのデータが増えていく
https://gcpug.jp
Primary Key
https://gcpug.jp
PKの戦略
16
https://gcpug.jp
PKの戦略
17
10,20,30,40,50
INSERT 10, 20, 30, 40, 50
10,20
30,40,50
INSERT 60,70,80,90
30,40,50, 60, 70, 80 ,90
10,20
30,40,50
60,70,80,90
10,20
分割
分割
後ろのSplitにINSERTが偏る
https://gcpug.jp
PKの戦略
18
30,40,50
60,70,80,90
10,20
INSERT 15,23,35,67
30,35,40,50
60,67,70,80,90
10,15,20,23
データが増えていく時に別々のSplitに書き込まれるようにしていく
データがない状態だと、分散していない状態からスタートするので、じわじわデータを入れていく
Cloud Datastoreのベストプラクティス "500/50/5" rule が参考になる
ローンチした瞬間に凄まじいトラフィックが予想される場合は、ダミーデータを入れておくなども有効
NOTE : “500/50/5” rule
新しいKindに対するオペレーションは毎秒 500 回を上限とし、その後でトラフィックを50%ずつ、5 分ごとに増やしていく
理論上は、この増加スケジュールを使用すると 90 分後に毎秒 740,000 回までオペレーションを増やすことをできる
https://gcpug.jp
PKの戦略
19
https://gcpug.jp
複合Keyの戦略
20
https://gcpug.jp
複数カラムをPKに設定
21
CREATE TABLE TweetCompositeKey (
Id STRING(MAX) NOT NULL,
Author STRING(MAX) NOT NULL,
) PRIMARY KEY (Id,Author)
https://gcpug.jp
ハッシュ
22
CREATE TABLE TweetHashKey (
Key STRING(MAX) NOT NULL,
Id STRING(MAX) NOT NULL,
Author STRING(MAX) NOT NULL,
) PRIMARY KEY (Key)
func NewKey(id string, author string) string {
return fmt.Sprintf("%x", sha256.Sum256([]byte(fmt.Sprintf("%s-_-%s", id, author))))
}
https://gcpug.jp
UUID + UNIQUE INDEX
23
CREATE TABLE TweetUniqueIndex (
Id STRING(MAX) NOT NULL,
TweetId STRING(MAX) NOT NULL,
Author STRING(MAX) NOT NULL,
) PRIMARY KEY (Id)
CREATE UNIQUE INDEX natural_key
ON TweetUniqueIndex (
TweetId,
Author
)
https://gcpug.jp
INDEXの仕組み
24
https://gcpug.jp
単調増加する値のINDEX
25
https://gcpug.jp
Shard利用時のクエリ
CREATE INDEX Order1MShardCreatedAtDesc
ON Order1M (ShardCreatedAt, CreatedAt DESC)
SELECT *
FROM Order1M@{FORCE_INDEX=Order1MShardCreatedAtDesc}
WHERE ShardCreatedAt BETWEEN 0 AND 9
ORDER BY CreatedAt DESC
LIMIT 10
26
https://gcpug.jp
Shard利用時のクエリ
27
Split
Split
Split
0 ~ 1
6 ~ 7
8 ~ 9
Node
Node
Root Server
Split
4 ~ 5
Split
2 ~ 3
クエリ時は ShardId BETWEEN 0 AND 9を指定することで、全Splitに対してクエリを実行する
INSERT時はShardIdによってSplitに分散される
https://gcpug.jp
PKを分散させればhotspotは起きないのか?
28
https://gcpug.jp
Spannerの気持ちになって、都度考える
29
https://gcpug.jp
Shard利用時のクエリ
CREATE INDEX Order1MShardCreatedAtDesc
ON Order1M (ShardCreatedAt, CreatedAt DESC)
SELECT *
FROM Order1M@{FORCE_INDEX=Order1MShardCreatedAtDesc}
WHERE ShardCreatedAt BETWEEN 0 AND 9
ORDER BY CreatedAt DESC
LIMIT 10
30
実はこのクエリ、フルスキャンするので、別に早くない。
ShardはREADを早くするものではなく、WRITEを早くするもの
https://gcpug.jp
Shard利用時のクエリ
31
フルスキャン!!!!!!
https://gcpug.jp
こうすると早くなる
SELECT c.*
FROM UNNEST(GENERATE_ARRAY(0, 9)) AS OneShardCreatedAt,
UNNEST(ARRAY(
SELECT AS STRUCT *
FROM Order1M@{FORCE_INDEX=Order1MShardCreatedAtDesc}
WHERE ShardCreatedAt = OneShardCreatedAt
ORDER BY CreatedAt DESC LIMIT 10
)) AS c
ORDER BY c.CreatedAt DESC
LIMIT 10
32
各ShardのORDER BY CreatedAt DESC LIMIT 10を取得した後、選別
https://gcpug.jp
各Shardの10件を取得
33
https://gcpug.jp
Query
https://gcpug.jp
FORCE_INDEX
35
https://gcpug.jp
FORCE_INDEX
# INDEX
CREATE INDEX OrderDetail1GItemIdAsc ON OrderDetail1G ( ItemId )
# QUERY
SELECT * FROM OrderDetail1G WHERE ItemId = "1" LIMIT 100
36
https://gcpug.jp
FORCE_INDEX
フルスキャン・・・!
37
https://gcpug.jp
FORCE_INDEX
# INDEX
CREATE INDEX OrderDetail1MItemIdAsc ON OrderDetail1M ( ItemId )
# QUERY
SELECT * FROM OrderDetail1M@{FORCE_INDEX=OrderDetail1MItemIdAsc}
WHERE ItemId = "1" LIMIT 100
38
https://gcpug.jp
FORCE_INDEX
39
https://gcpug.jp
Pagination
https://gcpug.jp
SpannerでPaginationは難しい
41
https://gcpug.jp
Paginationを頑張る
SELECT * FROM Order1M@{FORCE_INDEX=Order1MCreatedAtDesc} ORDER BY CreatedAt DESC, OrderId LIMIT 10
Last Row
42
https://gcpug.jp
Paginationを頑張る
SELECT *
FROM Order1M@{FORCE_INDEX=Order1MCreatedAtDesc}
WHERE
(CreatedAt < "2018-08-26T16:15:09.436784119Z")
OR (CreatedAt = "2018-08-26T16:15:09.436784119Z" AND OrderId > "b28e0a2d-f131-497b-8eb3-71d5cdeddcf0")
ORDER BY CreatedAt DESC, OrderId LIMIT 10
43
UNIQUEになるようにPKを指定する。
INDEXにも末尾にOrderIdを入れる。
https://gcpug.jp
JOIN HINT
https://gcpug.jp
45
https://gcpug.jp
JOIN HINT
SELECT
I.ItemSubId,
Count(1) as Count
FROM (SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM Item1K) I
JOIN@{JOIN_TYPE=HASH_JOIN}
(SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM OrderDetail1M) OD
ON I.ItemSubId = OD.ItemSubId
GROUP BY 1
ORDER BY Count DESC
LIMIT 10
46
未指定の場合は、大抵APPLY_JOIN使ってる気がする
https://gcpug.jp
APPLY JOIN
SELECT OD.OrderId, OD.OrderDetailID, OD.ItemId, I.Name, I.CategoryId, I.Price
FROM OrderDetail1M OD
JOIN Item1K I ON OD.ItemId = I.ItemId
WHERE OD.ItemId = "145f7a9a-58df-40c1-96e0-2184395a922d"
LIMIT 10
47
https://gcpug.jp
MAPを作ってぶつける
48
https://gcpug.jp
HASH JOIN
SELECT
I.ItemSubId,
Count(1) as Count
FROM (SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM Item1K) I
JOIN@{JOIN_TYPE=HASH_JOIN}
(SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM OrderDetail1M) OD
ON I.ItemSubId = OD.ItemSubId
GROUP BY 1
ORDER BY Count DESC
LIMIT 10
49
SUBSTRで取得した値で、JOINしてるので、INDEXが効かない
https://gcpug.jp
Hashを作ってぶつける
50
https://gcpug.jp
APPLY JOINでやると・・・?
SELECT
I.ItemSubId,
Count(1) as Count
FROM (SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM Item1K) I
JOIN
(SELECT SUBSTR(ItemId, 0, 2) as ItemSubId FROM OrderDetail1M) OD
ON I.ItemSubId = OD.ItemSubId
GROUP BY 1
ORDER BY Count DESC
LIMIT 10
51
https://gcpug.jp
フルスキャン・・・!
52
https://gcpug.jp
LOOP JOIN
ループでくるくる回すJOINのはずだが、Join HintでLOOP_JOIN指定しても、特に何も起こらない悲しみを背負ったしんめたる
for _, r1 := range t1 {
for _, r2 := range t2 {
}
}
53
LOOP_JOINはARRAY_JOINと同じになったらしい... !
https://gcpug.jp
INTERLIVE
https://gcpug.jp
INTERLEAVE
55
Order(1) | name:sinmetal | price:1100 | | | |
OrderDetails(1,1) | | | itemName:ビスケット | price:100 | count:10 |
OrderDetails(1,2) | | | itemName:ガム | price:10 | count:10 |
Order(2) | name:gcpug | price:500 | | | |
OrderDetails(2,1) | | | itemName:ガム | price:10 | count:50 |
https://gcpug.jp
INTERLEAVE
CREATE TABLE Order (
OrderId STRING(MAX) NOT NULL,
) PRIMARY KEY (OrderId)`
CREATE TABLE OrderDetail (
OrderId STRING(MAX) NOT NULL,
OrderDetailId STRING(MAX) NOT NULL,
) PRIMARY KEY (OrderId, OrderDetailId),
INTERLEAVE IN PARENT Order ON DELETE CASCADE`
56
PRIMARY KEYの先頭に親のKeyを入れる
https://gcpug.jp
Sub Queryによる順序指定
https://gcpug.jp
Priceが多い100件に、UserをJoin
SELECT OrderId, O.UserId, U.Name, O.Price
FROM Order1M@{FORCE_INDEX=Order1MPriceDesc} O
JOIN User1K U ON O.UserId = U.UserId
WHERE Price > 10000
ORDER BY Price DESC
LIMIT 100
58
https://gcpug.jp
JOINしてから100件に絞ってる
59
https://gcpug.jp
FilterしてからJOIN
SELECT OrderId, O.UserId, U.Name, O.Price
FROM
(SELECT OrderId, UserId, Price
FROM Order1M@{FORCE_INDEX=Order1MPriceDesc}
WHERE Price > 10000
ORDER BY Price DESC
Limit 100) O
JOIN User1K U ON O.UserId = U.UserId
ORDER BY Price DESC
60
https://gcpug.jp
100件取得した後、JOINしている
61
https://gcpug.jp
順序を指定したい気持ちになるやつの例
62
クエリの実行方法はBigQueryに似てる
https://gcpug.jp
Queryの実行プラン
63
https://gcpug.jp
Timestamp Bounds
https://gcpug.jp
65
https://gcpug.jp
複数カラムのINDEXの活用
https://gcpug.jp
複数カラムのINDEXの活用
67
https://gcpug.jp
カラムの順番の重要性
68
https://gcpug.jp
カラムの順番の重要性
69
EQUALのカラムを前に持ってくる
https://gcpug.jp
なんで?
70
Status | Price |
1 | 100 |
1 | 150 |
1 | 200 |
2 | 70 |
2 | 100 |
2 | 170 |
3 | 10 |
Price | Status |
10 | 3 |
70 | 2 |
100 | 1 |
100 | 2 |
150 | 1 |
170 | 2 |
200 | 1 |
Statusを前に持ってくれば、Status = 1 が固まってくれるので、そこを見ればよいだけになる
Status = 1 AND Price > 100 はここに並んでる
Priceが前にあると、該当データがちらばる
https://gcpug.jp
複数カラムへのinequality filter
71
https://gcpug.jp
複数カラムへのinequality filter
72
データがどのように入っているか次第で、どっちがいいかが変わる!
https://gcpug.jp
なんで?
73
Price | CreatedAt |
70 | 2018-01-20 |
100 | 2018-01-01 |
100 | 2018-01-18 |
150 | 2018-01-10 |
150 | 2018-01-18 |
200 | 2018-01-01 |
300 | 2018-01-25 |
CreatedAt | Price |
2018-01-01 | 100 |
2018-01-01 | 150 |
2018-01-10 | 150 |
2018-01-18 | 100 |
2018-01-18 | 150 |
2018-01-20 | 70 |
2018-01-25 | 300 |
どちらを前にしても、対象のRowは色んな所に散らばる。
なるべく固まってる方を前にしてあげれば、早くなる。
https://gcpug.jp
このケースだと、sinmetalはどう考えるか?
74
Price | CreatedAt |
70 | 2018-01-20 |
100 | 2018-01-01 |
100 | 2018-01-18 |
150 | 2018-01-10 |
150 | 2018-01-18 |
200 | 2018-01-01 |
300 | 2018-01-25 |
CreatedAt | Price |
2018-01-01 | 100 |
2018-01-01 | 150 |
2018-01-10 | 150 |
2018-01-18 | 100 |
2018-01-18 | 150 |
2018-01-20 | 70 |
2018-01-25 | 300 |
Price, CreatedAtの場合、データが増えていくと、Price > 100 に該当するCreatedAtに過去のデータが延々と積まれていって、つらくなっていく。
なので、直近1ヶ月分みたいなCreatedAtの指定が基本的にあるのであれば、CreatedAtが前の方が無難かなぁ?みたいな気持ち
https://gcpug.jp
ORDER BYは重要な要素
75
https://gcpug.jp
ORDER BY CreatedAt ASC
76
Price | CreatedAt |
70 | 2018-01-20 |
100 | 2018-01-01 |
100 | 2018-01-18 |
150 | 2018-01-10 |
150 | 2018-01-18 |
200 | 2018-01-01 |
300 | 2018-01-25 |
CreatedAt | Price |
2018-01-01 | 100 |
2018-01-01 | 150 |
2018-01-10 | 150 |
2018-01-18 | 100 |
2018-01-18 | 150 |
2018-01-20 | 70 |
2018-01-25 | 300 |
ORDER BYがある場合、高い確率でORDER BYで指定したカラムを一番前に持ってくることが多い。
Priceを前に持ってきてしまうとCreatedAtでの並び替えのために、Price > 100のデータをすべて読み込む必要が出てくる
https://gcpug.jp
NOT EQUAL より EQUAL
77
https://gcpug.jp
1つのQueryで完結させるかどうか?
78
https://gcpug.jp
Transaction
https://gcpug.jp
Abortしにくい設計
https://gcpug.jp
Aborted due to transient fault
81
https://gcpug.jp
Abortされやすい状況
82
https://gcpug.jp
Transactionの範囲が広い
83
Resultは1だが
Txの範囲は2322
https://gcpug.jp
存在しないRowを取得する
84
Not Foundを期待しているコードを書くとAbortされやすいので
INSERTでぶつけに行く方が良い
https://gcpug.jp
mutationの数を数える
https://gcpug.jp
Transaction Limit
86
https://gcpug.jp
mutationの数え方
87
https://gcpug.jp
Insert
88
ID | Col1 | Col2 |
A | Hello | |
INSERT INTO Measure (ID, Col1)
VALUES ('A', 'Hello')
ID |
NULL-A |
Data Table
Col2 Index Table
Col2がNULLの場合でも、NullのRowを作成する
Data Table (ID, Col1)
Col2 Index Table (ID)
mutationは 3
https://gcpug.jp
Update
89
ID | Col1 | Col2 |
A | Hello | a |
UPDATE Measure
SET Col2 = "a"
WHERE ID = "A"
ID |
NULL-A |
a-A |
Data Table
Col2 Index Table
Keyになっている部分を更新はできないので
既存の削除と新規追加をする
DELETE NULL-A
INSERT a-A
Data Table (ID, Col2)
Col2 Index Table (ID) * 2
mutationは 4
https://gcpug.jp
Delete
90
ID | Col1 | Col2 |
A | Hello | a |
DELETE Measure
WHERE ID = "A"
ID |
a-A |
Data Table
Col2 Index Table
Data Table (Row)
Col2 Index Table (Row)
mutationは 2
https://gcpug.jp
mutationの数え方
91
https://gcpug.jp
UPDATE, DELETE は難しい
92
https://gcpug.jp
Timestamp Boundsの使い所
93
https://gcpug.jp
Unit Test
https://gcpug.jp
Unit Test
95
https://gcpug.jp
Unit Test
96
https://gcpug.jp
Monitoring
https://gcpug.jp
Metrics
98
DBごとの粒度でチェックできる
https://gcpug.jp
Alerts for high CPU utilization
99
| single-region | multi-region |
High priority total | 65% | 45% |
24-hour smoothed aggregate | 90% | 90% |
https://gcpug.jp
ex : low priority
100
https://gcpug.jp
Resources
101
https://gcpug.jp
Resources
102
https://gcpug.jp
Resources
103
https://gcpug.jp
Additional
https://gcpug.jp
Googleの思想
105
https://gcpug.jp
Colossus
106
https://gcpug.jp
Dremel
107
https://gcpug.jp
Dremel v1 Architecture
108
Leaf
Leaf
Mixer1
Colossus
Jupiter
Leaf
Leaf
Mixer1
Mixer0
http://gcpug.jp
Dremel
SELECT
state,
COUNT(1) AS count
FROM
`bigquery-public-data.samples.natality`
WHERE
year = 1987
GROUP BY
state
ORDER BY
count DESC
109
https://gcpug.jp
Dremel v1 Query Processing
110
Leaf
Leaf
Mixer1
Colossus
Jupiter
Leaf
Leaf
Mixer1
Mixer0
Leafはそれぞれ取得したデータに対して以下を実行してMixerに渡す
year = 1987
COUNT
GROUP BY state
MixerはLeafから取得したデータに対して以下を実行して、上のMixerにわたす
COUNT
GROUP BY state
http://gcpug.jp