Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQLからPostgreSQLへの移行とDBリファクタリング/postgresqlJapan2018

 MySQLからPostgreSQLへの移行とDBリファクタリング/postgresqlJapan2018

2018-11-22 に行われた PostgreSQL Conference Japan 2018 のスライドです!

Takahashi Ikki

November 22, 2018
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

Transcript

  1. ஫ҙࣄ߲ • εϥΠυ͸ެ։͠·͢ɻ • ࣭໰ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β #pgcon18j ΁ ͓ئ͍க͠·͢ɻ • EC2

    ΍ RDS ͳͲͱ͍ͬͨAWSͷαʔϏεͷࣄલ஌͕ࣝඞཁͰ͢ɻ • EC2: AWS͕ఏڙ͢ΔԾ૝αʔόʔ • RDS: AWS͕ఏڙ͢ΔϚωʔδυRDBαʔϏε 3
  2. 1. ࣗݾ঺հ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΤϯδχΞ

    • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ • PostgreSQLྺ 4ϲ݄ ! 4
  3. ΦϛΧϨʹ͍ͭͯ • αʔϏε։͔࢝Β໿̓೥͕ܦա͍ͯ͠Δɻ • ͜͜൒೥ఔͰ ։ൃਓһ͕ࣾһ1໊ + ֎஫ ͷମ੍͔Β ࣾһ6໊

    ͷମ੍ʹมֵɻ • ৭Μͳେਓͷࣄ৘͔ΒͦΕͳΓʹ͍ΘΏΔٕज़తෛ࠴ͱݺ͹ ΕΔ΋ͷ͕͋Δɻ 8
  4. gender ΧϥϜ ૝ఆ ࣮ࡍ ܕ int ೖΔσʔλ 0, 1, 2

    σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 13
  5. gender ΧϥϜ ૝ఆ ࣮ࡍ ܕ int varchar ೖΔσʔλ 0, 1,

    2 0, 1, 2, உੑ, ঁੑ σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 14
  6. DBͷϦϑΝΫλϦϯάํ๏ • ผͷΠϯελϯεΛ༻ҙ͠ɺ AWS Database Migration ServiceΛ ࢖༻ͯ͠ϨϓϦέʔγϣϯ͢Δɻ • ϨϓϦέʔγϣϯ͞ΕͨλΠϛϯάͰ

    DBͷτϦΨʔΛൃಈͤ͞Δɻ • τϦΨʔ಺ͰچσʔλΛՃ޻͠ ϦϑΝΫλϦϯάͨ͠ςʔϒϧͷ ܗࣜʹ߹͏Α͏ʹొ࿥͢Δɻ • ࠓޙ͸NEWεΩʔϚΛࢀর͢Δɻ 20
  7. 22

  8. ͜ͷϦϑΝΫλϦϯάํ๏ͷϝϦοτ • ߋ৽API͸MySQL΁ॻࠐΛ͢ΔͷͰগͮͭ͠APIܦ༝ʹͯ͠΋ طଘαʔϏεͷςʔϒϧͷࢀরՕॴʹӨڹ͕ແ͍ɻ • ҰՕॴAPIܦ༝ʹͯ͠ΈΔ → ໰୊͕͋Ε͹ௐ੔͢Δɻˠ ... ͳͲͷΑ͏ʹ

    ΧφϦΞϦϦʔε ܗࣜͰ গͮͭ͠վળΛਐΊ͍͚ͯΔɻ • σʔλͷಡࠐ͸PostgreSQL͔Βߦ͏ͷͰ ॻࠐAPIΛݺͿଆ͸ͲͪΒʹॻ͖ࠐΜͰ͍Δ͔Λ ؾʹ͠ͳͯ͘΋ྑ͍ɻ 23
  9. ͜ͷϦϑΝΫλϦϯάํ๏ͷσϝϦοτ • ϩʔΧϧͷ։ൃ؀ڥΛ࡞੒͢Δࣄ͕ग़དྷͳ͍ɻ ※ ࠓɺׂͱҰ൪೰·͍͠ॴɻ • DMS͕ࢭ·ͬͨ࣌ʹࢀরAPI͕ίέΔɻ ※ τϦΨʔΤϥʔ౳ͰDMS͕ࢭ·ΔͱαʔϏε͕ࢭ·Δɻ •

    DMS͕஗Ԇͨ࣌͠ͷߟྀ͕೉͍͠ɻ ※ ࠓͷॴɺக໋ই͸ͳ͍ɻ(ই͕ແ͍ͱ͸ݴͬͯͳ͍) • ίϯϑϦΫτ͢Δࣄ͕ݒ೦͞ΕΔͷͰϦʔυϨϓϦΧʹ·ͰτϦΨʔΛషΕͳ͍͕ ϦʔυϨϓϦΧ͕Ϛελʔʹঢ֨͢ΔͱαʔϏε͕ࢭ·Δɻ 24
  10. 2. DBϦϑΝΫλϦϯάΛ΍Δཧ༝ͱͦͷख๏ ·ͱΊ • AWS DMSͰ MySQL͔ΒPostgreSQLʹ ϨϓϦέʔγϣϯ͍ͯ͠Δɻ • DMS͕Ҡߦͨ͠λΠϛϯάͰઃఆͨ͠τϦΨʔ͕ൃಈͯ͠

    ϦϑΝΫλϦϯά͞ΕͨςʔϒϧʹॻࠐΛ͢Δɻ • ߋ৽ɾࢀরΛAPIܦ༝ʹ͢ΔࣄͰطଘͷ։ൃΛਐΊͭͭ ϦϑΝΫλϦϯάͨ͠DBʹࢀরΛ੾Γସ͍͚͍͑ͯͯΔɻ • ϩʔΧϧͷ؀ڥߏங͕೉͍͠ͷͱɺDMS͕ࢭ·ͬͨ࣌ʹ ࢀরAPI͕ίέΔͷͰߟྀ͕೉͍͠ɻ 25
  11. 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
  12. 1ɿ PostgreSQLτϦΨʔʹ͍ͭͯ • PostgreSQL͚ͩͷػೳ • UPDATE࣌ʹSETʹࢦఆΧϥϜ͕͋Δ৔߹ͷτϦΨʔΛఆٛग़དྷΔɻ • UPDATE࣌ʹࢦఆΧϥϜ͕࣮ࡍʹมߋ͞Εͨ৔߹ͷτϦΨʔΛఆٛग़དྷ Δɻ •

    ߦϨϕϧ͚ͩͰແͯ͘จϨϕϧͰτϦΨʔΛఆٛग़དྷΔɻ ※ ྫɿ DELETEͰ10ߦফ͑ͨͱͯ͠ɺߦϨϕϧ͸10֤݅ߦʹର࣮ͯ͠ߦ ͞ΕΔɻจϨϕϧ͸DELETEจʹର࣮ͯ͠ߦ͞ΕΔɻಛʹ TRUNCATEจ ʹରͯ͠༗ޮɻ 31
  13. 2ɿ PostgreSQLͷڧΈ(खଓ͖ܕݴޠ) τϦΨʔͷ࣮ߦؔ਺ΛҎԼͷ֤ݴޠͰఆ͕ٛग़དྷΔɻ ໊শ ݴޠ ഑෍ํ๏ PL/pgSQL SQL جຊ഑෍ PL/Perl

    Perl جຊ഑෍ PL/Python Python جຊ഑෍ PL/Java Java ֦ு PL/v8 JavaScript ֦ு ※ ͦͷଞ 32
  14. PostgreSQLͷڧΈ(खଓ͖ܕݴޠ) • ※ୠ͠ɺAmazon RDS ͸ PL/Python Λαϙʔτͯ͠ͳ͍ɻɻ • ΦϛΧϨͰ͸ PL/v8Λར༻͍ͯ͠Δɻ

    • ࠾୒ཧ༝ɿ ܦݧͷ͋Δݴޠͷؔ܎͔ΒpgSQLͱv8͙Β͍͔͠બ୒ࢶ͕ແ͔ͬͨɻ ϕϯνϚʔΫͱͯ͠ pgSQLΑΓv8ͷํ͕਺ഒ଎͍ɺͱͷࣄͰpl/v8Λ࠾༻ɻ (PL/PythonΛ࢖͍͔ͨͬͨͳɾɾ) • ※ΦϛΧϨͰ͸ϐϡΞJavaScriptΛ࢖͍ͬͯΔ͕ɺ CoffeeScript΍React.js΋࢖͑ΔΒ͍͠ɻ 33
  15. AWS Database Migration Serviceͷ͝঺հ • AWS DMS Ͱ࢖ΘΕΔΩʔϫʔυ • ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ

    • λεΫͷৼΔ෣͍ʹ͍ͭͯ • λεΫͷઃఆํ๏ʹ͍ͭͯ • ҟछDBؒҠߦͷࡍͷΧϥϜͷܕม׵ʹ͍ͭͯ 38
  16. AWS DMS Ͱ࢖ΘΕΔΩʔϫʔυ • ιʔεΤϯυϙΠϯτ • σʔλͷίϐʔݩɻEC2onDB, RDS, ΦϯϓϨDB, S3

    ͳͲɻ • λʔήοτΤϯυϙΠϯτ • σʔλͷίϐʔઌɻEC2onDB, RDS, ΦϯϓϨDB, S3, DynamoDB ͳͲɻ • ϨϓϦέʔγϣϯΠϯελϯε • ίϐʔݩ͔Βίϐʔઌ΁σʔλͷϨϓϦέʔγϣϯΛߦ͏Πϯελϯεɻ • λεΫ • ͲͷςʔϒϧΛͲͷΑ͏ʹҠߦ͢Δ͔ΛఆΊͨϧʔϧɻ 39
  17. 40

  18. ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ • ΠϯελϯεΫϥε͸ T2, C4, R4ͷ̏छྨΛαϙʔτ. • T2: ։ൃɾݕূ༻ •

    C4: େྔͷԋࢉΛߦ͏৔߹ʹ໾ཱͭɻ (AWSతʹ͸ ҟछDBҠߦ͸͜ΕΛ͢͢Ί͍ͯΔ) • R4: ϝϞϦ૿ՃΠϯελϯεɻ (ҰճͷτϥϯβΫγϣϯ͕େ͖͍࣌͸͜Ε.) • ϚϧνAZʹରԠ͍ͯͯ͠ɺϑΣΠϧΦʔόʔΛαϙʔτ. • ࣮ࡍɺDMSͱ͓͕͔͔ͯۚ͠Δͷ͸Πϯελϯεར༻ྉͱετϨʔδ͚ͩɻ 41
  19. λεΫͷઃఆํ๏ʹ͍ͭͯ • ϨϓϦέʔγϣϯΛߦ͏ςʔϒϧͷࢦఆํ๏͸̎छྨ • userɿ จࣈྻҰகͰ user ςʔϒϧͷΈΛର৅ͱ͢Δɻ • user_%ɿ

    ਖ਼نදݱͰ user_xxx ςʔϒϧʹ߹க͢Δશͯͷ ςʔϒϧΛର৅ͱ͢Δɻ • ਖ਼نදݱͳͲͰෳ਺ࢦఆͨ͠৔߹͸λεΫ಺ͰฒྻͰςʔϒϧ͕ ϩʔυ͞ΕΔɻ (σϑΥϧτͰ͸8ςʔϒϧ) 43
  20. ΦϛΧϨͷλεΫӡ༻ํ਑ άϧʔϓ୯ҐͰখ͘͞ϨϓϦέʔγϣϯλεΫΛઃఆͯ͠ ҠߦΛਐΊ͍ͯΔɻ ྫɿ ϢʔβʔҠߦλεΫ, ύʔςΟҠߦλεΫ, ༧໿ҠߦλεΫ, ... • ཧ༝

    Ҡߦ׬ྃͨ͠ςʔϒϧΑΓDMSͷλεΫΛ࡟আ͍ͯ͘͠ࣄΛ ૝ఆ͍ͯ͠ΔͨΊɻ ӡ༻Λͯ͠ؾ෇͍͕ͨɺখ͘͞λεΫΛ෼ׂ͓ͯ͘͠ࣄͰҰݸͷςʔϒϧҠ ߦ͕ ࣦഊͨ࣌͠ʹαʔϏεશମͷো֐ʹͳΒͳ͍ɻ 45
  21. ҟछDBҠߦͷࡍͷΧϥϜͷܕม׵ʹ͍ͭͯ • ιʔεͷܕ → DMSઐ༻ͷܕʹΩϟετ → λʔήοτͷܕʹΩϟετ ͱม׵͢Δɻ DMSͷܕ ಺༰

    STRING จࣈྻ DATE ೔෇஋ DATETIME λΠϜελϯϓ஋ INT1 1όΠτɺූ߸෇͖੔਺ (INT2, INT4, INT8͕͋Δ) BOOLEAN ϒʔϧ஋ BLOB όΠφϦϥʔδΦϒδΣΫτ ※ ͦͷଞ 46
  22. 4. AWS DMSʹ͍ͭͯ ·ͱΊ • DMS͸ωοτϫʔΫӽ͠ʹϨϓϦέʔγϣϯΛ࣮ݱ͢ΔαʔϏε. • σʔλͷίϐʔݩͱઌΛࢦఆͯ͠ҠߦϧʔϧͱͳΔλεΫΛ࡞੒͢ΔࣄͰ σʔλͷҠߦ͸શͯDMSͷ੹຿Ͱ࣮ݱͯ͘͠ΕΔɻ •

    λεΫͷཻ౓Λଟͨ͘͠Γখͨ͘͞͠Γ͢ΔࣄͰ શମͷϨϓϦέʔγϣϯͷཻ౓Λௐ੔ग़དྷΔɻ • DMS୯ମͱͯ͠͸ϨϓϦέʔγϣϯΠϯελϯεͷӡ༻අ͕͔͔Δఔ౓Ͱ ҆ՁͰਐΊΔࣄ͕ग़དྷΔɻ 48
  23. νΣοΫ؂ࢹ߲໨ ------------------------------------------------ [plugin.checks.rds_log] command = "php /path/to/rds_log.php" check_interval = 5

    memo = "RDS Τϥʔϩά؂ࢹ" ------------------------------------------------ • mackerelͷઃఆϑΝΠϧʹ্هΛهड़͢Δͱ 5෼͓͖ʹ ࢦఆͨ͠ίϚϯυΛ࣮ߦͯ͘͠ΕΔ. 53
  24. PHPͷத਎ʹ͍ͭͯ AWS SDK for PHP Ͱ downloadDBLogFilePortionؔ਺Λ࣮ߦ. ʢDBͷϩάΛ ࢦఆҐஔ͔Βμ΢ϯϩʔυ͢Δɻʣ •

    :ERROR: ͳͲͷΩʔϫʔυΛरͬͯɺ Ωʔϫʔυ͕͋Ε͹ ͦͷจࣈྻΛechoͯ͠exit(1) Ͱऴྃ͢Δ. • MackerelͷcheckϓϥάΠϯ exit(0) : ਖ਼ৗ exit(1) : ҟৗ(WARNING) exit(2) : ۓٸ(CRITICAL) • MackerelͷઃఆͰWARNINGΞϥʔτ্͕͕ͬͨ࣌ͷ௨஌ઌΛઃఆ͢Δɻ 54
  25. ࠷େͷϝϦοτ • WebαʔϏεଆͷطଘόά(͜Μͳσʔλ͸ೖΒͳ͍)ʹ ؾ෇͘ࣄ͕ग़དྷΔɻ ʢ..ඞਢ߲໨ͳΜͰ NOT NULL͡Όͳ͍ͷʁͬͯ࿩͸ɾɾʣ • τϦΨʔ಺Ͱ ϩάʹ

    :ERROR: ͬͯจࣈΛؚΊΔࣄͰ௨஌ΛϋοΫग़དྷΔɻ ʮ͜ͷݱ৅͸࡟আϑϥά͕1͔Β0ʹ໭Βͳ͍ͱى͜Γ͑ͳ͍ʯ ͬͯ໰୊ʹରͯ͠ UPDATEͷτϦΨʔͰ࡟আϑϥά͕໭ͬͯͨΒ :ERROR:ͱϩάΛు͘ॲཧΛ࢓ࠐΉࣄͰࣄ৅͕ൃੜ͍ͯ͠Δࣄʹؾ෇͚Δɻ ʢ࣮ࡍ໭ͬͯͨɻɻɻʣ 56
  26. Zero Date໰୊ MySQLͰ͸ 0000-00-00 Λ DatetimeܕͷΧϥϜʹొ࿥͢Δࣄ͕ग़དྷΔɻ PostgreSQLͰ͸ 0000-00-00 Λ ೔෇ʹΩϟετ͢Δࣄ͸౰વग़དྷͳ͍ɻ

    • DMSͰϨϓϦέʔγϣϯ͠Α͏ͱͨ࣌͠఺ͰίέΔɻ • hoge_date NOT NULL DEFAULT '0000-00-00' ͷڪා... 58
  27. 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
  28. Πϕϯτࠂ஌ • ୈ25ճ தࠃ஍ํDBษڧձ in ௗऔ • 2018-12-01(౔) 13:00ʙ PostgreSQL

    11͸ੌ͍ͧʂ MySQL 8.0͸ੌ͍ͧʂ → ʮͲ͏΍ͬͯόʔδϣϯΞοϓ͢Δͷʁʯ ख๏Λ͓࿩ग़དྷΔߨࢣਞͷํΛ͓ট͖ͯ͠͝঺հ͠·͢ʂ 67