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