˜.*9* גࣜձࣾ.*9*ᇙ໌ल %FWFMPQFST4VNNJU ʮNJYJͷ෣୆ཪʯ 5J%#Ͱ࣮ݱ͢Δ4/4ͷεέʔ ϥϏϦςΟͱύϑΥʔϚϯε

mixi2ʹ͍ͭͯ • ୹จςΩετSNS • ʮϑΥϩʔʯλΠϜϥΠϯͷଞɺSNS mixiʹ͋ͬͨίϛϡχςΟػೳ΍ΑΓ ϦΞϧλΠϜͳίϛϡχέʔγϣϯ޲ ͚ͷΠϕϯτػೳ • αʔό͸GoɺϞόΠϧΫϥΠΞϯτ͸ FlutterΛ࠾༻ • ॳظͷػೳ։ൃ͸4ਓ(ϦϦʔε࣌͸7ਓ)

mixi2 architecture

DBͷબఆ SNSΛ։ൃ͢Δͱ͖ʹٻΊͨཁ݅ • ϦΫΤετɺϢʔβʔͷ૿ՃʹରԠͰ͖ΔεέʔϥϏϦςΟ • ো֐ൃੜ࣌ͷϑΣΠϧΦʔόʔ • ʢαʔϏεͱͯ͠ڐ༰Ͱ͖ΔʣϨΠςϯγʔ • τϥϯβΫγϣϯ • ϝϯςφϯεΦϖϨʔγϣϯͷ༰қ͞ • ίετ໘ NewSQLʹ͍ͭͯಋೖݕ౼Λͨ͠

NewSQLͱ͸ SQLΠϯλʔϑΣʔεΛ࣋ͪ ैདྷͷϦϨʔγϣφϧσʔλϕʔε͕࣋ͭτϥϯβΫγϣϯͱ ڧ͍੔߹ੑΛอূ͢Δ෼ࢄσʔλϕʔε TiDB, Spanner, CockroachDB, YugaByteDBͳͲ

NewSQLͷݕ౼ ͦΕͧΕͷಛੑΛൺֱ͠ɺࠓճ͸TiDBΛ࠾༻ τϥϯβΫ γϣϯ ϨΠςϯγʔ ଱ো֐ੑ ӡ༻ίετ Πϯϑϥඅ༻ 3%# 4IBSEJOH ̋ʢ˚ʣ ̋ ˚ ˚ ̋ /FX42- ̋ ˚ ̋ ̋ ˚

TiDBͷಛ௃ • ໾ׂ͝ͱͷίϯϙʔωϯτ͕෼ࢄ഑ஔ͞Ε ͨΞʔΩςΫνϟ • TiDB, TiKV, PD • ֤ίϯϙʔωϯτ͸৑௕Խ͢Δ͜ͱʹΑΓ ύϑΥʔϚϯεͱ଱ো֐ੑΛߴΊΒΕΔ • SQL LayerͷTiDBαʔό͸ਫฏํ޲ʹ ֦ுՄೳ • ΫϥελΛ؅ཧ͢ΔPDαʔό΋ɺ୆਺ Λ૿΍͢͜ͱͰՄ༻ੑΛߴΊΒΕΔ ʢ࠷௿̏୆ʣ Ҿ༻ݩ: https://docs.pingcap.com/tidb/stable/tidb-architecture

TiDB Storage (TiKV) • σʔλΛ֨ೲ͢Δͷ͸TiKVίϯϙʔωϯτ • σʔλʢϦʔδϣϯͱ͍͏୯ҐʣΛࣗಈͰෳ਺ ͷϨϓϦΧͰ؅ཧ͞ΕΔ • ಉ͡σʔλ͕ࡾͭίϐʔ͞ΕΔ • ߴՄ༻ੑΛ࣋ͪɺࣗಈϑΣΠϧΦʔόʔΛα ϙʔτ͢Δ • TiKVϊʔυΛΫϥελʹ௥Ճ͢Δ͜ͱͰ༰ྔ Λ૿΍͢͜ͱ͕Ͱ͖Δ • RDBͰߦ͍ͬͯͨσʔλංେԽʹ൐͏γϟʔ σΟϯάɺϚελʔ৑௕ԽͳͲ͕ෆཁʹͳΔ Ҿ༻ݩ: https://docs.pingcap.com/tidb/stable/tidb-storage

