Tweet
Tweet

Slide 1

Slide 1 text

MySQL ϨϓϦέʔγϣϯͷόάͱ ݪҼಛఆʹ͍ͭͯ YOSHIKAWA Ryota hbstyle-2014-12-11 1

Slide 2

Slide 2 text

ࣄ৅ • MySQL (5.5.32) ʹͯɺϨϓϦέʔγϣϯͷෆ۩߹͕ൃੜ • DDL ͸શͯ൓ө͞ΕΔ • DML ͸ಛఆͷςʔϒϧʹͯ൓ө͞Εͳ͍ • Slave_(IO|SQL)_Running ͸ڞʹ Yes • Τϥʔ΋ແ͠ • MySQL 5.6.10 Ҏલશ͕ͯ͜ͷόάͷର৅ YOSHIKAWA Ryota hbstyle-2014-12-11 2

Slide 3

Slide 3 text

ݪҼ • 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

Slide 4

Slide 4 text

ݪҼ (ଓ͖) • 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

Slide 5

Slide 5 text

• σόοάϩά # 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

Slide 6

Slide 6 text

Ͳ͏΍ͬͯಛఆ͔ͨ͠ YOSHIKAWA Ryota hbstyle-2014-12-11 6

Slide 7

Slide 7 text

Day1 YOSHIKAWA Ryota hbstyle-2014-12-11 7

Slide 8

Slide 8 text

ਪଌʹΑΔԾઆݕূͷ܁Γฦ͠ • ਓ͕ؒ૝૾Ͱ͖Δൣғͷόάͩͱ͜ͷํ๏͕ߴ଎(ܦݧతʹ) • ಉ༷ͷݱ৅(slave ߋ৽ෆՄ)Λ google Ͱͻͨ͢Βݕࡧɾݕূ • ਓؒͷ૝૾Ͱ͖ͳ͍ൣғͷόάͩͱϋϚΔ • લྫͷ஌ݟ͕ݕࡧͮ͠Β͍/ແ͍৔߹͸ಛʹ • ܦݧΛੵΉࣄͰൣғΛ޿͛Δ͜ͱ͸Մೳͳؾ͸͢Δ YOSHIKAWA Ryota hbstyle-2014-12-11 8

Slide 9

Slide 9 text

Day2 YOSHIKAWA Ryota hbstyle-2014-12-11 9

Slide 10

Slide 10 text

ࣄ৅ͷ࠶ݱɾ੾Γ෼͚ • ࣗ෼ͷݕࡧɾਪଌग़དྷΔϨϕϧΛ௒͍͑ͯΔࣄΛೝࣝ • ຊ൪ػͷ binlog ΛோΊͯոͦ͠͏ͳૢ࡞Λચ͍ग़͢ • Slave ͷσʔλΛݟͯԿ࣌ࠒʹෆ੔߹͕ى͖͔ͨ֬ೝ • ෆ۩߹͕ى͖ͯΔ DB Ͱࣄ৅ͷ੾Γ෼͚ • σʔλϑΝΠϧͷ༰ྔ͸ຊ౰ʹ૿͍͑ͯͳ͍͔ • ϦϨʔϩάʹ͸ॻ͖ࠐΈ͕͋Δ͔...౳ YOSHIKAWA Ryota hbstyle-2014-12-11 10

Slide 11

Slide 11 text

Day3 YOSHIKAWA Ryota hbstyle-2014-12-11 11

Slide 12

Slide 12 text

࠶ݱࡁΈ؀ڥͰͷσόοά • ো֐͕ى͖͍ͯͨ Master ʹผͷ Slave Λܨ͙ͱ࠶ݱग़དྷͨ • Slave ʹؔΘΒͣো֐͕ى͖Δ => Master ͓͔ͦ͠͏ • Slave ͷ MySQL Λ Debug ༗ޮͰϏϧυͯ͠ܨ͍ͰΈΔ cmake . -DWITH_DEBUG=1 -DMYSQL_MAINTAINER_MODE=0 • ਖ਼ৗͳ Master ʹܨ͍ͩ࣌ͷϩάͱҟৗͳϩάΛݟൺ΂Δ • ϩάͱιʔείʔυΛൺֱͯ͠Ͳ͜ͰԿ͕ى͖ͯΔ͔೺Ѳ YOSHIKAWA Ryota hbstyle-2014-12-11 12

