2016.11.02 JAWS-UG横浜 - Reboot!! http://jawsug-yokohama.connpass.com/event/40497/
RDS for MySQL ͔ΒAurora ʹҠߦͨ͠2016.11.02 JAWS-UGԣ - Reboot!!@fujiwara
View Slide
౻ݪ ढ़Ұ@fujiwaragithub.com/fujiwarasfujiwara.hatenablog.comٕज़෦
20149݄ϦϦʔεશ໘తʹ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/180046HAProxy1.6ͷಈత໊લղܾ + ֎෦scriptlisten mysql-slavebind 127.0.0.1:3307mode tcpoption external-checkexternal-check path "/usr/bin:/bin:/usr/local/bin"external-check command /usr/local/bin/check_aurora_slave.shbalance roundrobinserver master db01.xxx.rds.amazonaws.com:3306 check backup resolvers mydnsserver slave1 db02.xxx.rds.amazonaws.com:3306 check resolvers mydnsserver slave2 db03.xxx.rds.amazonaws.com:3306 check resolvers mydns
MySQL࣌ͱͷมߋHAProxyͷ֎෦scriptinnodb_read_only͕ON → ReadReplica#!/bin/bashREAD_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ͷ҆ఆੑ ⭕ϨϓϦέʔγϣϯΛ͑࠷খݶͷμϯλΠϜͰՄೳίετݮΒͤΔ(͔Εͳ͍)