Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
Recommendation for using your own tools
Search
Sponsored
·
Your Podcast. Everywhere. Effortlessly.
Share. Educate. Inspire. Entertain. You do you. We'll handle the rest.
→
Kenichi Masuda
April 19, 2012
Technology
5.6k
1
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
Recommendation for using your own tools
This materials for #mysqlcasual 3
Kenichi Masuda
April 19, 2012
Transcript
67 slides
ಓ۩Λຏ͘͜ͱͷεεϝ 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༵ۚ
More Decks by Kenichi Masuda
See All by Kenichi Masuda
2022-09-05 「明日から新規事業を作ってみよう(実践編)」トヨタ車体株式会社様ウェビナー
masudak
0
810
2022-08-10「明日から新規事業を作ってみよう」トヨタ車体株式会社様向けウェビナー
masudak
1
630
2022-09-05 「明日から新規事業を作ってみよう(実践編)」トヨタ車体株式会社様ウェビナー
masudak
0
78
2022-06-09「新規事業担当に必要な要素とは」ヤマトシステム開発株式会社様向けウェビナー
masudak
0
890
Development/QA environments and the Go tools in it
masudak
0
7.4k
How to utilize GKE for QA environment
masudak
3
1.4k
Introduction of mercari SET team
masudak
0
13k
Other Decks in Technology
See All in Technology
市販IoT機器や自作IoT機器とHomeAssistantを駆使して家の電気代をできる限り見える化する方法の考案
harutiro
0
410
認知負荷の減らし方 ~開発にも日常業務にも活かせるノウハウ~
minorun365
PRO
15
9.6k
現地速報! Microsoft Ignite アップデートレポート
kasada
1
420
その修正で本当に速くなったのか? k6とOpenTelemetryで確かめる性能改善 / Performance review with k6 and OpenTelemetry
ymotongpoo
3
420
AWS Well-Arhitected Frameworkセキュリティの柱 について
miu_crescent
PRO
2
170
メール到達性を支える、リンク先URLのドメイン評価の扱い方
wind111
0
190
みん強Lightning! データ基盤ひとりでできるかな
endonanana
0
170
共通基盤の実装に Kotlin - Quarkus を選定した理由と採用したアーキテクチャ
nealle
1
160
Kansai WordPress Meetup@大阪『ブロックテーマを作る上で知っておくべきこと
torounit
0
260
Go でトランザクション、どう書いてますか?
knwoop
1
270
落とし穴にハマってわかった 開発生産性を測るときに気をつけたいこと
munakata
3
480
2026-11-16 JAWS-UG コンテナ支部 #31 ゆるLT大会 ECR のライフサイクルポリシーを いい感じに設定したい / ECR Lifecycle policy
masasuzu
3
590
Featured
See All Featured
Agile that works and the tools we love
rasmusluckow
331
22k
The SEO Collaboration Effect
kristinabergwall1
1
620
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
12
2k
The SEO identity crisis: Don't let AI make you average
varn
0
630
Being A Developer After 40
akosma
PRO
92
590k
Crafting Experiences
bethany
1
450
Designing for Timeless Needs
cassininazir
1
570
Redefining SEO in the New Era of Traffic Generation
szymonslowik
1
530
How To Speak Unicorn (iThemes Webinar)
marktimemedia
1
640
The Hidden Cost of Media on the Web [PixelPalooza 2025]
tammyeverts
2
580
Mobile First: as difficult as doing things right
swwweet
225
10k
Why Our Code Smells
bkeepers
PRO
340
58k