Connector/JでMaster/Slave Replication構成のMySQLに接続する #mysql_casual_fukuoka /connector-j-master-slave-replication

Slide 1

Slide 1 text

Connector/JͰ Master/Slave Replicationߏ੒ͷ MySQLʹ઀ଓ͢Δ MySQL Casual Talks in Fukuoka vol.7 2018/08/09 @matsumana

Slide 2

Slide 2 text

ࣗݾ঺հ • ໊લɿ দ࡚ ֶ • ॴଐɿ LINE Fukuokaגࣜձࣾ  ։ൃ3ࣨ • Roleɿ SRE • Twitterɿ @matsumana

Slide 3

Slide 3 text

ΞδΣϯμ • Connector/JͷMaster/Slave Replication઀ଓػೳͷ঺հ • Spring BootΞϓϦ͔Β࢖ͬͯΈΔ

Slide 4

Slide 4 text

ࠓճ࢖༻ͨ͠όʔδϣϯ • MySQL: 5.7.22 • Spring Boot: 2.0.3 • Flyway: 5.0.7 • HikariCP: 2.7.9 • MySQL Connector/J: 5.1.46  Oracleࣾ͸8.0΁ͷҠߦΛڧ͘ਪ঑͍ͯ͠·͢  Spring Bootͷmasterϒϥϯν͸8.0.11ʹҠߦࡁͳͷͰɺSpring Boot 2.1ͰҠߦ͢ΔΈ͍ͨͰ͢  Spring Boot͸bugﬁxόʔδϣϯ࣌ʹґଘϥΠϒϥϦͷbugﬁx͔͠औΓࠐ·ͳ͍ϙϦγʔͰ͢ • MyBatis Spring Boot Starter: 1.3.2 • MyBatis: 3.4.6

Slide 5

Slide 5 text

Connector/Jͷ Master/Slave Replication઀ଓػೳ ͷ঺հ

Slide 6

Slide 6 text

Connector/JͷMulti-Host Connections͸͍͔ͭ͋͘Δ • Failover    • Load Balancing    • Master/Slave Replication ← ࠓ೔͸͜Ε͚ͩ  https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-multi-host-connections.html

Slide 7

Slide 7 text

Master/Slave Replicationͷಛ௃ • ҎԼͷΑ͏ͳ઀ଓURLΛઃఆ͓ͯ͘͠ͱɺconnection͕ReadOnlyͩͬͨ৔߹SQLΛSlaveʹ౤͛ͯ͘ΕΔ    • Slave͸ෳ਺ࢦఆՄೳͰɺϩʔυόϥϯε͞ΕΔ • Multiple-Master ReplicatonରԠ  • ެࣜυΩϡϝϯτʹγϯϓϧͳαϯϓϧιʔε͕͋Γ·͢  https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-master-slave-replication-connection.html

Slide 8

Slide 8 text

Spring BootΞϓϦ͔Β ࢖ͬͯΈΔ

Slide 9

Slide 9 text

αϯϓϧιʔε • source repo (CLIΞϓϦ):  https://github.com/matsumana/mysql-jdbc-replication-demo • Application software stack: • Docker (MySQLͷMaster/SlaveϨϓϦέʔγϣϯ؀ڥΛϩʔΧϧʹߏங) • Spring Boot • HikariCP • MyBatis • MySQL Connector/J • Flyway

Slide 10

Slide 10 text

ServiceΫϥε @Service @Transactional(readOnly = true) class TodoService(val repository: TodoRepository) { @Transactional(readOnly = false) fun insert(todo: Todo) { repository.insert(todo) }   fun selectFromSlave(): Todo { return repository.select() } }

Slide 11

Slide 11 text

