MySQL運用とらぶるすとーり〜^2 / MySQL-Troubleshooting-Story

Transcript

1. MySQLӡ༻

   ͱΒͿΔ͢ͱʔΓʙ^2
   גࣜձࣾ͸ͯͳ

   id: ichirin2501
   2016/8/31 Hatena Engineer Seminar #6 ʙΠϯϑϥฤʙ @ Tokyo
   
   

   View Slide

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

   ݩΞϓϦέʔγϣϯΤϯδχΞ(໿2೥൒)͔Βస৬
   ڵຯ
   
   

   View Slide

3. ΋͘͡
   • ςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩
   • binlogഁଛ͔Βٹ͍ΛٻΊͨ࿩
   
   

   View Slide

4. ʰςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩ʱ
   
   

   View Slide

5. (͋Δ࣌)DBΤϥʔग़ͯΔ
   &
   (ൃੜ௚ޙ͔Β)ۮવൃݟ
   
   ෺ޠ։࢝
   

   View Slide

6. The table 'relword' is full
   ෆཁͳσʔλ(޾͍)Λ࡟আͯ͠Ԇ໋(Ұϲ݄)
   
   

   View Slide

7. full?, σʔλαΠζ͔ͳʁ
   • DB͸MySQL-4.0.27(Senna, ಠࣗbuild)
   • MyISAMςʔϒϧͷ্ݶ͸4GB

   (Φϓγϣϯࢦఆͳ͠Ͱ࡞੒ͨ͠৔߹)
   • ALTERͰαΠζ্ݶΛมߋͯ͠ରॲՄ
   
   

   View Slide

8. mysql> show table status like 'relword' \G
   *************************** 1. row ***************************
   Name: relword
   Type: MyISAM
   Row_format: Fixed
   Rows: 4294967295
   Avg_row_length: 9
   Data_length: 38654705655
   Max_data_length: 38654705663
   Index_length: 106203639808
   Data_free: 0
   .....................
   ݏͳ༧ײɺߦ਺ʂʁ
   
   

   View Slide

9. 
   configureΦϓγϣϯʹ
   --with-big-tables ͕
   ݟ౰ͨΒͳ͍
   

   View Slide

10. ݹͷύοέʔδΛ

    ࠶Ϗϧυʁ....
    
    

    View Slide

11. ͳΜ͔͏·͍͔͘ͳ͍
    
    

    View Slide

12. ͜ͷ··Ͱ͸Ұϲ݄ޙʹࢮ͵
    
    

    View Slide

13. ΋͏όʔδϣϯ͋͛Α͏ʂʂ
    
    ͍ͭͰʹInnoDBԽ
    

    View Slide

