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
890
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
560
ユーザ目線でのPrometheus #mackerel_ug /monitoring-prometheus
matsumana
1
3.6k
Other Decks in Technology
See All in Technology
KubeCon + CloudNativeCon Japan 2025 に行ってきた! & containerd の新機能紹介
honahuku
0
120
Zephyr RTOSを使った開発コンペに参加した件
iotengineer22
1
190
Understanding_Thread_Tuning_for_Inference_Servers_of_Deep_Models.pdf
lycorptech_jp
PRO
0
160
SmartNewsにおける 1000+ノード規模 K8s基盤 でのコスト最適化 – Spot・Gravitonの大規模導入への挑戦
vsanna2
0
110
Lambda Web Adapterについて自分なりに理解してみた
smt7174
5
150
生成AI活用の組織格差を解消する 〜ビジネス職のCursor導入が開発効率に与えた好循環〜 / Closing the Organizational Gap in AI Adoption
upamune
6
4.9k
ビズリーチが挑む メトリクスを活用した技術的負債の解消 / dev-productivity-con2025
visional_engineering_and_design
2
5.3k
Backlog ユーザー棚卸しRTA、多分これが一番早いと思います
__allllllllez__
1
120
Tokyo_reInforce_2025_recap_iam_access_analyzer
hiashisan
0
170
AWS認定を取る中で感じたこと
siromi
1
150
AI導入の理想と現実~コストと浸透〜
oprstchn
0
180
怖くない!はじめてのClaude Code
shinya337
0
340
Featured
See All Featured
BBQ
matthewcrist
89
9.7k
Imperfection Machines: The Place of Print at Facebook
scottboms
267
13k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
46
9.6k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
How STYLIGHT went responsive
nonsquared
100
5.6k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
15
1.5k
Docker and Python
trallard
44
3.5k
[RailsConf 2023] Rails as a piece of cake
palkan
55
5.6k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
125
52k
Done Done
chrislema
184
16k
Adopting Sorbet at Scale
ufuk
77
9.4k
A better future with KSS
kneath
239
17k
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 :)