for things like fraud prevention Requirements Real-time reads / monthly import within a day Constraints Limited extra budget and engineering effort Bottlenecks ? (we check with a thought experiment next) 目的は、電話番号の契約状況を不正利用対策などに使うこと。要件は「リアルタイムに読む」「毎月 1日以内に 取り込む」。予算も工数も限られる 11
a vendor uploads compressed CSV files to S3 Delivered CSV characteristics ◦ More than 250 million phone numbers ◦ Holds up to 20+ years of contract status ◦ Contains the date and status at each contract-status change ベンダーから毎月、圧縮 CSVが S3 に届く。電話番号は 2.5億件超で、最大 20年以上の契約状況が入っている 12
rows written per second 2. Measure a reference value on our in-house DB 3. From [1.] and [2.], work out what system setup is needed 作り込む前に概算で桁をつかむ（ Back-of-the-envelope estimation）。3つの手順で考える https://highscalability.com/google-pro-tip-use-back-of-the-envelope-calculations-to-choo/ 22
infrastructure budget becomes huge ◦ A dozen to several dozen writers' worth (in Aurora terms), and 6–12 TB of storage Implementation effort and operations would be painful ◦ A. Change the data store: migrating the DB and new operational know-how ◦ B. Split Aurora: shard routing, re-runs, failure recovery, and operations でもどちらも、予算・容量・実装・運用がかなり大変。本当に現実的？ 28
/ monthly import within a day Constraints Limited extra budget and engineering effort Bottlenecks Writing 60B rows a day is unrealistic ここまでを整理すると、ボトルネックは「 600億件を1日で書き込むのは非現実的」 31
for things like fraud prevention Requirements Real-time reads / monthly import within a day Constraints Limited extra budget and engineering effort Bottlenecks Writing 60B rows a day is unrealistic プロジェクトの要件・制約・ボトルネックをおさらい 35
for things like fraud prevention Requirements Real-time reads / monthly import within a day Constraints Limited extra budget and engineering effort Bottlenecks Writing 60B rows a day is unrealistic ここからは、要件のうち「履歴をリアルタイムに読み取る」に注目する 36
is no requirement to search or aggregate across numbers by the history's month or status → We do not need to keep each history entry as its own record 履歴の年月やステータスで電話番号を横断して検索・集計する要件はない。だから履歴を 1件ずつ別レコード にしなくていい 42
• • • One phone number = one document The history is held as a list The number of stored items stays at the number of phone numbers 1電話番号 = 1ドキュメントにして、履歴はリストで持つ。保存件数は電話番号の数まで減らせる Works well if you don't need indexes on month or status! There are trade-offs, though... 43
• Needed: ◦ 250M ÷ 86,400 sec ≈ ~2,900 records/sec • Thought experiment measurement: ◦ ~10K–50K records/sec (single writer) • Note: ◦ Each record is ~4× larger than in the thought experiment, so expect slightly lower write throughput. → It looks feasible within one writer 必要なのは約 2,900件/秒。思考実験の計測（ 1万〜5万件/秒）から見て、 1 Writer でいけそう 46
Now Realistic Bottlenecks This project After the data model Writing 60B records a day (~694K/sec) Writing 250M records a day (~2,900/sec) → Revising the data model made the bottleneck manageable データモデルを見直したことで、ボトルネックが現実的な範囲に収まった 47
budget? Aurora MySQL DynamoDB Elasticsearch Added storage and I/O Import or writes + storage VCUs + storage ◦ ◦ ◦ → Every candidate can meet the cost constraint コストは課金の仕方こそ違うけど、どれも予算内に収まりそう。ここでも決まらない 60
did not differ on requirements or cost • We chose MySQL in terms of development effort and operational overhead → The downside, higher write load on the DB, we decided to handle by running in low-traffic hours 3候補とも、要件・コストでは差がつかなかった。開発工数と運用負担から MySQL を選び、書き込み負荷はト ラフィックの少ない時間帯に逃がす 66
project This section Requirements Real-time reads / monthly import within a day 250M rows within a day / redo safely Constraints Limited extra budget and engineering effort — Bottlenecks Writing 250M records Aurora write speed and load → Design the batch from the requirements and bottlenecks バッチ設計で見るのは、要件（ 1日以内・安全にやり直せる）とボトルネック（既存 Aurora への書き込み） 70
time Q2 Recovery Question Can we import within a day? How do we handle re-runs? Driven by Requirement, bottleneck Requirement この流れに要件とボトルネックを当てると、問いが 2つ出てくる。1日以内に取り込めるか、リランをどうするか 73
Needed: 250M ÷ 86,400 sec ≈ ~2,900 rows/sec • Measured with serial writes: ~1,000 rows/sec → It cannot be done without parallel import (3 or more in parallel) 直列だと約70時間かかる。並列で取り込まないと間に合わない（ 3並列以上） 77
Result • Write with Bulk Upsert keyed on the phone number • Importing the same data again gives the same result 電話番号をキーに Bulk Upsert で書けば、同じデータを何度取り込んでも結果は変わらない 82
Result • Write with Bulk Upsert keyed on the phone number • Importing the same data again gives the same result → But reprocessing everything still costs time ただし、これだけだと止まるたびに全件をやり直すコストが残る 83
Question Can we import within a day? How do we handle re-runs? Solution Bulk Upsert in parallel Idempotence: Bulk Upsert / Resumability: records per file 2つの問いの答えがそろった。並列 Bulk Upsert と、冪等性・ファイル単位の記録 87
the import batch's ECS task ran out of memory... Analysis: ◦ we ran a memory profiler the hard way and found the cause Cause: ◦ we were loading the entire split CSV being imported into memory 取り込みバッチのメモリが足りなくなった。 Memory Profiler を地道に回したら、分割 CSVを丸ごとメモリに載 せていた 95
• What happened: ◦ Frequent deadlocks during bulk upserts Analysis: ◦ Investigated locks using performance_schema.data_locks Cause: ◦ Under REPEATABLE READ, X locks on the PRIMARY supremum blocked each other's insert intention locks, causing circular waits 並列 Bulk Upsert で Deadlock が大量発生。 data_locks を見ると、REPEATABLE READ で supremum に X ロックが残っていた Scan the QR code to access the GitHub repository with the reproduction script! 98
Tx's isolation level: REPEATABLE READ → READ COMMITTED No more X lock on the supremum pseudo-record Downside: read skew can occur ◦ Given our requirements, this problem cannot happen, so we accepted it 分離レベルを READ COMMITTED に変えて、supremum のロックを取らないようにした。読み取りスキュー は今回の要件では起きないので許容 https://speakerdeck.com/occhi/prevent-concurrency-problems-a-practical-introduction-to-transactions?slide=101 100
As write traffic from users grew, the Writer DB's CPU usage kept climbing What we found: ◦ We were Bulk Upserting all 250M rows every month ◦ Of each monthly import, fewer than 5 million rows actually need updating ユーザーの書き込みが増えるにつれて Writer の CPU が上がり続けた。毎月 2.5億件全部を書いていたが、 毎月の取り込みで実際に更新したいのは 500万件未満だった 105
◦ Narrowed down the Bulk Upsert targets ◦ Changed DB writes from 4 parallel to serial Details ◦ Narrowed down what to update inside the decompress-and-split batch, greatly reducing the amount written ▪ Change in update targets: from all 250M rows to fewer than 5 million ◦ This let the serial import finish within a day 分割バッチで更新対象を絞って、書き込む量を大幅に減らした。これで直列でも 1日以内に取り込めるように なった Happy to talk about how we chose what to update at the after-party! 106
Docs and Benchmark Requirement Conclusion Evidence Reads One doc by ID; even complex searches P50 36ms, P99 316ms Get API / Stress test Writes Reference: ~51,700 docs/sec Elastic Search Labs: Serverless stress test → Expected to meet them, per official sources 付録：Elasticsearch も ID で取れて、公式ベンチマークでは秒間 5万件超の取り込み（条件は今回と違う） 112
• • • All are estimates from selection time. Their assumptions differ, so totals are not compared side by side Aurora MySQL: storage and I/O added to the existing cluster ◦ Expected to fit in $1,000/month; not yet calculated from measurements DynamoDB (update the existing table): ~$186/month for writes + storage ◦ Assuming items of 1KB or less, 1 WRU each, ~260M items DynamoDB (S3 Import): priced on uncompressed size; ~$1.37 came from the compressed size and is not settled Elasticsearch (Elastic Cloud Serverless): ~$105/month ◦ Search VCU ~$78, ingest ~$10, storage ~$17 (assumed VCU usage) 付録：選定時のコスト概算（前提条件つき） 113
Multiple processes Multiple threads Needs a separate mechanism Stays within one process's thread pool → We chose threads: management stays inside the import batch 付録：並列化の手段は複数スレッドにした。管理が取り込みバッチの中で完結するから 122
Time and DB Load 3 parallel 4 parallel Import time ~22 hours ~14 hours DB CPU usage ~10% higher ~15% higher → Balancing import time and DB load, we chose 4 付録：並列度は、取り込み時間と DB 負荷を計測して 4並列にした 123
SiteGround - Reliable hosting with speed, security, and support you can count on.
occhi
furuton
bgpat
tomokusaba
kworkdev
maimyyym
terapyon
riyaamemiya
.png){kind=avatar}
jmdc_presentation
puhitaku
takonda
oikon48
bitkey
tomoyanonymous
rabernat
axbom
aleyda
cassininazir
marketingsoph
baasie
keithpitt
codingconduct
lynnandtonic
shpigford
seathinner
