first_mysql_performance_tuning_at_builderscon2017

ॳΊͯͷ .Z42-αʔόʔ νϡʔχϯά σʔλϕʔε͸ා͘ͳ͍ʂ ·Έ΍ͳ͓͖ !NBNZ "VH!CVJMEFSTDPO

ࠓ೥ॳΊͯ NZDOGΛ৮ͬͨ๻͕

िؒͰ

αʔϏεΛ يಓʹ৐ͤͨ࿩Λ௨͠

ʮσʔλϕʔε͸ ා͘ͳ͍ʯΛ ͓࿩͠·͢

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ
▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

ࣗݾ঺հ ▶︎ ΦϥΫΕͳ͍ ▶︎ ϙεάΕͳ͍ ϫλγνϣοτμέ ϚΠΤεΩϡʔΤΔ

ࣗݾ঺հ Name ɿ·Έ΍ͳ͓͖ Twitterɿ@mamy1326 - ීஈ͸PHPॻ͍ͯ·͢ - 2017೥1݄ʹॳΊͯmy.cnfΛ৮Δ - ࠓ೥1೥
MySQLΛझຯʹͯ͠ΈΔ

ɹ૝ఆΦʔσΟΤϯε ɾΞϓϦέʔγϣϯΤϯδχΞ ɾ%#͸ීஈ͸πʔϧͰࢀরɾૢ࡞ ɹˠίϚϯυϥΠϯͰ%#ૢ࡞͠ͳ͍ ɾίϯιʔϧ͸͋Μ·Γ࢖Θͳ͍

σʔλϕʔε͸ ා͍Ͱ͔͢ʁ

ਖ਼௚๻͸ ා͔ͬͨͰ͢

ϫλγ.Z42- μΠϒίϫΠ

ɹ఻͍͑ͨ͜ͱ ⾣.Z42-ͱ਌͠Ή ⾣αʔόʔ͕ා͘ͳ͘ͳΔ ⾣ӡ༻Λ஌Δ ⾣ΠϯϑϥΛࢧ͑Δٕज़Λ஌Δ ⾣ΈΜͳνʔϜͰ͋Δ

μΠϒίϫΠ ˣ ΍ͬͯΈΑ͏ʂ

ɹͦΜͳΘ͚Ͱ θϩ͔Β࢝Ίͨ .Z42-νϡʔχϯά

ϓϩϩʔά

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ िʹҰ౓ %#αʔόʔμ΢ϯ ೾͕ऩ·ΔͷΛ ͻͨ͢Β଴ͭ

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ %#ͷ -"͕௓Ͷ্͕ͬͯΔΜͰ ͱΓ͋͑ͣ XFCͷ઀ଓ਺Λ੍ݶͯ͠ Ͳ͏ʹ͔͍ͯͨ͠

ɹӡ༻͔Βಀ͍͛ͯͨ ⾣ௐ΂Δ͜ͱΛ์ஔͯ͠ ⾣αʔϏεΛ൒୺ʹѻ͍ͬͯͨ ⾣΍ΕΔͷʹ΍Βͳ͍ ⾣ཧ༝Λ͚ͭͯઌԆ͹͠

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ %#ͷ -"͕௓Ͷ্͕ͬͯΔΜͰ ͱΓ͋͑ͣ 8&#ͷ઀ଓ਺੍ݶͯ͠ Ͳ͏ʹ͔͍ͯͨ͠ ౖ

ঢ়گ̍ ϊϯνϡʔχϯά .Z42- ઐ༻αʔόʔ

ঢ়گ̎ ࢭ·Βͳ͍ )551ϦΫΤετ Ͱ΋ඵؒ̑

ঢ়گ̏ ڊେͳϩάςʔϒϧ ϑϧεΩϟϯ º̎ ֤ສϨίʔυ

ঢ়گ̐ ࿈ൃ͢ΔεϩʔΫΤϦ ϩά͸औ͍ͬͯͳ͍

ঢ়گ̑ ࢮΜͩΒऴΘΔϚελʔ εϨʔϒ͕ͳ͍

ঢ়گ̒ .Z42-ܥ ੓࣏తʹ όʔδϣϯΞοϓͰ͖ͳ͍

໨ࢦ͢ .Z42-ͷઃఆΛ ͪΌΜͱཧղͯ͠ ࠷దͳύϑΥʔϚϯεΛɻ

ෛՙঢ়گ

ɹɹ.Z42-αʔόʔͷෛՙঢ়گ ⾣-"͕ಥ͖ൈ͚͍ͯΔ ⾣ϦΫΤετ਺͸ৗࣝత ⾣ϝϞϦͷ࢖༻ྔʹมԽ͕ͳ͍ ⾣TXBQ͸͍ͯ͠ͳ͍ ⾣ετϨʔδ༰ྔ͕ṧഭ͍ͯ͠Δ

ɹԾઆ ⾣εϩʔΫΤϦ ɹˠසൃ͍ͯ͠ΔͷͰ͸ʁ ⾣ϝϞϦ ɹˠਖ਼ׂ͘͠Γ౰ͯΒΕ͍ͯͳ͍ʁ ⾣֤छϩά ɹˠઃఆ͕ద੾Ͱ͸ͳ͍ʁ

