MySQLからBigQueryの同期を差分更新にしたら4倍高速になった話 / Sync from MySQL to BigQuery become 4x faster by incremental updating

Embulk Meetup Tokyo #3のLTです

Transcript

1. .Z42-͔Β#JH2VFSZͷಉظΛ
   ࠩ෼ߋ৽ʹͨ͠Βഒߴ଎ʹͳͬͨ࿩
   &NCVML
   .FFUVQ
   5PLZP
    VASILY,Inc. Ԙ㟒݈߂
2. Ԙ㟒݈߂ w ೥ʹ7"4*-:ʹ৽ଔೖࣾ
   w ීஈͷ͓࢓ࣄ͸3BJMTͰXFC
   "1*࡞ͬͨΓɺ4PMSͷϝϯςͨ͠Γɺ
 5BCMFBV
   
   #JH2VFSZͰμογϡϘʔυ࡞ͬͨΓ
   w &NCVML͸ࠓ೥ͷ݄ࠒʹ৮Γ࢝Ίͨ͹͔Γ
   7"4*-:
   *OD
   TPGUXBSF
   FOHJOFFS
3. *20/঺հ Ҏ্ͷϑΝογϣϯ&$αΠτ͔Β
   ྦྷܭ ສ఺Λ௒͑Δ঎඼Λܝࡌ ݄ؒສਓҎ্͕ར༻͢Δ೔ຊ࠷େڃͷϑΝογϣϯαΠτ
4. σʔλऩूج൫ͷ঺հ
   Πϕϯτϩά &-# "1*
   4FSWFS -PH
   "HHSFHBUPS 8FC
   4FSWFS #JH2VFSZ 3FDPNNFOE
5. σʔλ෼ੳج൫ͷ঺հ
   Ϛελʔσʔλ 3%4
   .Z42- %BUB
   USBOTGFS
   TFSWFS ($4 #JH2VFSZ ෗Γ͔͚͍ͯΔൿ఻ͷͨΕঢ়ଶ
   ɾNZTRMEVNQ
   US
   TFE
   FUD
   Λෳࡶʹ૊Έ߹Θ͍ͤͯΔ
   ɾςʔϒϧΛ෼ׂͯ͠సૹ͢ΔઃఆΛϋʔυίʔσΟϯά
6. σʔλ෼ੳج൫ͷ঺հ
   Ϛελʔσʔλ 3%4
   .Z42- %BUB
   USBOTGFS
   TFSWFS ($4 #JH2VFSZ ʹ&NCVMLΛಋೖ
   ɾಉظόον͕མͪΔ͜ͱ͕΄΅ͳ͘ͳΔ
7. ͔͜͜ΒϝΠϯςʔϚ
8. ϚελʔσʔλͷಉظΛ
   ߴ଎Խ͍ͨ͠ w Ϣʔβʔ΁ͷՁ஋ͷఏڙΛߴ଎Խ͢ΔͨΊ
   w ݱঢ়
   ঎඼৘ใͷಉظ͚ͩͰ໿࣌ؒ෼ ໿ ສߦ
   ໿(#
9. ࣌ؒܭଌ 3%4
   .Z42- %BUB
   USBOTGFS
   TFSWFS ($4 #JH2VFSZ NJO
   .Z42-ͷςʔϒϧΛμϯϓ NJO
   μϯϓΛ($4ʹΞοϓϩʔυ NJO
   ($4͔Β#JH2VFSZʹϩʔυ
10. .Z42-ͷμϯϓ͕
    ϘτϧωοΫ w Ϛελʔσʔλͷಉظ͸ຖ೔ߦ͍ͬͯΔͨΊɺ
 ࡢ೔ͱಉ͡σʔλ͸ಉظ͢Δඞཁͳ͠
    w 3VCZ
    PO
    3BJMTΛ࢖༻͍ͯ͠ΔͷͰɺ
 VQEBUFE@BUʹͦͷߦͷߋ৽࣌ࠁ͕ೖ͍ͬͯΔ
11. ࠩ෼ߋ৽ͯ͠ΈΔ ςʔϒϧ
    ࠩ෼ͷΈ ςʔϒϧ
    લ೔෼ ςʔϒϧͷϚʔδ
    0-"1ؔ਺Λ׆༻ લճͷಉظҎ߱ͷσʔλͷΈ
    where: "updated_at > ..."
12. લճͷಉظҎ߱ͷσʔλΛऔಘ w JOQVUQMVHJONZTRMͷઃఆʹXIFSFΛ௥Ճ͢Δ
    w औಘ͢Δඞཁͷ͋Δߦ͕ҎԼʹͳΔ in: type: mysql host: example.com user: user_name password: ******** database: db_name table: items select: "*" where: "updated_at > '2017-05-15 00:00:00'" # この行を追加
13. 42-ͰςʔϒϧΛϚʔδ w ͭͷςʔϒϧΛVOJPO
    BMMͰ݁߹͠ɺओΩʔͰ QBSUJUJPO
    CZͯ͠৽͍͠ߦͷΈΛબ୒
    w ͜ͷ42-ͷ݁ՌΛݩͷςʔϒϧʹॻ͖໭͢ select * from ( select *, row_number() over (partition by id order by updated_at desc) as rn from ( select * from tmp.items union all select * from mysql.items ) ) where rn = 1
14. ݁Ռ 3%4
    .Z42- %BUB
    USBOTGFS
    TFSWFS ($4 #JH2VFSZ NJO
    ˠ
    NJO NJO
    ˠ
    NJO NJO
    ˠ
    NJO શମͰഒͷߴ଎Խ
    ໿INJO
    ˠ
    NJO ςʔϒϧͷϚʔδ
    NJO
    ˠ
    NJO
15. ৽ͨͳ՝୊ w Ϛελʔσʔλͷಉظ͕ෳࡶʹͳΓͦ͏
    w ֤ॲཧͷʮྲྀΕʯΛ؅ཧ͢Δπʔϧ͕ཉ͍͠ %JHEBH "JSqPX -VJHJ
16. ·ͱΊ w &NCVML͸όϧΫసૹͷҋΛরΒͯ͘͠ΕΔπʔϧ
    w σʔλͷࠩ෼ߋ৽ʹΑͬͯഒߴ଎Խ
    w ӡ༻ϑΣʔζͰ͸ଞͷπʔϧͱͷ࿈ܞ͕ඞཁ