Recommendation for using your own tools

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