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

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 ӡ༻ϑΣʔζͰ͸ଞͷπʔϧͱͷ࿈ܞ͕ඞཁ