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
20250625 Snowflake Summit 2025活用事例 レポート / Nowcast Snowflake Summit 2025 Case Study Report
kkuv
1
310
Oracle Cloud Infrastructure:2025年6月度サービス・アップデート
oracle4engineer
PRO
2
240
GeminiとNotebookLMによる金融実務の業務革新
abenben
0
230
2025-06-26_Lightning_Talk_for_Lightning_Talks
_hashimo2
2
100
Fabric + Databricks 2025.6 の最新情報ピックアップ
ryomaru0825
1
140
AWS CDK 実践的アプローチ N選 / aws-cdk-practical-approaches
gotok365
6
740
Agentic Workflowという選択肢を考える
tkikuchi1002
1
500
_第3回__AIxIoTビジネス共創ラボ紹介資料_20250617.pdf
iotcomjpadmin
0
150
AWS アーキテクチャ作図入門/aws-architecture-diagram-101
ma2shita
29
11k
GitHub Copilot の概要
tomokusaba
1
130
地図も、未来も、オープンに。 〜OSGeo.JPとFOSS4Gのご紹介〜
wata909
0
110
強化されたAmazon Location Serviceによる新機能と開発者体験
dayjournal
2
210
Featured
See All Featured
Java REST API Framework Comparison - PWX 2021
mraible
31
8.6k
The Invisible Side of Design
smashingmag
299
51k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
130
19k
Agile that works and the tools we love
rasmusluckow
329
21k
GitHub's CSS Performance
jonrohan
1031
460k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
657
60k
A designer walks into a library…
pauljervisheath
207
24k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
Docker and Python
trallard
44
3.4k
GraphQLとの向き合い方2022年版
quramy
48
14k
Testing 201, or: Great Expectations
jmmastey
42
7.5k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
331
22k
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༵ۚ