UCO-Tech（MySQL Fabricデータシャーディング編）

コメントする
このエントリーをはてなブックマークに追加

MySQL Fabric検証シリーズ第5弾。
今回はデータシャーディング編。

データシャーディングとは、
あるテーブルのデータを複数に分割し、
複数のテーブルやサーバに分割して保存することである。
MySQLにおけるデータシャーディングには、
書き込み負荷分散ができるという大きなメリットがある。

MySQLの冗長構成では、shared nothing型のアーキテクチャを取る。
すなわち、たとえばMaster/Slave1台ずつの構成であれば、
MasterとSlaveそれぞれに別個のデータディスクを持ち、
データディスクを共有して利用する、ということはない。
これはRDBMSのデファクトスタンダードであるOracle Databaseにおいて
Real Application Cluster（RAC）が共有ディスクを持つ点と異なる。
このため、データを複数のサーバ（シャードという）に分割して持つことは、
異なるディスクに分割してテーブルのデータを持つことを意味し、
分割のポリシーを適切に設定すれば書き込み負荷（I/Oの観点で）の低減につながる。

データシャーディングのメリットが見えてきたところで、
MySQL Fabricでのデータシャーディング設定の流れを確認する。

なお、下記の検証での環境は下記構成となっている。
MySQL Fabric管理サーバ
　192.168.2.213
my_first_fabric
　192.168.2.214（Primary）
　192.168.2.215（Secondary）
my_second_fabric
　192.168.2.216（Primary）
　192.168.2.217（Secondary）
my_global_fabric
　192.168.2.218（Primary）
　192.168.2.219（Secondary）

流れといっても設定手順自体は極めて短いもので、
　　1．シャーディングの定義を作り
　　2．どのテーブルをシャーディングさせるか、さらにどのカラムをシャーディングのKeyにするかを指定し、
　　3．シャーディングの定義に高可用性グループ（MySQLサーバ単体ではない）を追加する
となる。

コマンドレベルでは、このようになる。

○　シャーディング定義の作成
ここではシャーディングをデータの値の範囲に基づくか、HASH値に基づくかを指定し、
さらにどの高可用性グループをグローバルグループにするかを指定する。
今回の例では、データの値の範囲（RANGE）に基づくシャーディングで、
my_global_fabricグループをグローバルグループに指定した。

[root@mysql-fabric-kensyo1 ~]# mysqlfabric sharding create_definition RANGE my_global_fabric
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
32d43d91-309d-4afc-a284-3b14be32f3e1        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.43606e+09 Triggered by .
    4       2   1.43606e+09                     Executing action (_define_shard_mapping).
    5       2   1.43606e+09                      Executed action (_define_shard_mapping).

設定内容は以下で確認できる。

[root@mysql-fabric-kensyo1 ~]# mysqlfabric sharding list_definitions
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name  global_group_id
---------- --------- ----------------
         1     RANGE my_global_fabric

○　シャーディングするテーブルの指定・Keyカラムの指定
構文は、
mysqlfabric sharding add_table シャーディング定義のmapping_id　データベース名.テーブル名 カラム名
となる。
テーブル名、カラム名は適宜決めるとして、
シャーディング定義のmapping_idとは、上のmysqlfabric sharding list_definitionsコマンドで確認できる、
各シャーディング定義に対して割り当てられたIDのことである。

実行例：

[root@mysql-fabric-kensyo1 ~]# mysqlfabric sharding add_table 1 test.t1 col1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
68b7c0bb-8709-47ab-bfad-093e6ed12803        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.43607e+09 Triggered by .
    4       2   1.43607e+09                        Executing action (_add_shard_mapping).
    5       2   1.43607e+09                         Executed action (_add_shard_mapping).

設定内容の確認は以下。

[root@mysql-fabric-kensyo1 ~]# mysqlfabric sharding lookup_table test.t1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name table_name     global_group column_name
---------- --------- ---------- ---------------- -----------
         1     RANGE    test.t1 my_global_fabric        col1

○　シャーディングの定義に高可用性グループを追加
残りのmy_first_fabric、my_second_fabricグループをシャーディング定義に追加する。
今回の定義ではRANGEを指定しているので、グループを追加する時に値のRANGEも同時に指定する。
構文は
mysqlfabric sharding add_shard シャーディング定義のmapping_id 高可用性グループ名/そのグループに振り分けるRANGEの最小値,（同様に続く・・・） –state=ENABLED
である。

[root@mysql-fabric-kensyo1 ~]# mysqlfabric sharding add_shard 1 my_first_fabric/1,my_second_fabric/10 --state=ENABLED
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
e487d026-3720-4557-b83f-57e8d5c9a2e7        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.43607e+09 Triggered by .
    4       2   1.43607e+09                                Executing action (_add_shard).
    5       2   1.43607e+09                                 Executed action (_add_shard).

