transaction_replay – MariaDB MaxScale 2.3 read/write split 新機能

2019年2月19日2024年5月22日

本記事はMariaDB Corporationより寄稿された記事となります

はじめに

MaxScale 2.2 で導入された，自動failover(auto_failover)ではMHAなどの3rd partyツールを必要とせず，Masterでの障害発生時にSlaveをMasterに自動昇格させることができますが，failoverの際にクライアント/アプリケーションとの接続が途切れ，再接続の必要があるのが難点でした。
2018年12月にGAとなった MariaDB MaxScale 2.3 の readwritesplit(read/write split)モジュールではクライアントからデータベースへの接続が途切れない新機能(transaction_replay/master_reconnection)が追加されています。

Announcing MariaDB MaxScale 2.3 GA

今回はこの新機能について解説したいと思います。

master_reconnection

master_reconnection=true(有効)に設定すると，セッション途中で接続先のMasterノードが自動failoverしても接続が維持(再接続)されるようにします。なお，以下の条件があり，これらを満たすと再接続されます。なお，デフォルトでは無効(false)です。

すでにSlaveに接続されており，そのSlaveノードがMasterに昇格される
トランザクションがない
Autocommit が有効
LOAD DATA LOCAL INFILE が実行中でない
旧Masterノードにルーティングされているクエリがない

transaction_replay

Masterの障害等で途中で中断されたトランザクションを再実行します(デフォルトではfalse)。なお，transaction_replay=true(有効)とすると，delayed_retry と master_reconnection が自動的に有効となります。

トランザクション実行中のサーバに障害が発生すると，readwritesplit はトランザクションを Master に昇格したサーバで実行します。クライアント/アプリケーション側で再接続を行う必要はありません。

他の代替ノードが存在せず，delayed_retry_timeout で設定した時間内に代替ノードが見つからない場合，クライアントとの接続は閉じられます。

すべてのトランザクションが再実行されるわけではなく，以下のSQL文がトランザクションに含まれ，初回の部分的なザクションと同一の結果が得られる場合のみ，トランザクションが再実行されます。

INSERT
UPDATE
DELETE
SELECT
FOR UPDATE

例えば，トランザクション中で，NOW() や @@server_id 等が含まれている場合，同一の結果にはなりませんので，再実行されません。

master_reconnection の検証

簡単なテスト・スクリプトを用い，master_reconnection 有効/無効での挙動の違いを確認してみます。

テスト環境

以下の環境でテストを実施しました。

MariaDB MaxScale 2.3.3 x 1
MariaDB Server 10.3.12 x 3 (Master x 1 – Slave x 2)
CentOS 7.6.1810

テスト・スクリプト

MaxScale read/write split経由でINSERTを定期的に実行する以下のRubyスクリプトを用います。

#!/usr/bin/env ruby
require 'mysql2'

begin
  client = Mysql2::Client.new(host: '127.0.0.1', port: 3306, username: "maxuser", password: 'maxpwd')
rescue
  print "#$!: "
end

id = 0
while true do
  begin
    query = "INSERT INTO test.rwsplit VALUES (#{id}, now());"
    results = client.query(query)
  rescue
    print "error: #$!: "
  end
  puts query
  sleep 5
  id += 1
end

#!/usr/bin/env ruby

require 'mysql2'

begin

client = Mysql2::Client.new(host: '127.0.0.1', port: 3306, username: "maxuser", password: 'maxpwd')

rescue

print "#$!: "

end

id = 0

while true do

begin

query = "INSERT INTO test.rwsplit VALUES (#{id}, now());"

results = client.query(query)

rescue

print "error: #$!: "

end

puts query

sleep 5

id += 1

end

テスト・テーブル作成

use test;
create table rwsplit (id int primary key, ts timestamp);

1 2	use test; create table rwsplit (id int primary key, ts timestamp);

master_reconnection=false (デフォルト)の場合

まずは比較のため，/etc/maxscale.cnf の read/write split serviceセクションを以下のように設定し，master_reconnectionを無効(デフォルト)にしてテストを実行します。

