RDS for MySQLからAuroraに移行した話 / RDS for MySQL to Aurora

2016.11.02 JAWS-UG横浜 - Reboot!!
http://jawsug-yokohama.connpass.com/event/40497/

Transcript

1. RDS for MySQL ͔Β
   Aurora ʹҠߦͨ͠࿩
   2016.11.02 JAWS-UGԣ඿ - Reboot!!
   @fujiwara
   

   View Slide

2. ౻ݪ ढ़Ұ࿠
   @fujiwara
   github.com/fujiwara
   sfujiwara.hatenablog.com
   ٕज़෦
   

   View Slide

3. 2014೥9݄ϦϦʔε
   શ໘తʹAWSΛར༻
   • ELB + EC2
   • RDS for MySQL
   • ElastiCache Redis
   • CloudFront
   • S3
   • Redshift
   

   View Slide

4. ετϨʔδ͸ MySQL
   ήʔϜͷಛੑ
   • 9ਓ x 2 νʔϜ͕ಉ࣌ʹҰͭͷࢼ߹Λૢ࡞͢Δ
   • ͦΕͱ͸ผʹࢼ߹ਐߦͰόονॲཧ͕૸Δ
   • → τϥϯβΫγϣϯ͕ඞਢ
   

   View Slide

5. MySQL → Aurora
   

   View Slide

6. MySQL → Aurora
   ͳͥҠߦ͔͔ͨͬͨ͠ (1)
   ࣌ʑى͖ΔEBS٧·Γ͕ͭΒ͍
   • ผAZ΁ͷॻ͖ࠐΈ͕਺ඵʙ਺ेඵࢗ͞Δݱ৅
   • Aurora͸ 3 AZ த 2 AZ ΁ͷॻ͖ࠐΈ׬ྃͰฦͬͯ͘ΔͷͰ
   (ݪཧతʹ) ൃੜ͠ʹ͍͘͸ͣ
   

   View Slide

7. MySQL → Aurora
   ͳͥҠߦ͔͔ͨͬͨ͠ (2)
   ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍
   • ϦʔυϨϓϦΧ͸EBS snapshot͔Β෮ݩ͞ΕΔ
   • EBSͷ First Touch Penalty
   • EC2ͳΒ dd ίϚϯυͰͰ͖Δ͕RDS͸…
   • primary key + secondary index͢΂ͯᢞΊΔඞཁ
   • ΠϕϯτલʹΠϯελϯεαΠζมߋ͕΍ΓͮΒ͍
   

   View Slide

8. MySQL → Aurora
   • 2016-08 ৽ϫʔϧυ্ཱͪ͛͸࠷ॳ͔ΒAuroraͰ
   • 2016-10 چϫʔϧυ MySQL → AuroraҠߦ
   

   View Slide

9. ࣄલͷݕূ
   • ։ൃαʔόΛAuroraʹͯ͠ςετΛճ͢
   • ΞϓϦέʔγϣϯϨϕϧͷޓ׵ੑ໰୊ͳ͠
   • ΞϓϦέʔγϣϯܦ༝ͰͷϕϯνϚʔΫ
   • ಛʹੑೳɺݶք্͕͕ΔΘ͚Ͱ͸ͳ͔ͬͨ
   ! Amazon AuroraΛਅʹཧղ͢ΔͨΊͷੑೳݕূ
   blog.father.gedow.net/2015/11/05/amazon-aurora-
   benchmark/
   

   View Slide

10. Ҡߦ࡞ۀ
    

    View Slide

11. Ҡߦ࡞ۀ
    ॳظঢ়ଶ
    

    View Slide

12. Ҡߦ࡞ۀ
    1. MySQLͷϦʔυϨϓϦΧΛ࡞ͬͯϨϓϦΛࢭΊΔ
    

    View Slide

13. Ҡߦ࡞ۀ
    2. ࢭΊͨMySQLϦʔυϨϓϦΧ͔ΒAuroraΛͭ͘Δ
    

    View Slide

14. Ҡߦ࡞ۀ
    3. AuroraΛMySQL masterͱϨϓϦ͢Δ
    

    View Slide

15. Ҡߦ࡞ۀ
    4. Ҡߦ༻ͷMySQLϦʔυϨϓϦΧ࡟আ
    

    View Slide

16. Ҡߦ࡞ۀ
    5. AuroraͷϦʔυϨϓϦΧΛ࡞Δ
    

    View Slide

17. Ҡߦ࡞ۀ
    6. AuroraΛঢ֨ɺΞϓϦέʔγϣϯͷ઀ଓઌมߋ
    

    View Slide

18. Ҡߦ࡞ۀ
    7. Ҡߦ׬ྃ!
    

    View Slide

19. MySQL࣌୅ͱͷมߋ఺
    

    View Slide