Backup • όοΫΞοϓˍϦετΞ • σʔλόοΫΞοϓ͸ฐࣾαʔϏεӡ ༻ʹ͓͍ͯඞਢཁ݅ • εφοϓγϣοτͱ૿෼ϩά͕औΕΔ • όοΫΞοϓ͔Βղੳ༻σʔλͷߏங • Dumpͷෛՙ͕ແࢹͰ͖ͳ͍͘Β͍ʹ σʔλ͕૿Ճͨ͠৔߹ɺόοΫΞοϓ ͔Β෮ݩͨ͠Ϋϥελ͔ΒDWH༻ͷ σʔλ͕औΕΔΑ͏ʹ͢Δ Starting component br: /.tiup/components/br/v8.1.0/br restore full --pd 10.30.2.92:2379 --storage s3://kan-tidb-test-tiup-backup/ snapshot-20240712-3 --send-credentials-to-tikv=false Detail BR log in /tmp/br.log.2024-07-12T08.13.06Z Full Restore <------------------------------------------------------------------- ----------------------------------------------------------------> 100.00%

TiUP • ΫϥελʔͷߏஙɺScale out/Scale inɺBackup΍෇ଐͷϕϯνπʔϧͳͲ ͢΂ͯͷૢ࡞ͰtiupίϚϯυ͕ඇৗʹ࢖ ͍΍͔ͬͨ͢ • υΩϡϝϯτ΋๛෋ • ϦϦʔεޙͷӡ༻ʹ΋ߩݙͯ͘͠Εͦ͏ tiup cluster scale-out test-cluster scaleout-tikv.yaml tikv_servers: - host: 10.20.1.206 config: server.labels: rack: r4 1. ௥Ճ͢ΔίϯϙʔωϯτΛyamlʹఆٛ͢Δ 2. tiupίϚϯυΛ࣮ߦ͢Δ

PingCAP TiDBͰࠓճͷαʔϏεཁ݅ΛຬͨͤΔͱ൑அ͠࠾༻ ։ൃॳظϑΣʔζͰ͸ɺPingCAPࣾͱִिͰmtgΛ͠ͳ͕ΒਐΊΔ

Timeline • λΠϜϥΠϯʹ͸ɺϢʔβʔͷ౤ߘͨ͠λΠ ϜϥΠϯʢϢʔβʔλΠϜϥΠϯʣͱϗʔϜ ը໘ͷλΠϜϥΠϯ͕͋Δ • ϗʔϜλΠϜϥΠϯΛදࣔ͢Δʹ͸ɺPOST ͨ͠౤ߘΛͰ͖ΔݶΓૣ͘ϑΥϩϫʔͷλΠ ϜϥΠϯʹಧ͚Δඞཁ͕͋Δ

Home Timeline Timeline • ࣄલʹϗʔϜλΠϜϥΠϯΛ࡞੒͢Δ • ౤ߘऀͷϑΥϩϫʔ/ίϛϡχςΟΛ୳ ͠ɺϑΥϩϫʔຖͷλΠϜϥΠϯςʔ ϒϧʹϙετΛॻ͖ࠐΉ ϙετIDΛ֤Ϣʔβʔ͝ͱʹ഑৴͍ͯ͠Δ Post API Redis Redis Redis Find Followers Find Community Members Timeline API

Timeline Tables (Redis) • ϢʔβʔͷϗʔϜλΠϜϥΠϯΛࣄલʹredis ʹ࡞੒͢Δ • ͜ͷ৘ใΛݩʹDBͷ৘ใͱ૊Έ߹Θͤͯ ϗʔϜλΠϜϥΠϯΛߏங͢Δ • Ұఆ਺ͷϙετ৘ใΛϢʔβʔຖʹอ࣋͢Δ • ࢀর͞ΕΔՄೳੑ͕௿͘ͳͬͨϙετ͸Ұ ఆִؒͰ࡟আ͍ͯ͠Δ post_id user: A post_id post_id post_id user: B post_id post_id post_id user: C post_id post_id Redis Sorted Set

