Save 37% off PRO during our Black Friday Sale! »

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/

Ca6281fff64797dc419b78f51f25c0a5?s=128

FUJIWARA Shunichiro
PRO

November 02, 2016
Tweet

Transcript

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

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

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

    • ElastiCache Redis • CloudFront • S3 • Redshift
  4. ετϨʔδ͸ MySQL ήʔϜͷಛੑ • 9ਓ x 2 νʔϜ͕ಉ࣌ʹҰͭͷࢼ߹Λૢ࡞͢Δ • ͦΕͱ͸ผʹࢼ߹ਐߦͰόονॲཧ͕૸Δ

    • → τϥϯβΫγϣϯ͕ඞਢ
  5. MySQL → Aurora

  6. MySQL → Aurora ͳͥҠߦ͔͔ͨͬͨ͠ (1) ࣌ʑى͖ΔEBS٧·Γ͕ͭΒ͍ • ผAZ΁ͷॻ͖ࠐΈ͕਺ඵʙ਺ेඵࢗ͞Δݱ৅ • Aurora͸

    3 AZ த 2 AZ ΁ͷॻ͖ࠐΈ׬ྃͰฦͬͯ͘ΔͷͰ (ݪཧతʹ) ൃੜ͠ʹ͍͘͸ͣ
  7. MySQL → Aurora ͳͥҠߦ͔͔ͨͬͨ͠ (2) ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍ • ϦʔυϨϓϦΧ͸EBS snapshot͔Β෮ݩ͞ΕΔ •

    EBSͷ First Touch Penalty • EC2ͳΒ dd ίϚϯυͰͰ͖Δ͕RDS͸… • primary key + secondary index͢΂ͯᢞΊΔඞཁ • ΠϕϯτલʹΠϯελϯεαΠζมߋ͕΍ΓͮΒ͍
  8. MySQL → Aurora • 2016-08 ৽ϫʔϧυ্ཱͪ͛͸࠷ॳ͔ΒAuroraͰ • 2016-10 چϫʔϧυ MySQL

    → AuroraҠߦ
  9. ࣄલͷݕূ • ։ൃαʔόΛAuroraʹͯ͠ςετΛճ͢ • ΞϓϦέʔγϣϯϨϕϧͷޓ׵ੑ໰୊ͳ͠ • ΞϓϦέʔγϣϯܦ༝ͰͷϕϯνϚʔΫ • ಛʹੑೳɺݶք্͕͕ΔΘ͚Ͱ͸ͳ͔ͬͨ !

    Amazon AuroraΛਅʹཧղ͢ΔͨΊͷੑೳݕূ blog.father.gedow.net/2015/11/05/amazon-aurora- benchmark/
  10. Ҡߦ࡞ۀ

  11. Ҡߦ࡞ۀ ॳظঢ়ଶ

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

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

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

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

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

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

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

  19. MySQL࣌୅ͱͷมߋ఺

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

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

    + ֎෦scriptʹ
  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
  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" ]
  24. MySQL࣌୅ͱͷมߋ఺ ReadReplicaͰ show engine innodb status ͕ݟ͑ͳ͍?? ࢓༷ͱͷ͜ͱ (by αϙʔτ)

    ؂ࢹͰ ROW OPERATIONS Λूܭ͍ͯͨ͠ͷͰࠔΔ
  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 | +----------------------+----------+
  26. MySQL࣌୅ͱͷมߋ఺ ΫΤϦͷϨΠςϯγ͕औΕΔΑ͏ʹͳͬͨ

  27. Ҡߦͯ͠Έͯ

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

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

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

    (by αϙʔτ)
  31. Ҡߦͯ͠Έͯ ! ίετ΋େࠩͳ͠(͔Լ͕ͬͨ) • Provisioned IOPS (ߴ͍) • GP2Ͱ΋ੑೳͷͨΊʹ༰ྔΛ֬อ͍ͯͨ͠ͷ͕ෆཁʹ •

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