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.9k
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.4k
SREcon19 Americas 参加レポート #srefukuoka / srecon19-americas-report
matsumana
0
890
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
ソフトウェア エンジニアとしての 姿勢と心構え
recruitengineers
PRO
8
2k
JavaScript 研修
recruitengineers
PRO
4
430
kintone開発チームの紹介
cybozuinsideout
PRO
0
73k
ZOZOTOWNフロントエンドにおけるディレクトリの分割戦略
zozotech
PRO
18
5.5k
広島発!スタートアップ開発の裏側
tsankyo
0
250
Goss: New Production-Ready Go Binding for Faiss #coefl_go_jp
bengo4com
0
1.1k
AIエージェントの開発に必須な「コンテキスト・エンジニアリング」とは何か──プロンプト・エンジニアリングとの違いを手がかりに考える
masayamoriofficial
0
420
新規案件の立ち上げ専門チームから見たAI駆動開発の始め方
shuyakinjo
0
150
Oracle Base Database Service:サービス概要のご紹介
oracle4engineer
PRO
2
20k
モダンな現場と従来型の組織——そこに生じる "不整合" を解消してこそチームがパフォーマンスを発揮できる / Team-oriented Organization Design 20250825
mtx2s
6
650
Goでマークダウンの独自記法を実装する
lag129
0
220
Product Management Conference -AI時代に進化するPdM-
kojima111
0
220
Featured
See All Featured
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.4k
Embracing the Ebb and Flow
colly
87
4.8k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
367
26k
Building Adaptive Systems
keathley
43
2.7k
Build The Right Thing And Hit Your Dates
maggiecrowley
37
2.8k
What's in a price? How to price your products and services
michaelherold
246
12k
The Language of Interfaces
destraynor
160
25k
Git: the NoSQL Database
bkeepers
PRO
431
65k
Scaling GitHub
holman
462
140k
Optimising Largest Contentful Paint
csswizardry
37
3.4k
Optimizing for Happiness
mojombo
379
70k
Rails Girls Zürich Keynote
gr2m
95
14k
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 :)