hbstyle-20141211.pdf

Transcript

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. ݪҼ (ଓ͖) • Slave ͕อ࣋͢Δ table_id ͸ uint • 64bit Linux ؀ڥͰ 4294967295 ͕࠷େ • Master ͕อ࣋͢Δ table_id (table_map_id) ͸ ulong • 64bit Linux ؀ڥͰ 18446744073709551615 ͕࠷େ • ςʔϒϧͷ࡞Γ௚͠, TRUNCATE ౳Ͱ table_id ͕ߋ৽͞ΕΔ • ෆ੔߹͕ى͖Δͱ Slave ͸σʔλΛߋ৽Ͱ͖ͳ͍ (Τϥʔແ) YOSHIKAWA Ryota hbstyle-2014-12-11 4
5. • σόοάϩά 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 + 1) ͸ 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. πʔϧͷ࢖͍ํͱ͔ιʔεಡΈͱ͔ͪΐͬͱ͚ͩ 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