hbstyle-2014-12-11

MySQL 5.6.10 以下に存在する レプリケーションのバグと原因特定について (Heartbeats 社内勉強会 2014/12/11)

1. MySQL ϨϓϦέʔγϣϯͷόάͱ ݪҼಛఆʹ͍ͭͯ YOSHIKAWA Ryota hbstyle-2014-12-11 1
2. ࣄ৅ • MySQL (5.5.32) ʹͯɺϨϓϦέʔγϣϯͷෆ۩߹͕ൃੜ • DDL ͸શͯ൓ө͞ΕΔ • DML ͸ಛఆͷςʔϒϧʹͯ൓ө͞Εͳ͍ • Slave_(IO|SQL)_Running ͸ڞʹ Yes • Τϥʔ΋ແ͠ • MySQL 5.6.10 Ҏલશ͕ͯ͜ͷόάͷର৅ YOSHIKAWA Ryota hbstyle-2014-12-11 2
3. ݪҼ • MasterDB ͷ table_id ͕ UINT_MAX Λ௒͑ͨ৔߹ʹൃੜ • SlaveDB ͷ table_id ͕Φʔόʔϑϩʔ • MasterDB ͕͍࣋ͬͯΔ table_id ͱ, ϨϓϦέʔγϣϯͰ఻ୡ͞ΕΔ table_id ʹෆ੔߹͕ൃੜ • binlog ͕ Row|Mixed ͷϨϓϦέʔγϣϯͰӨڹ • ৄ͘͠͸ http://bugs.mysql.com/bug.php?id=67352 YOSHIKAWA Ryota hbstyle-2014-12-11 3
4. ݪҼ (ଓ͖) • Master ͕؅ཧ͢Δ table_id (table_map_id) ͸ ulong • 64bit Linux ؀ڥͰ 18446744073709551615 ͕࠷େ • Slave ͕؅ཧ͢Δ table_id ͸ uint • 64bit Linux ؀ڥͰ 4294967295 ͕࠷େ • CREATE/DROP/TRUNCATE ౳Ͱ table_id ͕૿Ճ͍ͯ͘͠ • ෆ੔߹͕ى͖Δͱ Slave ͸σʔλΛߋ৽Ͱ͖ͳ͘ͳΔ YOSHIKAWA Ryota hbstyle-2014-12-11 4
5. • σόοάϩά # Master Ͱcheck_repl ςʔϒϧ΁ͷॻ͖ࠐΈΛͨ࣌͠ͷ Slave ͷσόοάϩά table_mapping::set_table(ulong,TABLE*): enter: table_id: 20497567 table: 0x3d361f0 (check_repl) table_mapping::set_table(ulong,TABLE*): info: tid 20497567 -> table 0x3d361f0 (check_repl) table_mapping::get_table(ulong): enter: table_id: 4315464863 table_mapping::get_table(ulong): info: tid 4315464863 is not mapped! # ... 20497567 = 4315464863 - (4294967295 + 1) # ... (4294967295 ... UINT_MAX) • ιʔείʔυ * table_map_id > # grep -n "ulong table_map_id" sql/table.h > 645: ulong table_map_id; /* for row-based replication */ * table_id > # grep -n "uint table_id" sql/table.h > 1457: uint table_id; /* table id (from binlog) for opened table */ YOSHIKAWA Ryota hbstyle-2014-12-11 5
6. Ͳ͏΍ͬͯಛఆ͔ͨ͠ YOSHIKAWA Ryota hbstyle-2014-12-11 6
7. Day1 YOSHIKAWA Ryota hbstyle-2014-12-11 7
8. ਪଌʹΑΔԾઆݕূͷ܁Γฦ͠ • ਓ͕ؒ૝૾Ͱ͖Δൣғͷόάͩͱ͜ͷํ๏͕ߴ଎(ܦݧతʹ) • ಉ༷ͷݱ৅(slave ߋ৽ෆՄ)Λ google Ͱͻͨ͢Βݕࡧɾݕূ • ਓؒͷ૝૾Ͱ͖ͳ͍ൣғͷόάͩͱϋϚΔ • લྫͷ஌ݟ͕ݕࡧͮ͠Β͍/ແ͍৔߹͸ಛʹ • ܦݧΛੵΉࣄͰൣғΛ޿͛Δ͜ͱ͸Մೳͳؾ͸͢Δ YOSHIKAWA Ryota hbstyle-2014-12-11 8
9. Day2 YOSHIKAWA Ryota hbstyle-2014-12-11 9
10. ࣄ৅ͷ࠶ݱɾ੾Γ෼͚ • ࣗ෼ͷݕࡧɾਪଌग़དྷΔϨϕϧΛ௒͍͑ͯΔࣄΛೝࣝ • ຊ൪ػͷ binlog ΛோΊͯոͦ͠͏ͳૢ࡞Λચ͍ग़͢ • Slave ͷσʔλΛݟͯԿ࣌ࠒʹෆ੔߹͕ى͖͔ͨ֬ೝ • ෆ۩߹͕ى͖ͯΔ DB Ͱࣄ৅ͷ੾Γ෼͚ • σʔλϑΝΠϧͷ༰ྔ͸ຊ౰ʹ૿͍͑ͯͳ͍͔ • ϦϨʔϩάʹ͸ॻ͖ࠐΈ͕͋Δ͔...౳ YOSHIKAWA Ryota hbstyle-2014-12-11 10
11. Day3 YOSHIKAWA Ryota hbstyle-2014-12-11 11
12. ࠶ݱࡁΈ؀ڥͰͷσόοά • ো֐͕ى͖͍ͯͨ Master ʹผͷ Slave Λܨ͙ͱ࠶ݱग़དྷͨ • Slave ʹؔΘΒͣো֐͕ى͖Δ => Master ͓͔ͦ͠͏ • Slave ͷ MySQL Λ Debug ༗ޮͰϏϧυͯ͠ܨ͍ͰΈΔ cmake . -DWITH_DEBUG=1 -DMYSQL_MAINTAINER_MODE=0 • ਖ਼ৗͳ Master ʹܨ͍ͩ࣌ͷϩάͱҟৗͳϩάΛݟൺ΂Δ • ϩάͱιʔείʔυΛൺֱͯ͠Ͳ͜ͰԿ͕ى͖ͯΔ͔೺Ѳ YOSHIKAWA Ryota hbstyle-2014-12-11 12
13. ໾ʹཱͬͨπʔϧ • mysqlbinlog (binlog, relaylog ղੳ) • tcpdump & Percona Toolkit (ύέοτɾσʔλෆ੔߹ௐࠪ) • icdiff (֤छϩάͷൺֱ) • gdb (MySQL ͷσόοά) • vim & ctags & Unite grep ... (ιʔείʔυϦʔσΟϯά) YOSHIKAWA Ryota hbstyle-2014-12-11 13
14. ໾ʹཱͬͨ஌ݟ (1) • hbstyle Ͱͷ஌ݟ • @indigo13love ͞Μͷੲͷൃද • sql/slave.cc ΍ϨϓϦέʔγϣϯͷ஌ݟ • ܭࢉػՊֶͷ஌ݟ • MySQL Packet ͷϔομ͙Β͍ͳΒಡΊΔ • ֶ෦ͷ࣌ʹύέοτோΊͨ஌ݟ YOSHIKAWA Ryota hbstyle-2014-12-11 14
15. ໾ʹཱͬͨ஌ݟ (2) • ιʔείʔυϦʔσΟϯάͷ஌ݟ • ֶ෦࢛೥Ͱ৭Μͳιϑτ΢ΣΞͷιʔείʔυಡΜͩ஌ݟ • Apache, Nginx, Keepalived, LVS ... • ϏϏΒͳ͍͜ͱ • ϩάͱಥ͖߹ΘͤͯಡΉ • πʔϧΛ࢖ָͬͯͯ͠ಡΉ YOSHIKAWA Ryota hbstyle-2014-12-11 15
16. [demo] πʔϧͷ࢖͍ํͱ͔ιʔεಡΈͱ͔ͪΐͬͱ͚ͩ YOSHIKAWA Ryota hbstyle-2014-12-11 16
17. ൓ল YOSHIKAWA Ryota hbstyle-2014-12-11 17
18. Don't guess, measure! — Robert C. Pike YOSHIKAWA Ryota hbstyle-2014-12-11 18
19. ൓ল • ਪଌ͢ΔͳɺܭଌͤΑ • ύϑΥʔϚϯε͚ͩͰ͸ͳ͘όά΋ಉ͡ • (૝ఆ಺ͷ)؆୯ͳόάΛ૬खʹͯ͠Δͱ๨ΕΔ͜ͱ͋Δ • ࣄ৅ɾݪҼ੾Γ෼͚ɾιʔείʔυ͕શͯ • ϩάΛݟΔɾιʔείʔυΛݟΔ YOSHIKAWA Ryota hbstyle-2014-12-11 19
20. • νϟοτͱ͔ͷݺͼ͔͚Ͱڠྗͯͩͬͨ͘͠͞օ͞Μ͋Γ͕ ͱ͏͍͟͝·ͨ͠ • ಛʹ @saito @takamoto @hosokoshi @matsuu ... • ΋ͬͱݕࡧྗ͕ߴ͚Ε͹ҰൃͰͨͲΓண͍͔ͨ΋͠Εͳ͍ • http://bugs.mysql.com/bug.php?id=67352 • ͱ͸͍͑ MySQL ʹͪΐͬͱৄ͘͠ͳΕͯΑ͔ͬͨ YOSHIKAWA Ryota hbstyle-2014-12-11 20