TRUNCATE user;に如何にして立ち向かうか

by meru_akimbo

Slide 1

Slide 1 text

TRUNCATE user;ʹ೗ Կʹཱͯͪ͠޲͔͏͔ @meru_akimbo

Slide 2

Slide 2 text

Who are you? • @meru_akimbo • αʔόॻ͍ͨΓWebΦϖϨʔγϣϯͨ͠Γ όΠΫ৐ͬͨΓ • 24ࡀ • ΊͬͨʹਓલͰ·ͤΜ

Slide 3

Slide 3 text

ԠืͷܦҢ

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

ίϫΠ!

Slide 6

Slide 6 text

க໋ইʹͳΓͦ͏ͳΫΤϦ • TRUNCATE user; • DROP TABLE user; • DROP DATABASE myapp; • DROP DATABASE myapp_user;

Slide 7

Slide 7 text

ோΊΔ͚ͩͰਏ͍

Slide 8

Slide 8 text

ྲྀੴʹͦΜͳΫΤϦଧͨͶʔΑ • ։ൃπʔϧ͕όάͬͯޡര • ։ൃDBͱצҧ͍ͯ͠ଧͬͨ • γφϦΦ͸৭ʑ…. • ຊ൪DBʹDROP DATABASEଧͪࠐΜͩ͜ͱ ͋Δਓ΋ੈͷதʹ͸͍Δ • ࢲͰ͢

Slide 9

Slide 9 text

େ͛͞ͳΫΤϦ͡Όͳͯ͘΋ • appͷόάͰக໋తͳෆ੔߹͕ɺͱ͔ • ͦ͏͍͏ͷ͸͋ΔΘ͚Ͱ • ϊʔΨʔυઓ๏͸΄΅΄΅͋Γ͑ͳ͍ • ͱ͍͏ͱ͜Ζ·Ͱ͸લఏͳΘ͚Ͱ

Slide 10

Slide 10 text

ʮͪΌΜͱόοΫΞοϓऔΕ͹ࡁΉ࿩Ͱ͸?ʯ

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

“ͪΌΜͱ” is Կ

Slide 13

Slide 13 text

ͦΕ”ͪΌΜͱ”Ͱ͖ͯΔ? • ͍͟ͱ͍͏࣌ຊ౰ʹϦετΞͰ͖Δͷ? • ϦετΞͨ͠Βͦͷ··αʔϏε࠶։Ͱ͖ Δͷ? • σʔλྔ૿͑ͯ΋ͦͷ΍ΓํͰେৎ෉?

Slide 14

Slide 14 text

ฐࣾͰΑ͋͘Δmysqlߏ੒ • લఏͱͯ͠ΦϯϓϨ • master 1୆ɺslave n୆ɺbackup 1୆ • backup͸24࣌ؒʹ1౓ • backup༻ͷDBΛҰ౓ࢭΊͯcp͢Δ • ऴΘͬͨΒbackup༻DBΛ࠶౓্ཱͪ͛

Slide 15

Slide 15 text

௕͍ؒ͜ͷӡ༻ͩͬͨ • ಛʹ໰୊ى͖ͯͳ͔ͬͨ • ࣮ࡍʹbackup͔ΒmasterΛrestore͢ΔϨ ϕϧͷࣄނ͸·ͩݟͨ͜ͱͳ͍ • ৽͍͠slaveͷߏஙͳͲʹ͸Α͘࢖ΘΕ͍ͯ ͨ

Slide 16

Slide 16 text

ن໛͕େ͖͘ͳ͖ͬͯͨ • ࠓ·ͰͷεϖοΫͷDBαʔόͩͱ௥͍͔ͭ ͳ͍Α͓…

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

HDD => SSD׵૷ • ࣓ྗΛଳͼͨԁ൫Λճసͤ͞ΔّࣜͰઓ͍ ଓ͚Δͷ͸ਏ͍… • ͷ஄ؙ • ͠͹Β͘͸ฏ࿨ʹͳͬͨ

Slide 19

Slide 19 text

IO wait΋Լ͕ͬͯΊͰͨ͠ΊͰͨ͠

Slide 20

Slide 20 text

