Recommendation for using your own tools

This materials for #mysqlcasual 3

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