20. MySQL࣌୅ͱͷมߋ఺
    HAProxyΛࢀর෼ࢄʹ࢖͍ͬͯΔ
    (֤ϗετͰىಈͯ͠localhost:3307ʹ઀ଓ)
    

    View Slide

21. MySQL࣌୅ͱͷมߋ఺
    Before: HAProxy૊ΈࠐΈͷmysqlϔϧενΣοΫ
    • Πϯελϯε࡞Γ௚͠ͰͷIPΞυϨεมߋʹ௥ैͰ͖ͳ͍
    • master͔slave͔͕෼͔Βͳ͍
    ↓
    After: HAProxy1.6ͷಈత໊લղܾ + ֎෦scriptʹ
    

    View Slide

22. MySQL࣌୅ͱͷมߋ఺
    tkuchiki.hatenablog.com/entry/2016/07/14/180046
    HAProxy1.6ͷಈత໊લղܾ + ֎෦script
    listen mysql-slave
    bind 127.0.0.1:3307
    mode tcp
    option external-check
    external-check path "/usr/bin:/bin:/usr/local/bin"
    external-check command /usr/local/bin/check_aurora_slave.sh
    balance roundrobin
    server master db01.xxx.rds.amazonaws.com:3306 check backup resolvers mydns
    server slave1 db02.xxx.rds.amazonaws.com:3306 check resolvers mydns
    server slave2 db03.xxx.rds.amazonaws.com:3306 check resolvers mydns
    

    View Slide

23. MySQL࣌୅ͱͷมߋ఺
    HAProxyͷ֎෦script
    innodb_read_only͕ON → ReadReplica
    #!/bin/bash
    READ_ONLY=$(mysql -u haproxy -h ${HAPROXY_SERVER_ADDR} \
    -P ${HAPROXY_SERVER_PORT} -BN\
    -e "SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';"\
    --connect-timeout=5 | awk '{print $2}')
    [ "${READ_ONLY}" = "ON" ]
    

    View Slide

24. MySQL࣌୅ͱͷมߋ఺
    ReadReplicaͰ show engine innodb status ͕ݟ͑ͳ͍??
    ࢓༷ͱͷ͜ͱ (by αϙʔτ)
    ؂ࢹͰ ROW OPERATIONS Λूܭ͍ͯͨ͠ͷͰࠔΔ
    

    View Slide

25. MySQL࣌୅ͱͷมߋ఺
    show status like '%Innodb_rows%' ʹมߋ
    +----------------------+----------+
    | Variable_name | Value |
    +----------------------+----------+
    | Innodb_rows_deleted | 1 |
    | Innodb_rows_inserted | 111002 |
    | Innodb_rows_read | 15682572 |
    | Innodb_rows_updated | 1020 |
    +----------------------+----------+
    

    View Slide

26. MySQL࣌୅ͱͷมߋ఺
    ΫΤϦͷϨΠςϯγ͕औΕΔΑ͏ʹͳͬͨ
    

    View Slide

27. Ҡߦͯ͠Έͯ
    

    View Slide

28. Ҡߦͯ͠Έͯ
    ࣌ʑى͖ΔEBS٧·Γ͕ͭΒ͍
    → 2ʙ3िִؒͰൃੜ͍ͯͨ͠ͷ͕0ʹ !
    ໷Α͘຾ΕΔ
    

    View Slide

29. Ҡߦͯ͠Έͯ
    ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍
    → ෆཁʹͳͬͨͷͰ৺ཧతʹָ !
    (·ཱͩͯͯͳ͍͚Ͳ)
    

    View Slide

30. Ҡߦͯ͠Έͯ
    ! snapshotͷෛՙΛؾʹ͠ͳͯ͘Α͘ͳͬͨ
    • (ΞϓϦͷ)ো֐ରԠͷͨΊʹ͜·ΊʹsnapshotΛऔ͍ͬͯΔ
    • EBS snapshot͸ଟগͳΓͱ΋ੑೳʹӨڹ͢Δ
    • AuroraͰ͸Өڹͳ͠ͱͷ͜ͱ (by αϙʔτ)
    

    View Slide

31. Ҡߦͯ͠Έͯ
    ! ίετ΋େࠩͳ͠(͔Լ͕ͬͨ)
    • Provisioned IOPS (ߴ͍)
    • GP2Ͱ΋ੑೳͷͨΊʹ༰ྔΛ֬อ͍ͯͨ͠ͷ͕ෆཁʹ
    • Πϯελϯεඅ༻ ↑
    • ετϨʔδɺIOඅ༻ ↓
    

    View Slide

32. ·ͱΊ
    MySQL ͔Β Aurora ͸Ҡߦ͢ΔՁ஋͋Γ
    ಛʹIOͷ҆ఆੑ ⭕
    ϨϓϦέʔγϣϯΛ࢖͑͹࠷খݶͷμ΢ϯλΠϜͰՄೳ
    ίετ΋ݮΒͤΔ(͔΋஌Εͳ͍)
    

    View Slide