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

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

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

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

MySQL → Aurora

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

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

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

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

Ҡߦ࡞ۀ

Ҡߦ࡞ۀ ॳظঢ়ଶ

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

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

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

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

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

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

Ҡߦ࡞ۀ 7. Ҡߦ׬ྃ!

MySQL࣌୅ͱͷมߋ఺

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

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

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

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" ]

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

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 | +----------------------+----------+

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

Ҡߦͯ͠Έͯ

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

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

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

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

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