Slide 13

Slide 13 text

໾ʹཱͬͨπʔϧ • mysqlbinlog (binlog, relaylog ղੳ) • tcpdump & Percona Toolkit (ύέοτɾσʔλෆ੔߹ௐࠪ) • icdiff (֤छϩάͷൺֱ) • gdb (MySQL ͷσόοά) • vim & ctags & Unite grep ... (ιʔείʔυϦʔσΟϯά) YOSHIKAWA Ryota hbstyle-2014-12-11 13

Slide 14

Slide 14 text

໾ʹཱͬͨ஌ݟ (1) • hbstyle Ͱͷ஌ݟ • @indigo13love ͞Μͷੲͷൃද • sql/slave.cc ΍ϨϓϦέʔγϣϯͷ஌ݟ • ܭࢉػՊֶͷ஌ݟ • MySQL Packet ͷϔομ͙Β͍ͳΒಡΊΔ • ֶ෦ͷ࣌ʹύέοτோΊͨ஌ݟ YOSHIKAWA Ryota hbstyle-2014-12-11 14

Slide 15

Slide 15 text

໾ʹཱͬͨ஌ݟ (2) • ιʔείʔυϦʔσΟϯάͷ஌ݟ • ֶ෦࢛೥Ͱ৭Μͳιϑτ΢ΣΞͷιʔείʔυಡΜͩ஌ݟ • Apache, Nginx, Keepalived, LVS ... • ϏϏΒͳ͍͜ͱ • ϩάͱಥ͖߹ΘͤͯಡΉ • πʔϧΛ࢖ָͬͯͯ͠ಡΉ YOSHIKAWA Ryota hbstyle-2014-12-11 15

Slide 16

Slide 16 text

[demo] πʔϧͷ࢖͍ํͱ͔ιʔεಡΈͱ͔ͪΐͬͱ͚ͩ YOSHIKAWA Ryota hbstyle-2014-12-11 16

Slide 17

Slide 17 text

൓ল YOSHIKAWA Ryota hbstyle-2014-12-11 17

Slide 18

Slide 18 text

Don't guess, measure! — Robert C. Pike YOSHIKAWA Ryota hbstyle-2014-12-11 18

Slide 19

Slide 19 text

൓ল • ਪଌ͢ΔͳɺܭଌͤΑ • ύϑΥʔϚϯε͚ͩͰ͸ͳ͘όά΋ಉ͡ • (૝ఆ಺ͷ)؆୯ͳόάΛ૬खʹͯ͠Δͱ๨ΕΔ͜ͱ͋Δ • ࣄ৅ɾݪҼ੾Γ෼͚ɾιʔείʔυ͕શͯ • ϩάΛݟΔɾιʔείʔυΛݟΔ YOSHIKAWA Ryota hbstyle-2014-12-11 19

Slide 20

Slide 20 text

• νϟοτͱ͔ͷݺͼ͔͚Ͱڠྗͯͩͬͨ͘͠͞օ͞Μ͋Γ͕ ͱ͏͍͟͝·ͨ͠ • ಛʹ @saito @takamoto @hosokoshi @matsuu ... • ΋ͬͱݕࡧྗ͕ߴ͚Ε͹ҰൃͰͨͲΓண͍͔ͨ΋͠Εͳ͍ • http://bugs.mysql.com/bug.php?id=67352 • ͱ͸͍͑ MySQL ʹͪΐͬͱৄ͘͠ͳΕͯΑ͔ͬͨ YOSHIKAWA Ryota hbstyle-2014-12-11 20