MySQL Fabricつらい(FABRIC_OPT_MODE = roはスレーブだけを見に行くわけじゃない)

mysqlコマンドラインクライアントにHA切り替え機能を実装する編 でいじったコマンドラインクライアントでちょこちょこいじってたらなんか不思議な挙動に辿り着く。

mysql> \F ro
Current FABRIC_OPT_DEFAULT_MODE is ro

mysql> SELECT @@port, @@read_only;
+--------+-------------+
| @@port | @@read_only |
+--------+-------------+
|  20886 |           0 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT @@port, @@read_only;
+--------+-------------+
| @@port | @@read_only |
+--------+-------------+
|  20886 |           0 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT @@port, @@read_only;
+--------+-------------+
| @@port | @@read_only |
+--------+-------------+
|  20887 |           1 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT @@port, @@read_only;
+--------+-------------+
| @@port | @@read_only |
+--------+-------------+
|  20887 |           1 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT @@port, @@read_only;
+--------+-------------+
| @@port | @@read_only |
+--------+-------------+
|  20886 |           0 |
+--------+-------------+
1 row in set (0.00 sec)

マスターとスレーブの間で分散してる。

mysql_options(&mysql, FABRIC_OPT_DEFAULT_MODE, "ro")を設定して10回mysql_queryを叩くプログラムで試してみると

$ ./a.out
Using Fabric for MYSQL connection
port: 20887, read_only: 1
port: 20886, read_only: 0
port: 20887, read_only: 1
port: 20887, read_only: 1
port: 20887, read_only: 1
port: 20887, read_only: 1
port: 20886, read_only: 0
port: 20887, read_only: 1
port: 20887, read_only: 1
port: 20887, read_only: 1

やっぱり分散してる。

$ mysqlfabric group lookup_servers my_first_fabric
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0

Fabric内部のweightとやらは1:1になっているのでそうなのか(にしては、スレーブに偏りすぎてる気がするんだけど 母数が足りなかっただけで、1000回に増やしたらまあこんなもんだろうって感じだった)

$ ./a.out | sort | uniq -c
    528 port: 20886, read_only: 0
    472 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

$ ./a.out | sort | uniq -c
    505 port: 20886, read_only: 0
    495 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

$ ./a.out | sort | uniq -c
    499 port: 20886, read_only: 0
    501 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

スレーブ側のweightを10000まで上げてみる。

$ mysqlfabric group lookup_servers my_first_fabric
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0


$ mysqlfabric server set_weight 4b7036a9-63fc-11e4-942e-fa163e020fd0 10000
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
4ca2ef7d-3770-4beb-b557-55ea025b18bf        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41619e+09 Triggered by <mysql.fabric.events.Event object at 0x1102610>.
    4       2   1.41619e+09                        Executing action (_set_server_weight).
    5       2   1.41619e+09                         Executed action (_set_server_weight).


$ mysqlfabric group lookup_servers my_first_fabric
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode  weight
------------------------------------ --------------- --------- ---------- -------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE     1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY 10000.0


$ ./a.out | sort | uniq -c
   1000 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

$ ./a.out | sort | uniq -c
   1000 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

$ ./a.out | sort | uniq -c
      1 port: 20886, read_only: 0
    999 port: 20887, read_only: 1
      1 Using Fabric for MYSQL connection

うん、よさげ。スレーブのweightを1のままでマスターのweight 0の方がいいのかしらんと思ったけど、weightを0にするのはダメみたい。

$ mysqlfabric server set_weight 47cf54df-63fc-11e4-942e-fa163e020fd0 0
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Cannot set the server's weight (0.0) to a value lower than or equal to 0.0

当たり前ながら、どれだけスレーブ側にweightを寄せてもFABRIC_OPT_MODE = rwなら必ずマスターを見に行く。

$ ./a.out | sort | uniq -c
   1000 port: 20886, read_only: 0
      1 Using Fabric for MYSQL connection

まだまだ検証要りそうだねぇ。。