$30 off During Our Annual Pro Sale. View Details »

Recommendation for using your own tools

Kenichi Masuda
April 19, 2012
Technology
1
5.4k

Recommendation for using your own tools

This materials for #mysqlcasual 3

Kenichi Masuda

April 19, 2012
Tweet

More Decks by Kenichi Masuda

See All by Kenichi Masuda
2022-09-05 「明日から新規事業を作ってみよう（実践編）」トヨタ車体株式会社様ウェビナー
masudak
0
120
2022-08-10「明日から新規事業を作ってみよう」トヨタ車体株式会社様向けウェビナー
masudak
0
76
2022-06-09「新規事業担当に必要な要素とは」ヤマトシステム開発株式会社様向けウェビナー
masudak
0
320
Development/QA environments and the Go tools in it
masudak
0
5.7k
How to utilize GKE for QA environment
masudak
3
950
Introduction of mercari SET team
masudak
0
11k

Other Decks in Technology

See All in Technology
StackHawkを使ってWebアプリケーションの脆弱性を診断する
smokeymonkey
0
490
APN AWS Top Engineersが語るAWSの最新セキュリティ
cmusudakeisuke
0
870
Kyashモバイルエンジニアのオンボーディング
sudo5in5k
0
200
Getting Started with Code Reviews
prateekthakare
1
150
[CNDT2022] 実践！OpenTelemetry と OSS を使った Observability 基盤の構築
k6s4i53rx
1
210
LINE NEWSにおけるJava移行の5年間の歩みとこれから / Five Years of Java Migration and the Future of LINE NEWS
line_developers
PRO
1
470
エムスリーデジスマ診療チーム紹介資料 / Introduction of M3 DigiKar Smart Development Team
m3_engineering
1
230
Aurora MySQL 一段飛ばしのバージョンアップとその他もろもろの話
hmatsu47
PRO
1
820
Autonomous Database Cloud 技術詳細 / adb-s_technical_detail_jp
oracle4engineer
PRO
10
22k
IFCによるアプリケーションのマイクロサービス化
ktaroabobon
0
110
Securing CI/CD Systems Through eBPF (recap)
whywaita
PRO
0
530
Make time count with Wear OS 🤖⌚️
moyheen
3
160

Featured

See All Featured
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
226
16k
Design by the Numbers
sachag
271
17k
A Philosophy of Restraint
colly
193
15k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
270
12k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
23
4.4k
Scaling GitHub
holman
453
140k
GraphQLとの向き合い方2022年版
quramy
19
9.3k
Fireside Chat
paigeccino
16
1.6k
Building Adaptive Systems
keathley
25
1.3k
Into the Great Unknown - MozCon
thekraken
0
180
What's in a price? How to price your products and services
michaelherold
231
9.6k
How New CSS Is Changing Everything About Graphic Design on the Web
jensimmons
214
11k