Timeline Tables (DB) • TiDBʹ͸શͯͷϙετͱϢʔβʔλΠϜ ϥΠϯͷϙετΛه࿥ • ϗʔϜλΠϜϥΠϯ͸DBʹ͸࡞ΒΕ ͳ͍ • ϗʔϜλΠϜϥΠϯΛ࡞ΔͨΊͷ৘ ใʢϑΥϩϫʔɺίϛϡχςΟϢʔ βʔʣ͕ଘࡏ͢Δ posts post_id user_id community_id text user_posts post_id user_id community_ users community_id user_id followings following_id user_id

ෛՙςετ ϦϦʔεલʹෛՙݕূΛ࣮ࢪɻAPIͷϨΠςϯγʔͷ໨ඪΛ100msʹઃఆ • Writeੑೳ • ୅දతͳRPC͕10000RPSΛग़ͤΔͱ͜Ζ·Ͱݕূ • Readੑೳ • ౤ߘ͕ϑΥϩϫʔͷλΠϜϥΠϯʹදࣔ͞ΕΔ·Ͱͷ͕࣌ؒ5ඵ͔Β10ඵʹͳΔ͜ͱ Λ໨ඪʹௐ੔ • αʔϏε໊͔Β͋Δఔ౓Ϣʔβʔ͕དྷΔ͜ͱ͕૝ఆͰ͖ͨ

Locust - Boomer • ෛՙςετ༻ͷ؀ڥΛ༻ҙͯ͠LocustΛ࢖͍ݕূ • ฒྻ౓Λେ͖͍ͨ͘͠ͷͰgo੡ͷworker࣮૷ ͷboomerΛ࢖༻ͨ͠ • ߈ܸΫϥΠΞϯτͷεέʔϧΞ΢τɺεέʔ ϧΞοϓΛසൟʹߦ͏ͨΊɺk8s্Ͱಈ͔ͨ͠ • γφϦΦ͸Ϣʔβʔ࡞੒͔ΒೝূΛߦ͍ɺϑΥ ϩʔɺϙετɺϥΠΫͳͲͷجຊతͳಈ࡞Λશͯ ࣮ࢪ

ෛՙςετ • ॻ͖ࠐΈੑೳ͸TiDB, TiKVͷεέʔϧΞ΢τͰ૝ఆ ௨Γʹੑೳ޲্ͨ͠ • Ұ෦ɺॏ͍ॲཧʢݕࡧΠϯσοΫε΁ͷॻ͖ࠐ ΈͳͲʣΛඇಉظॲཧʹมߋ • pprofΛ࢖͍ॏ͍ॲཧΛ୳ͨ͠ • ಡΈࠐΈॲཧ΍࡟আॲཧ΋ࠓճͷ໨ඪ஋Λୡ੒͢ Δ্Ͱ͸໰୊ͳ͠ • ߴෛՙ࣌ʹෳ਺ͷϑΥϩϫʔ͕ಉ࣌ʹϑΥϩʔ͢ Δɺ͍͍ͶΛ͢ΔέʔεͰසൟϩοΫ଴ͪͷλΠϜ Ξ΢τ͕ൃੜ

ো֐ςετ Locust͔ΒෛՙΛ͔͚ͳ͕Βtidbͷ֤ίϯϙʔωϯ τΛఀࢭͤ͞ਖ਼͘͠ಈ࡞͢Δ͔ݕূͨ͠ • TiKVͷΠϯελϯεΛఀࢭͤ͞ɺmax-store- down-timeͷ࣌ؒܦաޙʹɺϦʔδϣϯϨϓϦΧ ͕Ϧόϥϯε͢Δ͜ͱ • ΫϥελʔͷTiKV୆਺ΛݮΒͯ͠Ϧόϥϯε͢Δ ͜ͱ • Placement DriverͷϦʔμʔมߋ

