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

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

MySQL Casual Talks in Fukuoka vol.7 の発表資料です

0a98ad166f9cdf8d27d92c37438c6e9d?s=128

Manabu Matsuzaki

August 09, 2018
Tweet

Transcript

  1. 4.

    ࠓճ࢖༻ͨ͠όʔδϣϯ • 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͸bugfixόʔδϣϯ࣌ʹґଘϥΠϒϥϦͷbugfix͔͠औΓࠐ·ͳ͍ϙϦγʔͰ͢ • MyBatis Spring Boot Starter: 1.3.2 • MyBatis: 3.4.6
  2. 6.

    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
  3. 7.

    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
  4. 9.

    αϯϓϧιʔε • 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
  5. 10.

    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() } }
  6. 11.

    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 }
  7. 12.

    σϞ ͦͷ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
  8. 16.

    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
  9. 17.

    σϞ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
  10. 20.
  11. 21.
  12. 26.
  13. 33.

    1. CGLIBͰΤϯϋϯε͞ΕͨService ProxyΫϥεͷϝιου͕࣮ߦ͞ΕΔ 2. TransactionInterceptor͕ݺ͹ΕΔ 3. DataSource͔ΒίωΫγϣϯऔಘ & τϥϯβΫγϣϯ։࢝
 DataSourceTransactionManager#doBegin

    4. Ξϊςʔγϣϯ͔Βऩूͨ͠ReadOnlyଐੑΛConnectionͷReadOnlyʹઃఆ
 DataSourceUtils#prepareConnectionForTransaction ॲཧͷྲྀΕ (֓ཁ)