Transcript

  1. ಓ۩Λຏ͘͜ͱͷεεϝ 2012/04/19 #mysqlcasual 3 @masudaK 2012೥4݄20೔༵ۚ೔

  2. ࣗݾ঺հ • @masudaK • ྘ͷձࣾͰOperation Engineer • େن໛େ޷͖ • χʔϋΠ޷͖

    • ৄ͘͠͸ #૿ాνϟϥ͍ 2012೥4݄20೔༵ۚ೔

  3. Ͱ͸ɺຊ୊ 2012೥4݄20೔༵ۚ೔

  4. ಓ۩ʹ࿭Θ͞Εͯ͸ ͍͚ͳ͍ 2012೥4݄20೔༵ۚ೔

  5. ຊ࣭௫Ή 2012೥4݄20೔༵ۚ೔

  6. ͦͷͱ͓ΓͰ͟͝Δ 2012೥4݄20೔༵ۚ೔

  7. ବ՛ࢠ՛ࢠʂʂ 2012೥4݄20೔༵ۚ೔

  8. Α͋͘Δޫܠ 2012೥4݄20೔༵ۚ೔

  9. Q) ͦͷઃఆ ౥ࡌϝϞϦ௒͑ͯͳ͍ʁ 2012೥4݄20೔༵ۚ೔

  10. A) όοϑΝʹ ࡌͬͨΒऴΘΓ·͢ (ΩϦο 2012೥4݄20೔༵ۚ೔

  11. Q) όοϑΝ ͲΕ͘Β͍࢖ΘΕͯΔʁ 2012೥4݄20೔༵ۚ೔

  12. A) ෼͔Γ·ͤΜ (ΩϦο 2012೥4݄20೔༵ۚ೔

  13. Q) n࣌n෼͔Βn෼·Ͱ૸Δ ΫΤϦ෼ੳ͠ͱ͍ͯʔ 2012೥4݄20೔༵ۚ೔

  14. A) ͑ͬ(ΧδϡΞϧ෩ʹ 2012೥4݄20೔༵ۚ೔

  15. ࣄલʹ͋Δ ศརͳ΋ͷ࢖͓͏ͣ ʢΧδϡΞϧʹʣ 2012೥4݄20೔༵ۚ೔

  16. ϛεݮΒͦ͏ͣ ʢΧδϡΞϧʹʣ 2012೥4݄20೔༵ۚ೔

  17. ࠓ͔ΒՄೳͳݶΓ ͝঺հ ʢΧδϡΞϧʹʣ 2012೥4݄20೔༵ۚ೔

  18. ઃఆฤ 2012೥4݄20೔༵ۚ೔

  19. 1. mymemcheck 2012೥4݄20೔༵ۚ೔

  20. •࠷େ࢖༻ϝϞϦݟੵ΋ΓΛ ͝ఏग़ •http://dsas.blog.klab.org/ archives/50860867.html 2012೥4݄20೔༵ۚ೔

  21. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  22. • ͜Μͳײ͡ʢৄ͘͠͸΢Σϒ(ry • process heap = • innodb_buffer_pool + key_buffer

    + • max_connections * (sort_buffer + read_buffer + read_rnd_buffer) + max_connections * stack_size 2012೥4݄20೔༵ۚ೔

  23. 2GϚγϯͰେྔઃఆͯ͠Έͨ 2012೥4݄20೔༵ۚ೔

  24. 2G > 7.940 [G] ... LIMIT OVER!! 2012೥4݄20೔༵ۚ೔

  25. 2. MySQLTuner 2012೥4݄20೔༵ۚ೔

  26. •https://github.com/ rackerhacker/MySQLTuner- perl •Performance Metrics͍ͬͯ͏ ͷ͕͋Δ 2012೥4݄20೔༵ۚ೔

  27. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  28. • ͜Μͳ͜ͱڭ͑ͯ͘ΕΔ • query_cache_size (>= 8M) • thread_cache_size (start at

    4) • innodb_buffer_pool_size (>= 807M) • ͱ͔ͱ͔ 2012೥4݄20೔༵ۚ೔

  29. ோΊͯղੳฤ 2012೥4݄20೔༵ۚ೔

  30. • EXPLAIN • EXPLAIN • ADD INDEX • FORCE INDEX

    • ΫΤϦվળ͝ఏҊ • جຊதͷجຊ 2012೥4݄20೔༵ۚ೔

  31. 3. tcpdump͔Βͷʔ 2012೥4݄20೔༵ۚ೔

  32. 4. pt-query-digest 2012೥4݄20೔༵ۚ೔

  33. @mikeda͞Μ͕ ঺հͯ͘͠ΕΔ͸ͣ 2012೥4݄20೔༵ۚ೔

  34. 5. PROFILING 2012೥4݄20೔༵ۚ೔

  35. • SET PROFILE • ͦͷ࣮ߦεϨουʹͷΈ༗ޮ • Ͳ͜ʹ͔͔࣌ؒͬͯΔ͔ͱ͔ • ύοͱݟͰ෼͔ΓͮΒ͍࣌ศར 2012೥4݄20೔༵ۚ೔

  36. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  37. mysql> SET profiling=1; mysql> ద౰ͳΫΤϦൃߦ 2012೥4݄20೔༵ۚ೔

  38. 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 | 2012೥4݄20೔༵ۚ೔

  39. 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 2012೥4݄20೔༵ۚ೔

  40. 6. MySlowTranCapture 2012೥4݄20೔༵ۚ೔

  41. • ୯ൃΫΤϦ͸ૣ͍ͷʹɺτϥϯβΫ γϣϯͩͱ஗͍΋ͷͱ͔ݕ஌ͯ͘͠Ε Δ • https://github.com/yoshinorim/ MySlowTranCapture 2012೥4݄20೔༵ۚ೔

  42. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  43. দ৴ຊߪೖ͢Δ͔͠ͳ͍ɻ 2012೥4݄20೔༵ۚ೔

  44. ָ͍ͨ͠ฤ 2012೥4݄20೔༵ۚ೔

  45. 7. TPC-C 2012೥4݄20೔༵ۚ೔

  46. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  47. ΧδϡΞϧා͍ 2012೥4݄20೔༵ۚ೔

  48. • σʔλ࡞ΔͷΊΜͲ͍ • tpcc_load localhost tpcc1000 root "" 100 •

    ./tpcc_start -h localhost -d tpcc1000 -u root -w 10 - c 2 -r 10 -l 30 • Sͷਓ޲͚ɻh͸΋ͬͱϋʔυɻ 2012೥4݄20೔༵ۚ೔

  49. 8. Facebook, online schema change tool 2012೥4݄20೔༵ۚ೔

  50. ஌ͬͯΔਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  51. • http://www.facebook.com/notes/mysql-at- facebook/online-schema-change-for-mysql/ 430801045932 • ୭͔ϓϩμΫτͰ(ry 2012೥4݄20೔༵ۚ೔

  52. ৘ใऩूฤ 2012೥4݄20೔༵ۚ೔

  53. 9. MySQL Performance BlogΛѪಡ 2012೥4݄20೔༵ۚ೔

  54. Ѫಡͯ͠Δਓʔ ϊ 2012೥4݄20೔༵ۚ೔

  55. 2012೥4݄20೔༵ۚ೔

  56. γϡϫϧπͷসإ ΍͹͍ 2012೥4݄20೔༵ۚ೔

  57. 10. Baron Schwartz ܏౗ͯ͠ΈΔ 2012೥4݄20೔༵ۚ೔

  58. 2012೥4݄20೔༵ۚ೔

  59. 11. ΧϯϑΝϨϯε ࢿྉΛړΔ 2012೥4݄20೔༵ۚ೔

  60. 2012೥4݄20೔༵ۚ೔

  61. 2012೥4݄20೔༵ۚ೔

  62. ڳ೤ 2012೥4݄20೔༵ۚ೔

  63. ·ͩ·ͩ৭ʑ 2012೥4݄20೔༵ۚ೔

  64. • ಓ۩͕શͯͰ͸ͳ͍ • ͚Ͳɺ໰୊ղܾͷࢳޱͱͯ͠ • ϓϩμΫτͰ࢖͑Δ΋ͷ͸(ry • Ξ΢τϓοτΛੋඇ ऴΘΓʹ 2012೥4݄20೔༵ۚ೔

  65. ࠷ޙʹ 2012೥4݄20೔༵ۚ೔

  66. αΠόʔΤʔδΣϯτ Ұॹʹಇ͍ͯ͘ΕΔਓ ืूத 2012೥4݄20೔༵ۚ೔

  67. ͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠ 2012೥4݄20೔༵ۚ೔