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
800
A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=48 masudak
0
6.3k

Other Decks in Technology

See All in Technology
53850955f15249a1a9dc49df6113e400?s=48 line_developers
PRO
3
2.6k
C47bda32c8455a59471cd7e19c32c074?s=48 hamadakoji
0
1.1k
19fc8f6113c5c3d86e6176362ff29479?s=48 kanaugust
PRO
0
130
53850955f15249a1a9dc49df6113e400?s=48 line_developers
PRO
0
140
1bfc6e2ed04a895bb36f36b86828b689?s=48 110y
3
690
53e2d354b3299d64a54af680865516d5?s=48 satotakeshi
0
160
1f745ff900e1be51aedae18cae76593c?s=48 kurochan
0
520
D99b232749259de3083102b4b32199bc?s=48 ray_30cm_ns
0
270
Bd82857e5cd002562af37c7b6b9bc561?s=48 yokatsuki
0
190
50bc42471d197d0dbef7171f2ef85bb6?s=48 comucal
PRO
0
410
53850955f15249a1a9dc49df6113e400?s=48 line_developers
PRO
0
780
A83a7138276e3275d94d91ed90d13300?s=48 hecateball
1
12k

Featured

See All Featured
A415db3ac9e9668acbc1fb36eead84ac?s=48 mthomps
39
2.3k
Dafc4723e9a1c067796c0fec6f509774?s=48 lemiorhan
626
42k
A60068bce2e73de3a37ca9d2dbe36092?s=48 bryan
30
3.3k
7c8469ee8c9e594c65c59b919626c08d?s=48 scottboms
251
11k
5f83ef806155b403c3393160ce51f955?s=48 jlugia
216
16k
1b8ad785acdd1ce1c99914b1c2a4e10e?s=48 sugarenia
233
830k
78b475797a14c84799063c7cd073962f?s=48 holman
461
280k
5f2da528927a2ec9ba4fec2069cbc958?s=48 kneath
294
39k
B3d6434763caa0ef5dc4b792662c49f7?s=48 smashingmag
229
18k
282d599b414022eba5096510f675b6e2?s=48 chrislema
173
14k
1519587b1a0febb658c36c94f6272b0d?s=48 roundedbygravity
241
21k
20bfe76b3d6105641f879fe45cfc9272?s=48 bkeepers
321
53k

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