# /etc/maxscale.cnf 
[Splitter-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxuser
password=maxpwd
# 2.3 features
#master_failure_mode=error_on_write
#master_reconnection=true
#transaction_replay=true

# /etc/maxscale.cnf

[Splitter-Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=maxuser

password=maxpwd

# 2.3 features

#master_failure_mode=error_on_write

#master_reconnection=true

#transaction_replay=true

テスト・スクリプト実行結果

INSERT INTO test.rwsplit VALUES (0, now());
INSERT INTO test.rwsplit VALUES (1, now());
INSERT INTO test.rwsplit VALUES (2, now());
INSERT INTO test.rwsplit VALUES (3, now());
INSERT INTO test.rwsplit VALUES (4, now());
error: Connection killed by MaxScale: Router could not recover from connection errors: INSERT INTO test.rwsplit VALUES (5, now());
error: MySQL server has gone away: INSERT INTO test.rwsplit VALUES (6, now());
error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (7, now());
error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (8, now());
error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (9, now());
error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (10, now());

INSERT INTO test.rwsplit VALUES (0, now());

INSERT INTO test.rwsplit VALUES (1, now());

INSERT INTO test.rwsplit VALUES (2, now());

INSERT INTO test.rwsplit VALUES (3, now());

INSERT INTO test.rwsplit VALUES (4, now());

error: Connection killed by MaxScale: Router could not recover from connection errors: INSERT INTO test.rwsplit VALUES (5, now());

error: MySQL server has gone away: INSERT INTO test.rwsplit VALUES (6, now());

error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (7, now());

error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (8, now());

error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (9, now());

error: MySQL client is not connected: INSERT INTO test.rwsplit VALUES (10, now());

(id=4でMasterを停止，自動failover)

id=4完了後にMasterノードを停止させると接続が切れ，そのまま接続が切れていますので，クライアント/アプリケーションでデータベースへの再接続を行う必要があります。

master_reconnection=true の場合

これに対して，/etc/maxscale.cnf の read/write split serviceセクションの設定を以下のように設定し，master_reconnection を有効にしてテストを再実行します。

# /etc/maxscale.cnf 
[Splitter-Service] 
type=service 
router=readwritesplit 
servers=server1,server2,server3 
user=maxuser 
password=maxpwd 
# 2.3 features 
master_failure_mode=error_on_write
master_reconnection=true 
transaction_replay=true

# /etc/maxscale.cnf

[Splitter-Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=maxuser

password=maxpwd

# 2.3 features

master_failure_mode=error_on_write

master_reconnection=true

transaction_replay=true

テスト・スクリプト実行結果

master_reconnection=false の場合と異なり，failover時にRuby mysql2 コネクタからのエラーメッセージは表示されていません。

INSERT INTO test.rwsplit VALUES (0, now());
INSERT INTO test.rwsplit VALUES (1, now());
INSERT INTO test.rwsplit VALUES (2, now());
INSERT INTO test.rwsplit VALUES (3, now());
INSERT INTO test.rwsplit VALUES (4, now());
INSERT INTO test.rwsplit VALUES (5, now());
INSERT INTO test.rwsplit VALUES (6, now());
INSERT INTO test.rwsplit VALUES (7, now());
INSERT INTO test.rwsplit VALUES (8, now());
INSERT INTO test.rwsplit VALUES (9, now());
INSERT INTO test.rwsplit VALUES (10, now());

INSERT INTO test.rwsplit VALUES (0, now());

INSERT INTO test.rwsplit VALUES (1, now());

INSERT INTO test.rwsplit VALUES (2, now());

INSERT INTO test.rwsplit VALUES (3, now());

INSERT INTO test.rwsplit VALUES (4, now());

INSERT INTO test.rwsplit VALUES (5, now());

INSERT INTO test.rwsplit VALUES (6, now());

INSERT INTO test.rwsplit VALUES (7, now());

INSERT INTO test.rwsplit VALUES (8, now());

INSERT INTO test.rwsplit VALUES (9, now());

INSERT INTO test.rwsplit VALUES (10, now());

(id=4でMasterを停止，自動failover)

INSERTされたデータの確認

MariaDB [test]&gt; select * from rwsplit;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  0 | 2019-02-04 14:19:36 |
|  1 | 2019-02-04 14:19:41 |
|  2 | 2019-02-04 14:19:46 |
|  3 | 2019-02-04 14:19:51 |
|  4 | 2019-02-04 14:19:56 |
|  5 | 2019-02-04 14:20:09 |
|  6 | 2019-02-04 14:20:14 |
|  7 | 2019-02-04 14:20:19 |
|  8 | 2019-02-04 14:20:24 |
|  9 | 2019-02-04 14:20:29 |
| 10 | 2019-02-04 14:20:34 |
| 11 | 2019-02-04 14:20:39 |
+----+---------------------+

MariaDB [test]> select * from rwsplit;

+----+---------------------+

| id | ts |

+----+---------------------+

| 0 | 2019-02-04 14:19:36 |

| 1 | 2019-02-04 14:19:41 |

| 2 | 2019-02-04 14:19:46 |

| 3 | 2019-02-04 14:19:51 |

| 4 | 2019-02-04 14:19:56 |

| 5 | 2019-02-04 14:20:09 |

| 6 | 2019-02-04 14:20:14 |

| 7 | 2019-02-04 14:20:19 |

| 8 | 2019-02-04 14:20:24 |

| 9 | 2019-02-04 14:20:29 |

| 10 | 2019-02-04 14:20:34 |

| 11 | 2019-02-04 14:20:39 |

+----+---------------------+

テスト・スクリプトでは5秒間隔でINSERTを行っていますが，id=4とid=5のレコード間では13秒間隔となっているものの，接続は途切れていません。

このときの MaxScale のログ，/var/log/maxscale/maxscale.log には以下のように記録されています。

2019-02-04 14:19:56   info   : (6) &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (4, now());
2019-02-04 14:19:56   info   : (6) Route query to master: server2       [192.168.2.102]:3306  [Down]
2019-02-04 14:19:59   warning: Master has failed. If master status does not change in 4 monitor passes, failover begins.
2019-02-04 14:20:01   info   : (6) &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:01   info   : (6) Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:02   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:02   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:03   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:03   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:04   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:04   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:05   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:05   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:06   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:06   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:07   notice : Selecting a server to promote and replace 'server2'. Candidates are: 'server1', 'server3'.
2019-02-04 14:20:07   notice : Selected 'server1'.
2019-02-04 14:20:07   notice : Performing automatic failover to replace failed master 'server2'.
2019-02-04 14:20:07   notice : Redirecting 'server3' to replicate from 'server1' instead of 'server2'.
2019-02-04 14:20:07   notice : All redirects successful.
2019-02-04 14:20:07   notice : All redirected slaves successfully started replication from 'server1'.
2019-02-04 14:20:07   info   : Failover: slave replication confirmation took 0.5 seconds with 89.5 seconds to spare.
2019-02-04 14:20:07   notice : Failover 'server2' -&gt; 'server1' performed.
2019-02-04 14:20:07   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:07   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:08   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:08   info   : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:09   notice : Server changed state: server1[192.168.2.101:3306]: new_master. [Slave, Running] -&gt; [Master, Running]
2019-02-04 14:20:09   info   : &gt; Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());
2019-02-04 14:20:09   info   : Replacing old master 'server2' with new master 'server1'
2019-02-04 14:20:09   info   : Connected to 'server1'
2019-02-04 14:20:09   info   : Route query to master: server1   [192.168.2.101]:3306 &lt;
2019-02-04 14:20:09   info   : (6) Reply complete, last reply from server1

2019-02-04 14:19:56 info : (6) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (4, now());

2019-02-04 14:19:56 info : (6) Route query to master: server2 [192.168.2.102]:3306 [Down]

2019-02-04 14:19:59 warning: Master has failed. If master status does not change in 4 monitor passes, failover begins.

2019-02-04 14:20:01 info : (6) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:01 info : (6) Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:02 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:02 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:03 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:03 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:04 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:04 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:05 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:05 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:06 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:06 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:07 notice : Selecting a server to promote and replace 'server2'. Candidates are: 'server1', 'server3'.

2019-02-04 14:20:07 notice : Selected 'server1'.

2019-02-04 14:20:07 notice : Performing automatic failover to replace failed master 'server2'.

2019-02-04 14:20:07 notice : Redirecting 'server3' to replicate from 'server1' instead of 'server2'.

2019-02-04 14:20:07 notice : All redirects successful.

2019-02-04 14:20:07 notice : All redirected slaves successfully started replication from 'server1'.

2019-02-04 14:20:07 info : Failover: slave replication confirmation took 0.5 seconds with 89.5 seconds to spare.

2019-02-04 14:20:07 notice : Failover 'server2' -> 'server1' performed.

2019-02-04 14:20:07 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:07 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:08 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:08 info : Delaying routing: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:09 notice : Server changed state: server1[192.168.2.101:3306]: new_master. [Slave, Running] -> [Master, Running]

2019-02-04 14:20:09 info : > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 48, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.rwsplit VALUES (5, now());

2019-02-04 14:20:09 info : Replacing old master 'server2' with new master 'server1'

2019-02-04 14:20:09 info : Connected to 'server1'

2019-02-04 14:20:09 info : Route query to master: server1 [192.168.2.101]:3306 <

2019-02-04 14:20:09 info : (6) Reply complete, last reply from server1

server2(Master)がダウンしたことが検知され，server1がMasterに昇格，その後クエリがserver1にルーティングされていることが確認できます。

まとめ

今回は MaxScale 2.3 で新たに導入された，master_reconnection および transaction_replay について解説いたしました。Master-Slave構成にて自動failover時にアプリケーション/クライアント側でデータベースからの切断を検知，再接続を行う必要がなく，アプリケーション側でデータベース・システム構成に依存したコードを書く必要がなくなると考えます。