RepositoryΠϯλϑΣʔε (MyBatis) @Mapper interface TodoRepository {   @Insert(""" INSERT INTO `todo` (`title`, `detail`, `finished`)  VALUES  (#{title}, #{detail}, #{finished}) """) @Options(useGeneratedKeys = true) fun insert(todo: Todo) // Ambiguous Groups @Select(""" SELECT MAX(`id`) AS `id`, `title`, `detail`, `finished` FROM `todo` """) fun select(): Todo }

Slide 12

Slide 12 text

σϞ ͦͷ1 TransactionalΞϊςʔγϣϯͷreadOnlyଐੑʹΑͬͯ SQL͕MasterͱSlaveʹ౤͛෼͚ΒΕΔ༷ࢠΛݟͯΈΔ • long_query_time=0 ʹઃఆ͍ͯ͠ΔͷͰશͯͷSQL͕slow logʹग़ྗ͞ΕΔ • ࠓճͷαϯϓϧΞϓϦͰ͸slow logϑΝΠϧΛϗετOS͔Β֬ೝͰ͖Δ • ./docker-volumes/mysql/master/slow-log/slow.log • ./docker-volumes/mysql/slave/slow-log/slow.log • HikariCPͷconnectionInitSqlʹ "kamipo TRADITIONAL" Λઃఆ͍ͯ͠·͢ • http://www.songmu.jp/riji/entry/2015-07-08-kamipo-traditional.html

Slide 13

Slide 13 text

Master • ϓʔϧ͞Ε͍ͯΔConnectionຖʹHikariCPͷconnectionInitSql͕࣮ߦ͞Ε͍ͯΔ  → ૝ఆͲ͓Γͷڍಈ • INSERT͸࣮ߦ͞Ε͍ͯΔ͕ɺSELECT͸࣮ߦ͞Ε͍ͯͳ͍  → ૝ఆͲ͓Γͷڍಈ

Slide 14

Slide 14 text

Slave • HikariCPͷconnectionInitSql͕શ࣮͘ߦ͞Ε͍ͯͳ͍  → ? • SELECT͸࣮ߦ͞Ε͍ͯΔ͕ɺINSERT͸࣮ߦ͞Ε͍ͯͳ͍  → ૝ఆͲ͓Γͷڍಈ

Slide 15

Slide 15 text

ͳͥSlaveʹରͯ͠ HikariCPͷconnectionInitSql͕ ࣮ߦ͞Ε͍ͯͳ͍ͷ͔ʁ

Slide 16

Slide 16 text

HikariCPͱConnector/JͷιʔεΛಡΜͰΈͨ • HikariCPͷ֘౰Օॴ  https://github.com/brettwooldridge/HikariCP/blob/HikariCP-2.7.9/src/main/java/com/zaxxer/hikari/pool/PoolBase.java#L426 • connectionInitSql͸Master/Slave ReplicationΛҙ࣮ࣝͨ͠૷ʹ͸ͳ͍ͬͯͳ͍ͷͰ  connectionInitSql͸Masterʹରͯ͠ͷΈ࣮ߦ͍ͯ͠Δ • Connector/JͰSQL࣮ߦର৅ϊʔυ͕ܾఆ͞ΕΔͷͰɺ  HikariCPͷϨΠϠʔͰશͯͷϊʔυʹରͯ͠connectionInitSqlΛ  ࿙Εͳ࣮͘ߦ͢Δͷ͸೉͍͠ͱࢥΘΕ·͢ • Connector/Jͷ֘౰Օॴ  https://github.com/mysql/mysql-connector-j/blob/5.1.46/src/com/mysql/jdbc/ReplicationConnectionProxy.java#L49 • ReplicationConnectionProxyͷreadOnlyσϑΥϧτ஋͸false

Slide 17

Slide 17 text

σϞ1ͷ·ͱΊ • Master/Slave Replication઀ଓͰHikariCPͷconnectionInitSqlΛ࢖͏৔߹͸஫ҙ͕ඞཁ • connectionInitSqlʹઃఆͨ͠SQL͸Masterʹ͔࣮͠ߦ͞Εͳ͍ • sql_modeΛઃఆ͍ͨ͠৔߹͸ɺ  HikariCPͷconnectionInitSqlͰ͸ͳ͘MySQLαʔόͷmy.cnfͰ΍Γ·͠ΐ͏ • ิ଍ɿΞϯϏΪϡΞεάϧʔϓʢᐆດͳάϧʔϓʣ  SQLΞϯνύλʔϯ - ։ൃऀΛ଴ͪड͚Δ25ͷམͱ݀͠ (֦େ൛)  https://www.slideshare.net/t_wada/sql-antipatterns-digest/59

Slide 18

Slide 18 text

σϞ ͦͷ2 HikariCPͰϓʔϧ͞Ε͍ͯΔConnection਺ͱ MasterͱSalveͦΕͧΕͷ࣮ࡍͷConnection਺Λ֬ೝͯ͠ΈΔ

Slide 19

Slide 19 text

• HikariCPͷmaximumPoolSizeσϑΥϧτ஋͸10 ϓʔϧ͞Ε͍ͯΔConnection਺ͷϝτϦΫε

Slide 20

Slide 20 text

Master

Slide 21

Slide 21 text

Slave

Slide 22

Slide 22 text

σϞ2ͷ·ͱΊ • ྫ͑͹ɺHikariCPͷϝτϦΫεͰɺϓʔϧ͍ͯ͠Δ઀ଓ਺͕10ͱͳ͍ͬͯΔঢ়ଶͩͱɺ Master/Slaveͷશϊʔυʹରͯ͠10ͣͭ઀ଓ͕ுΒΕ͍ͯΔ • େ͖ͳ໰୊͸͋Γ·ͤΜ͕ɺҰԠ͓֮͑ͯ͘ํ͕ྑͦ͞͏Ͱ͢

Slide 23

Slide 23 text

σϞ2ͷٙ໰఺ • ϓʔϧ͞Ε͍ͯΔશͯͷConnection1ͭ1͕ͭMaster/Slaveશϊʔυ΁  ઀ଓ͍ͯ͠Δͱ͍͏ࣄʹͳΔ͕ɺ࣮૷͸Ͳ͏ͳ͍ͬͯΔʁ

Slide 24

Slide 24 text

࠶ͼιʔεΛಡΜͰΈͨ

Slide 25

Slide 25 text

σϞ2ͷٙ໰఺ (݁࿦) • SQLΛ࣮ߦ͢Δ౓ʹTransactionΞϊςʔγϣϯͷreadOnlyଐੑΛ࢖ͬͯ  SQLͷ࣮ߦઌΛ”౎౓”ܾఆ͍ͯ͠Δ • SQL࣮ߦઌͷ੾Γସ͑ϩδοΫ͕࣮૷͞Ε͍ͯΔͷ͸ɺ  Connector/JͷReplicationConnectionProxy#setReadOnly

Slide 26

Slide 26 text

Ϋϥεਤ

Slide 27

Slide 27 text

• HikariCPͷProxyConnection#setReadOnlyܦ༝ͰReadOnlyϑϥά͕ηοτ͞ΕΔ • ͍͔ͭ͘ͷܧঝͱҕৡΛܦͯɺConnector/JͷReplicationConnectionProxy#setReadOnly ͕ݺ͹ΕɺcorrentConnectionϑΟʔϧυͱͯ͠อ͍࣋ͯ͠ΔSQL࣮ߦઌ͕੾ΓସΘΔ ֓ཁ

Slide 28

Slide 28 text

͍ͭͰʹɺSpring͕HikariCPͷ ProxyConnection#setReadOnly ΛݺͿͱ͜Ζͷιʔε΋ ಡΜͰΈͨ

Slide 29

Slide 29 text

େ͖̎ͭ͘ʹ෼͚ͯઆ໌͠·͢ • TransactionΞϊςʔγϣϯ৘ใऩू • ServiceΫϥεͷϝιουݺͼग़͠ޙɺ  HikariCPͷProxyConnection#setReadOnly͕ݺ͹ΕΔ·Ͱ

Slide 30

Slide 30 text

1. TransactionΞϊςʔγϣϯ৘ใऩू

Slide 31

Slide 31 text

1. ΞϓϦέʔγϣϯىಈ 2. Beanੜ੒ 2-1. AOPॲཧ 2-1-1. TransactionΞϊςʔγϣϯ৘ใऩू  SpringTransactionAnnotationParser#parseTransactionAnnotation 2-1-2. ͦͷଞͷAOPॲཧ ॲཧͷྲྀΕ (֓ཁ)

Slide 32

Slide 32 text

2. ServiceΫϥεͷϝιουݺͼग़͠ޙ HikariCPͷ ProxyConnection#setReadOnly͕  ݺ͹ΕΔ·Ͱ

Slide 33

Slide 33 text

1. CGLIBͰΤϯϋϯε͞ΕͨService ProxyΫϥεͷϝιου͕࣮ߦ͞ΕΔ 2. TransactionInterceptor͕ݺ͹ΕΔ 3. DataSource͔ΒίωΫγϣϯऔಘ & τϥϯβΫγϣϯ։࢝  DataSourceTransactionManager#doBegin 4. Ξϊςʔγϣϯ͔Βऩूͨ͠ReadOnlyଐੑΛConnectionͷReadOnlyʹઃఆ  DataSourceUtils#prepareConnectionForTransaction ॲཧͷྲྀΕ (֓ཁ)

Slide 34

Slide 34 text

5. ServiceΫϥεͷϝιου࣮ߦ 6. Commit or Rollback 7. Connectionͷঢ়ଶΛϦηοτ (ReadOnly=falseʹ໭͢)  DataSourceUtils#resetConnectionAfterTransaction ॲཧͷྲྀΕ (֓ཁ)

Slide 35

Slide 35 text

·ͱΊ • Connector/JͷMaster/Slave Replication઀ଓػೳΛ͝঺հ͠·ͨ͠ • Master/Slave Replication઀ଓͰ͸ɺHikariCPͷconnectionInitSql͸Masterʹ͔͠  ࣮ߦ͞Εͳ͍ࣄ͕Θ͔Γ·ͨ͠ • Connector/J͕Ͳ͏΍ͬͯMaster/SlaveΛ੾Γସ͍͑ͯΔ͔ௐ΂·ͨ͠ • SpringͷServiceΫϥεʹઃఆͨ͠TransactionΞϊςʔγϣϯͷreadOnlyଐੑ͕ͲͷΑ͏ ʹͯ͠ConnectionͷreadOnlyଐੑʹηοτ͞ΕΔͷ͔ௐ΂·ͨ͠

Slide 36

Slide 36 text

Thank you :)