Upgrade to Pro — share decks privately, control downloads, hide ads and more …

mysql-freeze

 mysql-freeze

ichirin2501

March 10, 2018
Tweet

More Decks by ichirin2501

Other Decks in Programming

Transcript

  1. MySQL freeze ͷר גࣜձࣾ͸ͯͳ id: ichirin2501  2017-08-19 Kyoto.ͳΜ͔ #3

  2. ࣗݾ঺հ • ੢઒ ݩߊ ( @ichirin2501 ) • ͸ͯͳͷWebΦϖϨʔγϣϯΤϯδχΞ ڵຯ

    
  3. ࠓճͷ͓࿩ 

  4. MySQLͷfreeze!! 

  5. ౰࣌ʹ֬ೝग़དྷͨൣғΛྻڍ • ٸʹࢮ͵ • ࢮʹํ͸freeze, MySQLͷϓϩηε͸ੜ͖ͯΔ • mysqladmin pingͳͲͷԠ౴͸ग़དྷΔ •

    Ұ෦σʔλϕʔε΁ͷΫΤϦ͕ฦͬͯ͜ͳ͍ • TCPͷ઀ଓཱ֬͸ग़དྷΔ, TCP keepalive΋ಈ͍ͯΔ • Τϥʔϩάʹ͸Կ΋৘ใ͸ग़ྗ͞Ε͍ͯͳ͍ 
  6. ͏͚Δ 

  7. ͏͚ͳ͍ 

  8. MySQLͷόʔδϣϯͱ͔ • MySQL-5.5.46 • mysql-server-5.5_5.5.46-0+deb7u1_amd64.deb • mysql-server-5.5_5.5.46-0+deb8u1_amd64.deb • OS: Debian

    7.10(wheezy) or 8.1(jessie) • Kernel: wheezy=3.10.23, jessie=3.16.0 
  9. ڧҾʹ࠶ݱͤ͞Δ 

  10. sysbench !! (஫ҙ:benchmarkπʔϧ) 

  11. ߴෛՙΛ͔͚ଓ͚ͨΒ࠶ݱ͠ ͨʂʂ 

  12. sysbenchʹΑΔࡶͳݕূ݁Ռ  EFCJBO EFCJBO  P P  Y Y

     Y P  P P  P P x͕freeze
  13. ϚΠφʔόʔδϣϯΛ্͛Δ
 5.5.46 -> 5.5.52 

  14. ຊ൪Ͱ΋Freeze͠ͳ͘ͳͬͨ 

  15. Կ΋Θ͔ΒΜ͕ฏ࿨͕๚Εͨ 

  16. ࣌͸ա͗... 

  17. ։ൃऀɿʮscript࣮ߦͯͨ͠Βfreezeͨ͠ʯ ࢲɿʮ͋ʔ͋ʔɺͳʹ΋ฉ͑͜·ͤΜʯ 

  18. MySQL-5.6.27͕Freeze 

  19. ࠶ݕূ1 • աڈͷ؆қͳݕূͰ͸freeze͠ͳ͔ͬͨ • ಉݕূΛ࠶౓ߦ͕ͬͨ࠶ݱग़དྷͳ͍ • ஫: sysbench͸ετϨεςετͰ͸ͳ͍ 

  20. ࠶ݕূ2 • ࣮ࡍʹϑϦʔζͨ͠batchεΫϦϓτͰࢼ͢ • 1ճ໿25࣌ؒͷbatchεΫϦϓτ • ݕূճ਺͕ॏͶΒΕͳ͍͕ࢼͦ͏ʂ 

  21. 1ճ໨Ͱݟࣄʹfreeze ΍ͬͨͶʂʂʂʂʂ 

  22. ௐࠪ • Ҏલௐࠪͨ͠ͱ͖ʹεϧʔ͍߲ͯͨ͠໨͕ؾ ʹͳͬͨ • mysql-5.5.49ͷBugs FixedͷҰ෦ • InnoDB: Running

    REPLACE operations on multiple connections resulted in a hang. (Bug #22530768, Bug #79185) 
  23. bug report • ʮInnodb freeze running REPLACE statementsʯhttps://bugs.mysql.com/ bug.php?id=79185 •

    ಈ࡞؀ڥͱݱ৅͕ࠅࣅ͍ͯͨ͠ɻ
 Ͱ΋ɺREPLACEΫΤϦ͸ࠓճͷfreezeࣄ݅ʹ ͓͍ͯҰ౓΋ൃߦ͞Ε͍ͯͳ͍ 
  24. ؾʹͳͬͯίʔυΛݟʹߦ͘ • https://github.com/mysql/mysql-server/ search?q=22530768&type=Commits&utf8= %E2%9C%93 • |'o') TASपΓͷमਖ਼͡ΌΜ....REPLACEΫΤϦ ʹݶΒͳ͍ͷͰ͸....ෆԺ 

  25. ϚΠφʔόʔδϣϯΛ্͛Δ • 5.6.27ʹ͸ઌ΄ͲͷBugfix͸ؚ·Ε͍ͯͳ ͍ɻͱΓ͋͑ͣ5.6.37·Ͱ্͛ͯscriptΛ࣮ߦ • ಉ༷ͷscriptΛԿ౓͔࣮ߦ͕ͨ͠freezeͳ͠
 ࠓ΋ฏ࿨ʂʂʂ 

  26. Α͘Θ͔ΒΜ͕ղܾ΍ʂʂ 

  27. ฏ࿨͕Ұ൪ 

  28. ·ͱΊ • ϛυϧ΢ΣΞͷݕূ͸े෼ʹߦ͍·͠ΐ͏ • ͪΌΜͱετϨεςετΛ࣮ࢪ͠·͠ΐ͏ • ؾʹͳͬͨBugमਖ਼ͷίϛοτΛݟʹߦ͜͏