Recommendation for using your own tools

A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=47 masudak
April 19, 2012
Technology
1
5.3k

Recommendation for using your own tools

This materials for #mysqlcasual 3

A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=128

masudak

April 19, 2012
Tweet

More Decks by masudak

See All by masudak
A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=48 masudak
0
1.4k
A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=48 masudak
3
780
A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=48 masudak
0
6.3k

Other Decks in Technology

See All in Technology
8a84268593355816432ceaf78777d585?s=48 dena_tech
0
920
57eb5b1713206c5edb521dde99cc5f30?s=48 key
0
180
53850955f15249a1a9dc49df6113e400?s=48 line_developers
1
420
2564c99f616f1ecfc0f617a6fe87e2a9?s=48 penguin045
0
150
42ba906dc35ef460ffcb1b7c3307ef45?s=48 spring_raining
0
540
Cb88f765dd38cd942c39aacb5abbea06?s=48 ufoo68
0
260
9e7a653ed313b3da4b263a772bfc5026?s=48 clustervr
0
4k
Add4ea030be9e6aa7a319fc8c1b587c6?s=48 hashhub
2
2.1k
1def12864e30f8ea1a4d3bdd67cbb124?s=48 pitan
2
740
0aa238a274c2397214b20d6eed58939b?s=48 moomooya
0
350
0d29d155ce5c5a93ed46363626da3ea7?s=48 ocise
1
1.8k
B2fefbb30aba7c25bbe0c8819791631a?s=48 gunnargrosch
1
420

Featured

See All Featured
168ccec72eee0530b818d44f3fedaacf?s=48 shlominoach
175
7.2k
1177e050db6bafe62885362edf6e3537?s=48 lauravandoore
8
1.1k
D8fc2580cfaca035f666d9e4ee79a7f7?s=48 mongodb
23
3.7k
A16eb159db895e3b01d3dc95767ad595?s=48 jonyablonski
6
730
4394ceb8b277f35ee3da7030384efb5d?s=48 davidbonilla
69
3.5k
6804f1775cb4babfcc3851298566fbce?s=48 tanoku
85
8.4k
1b75d89772b7eea1f6c89fbf362607d9?s=48 moore
125
21k
245cee81a9c424266e5e401d844ea881?s=48 lara
13
2.4k
15f2b8221f247985a27a627d2ece72f0?s=48 pauljervisheath
193
15k
766b9746b59e6f09e280cd33cf4ed419?s=48 skipperchong
5
540
9fa239b3154a0169d99ab7bf1422dd12?s=48 marcelosomers
220
14k
B6a8f005f39d23ffc930508ac9da68b9?s=48 vanstee
115
4.7k

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೔༵ۚ೔