mysqlfabricによる書き込みのスケール

by

MySQL Replicationは読み込みをスケールアウトする為の方法として多くのサイトで使われてきましたが、
マスターに関してもMySQL Fabricでスケールアウト出来るようになったので動作確認してみました。

特定のデータを分散する為に、Sharding Groupの作成
Shard 1

mysqlfabric sharding create_definition RANGE global
# mysqlfabric sharding list_definitions

mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

Shard2

mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

Shardingの定義を追加
test.T_shardingテーブルのid列で100番を境にマスターを分ける場合

mysqlfabric sharding add_table 1 test.T_sharding id
mysqlfabric sharding add_shard 1 shard1/1,shard2/100 --state=enabled

shardingの状況確認

-bash-4.2$ mysql -uroot -p -h127.0.0.1 -P63300 -e 'select * from fabric.shards'
+----------+----------+---------+
| shard_id | group_id | state   |
+----------+----------+---------+
|        1 | shard1   | ENABLED |
|        2 | shard2   | ENABLED |
+----------+----------+---------+
-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding list_tables RANGE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name      table_name global_group column_name
---------- --------- --------------- ------------ -----------
         1     RANGE test.T_sharding       global          id


-bash-4.2$

fabricグループ確認

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

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


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

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$

test.T_shardingをベースとしたsharding情報を確認

-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
       test T_sharding          id           1        1     RANGE   shard1       global
       test T_sharding          id         100        2     RANGE   shard2       global


-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 99
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 100
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$

実際に分散されたデータを確認してみる

-bash-4.2$ mysql -uroot -p -P63304 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63305 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63306 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63307 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63308 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63309 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$

マスターデータの分散がされていて、書き込みも分散出来る事が確認出来た。
これまでのMySQLレプリケーションは読み込みが中心のスケールアウト手法でしたが、
書き込みもFabricでスケールアウトするという選択肢が増えた。

connector