.Z42- ਍அπʔϧΛ ࢖ͬͯΈΔ

ɹ.Z42-5VOFS ⾣.Z42-5VOFSͱ͸ ɹ.Z42-ͷνϡʔχϯά߲໨Λ ɹ਍அͯ͘͠ΕΔΞϓϦέʔγϣϯ ⾣πʔϧຊମ ɹIUUQTHJUIVCDPNSBDLFSIBDLFS.Z42-5VOFSQFSM

ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip
$ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326'

$ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' HJUIVC͔Β [JQΛXHFU

$ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' ղౚͱ ద੾ͳ࣮ߦݖݶ

$ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' Ϣʔβʔɺ 18ࢦఆ͠ɺ࣮ߦ

ɹ.Z42-5VOFS -------- Recommendations ----------------------------- General recommendations: Enable the slow query
log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. ⾣࣮ߦ݁Ռ

਍அ݁Ռ ⾣&OBCMFUIFTMPXRVFSZMPHUP USPVCMFTIPPUCBERVFSJFT ɹεϩʔΫΤϦ͕ઃఆ͞Εͯͳ͍ ɹɹˠ#"%ͳΫΤϦʔΛ ɹɹɹɹɹɹτϥϒϧγϡʔτͰ͖ͳ͍ͧˑ

਍அ݁Ռ ⾣JOOPEC@pMF@QFS@UBCMF0/ ɾ*OOP%#ͷσʔλΛ ɹˠςʔϒϧ୯ҐͰϑΝΠϧ෼͚ͨ ɹɹɹɹɹɹɹɹɹ΄͏͕͍͍Αˑ

