Recommendation for using your own tools

A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=47 masudak
April 19, 2012

Recommendation for using your own tools

This materials for #mysqlcasual 3

A2a2d96ab7fae2ccc4fb8e88cc7f8698?s=128

masudak

April 19, 2012
Tweet

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