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.5k
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
470
2022-08-10「明日から新規事業を作ってみよう」トヨタ車体株式会社様向けウェビナー
masudak
0
300
2022-06-09「新規事業担当に必要な要素とは」ヤマトシステム開発株式会社様向けウェビナー
masudak
0
540
Development/QA environments and the Go tools in it
masudak
0
6.2k
How to utilize GKE for QA environment
masudak
3
1k
Introduction of mercari SET team
masudak
0
12k
Other Decks in Technology
See All in Technology
TypeScript Quiz (Encraft #12 Frontend Quiz Night)
uhyo
2
340
なんで私に登壇依頼が?! ~頼られるエンジニアになるためには~ /
mixi_engineers
PRO
2
200
既存プロセスからの脱却と変化に適応するために必要なこと
cybozuinsideout
PRO
2
170
データ品質をコード化! LINEヤフーのMLOpsを最適化する "ACP Data Quality" の紹介
lycorptech_jp
PRO
2
150
HoneycombとOpenTelemetryでオブザーバビリティに入門してみる
sumiren
2
150
オブジェクト指向宗教史
tanakahisateru
13
12k
中央集権体制からDataOpsへの転換 / centralized-to-dataops-transformation
pei0804
7
1.4k
エンジニアブランディングチームの KPI / KPI's of engineer branding team
chaspy
1
140
長文から長文を生成するLLMツールをオープンソースで作ってみた。
tomohisa
2
140
OCI Data Integration技術情報 / ocidi_technical_jp
oracle4engineer
PRO
1
1.5k
Azureコストは水道代/The_47th_Tokyo_Jazug
aeonpeople
3
360
KubeCon EU 2024 : Knative Maintainers Session
salaboy
0
370
Featured
See All Featured
Building a Modern Day E-commerce SEO Strategy
aleyda
15
6.3k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
226
16k
Bash Introduction
62gerente
604
210k
Gamification - CAS2011
davidbonilla
76
4.5k
10 Git Anti Patterns You Should be Aware of
lemiorhan
644
57k
Design by the Numbers
sachag
274
18k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
8
8.2k
How to train your dragon (web standard)
notwaldorf
71
5.1k
The Brand Is Dead. Long Live the Brand.
mthomps
48
21k
Code Reviewing Like a Champion
maltzj
512
39k
Practical Orchestrator
shlominoach
180
9.7k
How to name files
jennybc
62
92k
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༵ۚ