14. MySQL 4.0 -> 5.1
    • (ඇޓ׵)char,varcharͷ௕͞ఆ͕ٛ

    όΠτ਺͔Βจࣈ਺΁มߋ(4.1 ~
    • (ඇޓ׵)timestamp(n)ഇࢭ(4.1 ~

    "20160830133257" -> "2016-08-30 13:32:57"

    ԼهͷΑ͏ʹจࣈྻͷ··ΞϓϦଆͰѻ͍ͬͯΔͱࢮ

    ྫ: http://anond.hatelabo.jp/20160830133257

    ͭΒ͍ɺ͕Μ͹Ζ͏
    
    

    View Slide

15. ΞοϓάϨʔυͷ४උͩʂ
    
    

    View Slide

16. 5.0
    4.0
    5.1 Slave
    Slave
    VIP
    Standby
    Standby
    Master
    Master
    
    client
    ߋ৽ܥ
    ࢀরܥ
    

    View Slide

17. 5.0
    4.0
    5.1 Slave
    Slave
    VIP
    Standby
    Standby
    Master
    Master
    
    client
    ߋ৽ܥ
    ࢀরܥ
    

    View Slide

18. 5.0
    4.0
    5.1 Slave
    Slave
    VIP
    Master
    Master
    Standby
    Standby
    
    client
    ࢀরܥ
    

    View Slide

19. 5.0
    4.0
    5.1 Slave
    Slave
    VIP
    Master
    Master
    Standby
    Standby
    
    client
    ߋ৽ܥ
    ࢀরܥ
    

    View Slide

20. μ΢ϯλΠϜ਺ඵ
    ߦ਺໰୊΋(ڧҾ)ղܾ
    InnoDBͰฏ࿨
    
    

    View Slide

21. ʰbinlogഁଛ͔Βٹ͍ΛٻΊͨ࿩ʱ
    
    

    View Slide

22. DBߏ੒
    Master Standby Master
    Slave
    VIP
    
    

    View Slide

23. VIP
    Master Standby Master
    Slave
    DiskFull !!
    
    ෺ޠ։࢝
    

    View Slide

24. Master Standby Master
    Slave
    ϨϓϦΤϥʔʂʂ
    x
    x
    x
    ᄉᅀʹMasterͷ༰ྔΛۭ͚Δ͕...
    x
    VIP
    
    

    View Slide

25. Master Standby Master
    Slave
    Master͚ͩਐΉ
    x
    x
    x
    ߋ৽ΫΤϦ͸ྲྀΕଓ͚ͯΔ
    x
    VIP
    
    

    View Slide

26. Error reading packet from server:
    log event entry exceeded
    max_allowed_packet;
    Increase max_allowed_packet on
    master (server_errno=1236)
    max_allowed_packet ???
    Τϥʔϩά
    ͓લ͸ԿΛݴ͍ͬͯΔΜͩ
    
    

    View Slide

27. binlog͕։͚ͳ͍
    $ mysqlbinlog --no-defaults antennadb08-bin.4406 > dump.bin
    mysqlbinlog: Error reading file 'antennadb08-bin.4406' (Errcode: 22 - Invalid argument)
    ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2964698656, event_type: -51
    ERROR: Could not read entry at offset 549168893: Error in log format or read error.
    ERROR: Could not read entry at offset 549168893:
    Error in log format or read error.
    શεϨʔϒ͸549168893ͷϙδγϣϯͰఀࢭ
    DiskFullͰbinlogഁଛ
    
    

    View Slide

28. ୈҰʹαʔϏε෮چ

    ࣍ͷbinlogʹมߋ

    => ϨϓϦ஗Ԇճආ
    = αʔϏεDBͷੑ࣭తʹηʔϑ
    
    

    View Slide

29. Master Slave
    Error Pos
    New binlog Pos
    MasterͷΈͷߋ৽෼
    Master/Slaveͷߋ৽ঢ়ଶ
    
    

    View Slide

30. Master͚ͩͷߋ৽෼Λൈ͖͍ͨ
    Error Posͷ࣍ͷPos৘ใ͕ඞཁ
    mysqlbinlog͸ػೳ͠ͳ͍
    
    

    View Slide

31. ͦ͏ͩɺόΠφϦͰݟΑ͏ʂ
    
    

    View Slide

32. timestamp
    magic type server_id length
    next pos flag change-masterͱ͔Ͱࢦఆ͢Δ࣍ͷpos
    data
    v3 event structure
    
    

    View Slide

33. 549168893 = 20BBA6FD ͷ৔ॴΛݟΔͱtimestampͰ͸ͳ͍
    ͜ͷपล͸ഁଛͯ͠ϑΥʔϚοτ่͕ΕͯΔ
    
    

    View Slide

34. पลͷ bc 56 ͷσʔλྻΛ୳͢
    20bba7d5 = 549169109 ʹ timestamp
    master_log_file = "antennadb08-bin.4406"
    master_log_pos = 549169109
    ΤϥʔͰϨϓϦఀࢭͯͨ͠backupDB͕͋ͬͨͷͰ

    CHANGE MASTER TO͢Ε͹ಈ͖ͦ͏
    
    

    View Slide

35. ਖ਼ৗʹಈ͍ͨ
    ॿ͔ͬͨ
    ࠔͬͨΒόΠφϦΛݟΑ͏
    
    

    View Slide

36. ·ͱΊ
    • ݹ͍DBͷ࠷େߦ|σʔλ਺͸ݱ࣮తͳ੍ݶ஋
    • binlogഁଛͯ͠΋௚઀όΠφϦΛݟΕ͹ྑ͍

    ௥هܗࣜͳͷͰޙଓ͸յΕͯͳ͍Մೳੑߴ
    
    

    View Slide