$30 off During Our Annual Pro Sale. View Details »

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

ichirin2501
September 02, 2016

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

ichirin2501

September 02, 2016
Tweet

More Decks by ichirin2501

Other Decks in Technology

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