Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

FUJIWARA Shunichiro

November 02, 2016
Tweet

More Decks by FUJIWARA Shunichiro

Other Decks in Technology

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