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

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

౻ݪ ढ़Ұ࿠ @fujiwara github.com/fujiwara sfujiwara.hatenablog.com ٕज़෦

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

MySQL → Aurora

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

Ҡߦ࡞ۀ

Slide 11

Slide 11 text

Ҡߦ࡞ۀ ॳظঢ়ଶ

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Ҡߦ࡞ۀ 7. Ҡߦ׬ྃ!

Slide 19

Slide 19 text

MySQL࣌୅ͱͷมߋ఺

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

Ҡߦͯ͠Έͯ

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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