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.1k
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
IIWレポートからみるID業界で話題のMCP
fujie
0
740
菸酒生在 LINE Taiwan 的後端雙刀流
line_developers_tw
PRO
0
1.1k
解析の定理証明実践@Lean 4
dec9ue
0
100
OAuth/OpenID Connectで実現するMCPのセキュアなアクセス管理
kuralab
5
880
キャディでのApache Iceberg, Trino採用事例 -Apache Iceberg and Trino Usecase in CADDi--
caddi_eng
0
170
PostgreSQL 18 cancel request key長の変更とRailsへの関連
yahonda
0
110
VISITS_AIIoTビジネス共創ラボ登壇資料.pdf
iotcomjpadmin
0
150
Oracle Cloud Infrastructure:2025年6月度サービス・アップデート
oracle4engineer
PRO
2
140
Observability infrastructure behind the trillion-messages scale Kafka platform
lycorptech_jp
PRO
0
130
新卒3年目の後悔〜機械学習モデルジョブの運用を頑張った話〜
kameitomohiro
0
390
Prox Industries株式会社 会社紹介資料
proxindustries
0
210
米国国防総省のDevSecOpsライフサイクルをAWSのセキュリティサービスとOSSで実現
syoshie
2
820
Featured
See All Featured
Git: the NoSQL Database
bkeepers
PRO
430
65k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.5k
Being A Developer After 40
akosma
90
590k
Music & Morning Musume
bryan
46
6.6k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Statistics for Hackers
jakevdp
799
220k
We Have a Design System, Now What?
morganepeng
52
7.6k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
228
22k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
45
7.4k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
32
2.3k
Six Lessons from altMBA
skipperchong
28
3.8k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
31
1.2k
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 :)