MySQL Casual Vol5 LT資料 http://www.zusaar.com/event/1086003
MySQLͷҠߦΛࢧ͑Δٕज़1310݄25༵ۚ
View Slide
Yudai@nekogeruge_987WEBܥاۀۈDB(RDBMS, NoSQL)RubyChefFluentdhttps://github.com/onigra1310݄25༵ۚ
ࠓͷ݄̍ʹIBM DB2͔ΒMySQL5.5ʹҠߦͨ͠Λ͠·͢1310݄25༵ۚ
نײ1310݄25༵ۚ
allཧεΩʔϚ 13ςʔϒϧ 330૯Ϩίʔυ 321,906,4701ςʔϒϧ͋ͨΓͷϨίʔυ 975,474Ұ൪Ϩίʔυ͕ଟ͍ςʔϒϧ 18,054,99013ӡ༻͞ΕͯΔجװγεςϜɺαʔϏεܥDB܈JavaɺPHPɺVB6ͱ͔…euc-jp -> utf-81310݄25༵ۚ
݁ՌɺϯԯԁͷܦඅൃੜΛ્ࢭ1310݄25༵ۚ
۩ମతͳ࡞ۀ༰1310݄25༵ۚ
• DB2ͷϨίʔυΛCSVΤΫεϙʔτ• MySQLLOAD INFILE• /var/lib/mysql ΛslaveͷαʔόҠͯ͠෮ݩʢColdBackupʣ• ϨϓϦέʔγϣϯઃఆ• FederatedΤϯδϯΛ༻͢ΔಛघͳϨϓϦέʔγϣϯͷߏங(MySQL -> DB2 ͷϨϓϦέʔγϣϯΛ͢Δඞཁ͕͋ͬͨͨΊ)• ͜ΕΒΛShellScript(bash)ͷόονͰߦ͏1310݄25༵ۚ
Time Limit4࣌ؒ※ΦϑϥΠϯϝϯς8࣌ؒͷ͏ͪલ4࣌ؒͰྃ͢Δඞཁ͕͋Δ1310݄25༵ۚ
ରࡦ1310݄25༵ۚ
1. LOAD࣌ؒͷॖΛਤΔ1310݄25༵ۚ
•Bulk Insert < LOAD INFILE < Cold Backup•Slow-logɺBin-log ͳͲࢭΊΕΔϩάࢭΊΔ•INDEXΛDROP -> LOAD -> INDEXషΔ(ςʔϒϧʹΑΓૣ͔ͬͨΓɺషͬͨ··ͱͦΜͳʹ͕ࠩແ͔ͬͨΓ)• csvετϨʔδΤϯδϯΛ͏ͱͪΐͬͺΒ͍͚͠Ͳ͏·͍͔͘ͳ͔ͬͨʢΓ͔ͨѱ͔ͬͨͷ͔ʣ1310݄25༵ۚ
͜ͷลάάͬͨΒׂͱग़ͯ͘Δ͕ɺશମͷ࡞ۀͰݟΔͱେ͖ͳॖʹͳΒͳ͔ͬͨ1310݄25༵ۚ
2.δϣϒϑϩʔͷ࠷దԽʢJenkinsͷ׆༻ʣ1310݄25༵ۚ
1310݄25༵ۚ
Jenkins Build Flow Pluginhttps://wiki.jenkins-ci.org/display/JENKINS/Build+Flow+Plugin1310݄25༵ۚ
Build Pipeline Plugin͕ڧྗʹͳͬͨײ͡Job FlowΛGroovyͰॻ͚Δฒྻ࣮ߦ(Parallel)ϦτϥΠ(Retry)ྫ֎ͷัଊ(guard / rescue)ͳͲͷػೳΛඋ͍͑ͯΔ1310݄25༵ۚ
࠷্Ґparallel ({ build(“order” ) },{ build(“user”) },{ build(“master”) })userbuild (“export”)build (“rsync”)build (“load”)build (“mysqld stop”)build (“rsync”)parallel ({ build(“master mysqld start”) },{ build(“slave mysqld start”) })1310݄25༵ۚ
ͪͳΈʹɺ͜ͷҊ݅ͷޙTivoliͱ͔JP1ͱ͔Γ·ͨ͠·ͩۀքྺͰݴ͏ͱएखͳΜͰ…1310݄25༵ۚ
σʔλҠߦΛCI͢ΔΑ͏ͳײ͡Ͱຖ·Θ͢࠷ऴతʹ1ΫϦοΫͰҠߦ͕ྃ͢ΔBuild FlowΛ࡞↓ͦΕΛຖ࣮ߦ͠ɺԿճޭͤ͞Δ↓Ҡߦຊ൪ʹର͢Δڪා৺Λແ͘͢1310݄25༵ۚ
̍ΫϦοΫσʔλҠߦʙ͍ͭ·ͰखͰDBҠߦͯ͠ΔΜͰ͔͢ʁʙ※ @ryuzee͞ΜͷΦϚʔδϡͰ͢http://www.slideshare.net/Ryuzee/devsumia1310݄25༵ۚ
શࣗಈԽͰ͖͔ͨۃͳɺ࡞ۀऀ͕͍ͳ͍ʢԿ͠ͳ͍ʣσʔλҠߦͰ͖Δؾ͕͢Δ1310݄25༵ۚ
༨ஊδϣϒεέδϡʔϥʔͷΦʔϓϯιʔειϑτΣΞͬͯΠέͯΔͷແ͍ΜͰ͔͢ͶʁδϣϒεέδϡʔϥʔͬͯΤϯλʔϓϥΠζք۾Ͱॏๅ͞Εͯͦ͏͔ͩΒWEBͷਓୡڵຯແ͍ʁDWHͱ͔݁ߏ͏ͱࢥ͏ΜͰ͚͢ͲjobschedulerͳΔάάϥϏϦςΟͷ͍ΦʔϓϯιʔεͷιϑτΣΞ͚͋ͬͨͲɺใগͳ͍1310݄25༵ۚ
͜ΕͰ·ͩؒʹ߹Θͳ͍1310݄25༵ۚ
3. σʔλసૹ࣌ؒͷॖ(Cold Backup)1310݄25༵ۚ
/var/lib/mysql ԼͷશϑΝΠϧΛฒྻͰrsync͢Δhttp://qiita.com/nekogeruge_987/items/ed1bd8704b25b1f509ec1310݄25༵ۚ
ҙσΟϨΫτϦߏΛઌʹίϐʔઌʹίϐʔ͓͔ͯ͠ͳ͍ͱrsync͕͚͜ΔωοτϫʔΫͷଳҬͷଠ͞ʹґଘ͍ͯ͠ΔͷͰɺଳҬ͕ڱ͍ͱ͋·ΓޮՌ͕ແ͍ϑΝΠϧͷϓϩηεΛىಈ͢ΔͨΊɺ100ສݸϑΝΠϧ͕͋Δͱ100ສϓϩηε͕ͨͪ͋Δྃޙʹී௨ͷrsyncΛ࣮ߦ͠ɺ͕ࠩແ͍͔ΛνΣοΫ͢Δͱϕλʔ1310݄25༵ۚ
1࣌ؒఔ͔͔ͬͯͨͷ͕20ఔͰྃ͢ΔΑ͏ʹͳΓ·ͨ͠1310݄25༵ۚ
൪֎ɿࠓࢥ͏ͱΓ͔ͨͬͨ͜ͱShellScripͷUnitTestΛॻ͘1310݄25༵ۚ
࣌ShellScript΄΅ॻ͍ͨࣄແ͍͍ͭͰʹςετॻ͍ͨࣄແ͍ࣄલͷҠߦͰΘΕͯͨίʔυΛ͍ճ࣌ؒ͢ແ͔ͬͨͷͰແઅૢʹScript͕૿͍͑ͯ͘ࠓshunit2ͱ͍͏ShellScriptͷTestingFrameworkͰςετॻ͍ͯΔhttps://speakerdeck.com/yudaisuzuki/shell-script-testing-framework-shunit21310݄25༵ۚ
Thanks!!https://twitter.com/nekogeruge_987 http://onigra.github.io/1310݄25༵ۚ