਍அ݁Ռ ⾣JOOPEC@CV⒎FS@QPPM@TJ[F ( JGQPTTJCMF σʔλͱJOEFYΛ ϝϞϦʹΩϟογϡ͓ͯ͘͠ αΠζΛઃఆ͠ͳ͍͞Αˑ

਍அ݁Ռ ⾣JOOPEC@MPH@pMF@TJ[FTIPVMECF FRVBMTUPPGCV⒎FSQPPMTJ[F . JGQPTTJCMF *OOP%#ͷߋ৽ϩάϑΝΠϧΛ JOOPEC@CV⒎FS@QPPM@TJ[Fͷ ෼ͷʹͯ͠Ͷˑ

ɹԾઆͱ਍அ݁Ռ·ͱΊ ⾣εϩʔΫΤϦ ɹˠه࿥͞Ε͍ͯͳ͔ͬͨ ⾣ϝϞϦ ɹˠద੾ʹׂΓ౰ͯΒΕ͍ͯͳ͔ͬͨ ⾣֤छϩά ɹˠߋ৽ϩάઃఆ͕ద੾Ͱ͸ͳ͔ͬͨ ⾣ςʔϒϧσʔλͷϑΝΠϧ ɹˠݸผྖҬʹͳ͍ͬͯͳ͔ͬͨ

NZDOG શ߲໨ͷ ௐࠪɾઃఆ

ɹNZDOG֬ೝ [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-lincs=0 expire_logs_days=3 sort_buffer=4M log-bin=mysql-bin server-id=1001 ΄΅σϑΥϧτ
ɾТɾA

ɹNZDOGઃఆํ਑ ɾڞ༗ྖҬ͔Β ɹɹɹςʔϒϧݸผྖҬ΁ ɾద੾ͳϝϞϦׂΓ౰ͯ

ɹڞ༗ྖҬͱ͸ ɾ.Z42-ͷσϑΥϧτ͸ɺ͓͖͍ͬശͷத ɹͰશςʔϒϧΛऔΓѻ͍ͬͯΔ ɾେ͖ͳςʔϒϧ΋খ͞ͳςʔϒϧ΋ಉډ ɾϨίʔυͰ΋ߋ৽ʹ͕͔͔࣌ؒΔ

ɹڞ༗ྖҬͷ࣮ྫ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB

ɹڞ༗ྖҬͷ࣮ྫ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB ͜ͷϑΝΠϧʹ શͯͷςʔϒϧͷσʔλ͕ ه࿥͞Ε͍ͯΔ

ɹςʔϒϧݸผྖҬͱ͸ ɾςʔϒϧΛ̍ͭͣͭ෼͚ͯऔΓѻ͏ ɾେ͖ͳςʔϒϧ΋খ͞ͳςʔϒϧ΋ݸผ ɾߋ৽ର৅ͷςʔϒϧͷྖҬʹͷΈߋ৽͕ ɹ͔͔ΔͨΊଞʹӨڹΛ༩͑ͳ͍

ɹઃఆ಺༰ ͜Ε͚ͩʂ [mysqld] innodb_file_per_table=1

ͨͩ͠ɾɾɾ ɾNZTRM࠶ىಈ͚ͩͰ͸ ɹطଘͷςʔϒϧʹରͯ͠͸ద༻͞Εͳ͍ ɾ࠶ىಈޙʹ$3&"5& ɹ͞ΕΔςʔϒϧʹͷΈ༗ޮ EVNQϦετΞ͕ඞཁ

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ EBUBCBTF໊Ͱ σΟϨΫτϦ͕࡞੒͞Ε ςʔϒϧ͝ͱʹ ϑΝΠϧ͕࡞ΒΕ͍ͯΔ

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ EBUBCBTF໊Ͱ σΟϨΫτϦ͕࡞੒͞Ε ςʔϒϧ͝ͱʹ ϑΝΠϧ͕࡞ΒΕ͍ͯΔ Ώɹ͑ɹʹ

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ SFQPSUςʔϒϧɹ͕ ߋ৽͞Εͨ৔߹ ֘౰ͷϑΝΠϧͷΈߋ৽

ɹNZDOGઃఆํ਑ ɾڞ༗ྖҬ͔Β ɹɹɹςʔϒϧݸผྖҬ΁ ɾద੾ͳϝϞϦׂΓ౰ͯ

ɹϝϞϦׂΓ౰ͯͱ͸ ɾσʔλͱJOEFY͸ ɹɹɹɹϝϞϦʹΩϟογϡ ɾద੾ͳαΠζ͸ ɹɹɹɹ෺ཧϝϞϦͷׂ

ɹઃఆ಺༰ ͜Ε͚ͩʂ [mysqld] # InnoDBͷσʔλͱindexΛΩϟογϡ͢ΔόοϑΝαΠζ # (ਪ঑͸෺ཧϝϞϦ8ׂ) innodb_buffer_pool_size=6G # InnoDBͷߋ৽ϩάΛه࿥͢ΔσΟεΫ্ͷϑΝΠϧαΠζ
innodb_log_file_size=1G

ղઆ ⾣JOOPEC@CV⒎FS@QPPM@TJ[F( ɾσʔλͱJOEFYΛΩϟογϡ͓ͯ͘͠αΠζ ɾ%#αʔόʔͷ෺ཧϝϞϦ(ʹର͢Δ͓Αׂͦ ɾNZTRM࠶ىಈ࣌ʹׂΓ౰ͯΒΕ·͢ ɾॻ͖ࠐΈ࣌ʹ΋ύϑΥʔϚϯε޲্͕ݟࠐ·ΕΔ ɹˏ.Z42-ެࣜ

ղઆ ⾣JOOPEC@MPH@pMF@TJ[F( ɾ*OOP%#ͷߋ৽ϩάαΠζ ɾDPNNJUཤྺΛه࿥͢Δ ɹˠ.Z42-͸DPNNJU࣌ɺ͍ͬͨΜϩάʹه࿥͞ΕΔ ɾϑΝΠϧ͸γʔέϯγϟϧͳͷͰɺߋ৽͕଎͍ ɾੵΜͩDPNNJUΛॱ࣮࣍σʔλʹ൓ө͢Δ

ͦͷଞͷઃఆ ⾣εϩʔΫΤϦϩάؔ࿈ [mysqld] # εϩʔΫΤϦͷग़ྗઃఆ slow_query_log=ON # εϩʔΫΤϦͱ൑ఆ͢Δඵ਺ long_query_time=3 #
εϩʔΫΤϦͷ৔ॴ log-slow-queries=/var/log/slow.log

ͦͷଞͷઃఆ ⾣ΫΤϦΩϟογϡؔ࿈ [mysqld] # ΫΤϦΩϟογϡ࠷େαΠζ query_cache_limit=16M # ΫΤϦΩϟογϡͰ࢖༻͢ΔϝϞϦαΠζ query_cache_size=512M #
ΫΤϦΩϟογϡͷλΠϓ # 0:off, 1:ON SELECT SQL_NO_CACHE, 2:DEMAND SELECT SQL_CACHE query_cache_type=1⊠

൓өखॱ

൓өखॱ ⾣%#΁ͷΞΫηεΛఀࢭ ɹˠϝϯςφϯεϞʔυʹ ⾣EVNQऔಘ ɹˠεΩʔϚશମΛEVNQ͢ΔͷͰɺετϨʔδ༰ྔʹ஫ҙ ⾣ߋ৽ޙͷNZDOGʹࠩ͠ସ͑ ɹˠεςʔδϯάͰݕূࡁΈ ⾣.Z42-࠶ىಈ ɹˠTUPQTUBSUΤϥʔͳͲͰ͍ͯͳ͍͔֬ೝ ⾣%#ϦετΞ
ɹˠEVNQͨ͠಺༰ΛϦετΞ ⾣ಈ࡞ݕূɾϝϯςφϯεϞʔυղআɾܦա؍࡯ ɹˠςετ߲໨Λ࣮ߦɺϩά౳Λ֬ೝ

NZDOGઃఆ݁Ռ ⾣ඵؒϦΫΤετʂ ⾣ϝϞϦ͕ੵ·Εͨ෼͚ͩՔಇʂ ⾣ςʔϒϧݸผͰߋ৽ܥ͕ૣ͘ʂ ⾣ΫΤϦΩϟογϡώοτʂ ⾣JOEFYݟ௚͠ͰεϩʔΫΤϦղফʂ

ɹNZDOGઃఆ·ͱΊ ⾣αʔϏεʹԠͯ͡ద੾ʹઃఆʂ ⾣σϑΥϧτμϝʂθολΠʂ ⾣ઃఆޙͷ਺஋͸ఆظతʹ؂ࢹʂ ɹˠνϡʔχϯά͸ܧଓ໋͕ʂ ⾣ςʔϒϧઃܭ͸ܭըతʹʂ

ແࣄʹɾɾɾ αʔϏε͕ ӡ༻ʹ଱͑ΒΕΔΑ͏ʹ

ϨϓϦέʔγϣϯ ࣮ࢪ

.Z42- Λ ෳ ੡ ͠ αʔόʔ ࢲ͕ ˓ΜͰ΋ ୅ΘΓ͸ ུ
͢Δ ͓࿩ ·Έ΍ͳϮ͖ !NBNZ

ɹϨϓϦέʔγϣϯ ࢲͷ୅ΘΓ͸ ͍͘ΒͰ΋͍ΔΘ ͱݴͬͯ

ɹϨϓϦέʔγϣϯ ࣮ࡍ͸ ࢮΜͩΒऴΘΔ .Z42-Λ

ɹϨϓϦέʔγϣϯ ෳ੡ ϨϓϦέʔγϣϯ ͢Δ

੢ྐྵ೥݄

ϑΝʔετ ΠϯύΫτ

NZDOG͕ σϑΥϧτ

੢ྐྵ೥݄

ηΧϯυ ΠϯύΫτ

՝୊ Ϛελʔ͕ࢮ͵ͱ σʔλ͕શ෦ࢮ͵

՝୊ Ϛελʔ͕ࢮ͵ͱ σʔλ͕શ෦ࢮ͵ ·͔͞ͷ େരൃ

ෳ੡ Ϛελʔ͔͠ͳ͍ ݱঢ়ʹ εϨʔϒΛ࡞Γ

ෳ੡ ϨϓϦέʔγϣϯ ΛՔಇ͠·͢

ɹϨϓϦέʔγϣϯͱ͸ ⾣σʔλϕʔεશମͷෳ੡ ϨϓϦΧ ⾣ผͷαʔόʔʹෳ੡ʢεϨʔϒʣ ⾣.Z42-ͷඪ४ػೳͰ͓खܰ ⾣΄΅࣌ؒࠩͳ͠ʹಉظ

ߏ੒ Ϛελʔ ʢ୯Ұʣ εϨʔϒ ʢʣ εϨʔϒ ʢOʣ ɾɾɾ

Ϛελʔ ⾣σʔλΛߋ৽͢Δଆ ⾣ߋ৽಺༰ΛεϨʔϒ͕औΓʹདྷΔ ɹˠεϨʔϒʹର͠ߋ৽ΠϕϯτΛ௨஌ ⾣ෳ਺ͷεϨʔϒΛ࣋ͯΔ

εϨʔϒ ⾣ߋ৽༻Ͱ͸ͳ͘ࢀর༻ ⾣Ϛελʔͷߋ৽಺༰Λड͚औΔ ⾣ϚελʔͷόΠφϦϩάΛऔಘ ⾣ߋ৽಺༰Λ%#ʹ൓ө ⾣ͭͷϚελʔͷΈ࣋ͯΔ

ϝϦοτ ⾣ࢀর 4&-&$5 ੑೳͷ޲্ ݕࡧॲཧ͕ॏ͍৔߹ εϨʔϒΛࢀরܥʹͯ͠ ෛՙ෼ࢄ

ϝϦοτ ⾣Մ༻ੑͷߴ͍ߏ੒ͷ࣮ݱ Ϛελʔো֐ͷ৔߹ εϨʔϒΛϚελʔʹঢ֨ μ΢ϯλΠϜ୹ॖ

ϝϦοτ ⾣όοΫΞοϓαʔόʔ εϨʔϒͰόοΫΞοϓ ϚελʔʹӨڹͳ͘ όοΫΞοϓऔಘ

ɹϨϓϦέʔγϣϯͷঢ়گ νϡʔχϯάલ͸ िʹҰ౓ Ϛελʔ͕μ΢ϯ͍ͯͨ͠

ɹϨϓϦέʔγϣϯͷঢ়گ Ϛελʔ࠶ىಈͷ౎౓ εϨʔϒͷ ઃఆΛ͠௚͍ͯͨ͠

ɹϨϓϦέʔγϣϯͷঢ়گ Ϛελʔ࠶ىಈͷ౎౓ εϨʔϒͷ ઃఆΛ͠௚͍ͯͨ͠ ͭΒ͍

ɹϨϓϦέʔγϣϯͷঢ়گ ͋·Γʹ΋໘౗Ͱ εϨʔϒΛ

ɹϨϓϦέʔγϣϯͷঢ়گ ͋·Γʹ΋໘౗Ͱ εϨʔϒΛ 4501ʂ

ͭ·Γ ӡ༻͔Β ಀ͍͛ͯͨ

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

๻͕ ΍Γ·͢ʂ

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ఆٛ ɹɾϚελʔଆʹ࡞੒ ɹɾεϨʔϒ͔Β઀ଓ͢Δ ɹɾϨϓϦέʔγϣϯઐ༻Ϣʔβʔ

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣࡞੒ίϚϯυ NZTRM NBTUFS $3&"5&64&3 NZTRM NBTUFS `SFQM`!`<εϨʔϒͷϗετ໊>` NZTRM NBTUFS
*%&/5*'*&%#:1"44803%` `

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ݖݶίϚϯυ NZTRM NBTUFS (3"/53&1-*$"5*0/4-"7& NZTRM NBTUFS 0/ 50
NZTRM NBTUFS `SFQM`!`<εϨʔϒͷϗετ໊>`

ϚελʔͷNZDOG DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999

ϚελʔͷNZDOG εϨʔϒ͔Βݟͨ Ϛελʔͷ*%

ϚελʔͷNZDOG ଟ͗͘͢͠Δͱ ετϨʔδѹഭ

ɹɹϚελʔόΠφϦϩάͷ֬ೝ ⾣όΠφϦϩάͱҐஔ৘ใ ɹɾόΠφϦϩάϑΝΠϧͱ͸ ɹɹɹˠϚελʔ͕DPNNJUͨ͠ཤྺ ɹɾϩʔςʔτ͞ΕΔ ɹɹɹˠݱࡏͷϑΝΠϧ໊Λ֬ೝ ɹɾͲ͜·ͰDPNNJUͰ͖ͯΔ͔ ɹɹɹˠҐஔ৘ใΛ֬ೝ

ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File:
mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999

mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) ϚελʔͰ DPNNJU͕࣮ߦ͞Εͨ ϙδγϣϯ

mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏

⾣εϨʔϒΛ࡞Δࡍͷݪଇ ɹϚελʔΛϩοΫͯ͠EVNQ͠ ɹಉҰͷঢ়ଶͰϦετΞͯ͠ ɹϑΝΠϧɾϙδγϣϯΛ߹ΘͤΔ ࠩ෼ɾൈ͚࿙Εͳ͘ ϨϓϦέʔγϣϯ։࢝ ɹɹϚελʔόΠφϦϩάͷ֬ೝ

ɹɹεϨʔϒͷઃఆ֬ೝ mysql> SHOW SLAVE STATUS\G **************** 1. row **************** Slave_IO_State:
Master_Host: [Ϛελʔͷϗετ໊] Master_User: repl Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 303456264 Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL Master_Server_Id: 0 1 row in set (0.01 sec)

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@*0@4UBUFɿۭจࣈ ɹεϨʔϒͷݱࡏͷεςʔλε ɹϨϓϦέʔγϣϯ్͕੾Ε͍ͯΔͨΊ ɹۭจࣈͱͳ͍ͬͯΔ ࣮ߦ͞Ε͍ͯΕ͹ 8BJUJOHGPSNBTUFSUPTFOEFWFOU

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣.BTUFS@6TFSɿSFQM εϨʔϒ͔ΒϚελʔʹ ઀ଓ͢ΔϢʔβʔ໊ ɹ ϚελʔͰ࡞੒ͨ͠ઐ༻Ϣʔβʔ

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣.BTUFS@-PH@'JMFɿNZTRMCJO εϨʔϒ͕Ϛελʔ͔Β औಘ͢ΔόΠφϦϩά ɹ Քಇ͍ͯ͠ͳ͔ͬͨͷͰ ζϨ͍ͯΔ

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣[email protected]@-PH@1PTɿ εϨʔϒͷ*0εϨου͕ ࠷ޙʹಡΈऔͬͨҐஔ ɹ ͔͜͜ΒϨϓϦέʔγϣϯ࠶։͕ͩ େ͖͘ζϨ͍ͯΔ

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@*0@3VOOJOHɿ/0 εϨʔϒͷ*0εϨου͕ ࣮ߦ͞Ε͍ͯͳ͍ ɹ ϚελʔͷόΠφϦϩά͕ҧ͏ͷͰ ઀ଓͰ͖͍ͯͳ͍

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@42-@3VOOJOHɿ/0 εϨʔϒͷ42-εϨου͕ ࣮ߦ͞Ε͍ͯͳ͍ ɹ εϨʔϒͷ%#͕ ߋ৽͞Ε͍ͯͳ͍

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4FDPOET@#[email protected]ɿ/6-- Ϛελʔʹൺ΂ͯ ஗Ԇ͍ͯ͠Δඵ਺ ɹ ϨϓϦέʔγϣϯ࣮ߦ͞Ε͍ͯͳ͍

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔΛϩοΫ mysql(master)> FLUSH TABLES WITH READ LOCK; Query OK,
0 rows affected (0.00 sec) ⾣όΠφϦϩάͱϙδγϣϯऔಘ mysql(master)> SHOW MASTER STATUS; File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec)

ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔΛϩοΫ mysql(master)> FLUSH TABLES WITH READ LOCK; Query OK,
0 rows affected (0.00 sec) ⾣όΠφϦϩάͱϙδγϣϯऔಘ mysql(master)> SHOW MASTER STATUS; File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏

ɹɹϚελʔͷEVNQΛऔಘ ⾣EVNQऔಘ $ mysqldump -u root -p mamy1326 > gzip
mamy1326.dump.gz ɾผίϯιʔϧ͔Β࣮ࢪ ɹˠRVJU͢ΔͱϩοΫ͕֎Ε·͢ ɾετϨʔδ༰ྔΛߟྀͯ͠H[JQ ɾTDQίϚϯυ౳ͰεϨʔϒʹసૹ

ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔͷϩοΫΛղআ mysql(master)> UNLOCK TABLES; Query OK, 0 rows affected
(0.00 sec)

ɹɹεϨʔϒͷNZDOGઃఆ ϚελʔɺεϨʔϒ શͯ߹Θͤͯ Ұҙͷ*%

ɹɹεϨʔϒͷNZDOGઃఆ όΠφϦϩάઃఆ Ϛελʔʹঢ֨ͨ͠৔߹ εϨʔϒʹ௨஌͢Δ

ɹɹεϨʔϒͷNZDOGઃఆ ༰ྔѹഭ͠ͳ͍Α͏ʹ

ɹɹεϨʔϒͷNZDOGઃఆ εϨʔϒͰ΋ όΠφϦϩάΛग़ྗ͠ Ϛελʔঢ֨Մೳʹ

ɹɹεϨʔϒͷͦͷଞͷ࡞ۀ ⾣NZDOGΛຊ൪ʹ߹ΘͤΔ ⾣NZTRM࠶ىಈ ⾣ϦετΞର৅ͷ ɹɹɹσʔλϕʔε࡟আɾ࠶࡞੒

ɹɹεϨʔϒʹϦετΞ ⾣ϦετΞ࣮ߦ ɾετϨʔδ༰ྔʹ஫ҙʂ ɹɹˠαΠζେ͖ΊͷόΠφϦϩά͕ ɹɹɹɹͲΜͲΜͰ͖ͯ༰ྔΛѹഭ $ zcat mamy1326.dump.gz | mysql
-u root -p mamy1326

ɹɹεϨʔϒʹϦετΞ ⾣ϦετΞ࣮ߦ ɾετϨʔδ༰ྔʹ஫ҙʂ ɹɹˠαΠζେ͖ΊͷόΠφϦϩά͕ ɹɹɹɹͲΜͲΜͰ͖ͯ༰ྔΛѹഭ $ zcat mamy1326.dump.gz | mysql
-u root -p mamy1326 163(&."45&3-0(450 bNZTRMCJOYYYYYY` ద੾ʹύʔδ͠·͠ΐ͏

mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO
-> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒΛࢭΊ·͢

-> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒ͕อ͍࣋ͯͨ͠ ϨϓϦέʔγϣϯҐஔΛ Ϧηοτ͠·͢

-> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ ϚελʔͷόΠφϦϩάɺ ϙδγϣϯΛઃఆ

ɹϨϓϦέʔγϣϯ࣮ߦ ⾣ϨϓϦέʔγϣϯͷ։࢝ mysql> START SLAVE; ઃఆͨ͠ϙδγϣϯ͔Β ಉظ͕࣮ߦ͞Ε·͢

ɹϨϓϦέʔγϣϯ࣮ߦ ⾣ϨϓϦέʔγϣϯͷ֬ೝ mysql> SHOW SLAVE STATUS\G ***************** 1. row *****************
Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@*0@4UBUFɿ 8BJUJOHGPSNBTUFSUPTFOEFWFOU εϨʔϒͷݱࡏͷεςʔλε ϨϓϦέʔγϣϯ͕։࢝͞Ε Ϛελʔ͔ΒͷΠϕϯτ଴ͪ

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣.BTUFS@-PH@'JMFɿ NZTRMCJO ϚελʔͷόΠφϦϩάϑΝΠϧ ݱࡏϚελͰߋ৽͞Ε͍ͯΔ ϑΝΠϧ໊ʹͳ͍ͬͯΔ

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣[email protected]@-PH@1PTɿ ϚελʔͷόΠφϦϩά͔Β *0εϨου͕ಡΈऔͬͨҐஔ ˞42-࣮ߦ͞Εͨͱ͸ݶΒͳ͍

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣&[email protected]@-PH@1PTɿ *0εϨου͕ಡΈࠐΜͩϩά͔Β 42-εϨου͕ ࣮ߦͨ͠ΫΤϦͷҐஔ

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4FDPOET@#[email protected]ɿ εϨʔϒ͕Ϛελʔʹൺ΂ ஗Ԇ͍ͯ͠Δඵ਺

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@*0@3VOOJOHɿ:&4 ϚελʔͷόΠφϦϩάΛ औಘͰ͖͍ͯΔ

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@42-@3VOOJOHɿ:&4 औಘͨ͠όΠφϦϩάͷ 42-࣮ߦͰ͖͍ͯΔ

ɹ࣮ߦঢ়گΛ؂ࢹ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos:
878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 ⾣࣮ߦ௚ޙ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos: 879066686 Exec_Master_Log_Pos: 879066686 Seconds_Behind_Master: 0 ⾣ಉظ׬ྃ

ॠؒɺ৺ɺॏͶͯ

γϯΫϩ཰ ˋ

݁Ռ ࢲ Ϛελʔ ͕ མͪͯ΋

݁Ռ ୅ΘΓ ϨϓϦΧ ͕͋Δ΋ͷʂ

ɹϨϓϦέʔγϣϯ·ͱΊ ⾣ෳ੡࡞੒ɾӡ༻׬ྃʂ ⾣ࢀরܥͷαʔόʔ४උ׬ྃʂ ⾣όοΫΞοϓαʔόʔ࡞੒׬ྃ

σʔλ͸ ෳ੡໋͕

Մ༻ੑΛߴΊ γεςϜΛ αʔϏεαʔϏε͎ ͍͖ͯ͠·͠ΐ͏ʂ

ৼΓฦΓ

શମͷৼΓฦΓ ⾣NZDOGઃఆ ຊདྷͷύϑΥʔϚϯεΛಘͨ ⾣ϨϓϦέʔγϣϯ࣮ࢪ ৑௕Խͷ४උ͕Ͱ͖ͨ

՝୊ ⾣ܧଓ؂ࢹͷ࢓૊Έ ֤छγΣϧˠ.BDLFSFMͱ͔ ⾣ࢀরܥΛεϨʔϒʹ εϨʔϒ࡞੒ˠϓϩάϥϜվम ⾣ෆཁϨίʔυͷ࡟আ ϩάܥ ઃܭˠୀආˠ࡟আ

ӡ༻ɾҡ࣋͸ େࣄͩΑ

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

ɹ.Z42-ා͍ʜʜʜ ⾣ࠓ·Ͱ͸ʜ ਖ਼௚ܟԕͯ͠·ͨ͠ ࠇ͍ը໘ා͔ͬͨͰ͢

ɹ.Z42-ා͍ʜʜʁ ⾣ॳΊͯʜ ઃఆΛ৮Γ·ͨ͠ NZDOG͕ා͘ͳ͘ͳͬͨ

ɹ.Z42-ා͍ʜʁʁ ⾣ઃఆޙʜ ಓ۩ͷಈ͖Λ஌Γ ઃܭͷ෯͕޿͕Γ τϥϒϧͰ΋৺ʹ༨༟͕

͍ͭͷ·ʹ͔ ා͘ͳ͘ͳͬͨ

ɹ਌͠Ή·Ͱͷϓϩηε ⾣·ͣઃఆΛ஌Δ ⾣൓өखॱΛߟ࣮͑ߦ ⾣࣮ࡍͷޮՌΛଌఆ ⾣ઃܭɾ࣮૷ͷߟྀ͕ਂ·Δ ⾣ଞͷίϚϯυૢ࡞ʹ׳Ε͍ͯ͘

਌͠Ήͱ

ӡ༻ҡ࣋ͷ ຊ౰ͷҙຯΛ஌Δ

ɹӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣࣮͸ʜ ΞϓϦέʔγϣϯΑΓ Πϯϑϥʢσʔλʣͷํ͕ ण໋͕௕͍

ɹӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ण໋͕௕͍ͱʜ Πϯϑϥͷӡ༻ҡ࣋ʹ͸ Ұఆͷίετ͕ ͔͔Γଓ͚Δ

͔͠͠ӡ༻͸

ಓΛ੾Γ୓͘ ਓ͕͍Δ͔Βͦ͜ ͋Δͱݴ͑Δ

ٯʹɾɾɾ

ӡ༻อक͕ ͋Δ͔Βͦ͜

ಓ͸ ੾Γ୓͔ΕΔ

૬ޓʹײँ ͜Ε͕νʔϜ

ɹҡ࣋͢ΔਓͷଘࡏΛ஌Δ ͋ͳ͕ͨ࡞ͬͨ ΞϓϦέʔγϣϯ ඞͣ୭͔͕ ҡ࣋ͯ͠·͢

Ͱ͸ ҡ࣋ͱ͸Կ͔

Ϗοάόϯ

খӉ஦೩ম

Ի଎͔Βޫ଎

ʜ ʁʁʁʁʁ

ޫ଎ͷ·· ਐΜͰ͍͘

͜Ε͕ ҡ࣋ɾӡ༻อक

ɹҡ࣋ͬͯେࣄʂ ⾣͍͢͝଎͞ͰಓΛ୓͚͹ ⾣ಓΛ੔උ͍ͯ͘͠ਓ͕͍Δ ⾣੒௕ͱҡ࣋Λࢧ͑Δਓ͕͍Δ ⾣ͦ͏ͯ͠ੈք͕Ͱ͖͕͋Δ

ͭ·Γ

ΈΜͳ νʔϜͰ͢ʂ

ͦͯ͠ɾɾɾ

ɹσʔλϕʔε͸ා͘ͳ͍ʂ ⾣ಈ͖Λ஌Δ ⾣࢖͍ํɾઃఆΛ஌Δ ⾣σʔλϕʔεΛ஌Δ

ɹσʔλϕʔε͸ා͘ͳ͍ʂ ⾣ಈ͖Λ஌Δ ⾣࢖͍ํɾઃఆΛ஌Δ ⾣σʔλϕʔεΛ஌Δ ා͘ͳ͍ʂ

΍Ζ͏ͱ ࢥ͍͚ͬͯͨͲʜ

ͦΜͳਓʹ ʮ΍ͬͯΈΑ͏ʯΛ ಧ͚͍ͨ

ΞϓϦΤϯδχΞ͔ͩΒͬͯ ΠϯϑϥΛ ଞਓ೚ͤʹ͠ͳ͍

ΞϓϦΤϯδχΞ͔ͩΒͬͯ ϑϩϯτΤϯυΛ ଞਓ೚ͤʹ͠ͳ͍

ੈքΛ޿͛Δ͜ͱ͸ ΤϯδχΞਓੜͷ޿͕Γ Λҙຯ͢Δ

஌Βͳ͍ΑΓ΋ ஌Δ΄͏ָ͕͍͠ʂ

ੈքΛ޿͛ͯ ͜Ε͔Β΋ ָ͍͠ΤϯδχΞϥΠϑΛʂ

͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ

͕࣌ؒ ༨ͬͨΒ ࿩͢Α

.Z42- ઃఆྫ

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server
= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4

= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ໌֬ʹ ΤϥʔϩάͰ͋Δ͜ͱΛ ࢦఆ͢Δ

= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCollation collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͜͜Ͱઃఆ͍ͯ͠Ε͹ %#ɺςʔϒϧɺΧϥϜͰ ࢦఆ͢Δඞཁ͕ͳ͍

= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 $IBSBTFUಉ༷ ͜͜Ͱઃఆ͓ͯ͘͠

= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͨͩ͠ $)"3"$5&34&5͢Δͱ ແࢹ͞ΕΔͷͰ஫ҙ

= utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͜͜Ͱઃఆ͍ͯ͠Ε͹ ௨৴࣌ʹ΋ จࣈԽ͚Λ๷ࢭͰ͖Δ

ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON #
slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0

slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 OPJOEFYͷΫΤϦΛ ϩάʹग़͢

slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 OPJOEFYͷΫΤϦΛ ϩάʹग़ִؒ͢ʢ෼ʣ

slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 4&-&$5ͷ ߦ਺ࢦఆ

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use —explicit_defaults_for_timestamp server option (see documentation for more details).

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use —explicit_defaults_for_timestamp server option (see documentation for more details). ͍ΘΏΔθϩλΠϜελϯϓ ‘0000-00-00 00:00:00’

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ྫɿUJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use —explicit_defaults_for_timestamp server option (see documentation for more details). ͍ΘΏΔθϩλΠϜελϯϓ ‘0000-00-00 00:00:00’ ҉໧తͳଐੑ෇༩Ͱ ҙਤ͠ͳ͍஋ʹͳΔ

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ݟͨ͜ͱ͋Γ·ͤΜ͔ʁ mysql> SELECT cuntomer_name -> FROM customers -> WHERE
create_at != ‘0000-00-00 00:00:00’ -> AND create_at < NOW();

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ݟͨ͜ͱ͋Γ·ͤΜ͔ʁ ҙਤ͠ͳ͍ͱ ΍Ήͳ͘ରԠ͕૿͑Δ mysql> SELECT cuntomer_name -> FROM customers
-> WHERE create_at != ‘0000-00-00 00:00:00’ -> AND create_at < NOW();

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣΍Ήͳ͘ରԠ ɹ΍Ήͳ͘ϓϩάϥϜଆͰରԠ ɹ΍Ήͳ͘8)&3&۟ͰରԠ

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣΍Ήͳ͘ରԠ ɹ΍Ήͳ͘ϓϩάϥϜଆͰରԠ ɹ΍Ήͳ͘8)&3&۟ͰରԠ σϑΥϧτΛআ֎ͯ͠ ద੾ͳσʔλอ࣋ͷઃܭΛ

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ [mysqld] # timestampܕͷ҉໧తͳσϑΥϧτ஋ explicit_defaults_for_timestamp = ON ⾣ઃఆ ⾣֬ೝ mysql>
show variables like 'explicit_defaults_for_timestamp'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON | +---------------------------------+-------+ 1 row in set (0.00 sec)

ɹJOOPECؔ࿈ [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M' ⾣*OOP%#ͷ಺෦σʔλอ࣋ྖҬ ɹˠͰഇࢭ͞Ε·ͨ͠ *OOP%#ಠࣗͷϝϞϦΞϩέʔλ ͔Β 04ͷϝϞϦΞϩέʔλ΁

ɹJOOPECؔ࿈ ⾣*OOP%#ͷ಺෦σʔλอ࣋ྖҬ ɹˠͰഇࢭ͞Ε·ͨ͠ *OOP%#ಠࣗͷϝϞϦΞϩέʔλ ͔Β 04ͷϝϞϦΞϩέʔλ΁ [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M'

first_mysql_performance_tuning_at_builderscon2017

first_mysql_performance_tuning_at_builderscon2017

More Decks by mamy1326

Other Decks in Technology

Featured

Transcript