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

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ͷ҆ఆੑ ⭕ ϨϓϦέʔγϣϯΛ࢖͑͹࠷খݶͷμ΢ϯλΠϜͰՄೳ ίετ΋ݮΒͤΔ(͔΋஌Εͳ͍)