mysql_first_performance_ tuning_y8

ॳΊͯͷ .Z42-αʔόʔ ύϑΥʔϚϯε νϡʔχϯά ·Έ΍ͳ͓͖ !NBNZ .BZ!Z4QSJOHJO4IJCVZB

ࣗݾ঺հ Name ɹɹ ɿ·Έ΍ͳ͓͖ Twitterɹɹ ɿ@mamy1326 2016೥·ͰΞϓϦத৺ 2017೥͔Βຊ֨తʹ ɹɹɹɹɹɹɹDBɾΠϯϑϥ΁

ϫλγ Πϯϑϥ νϣοτσΩϧ

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

ߏ੒ ⾣NZDOG ɹશ߲໨ͷௐࠪɾઃఆ ⾣ΫΤϦΩϟογϡͷଌఆ ⾣ϨϓϦέʔγϣϯ

ϓϩϩʔά

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

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

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

ঢ়گ̍ νϡʔχϯά͞Ε͍ͯͳ͍ .Z42- ઐ༻αʔόʔ

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

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

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

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

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

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

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⊠

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

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

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

ΫΤϦΩϟογϡ ଌఆ

ଌఆ NZDOGઃఆ͔ͯ͠Βिؒ ΫΤϦΩϟογϡ͕ Ͳ͏ͳͬͨͷ͔

΍Δ͜ͱ ΫΤϦΩϟογϡΛద੾ʹ࣋ͨͤ ݁ՌΛͳΔ΂͘Ωϟογϡ͔Βฦ͢Α͏ νϡʔχϯάΛߦͬͨ ݁ՌΛݕূ ਺஋Ͱݕূ

ΫΤϦΩϟογϡͱ͸ %#Λ4&-&$5ͨ݁͠ՌΛ ϝϞϦʹΩϟογϡ͠ ࣍ʹಉҰͷΫΤϦͷ৔߹ Ωϟογϡ͔Β݁ՌΛฦ͢

ɹઃఆ಺༰ [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⊠

ઃఆ஋ͷ֬ೝ NZTRM4)087"3*"#-&4-*,&bRVFSZ@DBDIF +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ |
have_query_cache | YES | | query_cache_limit | 16777216 | | query_cache_min_res_unit | 4096 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+

ઃఆ஋ͷղઆ ⾣IBWF@RVFSZ@DBDIFɿ:&4 ɹΫΤϦΩϟογϡΛ ɹ࢖༻Մೳ͔Ͳ͏͔

ઃఆ஋ͷղઆ ⾣RVFSZ@DBDIF@MJNJUɿ ɹͭͷRVFSZͷ࣮ߦ݁Ռ ɹɹͱͯ͠ ɹDBDIF͢Δ࠷େͷαΠζ

ઃఆ஋ͷղઆ ⾣RVFSZ@DBDIF@TJ[Fɿ ɹΫΤϦΩϟογϡͷ ɹ૯ྖҬ

ઃఆ஋ͷղઆ ⾣RVFSZ@DBDIF@UZQFɿ0/PS ɹ4&-&$542-@/0@$"$)& ɹͰ࢝·ΔΫΤϦҎ֎Ωϟογϡ Ωϟογϡ͔Β ໌ࣔతʹআ֎Ͱ͖Δ

ɹ൓өঢ়ଶͷݕূ NZTRM4)084&44*0/45"564-*,&b2DBDIF +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ |
Qcache_free_blocks | 791 | | Qcache_free_memory | 533156064 | | Qcache_hits | 1442086 | | Qcache_inserts | 291072 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3599 | | Qcache_queries_in_cache | 1760 | | Qcache_total_blocks | 4361 | +-------------------------+-----------+

൓ө஋ͷղઆ ⾣2DBDIF@IJUTɿ ɹΫΤϦʔ݁Ռ͕ ɹΫΤϦʔΩϟογϡ͔Βฦ͞Εͨ਺ ɹͭ·ΓΩϟογϡώοτ਺ Ωϟογϡʹ͋ΔΫΤϦʔ͕ ࢖ΘΕΔͨͼʹΠϯΫϦϝϯτ

൓ө஋ͷղઆ ⾣2DBDIF@JOTFSUTɿ ɹΫΤϦʔΩϟογϡʹ௥Ճ͞Εͨ ɹΫΤϦʔͷ਺

