Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Connector/JでMaster/Slave Replication構成のMySQLに接続...
Search
Manabu Matsuzaki
August 09, 2018
Technology
0
1.5k
Connector/JでMaster/Slave Replication構成のMySQLに接続する #mysql_casual_fukuoka /connector-j-master-slave-replication
MySQL Casual Talks in Fukuoka vol.7 の発表資料です
Manabu Matsuzaki
August 09, 2018
Tweet
Share
More Decks by Manabu Matsuzaki
See All by Manabu Matsuzaki
Spring BootユーザのためのArmeria入門 #jsug / Introduce to Armeria for Spring users
matsumana
0
2.8k
Canary Release with Argo Rollouts #ふくばねてす / canary-release-with-argo-rollouts
matsumana
1
1.2k
Getting started Central Dogma with Golang #fukuokago #umedago / getting-started-central-dogma-with-golang
matsumana
0
900
Micrometer入門 #javaq / introduce-to-micrometer
matsumana
1
2.9k
ArmeriaとCentral Dogmaから学ぶ、マイクロサービスに必要な機能 #edayfuk / lean-from-armeria-and-central-dogma
matsumana
0
4.3k
SREcon19 Americas 参加レポート #srefukuoka / srecon19-americas-report
matsumana
0
880
SRE入門 & チームで取り組んでいるSRE #srefukuoka / introduce-to-sre
matsumana
0
1.3k
Introduce to Armeria and Central Dogma #GWD_Nulab / introduce-to-armeria-and-central-dogma
matsumana
0
570
ユーザ目線でのPrometheus #mackerel_ug /monitoring-prometheus
matsumana
1
3.6k
Other Decks in Technology
See All in Technology
With Devin -AIの自律とメンバーの自立
kotanin0
2
1.1k
AIに目を奪われすぎて、周りの困っている人間が見えなくなっていませんか?
cap120
1
380
【CEDEC2025】『ウマ娘 プリティーダービー』における映像制作のさらなる高品質化へ!~ 豊富な素材出力と制作フローの改善を実現するツールについて~
cygames
PRO
0
220
SAE J1939シミュレーション環境構築
daikiokazaki
1
220
多様なニーズに応える Movable Type ラインナップ 全紹介
masakah
0
120
AI時代の知識創造 ─GeminiとSECIモデルで読み解く “暗黙知”と創造の境界線
nyagasan
0
190
Findy Freelance 利用シーン別AI活用例
ness
0
260
データエンジニアがクラシルでやりたいことの現在地
gappy50
3
840
【2025 Japan AWS Jr. Champions Ignition】点から線、線から面へ〜僕たちが起こすコラボレーション・ムーブメント〜
amixedcolor
1
120
Amazon Q Developerを活用したアーキテクチャのリファクタリング
k1nakayama
1
130
大規模イベントに向けた ABEMA アーキテクチャの遍歴 ~ Platform Strategy 詳細解説 ~
nagapad
0
180
마라톤 끝의 단거리 스퍼트: 2025년의 AI
inureyes
PRO
1
630
Featured
See All Featured
Code Reviewing Like a Champion
maltzj
524
40k
Site-Speed That Sticks
csswizardry
10
750
4 Signs Your Business is Dying
shpigford
184
22k
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.9k
Designing for humans not robots
tammielis
253
25k
The Cost Of JavaScript in 2023
addyosmani
51
8.7k
How STYLIGHT went responsive
nonsquared
100
5.7k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
182
54k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
34
6k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
130
19k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
161
15k
Visualization
eitanlees
146
16k
Transcript
Connector/JͰ Master/Slave Replicationߏͷ MySQLʹଓ͢Δ MySQL Casual Talks in Fukuoka vol.7
2018/08/09 @matsumana
ࣗݾհ • ໊લɿ দ࡚ ֶ • ॴଐɿ LINE Fukuokaגࣜձࣾ ։ൃ3ࣨ
• Roleɿ SRE • Twitterɿ @matsumana
ΞδΣϯμ • Connector/JͷMaster/Slave Replicationଓػೳͷհ • Spring BootΞϓϦ͔ΒͬͯΈΔ
ࠓճ༻ͨ͠όʔδϣϯ • 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 Bootbugfixόʔδϣϯ࣌ʹґଘϥΠϒϥϦͷbugfix͔͠औΓࠐ·ͳ͍ϙϦγʔͰ͢ • MyBatis Spring Boot Starter: 1.3.2 • MyBatis: 3.4.6
Connector/Jͷ Master/Slave Replicationଓػೳ ͷհ
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
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
Spring BootΞϓϦ͔Β ͬͯΈΔ
αϯϓϧιʔε • 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
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() } }
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 }
σϞ ͦͷ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
Master • ϓʔϧ͞Ε͍ͯΔConnectionຖʹHikariCPͷconnectionInitSql͕࣮ߦ͞Ε͍ͯΔ → ఆͲ͓Γͷڍಈ • INSERT࣮ߦ͞Ε͍ͯΔ͕ɺSELECT࣮ߦ͞Ε͍ͯͳ͍ → ఆͲ͓Γͷڍಈ
Slave • HikariCPͷconnectionInitSql͕શ࣮͘ߦ͞Ε͍ͯͳ͍ → ? • SELECT࣮ߦ͞Ε͍ͯΔ͕ɺINSERT࣮ߦ͞Ε͍ͯͳ͍ → ఆͲ͓Γͷڍಈ
ͳͥSlaveʹରͯ͠ HikariCPͷconnectionInitSql͕ ࣮ߦ͞Ε͍ͯͳ͍ͷ͔ʁ
HikariCPͱConnector/JͷιʔεΛಡΜͰΈͨ • HikariCPͷ֘Օॴ https://github.com/brettwooldridge/HikariCP/blob/HikariCP-2.7.9/src/main/java/com/zaxxer/hikari/pool/PoolBase.java#L426 • connectionInitSqlMaster/Slave ReplicationΛҙ࣮ࣝͨ͠ʹͳ͍ͬͯͳ͍ͷͰ connectionInitSqlMasterʹରͯ͠ͷΈ࣮ߦ͍ͯ͠Δ • 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
σϞ1ͷ·ͱΊ • Master/Slave ReplicationଓͰHikariCPͷconnectionInitSqlΛ͏߹ҙ͕ඞཁ • connectionInitSqlʹઃఆͨ͠SQLMasterʹ͔࣮͠ߦ͞Εͳ͍ • sql_modeΛઃఆ͍ͨ͠߹ɺ HikariCPͷconnectionInitSqlͰͳ͘MySQLαʔόͷmy.cnfͰΓ·͠ΐ͏ •
ิɿΞϯϏΪϡΞεάϧʔϓʢᐆດͳάϧʔϓʣ SQLΞϯνύλʔϯ - ։ൃऀΛͪड͚Δ25ͷམͱ݀͠ (֦େ൛) https://www.slideshare.net/t_wada/sql-antipatterns-digest/59
σϞ ͦͷ2 HikariCPͰϓʔϧ͞Ε͍ͯΔConnectionͱ MasterͱSalveͦΕͧΕͷ࣮ࡍͷConnectionΛ֬ೝͯ͠ΈΔ
• HikariCPͷmaximumPoolSizeσϑΥϧτ10 ϓʔϧ͞Ε͍ͯΔConnectionͷϝτϦΫε
Master
Slave
σϞ2ͷ·ͱΊ • ྫ͑ɺHikariCPͷϝτϦΫεͰɺϓʔϧ͍ͯ͠Δଓ͕10ͱͳ͍ͬͯΔঢ়ଶͩͱɺ Master/Slaveͷશϊʔυʹରͯ͠10ͣͭଓ͕ுΒΕ͍ͯΔ • େ͖ͳ͋Γ·ͤΜ͕ɺҰԠ͓֮͑ͯ͘ํ͕ྑͦ͞͏Ͱ͢
σϞ2ͷٙ • ϓʔϧ͞Ε͍ͯΔશͯͷConnection1ͭ1͕ͭMaster/Slaveશϊʔυ ଓ͍ͯ͠Δͱ͍͏ࣄʹͳΔ͕ɺ࣮Ͳ͏ͳ͍ͬͯΔʁ
࠶ͼιʔεΛಡΜͰΈͨ
σϞ2ͷٙ (݁) • SQLΛ࣮ߦ͢ΔʹTransactionΞϊςʔγϣϯͷreadOnlyଐੑΛͬͯ SQLͷ࣮ߦઌΛ””ܾఆ͍ͯ͠Δ • SQL࣮ߦઌͷΓସ͑ϩδοΫ͕࣮͞Ε͍ͯΔͷɺ Connector/JͷReplicationConnectionProxy#setReadOnly
Ϋϥεਤ
• HikariCPͷProxyConnection#setReadOnlyܦ༝ͰReadOnlyϑϥά͕ηοτ͞ΕΔ • ͍͔ͭ͘ͷܧঝͱҕৡΛܦͯɺConnector/JͷReplicationConnectionProxy#setReadOnly ͕ݺΕɺcorrentConnectionϑΟʔϧυͱͯ͠อ͍࣋ͯ͠ΔSQL࣮ߦઌ͕ΓସΘΔ ֓ཁ
͍ͭͰʹɺSpring͕HikariCPͷ ProxyConnection#setReadOnly ΛݺͿͱ͜Ζͷιʔε ಡΜͰΈͨ
େ͖̎ͭ͘ʹ͚ͯઆ໌͠·͢ • TransactionΞϊςʔγϣϯใऩू • ServiceΫϥεͷϝιουݺͼग़͠ޙɺ HikariCPͷProxyConnection#setReadOnly͕ݺΕΔ·Ͱ
1. TransactionΞϊςʔγϣϯใऩू
1. ΞϓϦέʔγϣϯىಈ 2. Beanੜ 2-1. AOPॲཧ 2-1-1. TransactionΞϊςʔγϣϯใऩू SpringTransactionAnnotationParser#parseTransactionAnnotation 2-1-2.
ͦͷଞͷAOPॲཧ ॲཧͷྲྀΕ (֓ཁ)
2. ServiceΫϥεͷϝιουݺͼग़͠ޙ HikariCPͷ ProxyConnection#setReadOnly͕ ݺΕΔ·Ͱ
1. CGLIBͰΤϯϋϯε͞ΕͨService ProxyΫϥεͷϝιου͕࣮ߦ͞ΕΔ 2. TransactionInterceptor͕ݺΕΔ 3. DataSource͔ΒίωΫγϣϯऔಘ & τϥϯβΫγϣϯ։࢝ DataSourceTransactionManager#doBegin
4. Ξϊςʔγϣϯ͔Βऩूͨ͠ReadOnlyଐੑΛConnectionͷReadOnlyʹઃఆ DataSourceUtils#prepareConnectionForTransaction ॲཧͷྲྀΕ (֓ཁ)
5. ServiceΫϥεͷϝιου࣮ߦ 6. Commit or Rollback 7. Connectionͷঢ়ଶΛϦηοτ (ReadOnly=falseʹ͢) DataSourceUtils#resetConnectionAfterTransaction
ॲཧͷྲྀΕ (֓ཁ)
·ͱΊ • Connector/JͷMaster/Slave ReplicationଓػೳΛ͝հ͠·ͨ͠ • Master/Slave ReplicationଓͰɺHikariCPͷconnectionInitSqlMasterʹ͔͠ ࣮ߦ͞Εͳ͍ࣄ͕Θ͔Γ·ͨ͠ • Connector/J͕Ͳ͏ͬͯMaster/SlaveΛΓସ͍͑ͯΔ͔ௐ·ͨ͠
• SpringͷServiceΫϥεʹઃఆͨ͠TransactionΞϊςʔγϣϯͷreadOnlyଐੑ͕ͲͷΑ͏ ʹͯ͠ConnectionͷreadOnlyଐੑʹηοτ͞ΕΔͷ͔ௐ·ͨ͠
Thank you :)