• ϦϦʔε೔͸૝ఆΛ্ճΔϦΫΤετ͕དྷͨ ͕ɺ֤ίϯϙʔωϯτΛεέʔϧΞ΢τ͢Δ ͜ͱͰແࣄՔಇ͠ଓ͚Δ͜ͱ͕Ͱ͖ͨ • ͓ਖ਼݄ͳͲҰ࣌తͳෛՙ͕ݟࠐ·ΕΔ৔߹΋ εέʔϧΞ΢τ͢Δ͜ͱͰ໰୊ͳ͘ӡ༻Λଓ ͚Δ͜ͱ͕Ͱ͖͍ͯΔ • ࠓ·ͰαʔϏεແఀࢭͰӡ༻ • TiDBΠϯελϯεͷϊʔυো֐࣌΋αʔϏε Өڹ͸ͳ͔ͬͨ ϦϦʔε

Slow Query • Secondary IndexΛ࢖ͬͨΫΤϦͰIndexͷઌ ಄ΧϥϜͰର৅ΛߜΓɺ̎൪໨ͷΧϥϜͰ ιʔτ͢Δ • In۟ͷཁૉ͕Ұͭͷ৔߹͸sort͕ޮ͍͕ͨɺෳ ਺ཁૉࢦఆ͞ΕΔͱsort͕ޮ͔ͳ͍ • ର৅͕਺ेສ݅ʹͳΔ͜ͱ΋͋ΓSlow Queryʹͳͬͯ͠·ͬͨ KEY `idx_1` (`community_id`,`time_series_id`,`user_id`) /* ҎԼͷΑ͏ͳΫΤϦͷ৔߹ʹkeep order:falseͱͳΔ */ explain analyze select /*+ USE_INDEX(`community_posts`, idx_1) */ * from community_posts where community_id IN (‘aaaa’, ‘bbbb’, ‘cccc’) and time_series_id < 1736935091883343234 order by time_series_id ASC LIMIT 30; +----------------------------------+---------+---------+ | id | estRows | actRows | +----------------------------------+---------+---------+ | TopN_9 | 30.00 | 30 | | └ ─ IndexLookUp_16 | 30.00 | 30 | | ├ ─ TopN_15(Build) | 30.00 | 30 | | │ └ ─ IndexRangeScan_13 | 1317.05 | 1265 | | └ ─ TableRowIDScan_14(Probe) | 30.00 | 30 | +----------------------------------+---------+---------+

Slow Query total_keys: εΩϟϯ͞ΕͨKeyͷ߹ܭɻݹ͍όʔδϣϯΛؚΉ total_process_keys: ॲཧ͞ΕͨKey਺ɻݹ͍όʔδϣϯΛؚ·ͳ͍ delete_skipped_count: ࡟আϚʔΧʔ͕͍ͭͨKeyͷ߹ܭ key_skipped_count: εΩοϓͨ͠Keyͷ߹ܭ • ୹ظؒͰUPDATEΛ܁Γฦ͔͢DELETE͢Δ έʔε • ෳ਺ͷόʔδϣϯ͕ҙਤͤͣ஝ੵ͞Εͨ • ಉ͡ςʔϒϧͷϨίʔυΛݕࡧ͢Δͱ͖ ʹSlow Query͕ൃੜ scan_detail: { total_process_keys: 1, total_process_keys_size: 102, total_keys: 26150, get_snapshot_time: 10.5µs, rocksdb: { delete_skipped_count: 179504, key_skipped_count: 205653, . . . }

Operation • ϦϦʔεޙɺTiDB Clusterͷӡ༻͸tiupίϚϯ υΛ࢖࣮ͬͯࢪ • Scale out/Scale in • Backup • Dump

ϨϓϦΧ εέδϡʔϧ Մ༻ੑͷ޲্ͷͨΊɺτϙϩδϥϕϧͷػೳΛ ࢖͍TiKVϊʔυΛҟͳΔϥοΫʹ഑ஔ͍ͯ͠Δ EC2 PlacementGroupͰ࡞੒ͨ͠ύʔςΟγϣ ϯΛPD͕ཧղͰ͖ΔΑ͏ʹ location-labelsͷઃ ఆΛߦ͏ $ tiup ctl:v8.3.0 pd config set location-labels rack -u 10.20.3.13:2379 { "max-replicas": 3, "location-labels": "rack", "strictly-match-label": "false", "enable-placement-rules": "true", "enable-placement-rules-cache": "false", "isolation-level": "" }

Dumpling • tiup dumplingίϚϯυͰDBσʔλΛS3ʹΤ Ϋεϙʔτ(CSV) • ECSͷεέδϡʔϧυλεΫͱͯ͠ఆظత ʹ࣮ߦ͢Δ • Dumpͨ͠σʔλΛղੳ༻DBʢBigQuery) ʹऔΓࠐΉ $ tiup dumpling -u username -P 4000 -h 10.20.1.244 -- filetype csv -t 8 -o “s3://dump-bucket/20250214" -r 20000 -F 256MiB --csv-output-dialect bigquery --csv-null-value "" --escape-backslash