൓ө஋ͷղઆ ⾣2DBDIF@OPU@DBDIFEɿ ɹΩϟογϡͰ͖ͳ͍͔ ɹRVFSZ@DBDIF@UZQFઃఆͷͨΊ ɹΩϟογϡ͞Εͳ͔ͬͨ਺ ϝϞϦෆ଍ͳͲͰΩϟογϡ͞Εͳ͍ ৔߹΋͋ΔͷͰ஫ҙ

ɹ݁Ռ ΫΤϦΩϟογϡIJU཰

ώοτ཰ͷܭࢉ શΫΤϦʔ ͷ਺Ͱ ΫΤϦΩϟογϡώοτ਺Λ আࢉ͢Δ

ώοτ཰ͷܭࢉ શΫΤϦʔ Ωϟογϡ͔Βฦͨ͠਺ ʴ Ωϟογϡ͔Βฦͤͳ͔ͬͨ਺

ώοτ཰ͷܭࢉ Ωϟογϡ͞ΕΔͱ 2DBDIF@JOTFSUT͕ Χ΢ϯτΞοϓ͞ΕΔ Ωϟογϡʹ ৽نొ࿥ͨ͠஋

ώοτ཰ͷܭࢉ Ωϟογϡʹొ࿥Ͱ͖ͳ͍ͱ 2DBDIF@OPU@DBDIFE͕ Χ΢ϯτΞοϓ͞ΕΔ Ωϟογϡ͔Β ฦͤͳ͔ͬͨ஋

ώοτ཰ͷܭࢉ ⾣2DBDIF@IJUTɿ ⾣2DBDIF@JOTFSUTɿ ⾣2DBDIF@OPU@DBDIFEɿ
ʜ

ɹͨͩ͠ɾɾɾ Ұൠతʹ ΛԼճͬͨΒ ͍Ζ͍Ζݟ௚ͨ͠ํ͕͍͍

஫ҙ఺ ʲલఏʳ ΫΤϦΩϟογϡ͸ 4&-&$5ʢݕࡧʣ

஫ҙ఺ ॻ͖ࠐΈ͕ଟൃ͢Δ ςʔϒϧʹ͸޲͔ͳ͍ Ωϟογϡ͕ॻ͖׵ΘΔ

஫ҙ఺ ΫΤϦΩϟογϡΛ΍Ί ϓϩάϥϜଆͰΩϟογϡ NFNDBDIFEͳͲ

ɹΫΤϦΩϟογϡ·ͱΊ ⾣ϝϞϦʹԠͯ͡ద੾ʹઃఆʂ ⾣IJU཰Λܧଓͯ͠ܭଌʂ ⾣αʔϏεͷੑ࣭ΛݟۃΊΔʂ ⾣ܿ͘ϓϩάϥϜʹ೚ͤΔʂ

ϨϓϦέʔγϣϯ ࣮ࢪ

͜͜·ͰͰɾɾɾ ɾӡ༻͕҆ఆ ɾΫΤϦΩϟογϡ ɹɹɹώοτ཰΋҆ఆ

͔͠͠

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

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

ෳ੡ ϨϓϦέʔγϣϯ Λ࣮ࢪ͠·͢

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

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

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

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

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

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

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

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

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

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣࡞੒ίϚϯυ 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 [mysqld] # εϨʔϒʹ௨஌͢ΔόΠφϦϩά(࣮ࡍ͸φόϦϯά͞ΕΔ) log-bin=mysql-bin # ϨϓϦέʔγϣϯ࣌ͷɺαʔόʔϢχʔΫID server-id=1001 # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺
set-variable=expire_logs_days=3

[mysqld] # εϨʔϒʹ௨஌͢ΔόΠφϦϩά(࣮ࡍ͸φόϦϯά͞ΕΔ) log-bin=mysql-bin # ϨϓϦέʔγϣϯ࣌ͷɺαʔόʔϢχʔΫID server-id=1001 # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺ set-variable=expire_logs_days=3
Ϛελʔͷ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)

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ઃఆ [mysqld] # εϨʔϒͷαʔόʔID server-id=1002 # Ϛελʔͷঢ֨Λߟྀ͠ɺϩΪϯάΛ༗ޮʹ͢Δ log-bin=mysql-bin # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺
set-variable=expire_logs_days=3 # όΠφϦϩάͷग़ྗઃఆ log_slave_updates

