Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Recommendation for using your own tools
Search
Kenichi Masuda
April 19, 2012
Technology
1
5.6k
Recommendation for using your own tools
This materials for #mysqlcasual 3
Kenichi Masuda
April 19, 2012
Tweet
Share
More Decks by Kenichi Masuda
See All by Kenichi Masuda
2022-09-05 「明日から新規事業を作ってみよう(実践編)」トヨタ車体株式会社様ウェビナー
masudak
0
690
2022-08-10「明日から新規事業を作ってみよう」トヨタ車体株式会社様向けウェビナー
masudak
1
520
2022-09-05 「明日から新規事業を作ってみよう(実践編)」トヨタ車体株式会社様ウェビナー
masudak
0
24
2022-06-09「新規事業担当に必要な要素とは」ヤマトシステム開発株式会社様向けウェビナー
masudak
0
770
Development/QA environments and the Go tools in it
masudak
0
7.1k
How to utilize GKE for QA environment
masudak
3
1.3k
Introduction of mercari SET team
masudak
0
13k
Other Decks in Technology
See All in Technology
5min GuardDuty Extended Threat Detection EKS
takakuni
0
180
生成AIで小説を書くためにプロンプトの制約や原則について学ぶ / prompt-engineering-for-ai-fiction
nwiizo
6
3.8k
CursorによるPMO業務の代替 / Automating PMO Tasks with Cursor
motoyoshi_kakaku
2
840
AWS Summit Japan 2025 Community Stage - App workflow automation by AWS Step Functions
matsuihidetoshi
1
310
Model Mondays S2E03: SLMs & Reasoning
nitya
0
240
解析の定理証明実践@Lean 4
dec9ue
1
210
rubygem開発で鍛える設計力
joker1007
3
310
「Chatwork」の認証基盤の移行とログ活用によるプロダクト改善
kubell_hr
1
240
低レイヤを知りたいPHPerのためのCコンパイラ作成入門 完全版 / Building a C Compiler for PHPers Who Want to Dive into Low-Level Programming - Expanded
tomzoh
4
3.4k
LangChain Interrupt & LangChain Ambassadors meetingレポート
os1ma
2
250
Tech-Verse 2025 Global CTO Session
lycorptech_jp
PRO
0
1.3k
SmartNewsにおける 1000+ノード規模 K8s基盤 でのコスト最適化 – Spot・Gravitonの大規模導入への挑戦
vsanna2
0
100
Featured
See All Featured
Fireside Chat
paigeccino
37
3.5k
Agile that works and the tools we love
rasmusluckow
329
21k
Embracing the Ebb and Flow
colly
86
4.7k
The Invisible Side of Design
smashingmag
300
51k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
194
16k
[RailsConf 2023] Rails as a piece of cake
palkan
55
5.6k
Making the Leap to Tech Lead
cromwellryan
134
9.4k
A designer walks into a library…
pauljervisheath
207
24k
Into the Great Unknown - MozCon
thekraken
39
1.9k
YesSQL, Process and Tooling at Scale
rocio
173
14k
Designing for humans not robots
tammielis
253
25k
Designing for Performance
lara
609
69k
Transcript
ಓ۩Λຏ͘͜ͱͷεεϝ 2012/04/19 #mysqlcasual 3 @masudaK 20124݄20༵ۚ
ࣗݾհ • @masudaK • ͷձࣾͰOperation Engineer • େنେ͖ • χʔϋΠ͖
• ৄ͘͠ #૿ాνϟϥ͍ 20124݄20༵ۚ
Ͱɺຊ 20124݄20༵ۚ
ಓ۩ʹΘ͞Εͯ ͍͚ͳ͍ 20124݄20༵ۚ
ຊ࣭௫Ή 20124݄20༵ۚ
ͦͷͱ͓ΓͰ͟͝Δ 20124݄20༵ۚ
ବ՛ࢠ՛ࢠʂʂ 20124݄20༵ۚ
Α͋͘Δޫܠ 20124݄20༵ۚ
Q) ͦͷઃఆ ࡌϝϞϦ͑ͯͳ͍ʁ 20124݄20༵ۚ
A) όοϑΝʹ ࡌͬͨΒऴΘΓ·͢ (ΩϦο 20124݄20༵ۚ
Q) όοϑΝ ͲΕ͘Β͍ΘΕͯΔʁ 20124݄20༵ۚ
A) ͔Γ·ͤΜ (ΩϦο 20124݄20༵ۚ
Q) n࣌n͔Βn·ͰΔ ΫΤϦੳ͠ͱ͍ͯʔ 20124݄20༵ۚ
A) ͑ͬ(ΧδϡΞϧ෩ʹ 20124݄20༵ۚ
ࣄલʹ͋Δ ศརͳͷ͓͏ͣ ʢΧδϡΞϧʹʣ 20124݄20༵ۚ
ϛεݮΒͦ͏ͣ ʢΧδϡΞϧʹʣ 20124݄20༵ۚ
ࠓ͔ΒՄೳͳݶΓ ͝հ ʢΧδϡΞϧʹʣ 20124݄20༵ۚ
ઃఆฤ 20124݄20༵ۚ
1. mymemcheck 20124݄20༵ۚ
•࠷େ༻ϝϞϦݟੵΓΛ ͝ఏग़ •http://dsas.blog.klab.org/ archives/50860867.html 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
• ͜Μͳײ͡ʢৄ͘͠Σϒ(ry • process heap = • innodb_buffer_pool + key_buffer
+ • max_connections * (sort_buffer + read_buffer + read_rnd_buffer) + max_connections * stack_size 20124݄20༵ۚ
2GϚγϯͰେྔઃఆͯ͠Έͨ 20124݄20༵ۚ
2G > 7.940 [G] ... LIMIT OVER!! 20124݄20༵ۚ
2. MySQLTuner 20124݄20༵ۚ
•https://github.com/ rackerhacker/MySQLTuner- perl •Performance Metrics͍ͬͯ͏ ͷ͕͋Δ 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
• ͜Μͳ͜ͱڭ͑ͯ͘ΕΔ • query_cache_size (>= 8M) • thread_cache_size (start at
4) • innodb_buffer_pool_size (>= 807M) • ͱ͔ͱ͔ 20124݄20༵ۚ
ோΊͯղੳฤ 20124݄20༵ۚ
• EXPLAIN • EXPLAIN • ADD INDEX • FORCE INDEX
• ΫΤϦվળ͝ఏҊ • جຊதͷجຊ 20124݄20༵ۚ
3. tcpdump͔Βͷʔ 20124݄20༵ۚ
4. pt-query-digest 20124݄20༵ۚ
@mikeda͞Μ͕ հͯ͘͠ΕΔͣ 20124݄20༵ۚ
5. PROFILING 20124݄20༵ۚ
• SET PROFILE • ͦͷ࣮ߦεϨουʹͷΈ༗ޮ • Ͳ͜ʹ͔͔࣌ؒͬͯΔ͔ͱ͔ • ύοͱݟͰ͔ΓͮΒ͍࣌ศར 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
mysql> SET profiling=1; mysql> దͳΫΤϦൃߦ 20124݄20༵ۚ
mysql> SHOW PROFILE; +--------------------+----------+ | Status | Duration | +--------------------+----------+
| starting | 0.000087 | | Opening tables | 0.000063 | | System lock | 0.000009 | | init | 0.000009 | | optimizing | 0.000004 | | statistics | 0.000009 | | preparing | 0.000010 | | executing | 0.002878 | | Sending data | 0.000292 | | end | 0.000010 | | query end | 0.000003 | | closing tables | 0.000002 | | removing tmp table | 0.000008 | | closing tables | 0.000003 | | freeing items | 0.003564 | | logging slow query | 0.000009 | | cleaning up | 0.000003 | 20124݄20༵ۚ
mysql> SHOW PROFILE SOURCE; +--------------------+----------+-----------------------+---------------+-------------+ | Status | Duration |
Source_function | Source_file | Source_line | +--------------------+----------+-----------------------+---------------+-------------+ | starting | 0.000087 | NULL | NULL | NULL | | Opening tables | 0.000063 | open_tables | sql_base.cc | 4837 | | System lock | 0.000009 | mysql_lock_tables | lock.cc | 299 | | init | 0.000009 | mysql_select | sql_select.cc | 2554 | | optimizing | 0.000004 | optimize | sql_select.cc | 863 | | statistics | 0.000009 | optimize | sql_select.cc | 1054 | | preparing | 0.000010 | optimize | sql_select.cc | 1076 | | executing | 0.002878 | exec | sql_select.cc | 1823 | | Sending data | 0.000292 | exec | sql_select.cc | 2365 | | end | 0.000010 | mysql_select | sql_select.cc | 2590 | StatusΛ֨ೲͯ͠ΔՕॴͷߦ·Ͱग़ͯ͘͠ΕΔ ͜͜·ͰඞཁͳΫΤϦ΄ͱΜͲͳ͍ͱࢥ(ry 20124݄20༵ۚ
6. MySlowTranCapture 20124݄20༵ۚ
• ୯ൃΫΤϦૣ͍ͷʹɺτϥϯβΫ γϣϯͩͱ͍ͷͱ͔ݕͯ͘͠Ε Δ • https://github.com/yoshinorim/ MySlowTranCapture 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
দ৴ຊߪೖ͢Δ͔͠ͳ͍ɻ 20124݄20༵ۚ
ָ͍ͨ͠ฤ 20124݄20༵ۚ
7. TPC-C 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
ΧδϡΞϧා͍ 20124݄20༵ۚ
• σʔλ࡞ΔͷΊΜͲ͍ • tpcc_load localhost tpcc1000 root "" 100 •
./tpcc_start -h localhost -d tpcc1000 -u root -w 10 - c 2 -r 10 -l 30 • Sͷਓ͚ɻhͬͱϋʔυɻ 20124݄20༵ۚ
8. Facebook, online schema change tool 20124݄20༵ۚ
ͬͯΔਓʔ ϊ 20124݄20༵ۚ
• http://www.facebook.com/notes/mysql-at- facebook/online-schema-change-for-mysql/ 430801045932 • ୭͔ϓϩμΫτͰ(ry 20124݄20༵ۚ
ใऩूฤ 20124݄20༵ۚ
9. MySQL Performance BlogΛѪಡ 20124݄20༵ۚ
Ѫಡͯ͠Δਓʔ ϊ 20124݄20༵ۚ
20124݄20༵ۚ
γϡϫϧπͷসإ ͍ 20124݄20༵ۚ
10. Baron Schwartz ͯ͠ΈΔ 20124݄20༵ۚ
20124݄20༵ۚ
11. ΧϯϑΝϨϯε ࢿྉΛړΔ 20124݄20༵ۚ
20124݄20༵ۚ
20124݄20༵ۚ
ڳ 20124݄20༵ۚ
·ͩ·ͩ৭ʑ 20124݄20༵ۚ
• ಓ۩͕શͯͰͳ͍ • ͚Ͳɺղܾͷࢳޱͱͯ͠ • ϓϩμΫτͰ͑Δͷ(ry • ΞτϓοτΛੋඇ ऴΘΓʹ 20124݄20༵ۚ
࠷ޙʹ 20124݄20༵ۚ
αΠόʔΤʔδΣϯτ Ұॹʹಇ͍ͯ͘ΕΔਓ ืूத 20124݄20༵ۚ
͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠ 20124݄20༵ۚ