Backup • tiup brίϚϯυͰϑϧόοΫΞοϓͱϩά όοΫΞοϓΛߦ͏ • ECSͷεέδϡʔϧυλεΫͱͯ͠ఆظత ʹ࣮ߦ͢Δ Ҿ༻ݩ: https://docs.pingcap.com/ja/tidb/stable/br-snapshot-architecture#architecture

Monitoring TiDB Dashboard - Ϋϥελશମͷ࣮ߦεςʔλ εʢQPSɺ࣮ߦ࣌ؒͳͲʣͱSQL࣮ߦ࣌ؒɺε ϩʔΫΤϦΛ֬ೝ͢Δ Grafana - TiDB, TiKV, PDͷओཁͳϝτϦΫεͷ ؂ࢹ - Ϧʔδϣϯ਺ɺҟৗͳϦʔδϣϯ - εΩοϓ͞ΕͨΩʔɺ࡟আͣΈΩʔͷ਺

·ͱΊͱࠓޙͷల๬ • গͳ͍ਓ਺ͷ։ൃνʔϜ͕ͩɺTiDB ClusterΛ࢖༻ͨ͠αʔϏε։ൃͱӡ༻Λߦ͍͑ͯΔ • TiDB͸ແఀࢭͰӡ༻Λܧଓɺٸܹͳෛՙʹ΋εέʔϧΞ΢τͰରԠ͢Δ͜ͱ͕Ͱ͖ͨ • υΩϡϝϯτ͕๛෋ʹ͋Δ͜ͱ΍؂ࢹπʔϧ͕Ұ௨Γ෇ଐ͢Δ͜ͱ΋গਓ਺νʔϜʹ ͸͋Γ͕͍ͨ • ࠓޙͷల๬ • TiCDCΛ࢖͍νΣϯδΠϕϯτΛϝοηʔδͱͯ͠ૹ৴͠ඇಉظॲཧʹ࢖͍͍ͨʢSQS ͱඇಉظδϣϒͷ෦෼Λஔ͖׵͑ΔͨΊʣ

Ξϯέʔτճ౴ऀʹϊϕϧςΟΛϓϨθϯτʂ ϐϯΫ ωΠϏʔ άϨʔ ΠΤϩʔ ϒϧʔ άϦʔϯ ※λΦϧϋϯΧνͷΧϥʔͱຕ਺ʹݶΓ͕͍͟͝·͢ɻແ͘ͳΓ࣍ୈऴ͍ྃͨ͠·͢ͷͰྃ͝ঝ͍ͩ͘͞ɻ ※ϊϕϧςΟ͓ड͚औΓͷࡍɺճ౴׬ྃը໘ͷදࣔΛ͓ئ͍͠·͢ɻ ·ͨ͸ + SCAN HERE!