Share
Embed
MySQLからPostgreSQLへの移行とDBリファクタリング/postgresqlJapan2018
2018-11-22 に行われた PostgreSQL Conference Japan 2018 のスライドです!
Takahashi Ikki
November 22, 2018
Transcript
68 slides
-
MySQL ͔Β PostgreSQL ͷҠߦͱ DBϦϑΝΫλϦϯά 2018-11-22 PostgreSQL Conference Japan 2018
ຊPostgreSQLϢʔβʔձ தࠃํࢧ෦ ߴڮ Ұٍ 1 -
͓͠ͳ͕͖ 1. ࣗݾհ 2. DBϦϑΝΫλϦϯάΛΔཧ༝ͱͦͷख๏ 3. MySQL͔ΒPostgreSQLΛҠߦઌʹબΜͩཧ༝ 4. AWS Database
Migration Serviceͱ 5. ҠߦࣦഊͷࢹΛͲͷΑ͏ʹ͍ͬͯΔ͔ 6. ·ͱΊ 2 -
ҙࣄ߲ • εϥΠυެ։͠·͢ɻ • ࣭ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β #pgcon18j ͓ئ͍க͠·͢ɻ • EC2
RDS ͳͲͱ͍ͬͨAWSͷαʔϏεͷࣄલ͕ࣝඞཁͰ͢ɻ • EC2: AWS͕ఏڙ͢ΔԾαʔόʔ • RDS: AWS͕ఏڙ͢ΔϚωʔδυRDBαʔϏε 3 -
1. ࣗݾհ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΤϯδχΞ
• ຊPostgreSQLϢʔβʔձ தࠃࢧ෦ • PostgreSQLྺ 4ϲ݄ ! 4 -
ΦϛΧϨʹ͍ͭͯ • αʔϏε։͔࢝Β͕̓ܦա͍ͯ͠Δɻ • ͜͜ఔͰ ։ൃਓһ͕ࣾһ1໊ + ֎ ͷମ੍͔Β ࣾһ6໊
ͷମ੍ʹมֵɻ • ৭Μͳେਓͷࣄ͔ΒͦΕͳΓʹ͍ΘΏΔٕज़తෛ࠴ͱݺ ΕΔͷ͕͋Δɻ 8 -
gender ΧϥϜ ఆ ࣮ࡍ ܕ int ೖΔσʔλ 0, 1, 2
σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 13 -
gender ΧϥϜ ఆ ࣮ࡍ ܕ int varchar ೖΔσʔλ 0, 1,
2 0, 1, 2, உੑ, ঁੑ σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 14 -
DBͷϦϑΝΫλϦϯάํ๏ • ผͷΠϯελϯεΛ༻ҙ͠ɺ AWS Database Migration ServiceΛ ༻ͯ͠ϨϓϦέʔγϣϯ͢Δɻ • ϨϓϦέʔγϣϯ͞ΕͨλΠϛϯάͰ
DBͷτϦΨʔΛൃಈͤ͞Δɻ • τϦΨʔͰچσʔλΛՃ͠ ϦϑΝΫλϦϯάͨ͠ςʔϒϧͷ ܗࣜʹ߹͏Α͏ʹొ͢Δɻ • ࠓޙNEWεΩʔϚΛࢀর͢Δɻ 20 -
ϦϑΝΫλϦϯάख๏ • Webগͮͭ͠APIܦ༝Ͱ ࢀরɾॻࠐ͢ΔΑ͏ʹมߋΛ ਐΊΔɻ (WebͷϦϑΝΫλϦϯά) • APIॻࠐMySQLଆߦ͍ɺ ಡࠐPostgreSQLଆ͔Βߦ͏ɻ (DBͷϦϑΝΫλϦϯά)
• WebଆΛશͯAPIܦ༝ʹग़དྷͨΒ APIͷॻࠐPostgreSQLଆ มߋ͢Δɻ 21 -
͜ͷϦϑΝΫλϦϯάํ๏ͷϝϦοτ • ߋ৽APIMySQLॻࠐΛ͢ΔͷͰগͮͭ͠APIܦ༝ʹͯ͠ طଘαʔϏεͷςʔϒϧͷࢀরՕॴʹӨڹ͕ແ͍ɻ • ҰՕॴAPIܦ༝ʹͯ͠ΈΔ → ͕͋Εௐ͢Δɻˠ ... ͳͲͷΑ͏ʹ
ΧφϦΞϦϦʔε ܗࣜͰ গͮͭ͠վળΛਐΊ͍͚ͯΔɻ • σʔλͷಡࠐPostgreSQL͔Βߦ͏ͷͰ ॻࠐAPIΛݺͿଆͲͪΒʹॻ͖ࠐΜͰ͍Δ͔Λ ؾʹ͠ͳͯ͘ྑ͍ɻ 23 -
͜ͷϦϑΝΫλϦϯάํ๏ͷσϝϦοτ • ϩʔΧϧͷ։ൃڥΛ࡞͢Δࣄ͕ग़དྷͳ͍ɻ ※ ࠓɺׂͱҰ൪·͍͠ॴɻ • DMS͕ࢭ·ͬͨ࣌ʹࢀরAPI͕ίέΔɻ ※ τϦΨʔΤϥʔͰDMS͕ࢭ·ΔͱαʔϏε͕ࢭ·Δɻ •
DMS͕Ԇͨ࣌͠ͷߟྀ͕͍͠ɻ ※ ࠓͷॴɺக໋ইͳ͍ɻ(ই͕ແ͍ͱݴͬͯͳ͍) • ίϯϑϦΫτ͢Δࣄ͕ݒ೦͞ΕΔͷͰϦʔυϨϓϦΧʹ·ͰτϦΨʔΛషΕͳ͍͕ ϦʔυϨϓϦΧ͕Ϛελʔʹঢ֨͢ΔͱαʔϏε͕ࢭ·Δɻ 24 -
2. DBϦϑΝΫλϦϯάΛΔཧ༝ͱͦͷख๏ ·ͱΊ • AWS DMSͰ MySQL͔ΒPostgreSQLʹ ϨϓϦέʔγϣϯ͍ͯ͠Δɻ • DMS͕Ҡߦͨ͠λΠϛϯάͰઃఆͨ͠τϦΨʔ͕ൃಈͯ͠
ϦϑΝΫλϦϯά͞ΕͨςʔϒϧʹॻࠐΛ͢Δɻ • ߋ৽ɾࢀরΛAPIܦ༝ʹ͢ΔࣄͰطଘͷ։ൃΛਐΊͭͭ ϦϑΝΫλϦϯάͨ͠DBʹࢀরΛΓସ͍͚͍͑ͯͯΔɻ • ϩʔΧϧͷڥߏங͕͍͠ͷͱɺDMS͕ࢭ·ͬͨ࣌ʹ ࢀরAPI͕ίέΔͷͰߟྀ͕͍͠ɻ 25 -
1ɿ PostgreSQLτϦΨʔͷಛ (Ұ෦) ------------------------------------------------------------ CREATE TRIGGER 'τϦΨʔ໊' trigger_time trigger_event ON
'ςʔϒϧ໊' FOR EACH ROW '࣮ߦؔ' ------------------------------------------------------------ MySQL PostgreSQL trigger_time BEFORE, AFTER ͷͲͪΒ͔ BEFORE, AFTER ͷͲͪΒ͔ trigger_event INSERT, UPDATE, DELETE ͔ΒҰͭͷΈબ INSERT, UPDATE, DELETE ͔Βෳબ ෳτϦΨʔ ֤Πϕϯτຖʹ1ͭͣͭ (※ v5.6·Ͱ) ෳࢦఆՄೳ ෳτϦΨʔࢦఆ࣌ͷॱং ίϚϯυͰࢦఆ ໊લॱ 30 -
1ɿ PostgreSQLτϦΨʔʹ͍ͭͯ • PostgreSQL͚ͩͷػೳ • UPDATE࣌ʹSETʹࢦఆΧϥϜ͕͋Δ߹ͷτϦΨʔΛఆٛग़དྷΔɻ • UPDATE࣌ʹࢦఆΧϥϜ͕࣮ࡍʹมߋ͞Εͨ߹ͷτϦΨʔΛఆٛग़དྷ Δɻ •
ߦϨϕϧ͚ͩͰແͯ͘จϨϕϧͰτϦΨʔΛఆٛग़དྷΔɻ ※ ྫɿ DELETEͰ10ߦফ͑ͨͱͯ͠ɺߦϨϕϧ10֤݅ߦʹର࣮ͯ͠ߦ ͞ΕΔɻจϨϕϧDELETEจʹର࣮ͯ͠ߦ͞ΕΔɻಛʹ TRUNCATEจ ʹରͯ͠༗ޮɻ 31 -
2ɿ PostgreSQLͷڧΈ(खଓ͖ܕݴޠ) τϦΨʔͷ࣮ߦؔΛҎԼͷ֤ݴޠͰఆ͕ٛग़དྷΔɻ ໊শ ݴޠ ํ๏ PL/pgSQL SQL جຊ PL/Perl
Perl جຊ PL/Python Python جຊ PL/Java Java ֦ு PL/v8 JavaScript ֦ு ※ ͦͷଞ 32 -
PostgreSQLͷڧΈ(खଓ͖ܕݴޠ) • ※ୠ͠ɺAmazon RDS PL/Python Λαϙʔτͯ͠ͳ͍ɻɻ • ΦϛΧϨͰ PL/v8Λར༻͍ͯ͠Δɻ
• ࠾ཧ༝ɿ ܦݧͷ͋Δݴޠͷ͔ؔΒpgSQLͱv8͙Β͍͔͠બࢶ͕ແ͔ͬͨɻ ϕϯνϚʔΫͱͯ͠ pgSQLΑΓv8ͷํ͕ഒ͍ɺͱͷࣄͰpl/v8Λ࠾༻ɻ (PL/PythonΛ͍͔ͨͬͨͳɾɾ) • ※ΦϛΧϨͰϐϡΞJavaScriptΛ͍ͬͯΔ͕ɺ CoffeeScriptReact.js͑ΔΒ͍͠ɻ 33 -
(ࢀߟ) PL/v8 ͷ༗ޮԽํ๏ RDS for PostgreSQLʹσϑΥϧτͰ plv8͕Πϯετʔϧ͞Ε͍ͯΔͷͰɺextensionΛ༗ޮԽͯ͠Εok. ------------------------------------------------ # Extension͕ରԠͯ͠Δ͔νΣοΫ
postgres=> show rds.extensions; postgres=> create extension plv8; CREATE EXTENSION ------------------------------------------------ 34 -
AWS Database Migration Serviceͷ͝հ • AWS DMS ͰΘΕΔΩʔϫʔυ • ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ
• λεΫͷৼΔ͍ʹ͍ͭͯ • λεΫͷઃఆํ๏ʹ͍ͭͯ • ҟछDBؒҠߦͷࡍͷΧϥϜͷܕมʹ͍ͭͯ 38 -
AWS DMS ͰΘΕΔΩʔϫʔυ • ιʔεΤϯυϙΠϯτ • σʔλͷίϐʔݩɻEC2onDB, RDS, ΦϯϓϨDB, S3
ͳͲɻ • λʔήοτΤϯυϙΠϯτ • σʔλͷίϐʔઌɻEC2onDB, RDS, ΦϯϓϨDB, S3, DynamoDB ͳͲɻ • ϨϓϦέʔγϣϯΠϯελϯε • ίϐʔݩ͔ΒίϐʔઌσʔλͷϨϓϦέʔγϣϯΛߦ͏Πϯελϯεɻ • λεΫ • ͲͷςʔϒϧΛͲͷΑ͏ʹҠߦ͢Δ͔ΛఆΊͨϧʔϧɻ 39 -
ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ • ΠϯελϯεΫϥε T2, C4, R4ͷ̏छྨΛαϙʔτ. • T2: ։ൃɾݕূ༻ •
C4: େྔͷԋࢉΛߦ͏߹ʹཱͭɻ (AWSతʹ ҟछDBҠߦ͜ΕΛ͢͢Ί͍ͯΔ) • R4: ϝϞϦ૿ՃΠϯελϯεɻ (ҰճͷτϥϯβΫγϣϯ͕େ͖͍࣌͜Ε.) • ϚϧνAZʹରԠ͍ͯͯ͠ɺϑΣΠϧΦʔόʔΛαϙʔτ. • ࣮ࡍɺDMSͱ͓͕͔͔ͯۚ͠ΔͷΠϯελϯεར༻ྉͱετϨʔδ͚ͩɻ 41 -
λεΫͷઃఆํ๏ʹ͍ͭͯ • ϨϓϦέʔγϣϯΛߦ͏ςʔϒϧͷࢦఆํ๏̎छྨ • userɿ จࣈྻҰகͰ user ςʔϒϧͷΈΛରͱ͢Δɻ • user_%ɿ
ਖ਼نදݱͰ user_xxx ςʔϒϧʹ߹க͢Δશͯͷ ςʔϒϧΛରͱ͢Δɻ • ਖ਼نදݱͳͲͰෳࢦఆͨ͠߹λεΫͰฒྻͰςʔϒϧ͕ ϩʔυ͞ΕΔɻ (σϑΥϧτͰ8ςʔϒϧ) 43 -
ΦϛΧϨͷλεΫӡ༻ํ άϧʔϓ୯ҐͰখ͘͞ϨϓϦέʔγϣϯλεΫΛઃఆͯ͠ ҠߦΛਐΊ͍ͯΔɻ ྫɿ ϢʔβʔҠߦλεΫ, ύʔςΟҠߦλεΫ, ༧ҠߦλεΫ, ... • ཧ༝
Ҡߦྃͨ͠ςʔϒϧΑΓDMSͷλεΫΛআ͍ͯ͘͠ࣄΛ ఆ͍ͯ͠ΔͨΊɻ ӡ༻Λͯ͠ؾ͍͕ͨɺখ͘͞λεΫΛׂ͓ͯ͘͠ࣄͰҰݸͷςʔϒϧҠ ߦ͕ ࣦഊͨ࣌͠ʹαʔϏεશମͷোʹͳΒͳ͍ɻ 45 -
ҟछDBҠߦͷࡍͷΧϥϜͷܕมʹ͍ͭͯ • ιʔεͷܕ → DMSઐ༻ͷܕʹΩϟετ → λʔήοτͷܕʹΩϟετ ͱม͢Δɻ DMSͷܕ ༰
STRING จࣈྻ DATE DATETIME λΠϜελϯϓ INT1 1όΠτɺූ߸͖ (INT2, INT4, INT8͕͋Δ) BOOLEAN ϒʔϧ BLOB όΠφϦϥʔδΦϒδΣΫτ ※ ͦͷଞ 46 -
ͦͷଞDMSͷओͳ׆༻ࣄྫ • ΦϯϓϨͷDB͔ΒAWSͷRDSσʔλΛҠߦ͢Δɻ • ͔ͷߴڮগɾɾҎલਂϑϧόοΫΞοϓΛऔͬͯ AWSʹసૹͯ͠෮ݩ͢Δ࡞ۀΛ͍ͯͨ͠ɻɻ • Lambda+RDSಉ࣌ଓͷ͔Βਪ͞Ε͍ͯͳ͍ͷͰɺ RDS →
DynamoDB ʹܧଓతʹϨϓϦέʔγϣϯΛͯ͠ Lambda͕ଓ͢ΔDynamoDBΛ࡞Δɻ 47 -
4. AWS DMSʹ͍ͭͯ ·ͱΊ • DMSωοτϫʔΫӽ͠ʹϨϓϦέʔγϣϯΛ࣮ݱ͢ΔαʔϏε. • σʔλͷίϐʔݩͱઌΛࢦఆͯ͠ҠߦϧʔϧͱͳΔλεΫΛ࡞͢ΔࣄͰ σʔλͷҠߦશͯDMSͷͰ࣮ݱͯ͘͠ΕΔɻ •
λεΫͷཻΛଟͨ͘͠Γখͨ͘͞͠Γ͢ΔࣄͰ શମͷϨϓϦέʔγϣϯͷཻΛௐग़དྷΔɻ • DMS୯ମͱͯ͠ϨϓϦέʔγϣϯΠϯελϯεͷӡ༻අ͕͔͔ΔఔͰ ҆ՁͰਐΊΔࣄ͕ग़དྷΔɻ 48 -
νΣοΫࢹ߲ ------------------------------------------------ [plugin.checks.rds_log] command = "php /path/to/rds_log.php" check_interval = 5
memo = "RDS Τϥʔϩάࢹ" ------------------------------------------------ • mackerelͷઃఆϑΝΠϧʹ্هΛهड़͢Δͱ 5͓͖ʹ ࢦఆͨ͠ίϚϯυΛ࣮ߦͯ͘͠ΕΔ. 53 -
PHPͷதʹ͍ͭͯ AWS SDK for PHP Ͱ downloadDBLogFilePortionؔΛ࣮ߦ. ʢDBͷϩάΛ ࢦఆҐஔ͔Βμϯϩʔυ͢Δɻʣ •
:ERROR: ͳͲͷΩʔϫʔυΛरͬͯɺ Ωʔϫʔυ͕͋Ε ͦͷจࣈྻΛechoͯ͠exit(1) Ͱऴྃ͢Δ. • MackerelͷcheckϓϥάΠϯ exit(0) : ਖ਼ৗ exit(1) : ҟৗ(WARNING) exit(2) : ۓٸ(CRITICAL) • MackerelͷઃఆͰWARNINGΞϥʔτ্͕͕ͬͨ࣌ͷ௨ઌΛઃఆ͢Δɻ 54 -
࠷େͷϝϦοτ • WebαʔϏεଆͷطଘόά(͜ΜͳσʔλೖΒͳ͍)ʹ ؾ͘ࣄ͕ग़དྷΔɻ ʢ..ඞਢ߲ͳΜͰ NOT NULL͡Όͳ͍ͷʁͬͯɾɾʣ • τϦΨʔͰ ϩάʹ
:ERROR: ͬͯจࣈΛؚΊΔࣄͰ௨ΛϋοΫग़དྷΔɻ ʮ͜ͷݱআϑϥά͕1͔Β0ʹΒͳ͍ͱى͜Γ͑ͳ͍ʯ ͬͯʹରͯ͠ UPDATEͷτϦΨʔͰআϑϥά͕ͬͯͨΒ :ERROR:ͱϩάΛు͘ॲཧΛࠐΉࣄͰࣄ͕ൃੜ͍ͯ͠Δࣄʹؾ͚Δɻ ʢ࣮ࡍͬͯͨɻɻɻʣ 56 -
Zero Date MySQLͰ 0000-00-00 Λ DatetimeܕͷΧϥϜʹొ͢Δࣄ͕ग़དྷΔɻ PostgreSQLͰ 0000-00-00 Λ ʹΩϟετ͢Δࣄવग़དྷͳ͍ɻ
• DMSͰϨϓϦέʔγϣϯ͠Α͏ͱͨ࣌͠ͰίέΔɻ • hoge_date NOT NULL DEFAULT '0000-00-00' ͷڪා... 58 -
Zero Dateͷରॲ • ·ͣɺจࣈྻͰड͚ͱΔɻ ALTER TABLE hoge ALTER COLUMN hoge_date
TYPE VARCHAR(35); - BEFORE τϦΨʔͰ จࣈྻҰகͰஔ͢Δɻ if (NEW.hoge_date === '0000-00-00 00:00:00') { NEW.hoge_date = '0001-01-01 00:00:00'; } 59 -
with timezone ͪͳΈʹ RDSͩͱ SELECT NOW(); ͕ UTCʹͳΔɻ ίʔυͷόάͰ NOW()
ͰσʔλΛೖΕͯΔ࣌ɾɾ Կͱ18࣌ؒͣΕΔɻɻ 61 -
Πϕϯτࠂ • ୈ25ճ தࠃํDBษڧձ in ௗऔ • 2018-12-01() 13:00ʙ PostgreSQL
11ੌ͍ͧʂ MySQL 8.0ੌ͍ͧʂ → ʮͲ͏ͬͯόʔδϣϯΞοϓ͢Δͷʁʯ ख๏Λ͓ग़དྷΔߨࢣਞͷํΛ͓ট͖ͯ͠͝հ͠·͢ʂ 67