確認は以下のコマンドで。

[root@mysql-fabric-kensyo1 ~]# 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         t1        col1           1        2     RANGE  my_first_fabric my_global_fabric
       test         t1        col1          10        3     RANGE my_second_fabric my_global_fabric

これで、my_global_fabricグループをグローバルグループとし、
testデータベースのt1テーブルのcol1カラムが1～9であればmy_first_fabric高可用性グループに、
10以上であればmy_second_fabricに振り分ける、
というシャーディング定義が完成した。

なお、ここまで構成が終わっていると、
グローバルグループに対してかけられた更新がその配下の高可用性グループに伝播するようになっている。
これはシャーディングを定義したテーブル以外でも同様である。
この仕組みについては、こちらの記事で図を掲載しているので、確認してみてもらいたい。

これでシャーディング設定は完了となる。
続くテストに向け、シャーディング対象テーブルを作成し、APサーバからの接続用ユーザを作成しておく。
このクエリは、グローバルグループのPrimaryサーバ（今回は192.168.2.218）に対して実行すれば、
他の全サーバに伝播する。

mysql> create table test.t1 (col1 int primary key, col2 varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> grant all on test.* to 'ap_user'@'192.168.2.%' identified by 'XXXXXXXX';
Query OK, 0 rows affected (0.01 sec)

次にAPサーバを構成する。
APサーバといってもJava、Python、PHPのどれかと、
それに対応するコネクタが入っていればよい。
Pythonでのスクリプトについては他ブログ等でいくつかサンプルを見かけたが、
Javaでのサンプルを見かけなかったので、
Javaでの接続をしてみることにした。

今回のAPサーバの構成は以下。
CentOS 6.5
jdk1.8.0_45
ちなみにIPは192.168.2.220である。

○　JAVAインストール
JDKをRPMパッケージでインストールする。
バイナリはOracleホームページより。

# rpm -ivh jdk-8u45-linux-x64.rpm

PATHとCLASSPATHの設定を.bash_profileに入れる。

編集後の.bash_profile：

[root@mysql-ap ~]# cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=/usr/java/jdk1.8.0_45/bin:$PATH:$HOME/bin
CLASSPATH=$CLASSPATH:/usr/java/jdk1.8.0_45/lib/mysql-connector-java-5.1.36-bin.jar

export PATH
export CLASSPATH

これを適用するため、source ~/.bash_profileを実行するか、ログインし直す必要があることを忘れずに。

○　Connector/J配備
MySQL公式サイトより、mysql-connector-java-5.1.36.tar.gzを落とし、tarを展開。

# tar -xvzf mysql-connector-java-5.1.36.tar.gz

JAVAのライブラリディレクトリにjarファイルを移動。

# mv ~/mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar /usr/java/jdk1.8.0_45/lib/

これでAPサーバのセットアップは完了。

○　Javaコーディング
私がJavaを全くと言っていいほど知らないので、
あまりセンスのあるコーディングではないかもしれないが、
そこはお許し頂きたい。

ずばり、コードを公開する。
これは、test.t1テーブルに1～20の値をinsertするプログラムである。

[root@mysql-ap ~]# cat fabric_test.java
import java.sql.*;
import com.mysql.fabric.jdbc.*;

public class fabric_test{
    public static void main(String[] args) throws Exception {
        String driver = "com.mysql.fabric.jdbc.FabricMySQLDriver";
        String url = "jdbc:mysql:fabric://192.168.2.213:32274";
        String user = "ap_user";
        String password = "ap_user";
        String fabric_user = "";
        String fabric_password = "";
        String database = "test";
        Class.forName(driver);

        Connection rawcon = DriverManager.getConnection(url + "/" + database + "?fabricShardTable=t1", user, password);

        FabricMySQLConnection con = (FabricMySQLConnection)rawcon;

        Integer i = 1;

        PreparedStatement ps = con.prepareStatement (
        "insert into test.t1 values(?,?)");

        for (; i < 21;){
            con.setShardKey(i.toString());
            ps.setInt(1, i);
            ps.setString(2, "query from ap server");
            ps.executeUpdate();
            i++;
        }
        ps.close();
        con.close();
        }
}

公式マニュアルにサンプルスクリプトがあるので、これを参照した。
ただし、1箇所マニュアルに誤りと思われる箇所があった（後述）。

これだけ書くのでは不親切かと思うので、
私の極めて限られたJavaの知識をフル活用して、
要所だけでも解説していきたいと思う。

・クラスのインポート

import java.sql.*;
import com.mysql.fabric.jdbc.*;

インポートするのはとりあえずこれで十分のはず。
絞ろうとすれば絞れるのかもしれないが、
検証でそこまでやる意義もないと思ったのでこれで。

・接続情報の定義

String driver = "com.mysql.fabric.jdbc.FabricMySQLDriver";
String url = "jdbc:mysql:fabric://192.168.2.213:32274";
String user = "ap_user";
String password = "ap_user";
String fabric_user = "";
String fabric_password = "";
String database = "test";
Class.forName(driver);

ここでベースとなる接続情報を定義。
driverは、そんな名前なんですか、程度に思っておけば良い（と思う）。
urlは大事で、ここでMySQL fabric管理サーバを指して接続しなければならないのは要チェックポイント。
user、passwordは、MySQLに接続する時のデータベースユーザ。
fabric_user、fabric_passwordは、XML-RPCプロトコルでの接続をする際に認証をONにした場合に
入力しようと思って設けた変数だが、結局使わなかったので意味なし。
使う場合は、下のgetConnectionで追記が必要。
書く場合の構文はマニュアルによると

jdbc:mysql:fabric://fabrichost:32274/database?fabricUsername=admin&fabricPassword=secret

とのこと。

・接続＆テーブル指定

        Connection rawcon = DriverManager.getConnection(url + "/" + database + "?fabricShardTable=t1", user, password);

このfabricShardTableを指定しておくのが重要。
なお、ここについてはマニュアルに誤りがあり、マニュアルには

rawConnection = DriverManager.getConnection(
	baseUrl + "employees" + "?fabricShardTable=employees.employees",
	user,
	password);

とあるが、ここで、
データベース名.テーブル名
の形式でfabricShardTableを指定すると、
下記のようなエラーが出力されて接続ができない。

Exception in thread "main" java.sql.SQLException: Shard mapping not found for table `test.t1'

要注意である。

・MySQL Fabric接続のためのメソッドの使用

FabricMySQLConnection con = (FabricMySQLConnection)rawcon;

MySQL Fabric接続をする際にはFabricMySQLConnectionメソッドを利用する必要があるらしい。

・データの挿入

Integer i = 1;

PreparedStatement ps = con.prepareStatement (
"insert into test.t1 values(?,?)");

for (; i < 21;){
    con.setShardKey(i.toString());
    ps.setInt(1, i);
    ps.setString(2, "query from ap server");
    ps.executeUpdate();
    i++;
}
ps.close();
con.close();
}
}

ここは概ね通常のMySQL接続の際と同じなのだが、
1点重要なのが、
setShardKeyである。
この引数に与える値によって、どのシャードにデータが格納されるか決まる。
今回のシャーディング定義で考えると、、
たとえばsetShardKey("1")となっていれば
my_first_fabricグループにクエリが飛ぶし、
setShardKey("10")となっていればmy_second_fabricグループにクエリが飛ぶ、
という具合である。
ここでsetShardKeyを行わない場合、グローバルグループにクエリが飛び、
シャード全体に対してクエリが実行される。
なお、引数にはなぜか文字列でなければならない制約があるようなので、
toString()関数を利用している。

JAVAコードについてはこんなところで、
実際にプログラムを実行してみる。

[root@mysql-ap ~]# javac fabric_test.java
[root@mysql-ap ~]# java fabric_test

なんの変化もなし。
当然だが。
だが、管理下のMySQLサーバを見てみると・・・？

192.168.2.214（my_first_fabricのPrimaryサーバ）

mysql> select * from test.t1;
+------+----------------------+
| col1 | col2                 |
+------+----------------------+
|    1 | query from ap server |
|    2 | query from ap server |
|    3 | query from ap server |
|    4 | query from ap server |
|    5 | query from ap server |
|    6 | query from ap server |
|    7 | query from ap server |
|    8 | query from ap server |
|    9 | query from ap server |
+------+----------------------+
9 rows in set (0.00 sec)

192.168.2.216（my_second_fabricのPrimary）

mysql> select * from test.t1;
+------+----------------------+
| col1 | col2                 |
+------+----------------------+
|   10 | query from ap server |
|   11 | query from ap server |
|   12 | query from ap server |
|   13 | query from ap server |
|   14 | query from ap server |
|   15 | query from ap server |
|   16 | query from ap server |
|   17 | query from ap server |
|   18 | query from ap server |
|   19 | query from ap server |
|   20 | query from ap server |
+------+----------------------+
11 rows in set (0.00 sec)

おおー。
ちゃんと分割されている！

これで動作確認もOK。

長かったMySQL Fabric検証シリーズもこれでおしまい。
読んで下さった方、ありがとうございました！

=====MySQL Fabric検証シリーズ=====
第1弾：UCO-Tech（MySQL Fabricアーキテクチャ編）
第2弾：UCO-Tech（MySQL Fabricインストール＆検証準備編）
第3弾：UCO-Tech（MySQL Fabric基本設定編）
第4弾：UCO-Tech（MySQL Fabric死活監視編）
第5弾：UCO-Tech（MySQL Fabricデータシャーディング編）
==================================

このエントリーをはてなブックマークに追加

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です