(ͦͷޙͷνϡʔχϯάͷઓ͍͸·ͨผͷ ࿩…

Slide 21

Slide 21 text

݄ͦͯ͠೔͸ྲྀΕ…

Slide 22

Slide 22 text

upstartʹbackupΛࡴ͞Εͨ

Slide 23

Slide 23 text

/etc/init/mysqlΑΓ # MySQL Service description "MySQL Server" author "Mario Limonciello " start on runlevel [2345] stop on starting rc RUNLEVEL=[016] respawn respawn limit 2 5 env HOME=/etc/mysql umask 007 # The default of 5 seconds is too low for mysql which needs to flush buffers kill timeout 300 ҎԼলུ

Slide 24

Slide 24 text

mysqlͷఀࢭʹ͕͔͔࣌ؒΓ͍͗ͯͨ͢…

Slide 25

Slide 25 text

͋ɺkill timeoutɺSIGKILLଧͨΕ·͢

Slide 26

Slide 26 text

backupͷmysqlىಈͰ͖ͳ͘ͳͬͨ

Slide 27

Slide 27 text

ͻͱ·ͣtimeoutΛ৳͹͍ͯ྇ͩ͠…

Slide 28

Slide 28 text

next battle ﬁeld….

Slide 29

Slide 29 text

ϨϓϦέʔγϣϯ஗Ԇઓ૪

Slide 30

Slide 30 text

backupͷΈ஗Ԇ͕ى͖࢝Ίͨ • ͦ΋ͦ΋ͳΜͰbackup͚͓͖ͩͨͷ?

Slide 31

Slide 31 text

backup͸HDDͷ··͔ͩͬͨΒ….

Slide 32

Slide 32 text

ΞϗΈ͍ͨʹݟ͑Δ͕… • ͨͩϨϓϦέʔγϣϯ͢Δ෼ʹ͸౰ॳ͸େ ৎ෉ͩͬͨͷͩ • backup༻్ͰSSDΛ༻ҙ͢Δͱɺ༰ྔ΋େ ͖͘ͳΓ݁ߏ͓ߴ͍ • σʔλαΠζ૿͑ͨΒ΋ͬͱͰ͔͍ͷ౎౓ ങͬͯೖΕସ͑Δͷ͔? • ͻͱ·ͣ࠷ॳ͸ݟૹ͍ͬͯͨΑ͏

Slide 33

Slide 33 text

࠷ॳ͸஗Εͯ΋௥͍͍ͭͨ • ࠶ىಈͷλΠϛϯάͰ͕ͬͭΓ஗Εɺঃʑ ʹղফ • ͕ɺͩΜͩΜͻͲ͘ͳ͖ͬͯͨ • master͔ΒҾ͖཭͞ΕΔҰํʹ

Slide 34

Slide 34 text

backup͕஗ΕΔͱࠔΔ͜ͱ • ·͋ΞϓϦέʔγϣϯ͔Β͸௚઀࢖ΘΕͯ ͳ͍ͷͰɺଈக໋తͰ͸ͳ͍ • ஗Εͨঢ়ଶͰ࡞ͬͨbackup͸Ձ஋͕௿͍ • 1೔஗Εͨঢ়ଶͰ࡞ΔͱɺϦετΞ࣌ʹ࠷େ 2೔஗ΕΔܭࢉʹͳΔ

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

SSDΛͬ͞͞ͱങͬͯ΋ྑ͔͕ͬͨ… • ͻͱ·ͣmy.cnfͷௐ੔Λͯ͠ΈΔ͜ͱʹ͠ ͨ • innodb_lru_scan_depth • innodb_io_capacity_max • ͳͲioʹؔ܎͢Δ෦෼Λௐ੔

Slide 37

Slide 37 text

վળ

Slide 38

Slide 38 text

͕ɺμϝͬ • ن໛ͷ֦େʹͭΕͯ·ͨ஗Ε࢝Ίͨ • εϖοΫࠩΛ޻෉ͰຒΊΔͷ͕ݶքʹདྷͨ

Slide 39

Slide 39 text

΋͏͔ͭͬͯ΋͍͍ΑͶ…?

Slide 40

Slide 40 text

͜ͷػձʹ৭ʑݟ௚͍ͨ͠… • ༰ྔ͕૿͖͑ͯͯ΋ਏ͘ͳ͍ঢ়ଶ • ஗Ԇ͕ൃੜͮ͠Β͍ঢ়ଶ • ͦ΋ͦ΋࠷େ1೔ר͖໭Δͷ΋ڐ༰Ͱ͖ͳ͘ ͳ͖ͬͯͯΔͷͰͳΜͱ͔͠ͱ͖͍ͨ

Slide 41

Slide 41 text

Ҋ1: SSDͱHDD྆ํϚ΢ϯτͯ͠HDDʹϑ ϧόοΫΞοϓΛஔ͘ • ᝑମͷ౎߹Ͱ2ຕϚ΢ϯτෆՄͳͷͰແཧ • ૬มΘΒͣσʔλ૕ࣦ͢Δ࣌ؒଟΊ

Slide 42

Slide 42 text

Ҋ2: SSDԽ + ࠩ෼όοΫΞοϓ • SSDʹͭͭ͠ɺࠩ෼όοΫΞοϓΛऔಘͯ͠ ϩʔϧόοΫ͢Δ࣌ؒΛ୹ͭͭ͘͠༰ྔͷ ૿Ճ΋཈͑Δ • Քಇͯ͠ΔDB+ϑϧόοΫΞοϓ+ࠩ෼෼ͷ ༰ྔ͕ඞཁͰɺ͜ΕͰ΋ΘΓͱͭΒ͍

Slide 43

Slide 43 text

Ҋ3: SSDԽ+binlogͰϩʔϧϑΥϫʔυ • SSDʹͯ͠ɺϑϧόοΫΞοϓͷස౓͸૿΍ ͣ͞ɺ໰୊ൃੜ࣌͸binlogΛ໰୊ൃੜ௚લ ·Ͱద༻ͤͯ͞σʔλͷফࣦΛ΄΅0ʹ͢Δ • ՔಇDB+ϑϧόοΫΞοϓΛ͓࣋ͬͯ͘ͷ ͸༰ྔ͕(ry

Slide 44

Slide 44 text

࠾༻Ҋ: SSDԽ+஗ԆϨϓϦέʔγϣϯ • ஗ԆϨϓϦέʔγϣϯ͢ΔslaveΛઃஔ͠ɺ ͦΕΛόοΫΞοϓͱ͢Δ • ໰୊ൃੜͨ͠Βstop slave͔ͯ͠Βrelaylog Ͱ໰୊ൃੜ௚લ·Ͱ໭͢ • ՔಇDB෼+஗Ԇ෼ͷrelaylogͷ༰ྔͰࡁΉ

Slide 45

Slide 45 text

೰·͍͠ͱ͜Ζ • TRUNCATE user;࣮ߦ͔Β஗Ԇ෼࢖͍Ռͨ͢ ·Ͱ์ஔͯ͠͠·͏ͱࢮ͵ • ͱ͸͍͑஗Ԇ͕े෼௕͚Ε͹·͋େৎ෉Ͱ ͠ΐ • ෮چʹ͕͔͔࣌ؒΔͷ͸ڐ༰

Slide 46

Slide 46 text

Ͳͷ͘Β͍஗Ԇͤ͞Δ? • ࠓ͸24࣌ؒʹͯ͠Δ • 24࣌ؒαʔϏε௜໧ͨ͠Βྲྀੴʹؾͮ͘ • ௕͚Ε͹௕͍΄Ͳ༛༧͕૿͑ΔͷͰɺ΋ͬ ͱ௕ͯ͘΋͍͍͔΋ • ௕ա͗Δͱrelaylog͕ͨ·Γ͗͢Δ

Slide 47

Slide 47 text

ϦετΞͲ͏΍Δͷ? • relaylogΛmysqlbinlog͔ΒSQLʹॻ͖ى͜ ͯ͋͛ͯ͠ɺ໰୊ͷ௚લ·Ͱ࣮ߦ͢Δ? • ͱࢥ͍ͬͯͨ࣌ظ͕๻ʹ΋͋Γ·ͨ͠ • start slaveͷΦϓγϣϯͰָͰ͖Δ

Slide 48

Slide 48 text

START SLAVE ͷUTILΦϓγϣϯ • START SLAVE UTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos • ࢦఆͷrelaylogͷࢦఆͷpositionͰstop͢Δ Α͏ʹ্ͨ͠Ͱstart slaveͰ͖Δ • ͋ͱ͸RESET SLAVE ALL;ͰmasterʹͳΕΔ

Slide 49

Slide 49 text

TRUNCATE user;ଧͬͯ΋Ͳ͏ʹ͔ͳΔ

Slide 50

Slide 50 text

ଧͨͳ͍ͷ͕Ұ൪͍͍

Slide 51

Slide 51 text

׬ᘳͰ͸ͳ͍… • TRUNCATE user;ͱbackupαʔόͷ෺ཧత ͳഁଛ͕ಉ࣌ʹى͖ͨΒ..? • ୆਺૿΍ͯ͠΋͍͍͕…. • ׬ᘳΛٻΊΔͱ͍͘ΒϦιʔε͕͋ͬͯ΋ ଍Γͳ͍…

Slide 52

Slide 52 text

ͦͷଞ࠷ۙࢥ͏͚Ͳ • RDS͸ϩʔϧϑΥϫʔυͰ͖ͳ͍Β͍͚͠ ͲͲ͏ͳΜ͡ΌΖ? • σʔλͷϩετΈΜͳڐ༰ͯ͠Δͷ? • όοΫΞοϓઓུɺ࿩୊ʹͳΔ͜ͱ͘͢ͳ ͍Α͏ͳ͖͕͢ΔͷͰଞࣾࣄྫ͠Γͨ͞