[mysqld] # εϨʔϒͷαʔόʔID server-id=1002 # Ϛελʔͷঢ֨Λߟྀ͠ɺϩΪϯάΛ༗ޮʹ͢Δ log-bin=mysql-bin # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺ set-variable=expire_logs_days=3
# όΠφϦϩάͷग़ྗઃఆ log_slave_updates ɹɹεϨʔϒͷNZDOGઃఆ ϚελʔɺεϨʔϒ શͯ߹Θͤͯ Ұҙͷ*%

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

# όΠφϦϩάͷग़ྗઃఆ log_slave_updates ɹɹεϨʔϒͷNZDOGઃఆ ༰ྔѹഭ͠ͳ͍Α͏ʹ

# όΠφϦϩάͷग़ྗઃఆ log_slave_updates ɹɹεϨʔϒͷ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;

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:
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ઃఆ ຊདྷͷύϑΥʔϚϯεΛಘͨ ⾣ΫΤϦΩϟογϡଌఆ ܧଓ؂ࢹͷେࣄ͞Λ஌ͬͨ ⾣ϨϓϦέʔγϣϯ࣮ࢪ ৑௕Խͷ४උ͕Ͱ͖ͨ

ࠓޙͷ՝୊ ⾣ܧଓ؂ࢹͷ࢓૊Έ ֤छγΣϧͰରԠத ⾣ࢀরܥΛεϨʔϒʹ ϓϩάϥϜվमઃܭத ⾣ෆཁϨίʔυͷ࡟আ ϩάܥ ஈ֊Λ౿ΜͰ࡟আܭըத

Τϐϩʔά

ઃఆ͔ͨ͠Β ऴΘΓͰ͸ͳ͍

Πϯϑϥ͸ ϥΠϑαΠΫϧ͕௕͍

೔ʑͷ؂ࢹɾܭଌ͔Β ઌखΛଧͭʂ

ܧଓ͸ྗͳΓ

ࠓ೔Ұ൪ ఻͔͑ͨͬͨ͜ͱ

Πϯϑϥʹಓʹೖͬͨ͹͔Γ ਖ਼௚ܟԕͯ͠·ͨ͠

ͻΐΜͳ͜ͱ͔Βೖͬͨಓ͕ ࠓ͸ָͯ͘͠࢓ํ͕ͳ͍ʂ

΄ͱΜͲ1)1ॻ͍ͯͳ͍͠ ॻ͍ͯ΋γΣϧ

WJN͔͠࢖Θͳ͍ͷͰ ຖ೔ࠇ͍ը໘

͔͠͠ʂ

ਂ͘ߟ͑ ͭͣͭௐ΂ͯ ൓ө͢Δ

ܭଌͯ݁͠ՌΛௐ΂ ࣍ͷखΛଧͭ

࡞ۀϩάΛ ϚʔΫμ΢ϯͰશͯه࿥

ΤϯτϦʔʹ͢Δͱ͖ શ෦ௐ΂௚͢

ϩʔΧϧʹ؀ڥߏஙΛ ΧδϡΞϧʹ͍ͯ͠Δ

Ξ΢τϓοτ ָ͍͠ʂʂʂʂ

ָ͍͠ʂʂʂʂ

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

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

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

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

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

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

͓·͚ ⾣ݩهࣄͷ͝঺հ ɾ.Z42-ύϑΥʔϚϯενϡʔχϯά ɹɹNZDOGͷݟ௚͠ ɹIUUQRJJUBDPNNBNZJUFNTDFBFFDD⒎B ɾ.Z42-ύϑΥʔϚϯενϡʔχϯά ɹɹΫΤϦΩϟογϡద༻ঢ়گͷ֬ೝ ɹIUUQRJJUBDPNNBNZJUFNTEEDGB ɾ.Z42-ϨϓϦέʔγϣϯઃఆ ɹɹखॱɾ֤छεςʔλεͷৄࡉͱτϥϒϧγϡʔςΟϯά
ɹIUUQRJJUBDPNNBNZJUFNTBFEGBCGE

mysql_first_performance_ tuning_y8

mysql_first_performance_ tuning_y8

More Decks by mamy1326

Other Decks in Programming

Featured

Transcript