Database JUNKY

MySQL,MariaDBを中心としたブログです

InfiniDB でリアルタイムDWHを作る PART3 ~驚速DWHの構築

さあ、いよいよ、リアルタイムDWHの構築です。環境につきましては、前回ここで説明した通りです、最終的なリアルタイムDWHを運用するまでには、すこし手間はかかりますが、順序を把握していれば数が増えても同じ手順で行えます。とか言ってみる

前回の図を再度記載します。

▼環境構成図 上記ホストとスキーマの関係を表したのが、下記の図になります。前回同様、肝の部分は、ブラックボックスなのですが、一度にここで書くと話がややこしくなるので、ここの話は次回にいたします。

host:sqdbm02 : MySQL5.5 host:sqdbm03 : MySQL5.5 host:sqdbm04 : MySQL5.5 host:sqdwh01 : InfiniDB また今回の、sqdwh01で記載されているInfiniDBの構築方法につきましては、OSSなDWH InfiniDB 2.2.2最新版をインストールしてみる をご参照ください。というわけでこちらの環境では、すでに作成されております。

▼リアルタイムDWHの構築手順 1. DWH側の構築 2. BLACKBOX側の構築 の順で行います。sqdbm02と03につきましては、すでに存在しているデータベースという前提です。今回は「1. DWH側の構築について」説明します。

▼必要テーブルの調査 今回のシナリオでは、必要なテーブルはすべてではなく、一部です。sqdbm02,03には、いくつかテーブルが存在しますがDWHで必要なテーブルは、trn_jyucyu、mst_item、usersの合計3つのテーブルだけです。

▼構築手順 以下に構築手順を記載します。

【sqdwh01 : root】 1. DWHのスキーマ作成

1) ストレージエンジンの確認 InfiniDB および、InnoDBが登録されていることを確認します。尚、InfiniDBのインストール時に、InnoDBはインストールされません、個別にインストールする必要がございます。 [shell] [root@sqdwh01 ~]# idbmysql -u sysadm -pmypasswd Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.39 MySQL Community / Calpont InfiniDB Community 2.2.6-1 Final (COSS LA)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InfiniDB | YES | Calpont InfiniDB storage engine | YES | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 7 rows in set (0.00 sec) [/shell]

2)データベース(スキーマ)の作成 図に習ってワーク用の、スキーマと、「Infinidb用」のスキーマを作成します。 [sql] mysql> create database wrkorder; Query OK, 1 row affected (0.00 sec)

mysql> create database wrkitem; Query OK, 1 row affected (0.00 sec)

mysql> create database wrkuser; Query OK, 1 row affected (0.00 sec)

mysql> create database dwhorder; Query OK, 1 row affected (0.00 sec)

mysql> create database dwhitem; Query OK, 1 row affected (0.00 sec)

mysql> create database dwhuser; Query OK, 1 row affected (0.00 sec)

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | calpontsys | | dwhitem | | dwhorder | | dwhuser | | infinidb_vtable | | mysql | | wrkitem | | wrkorder | | wrkuser | +--------------------+ 10 rows in set (0.00 sec)

mysql> show create database wrkorder; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | wrkorder | CREATE DATABASE wrkorder /!40100 DEFAULT CHARACTER SET utf8 / | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) [/sql]

2. テーブルのダンプ 各サーバ、および各スキーマから必要なテーブルのみdumpファイルを取得します。

2.1) データ、定義を含むdumpを取得 こちらは、ワークスキーマに格納するためのダンプです。 [shell] [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -h sqdbm02 -u sysadm -pmypasswd sqorder trn_jyucyu > /tmp/trn_jyucyu.dump [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -h sqdbm03 -u sysadm -pmypasswd sqitem mst_item > /tmp/mst_item.dump [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -h sqdbm03 -u sysadm -pmypasswd squser users > /tmp/users.dump [/shell]

2.2) 定義のみのダンプを取得 こちらは、後の、InfiniDB用に加工するために出力します [shell] [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -d -h sqdbm02 -u sysadm -pmypasswd sqorder trn_jyucyu > /tmp/trn_jyucyu_def_only.sql [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -d -h sqdbm03 -u sysadm -pmypasswd sqitem mst_item > /tmp/mst_item_def_only.sql [root@sqdwh01 ~]# /usr/local/Calpont/mysql/bin/mysqldump -d -h sqdbm03 -u sysadm -pmypasswd squser users > /tmp/users_def_only.sql [/shell]

3. テーブルのリストア 当該テーブルは、当該スキーマは後のデータ連携用ワークスキーマとして利用されます。よってストレージエンジン、およびテーブル定義は、sqdbm01と、sqdbm02のそれと何もかわりません。つまり、ストレージエンジンは、innodbであり、データも同様のものが入っております。 [shell] [root@sqdwh01 ~]# idbmysql -u sysadm -pmypasswd wrkorder < /tmp/trn_jyucyu.dump [root@sqdwh01 ~]# idbmysql -u sysadm -pmypasswd wrkitem < /tmp/mst_item.dump [root@sqdwh01 ~]# idbmysql -u sysadm -pmypasswd wrkuser < /tmp/users.dump [/shell]

4. InfiniDBストレージエンジンテーブルの作成 InfiniDB用にカスタマイズしたテーブル定義を作成します。この定義を作成するために、「2.2) 定義のみのダンプを取得」にて作成しました定義をInfinbiDB用に加工します。どこをどう直すかについては、以下にdiffを作成しましたのでご確認ください。尚、Infinidbの制約事項に関しては、DWH InfiniDB community edition の制約事項をご参照ください。 [shell] [root@sqdwh01 tmp]# cp trn_jyucyu_def_only.sql trn_jyucyu_infinidb.sql [root@sqdwh01 tmp]# cp mst_item_def_only.sql mst_item_infinidb.sql [root@sqdwh01 tmp]# cp users_def_only.sql users_infinidb.sql [root@sqdwh01 tmp]# vi trn_jyucyu_infinidb.sql [root@sqdwh01 tmp]# diff trn_jyucyu_def_only.sql trn_jyucyu_infinidb.sql 26,34c26,33 < id int(11) unsigned NOT NULL AUTO_INCREMENT, < j_cd int(11) NOT NULL COMMENT '受注コード', < j_date date NOT NULL COMMENT '受注日', < s_cd int(11) NOT NULL COMMENT '商品コード', < j_price int(11) NOT NULL COMMENT '受注金額', < user_id int(11) NOT NULL COMMENT 'ユーザーコード', < last_update datetime NOT NULL COMMENT '最終更新日', < PRIMARY KEY (id)

< ) ENGINE=InnoDB AUTO_INCREMENT=599168 DEFAULT CHARSET=utf8;

> id int(11) , > j_cd int(11) COMMENT '受注コード', > j_date date COMMENT '受注日', > s_cd int(11) COMMENT '商品コード', > j_price int(11) COMMENT '受注金額', > user_id int(11) COMMENT 'ユーザーコード', > last_update datetime COMMENT '最終更新日' > ) ENGINE=InfiniDB DEFAULT CHARSET=utf8;

[root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhorder < trn_jyucyu_infinidb.sql [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhitem < mst_item_infinidb.sql [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhuser < users_infinidb.sql [/shell]

4.1) 作成された、InfiniDBストレージエンジンの定義を確認する ちょっとみずらくて申し訳けないですが、これがInfiniDBで定義したテーブル群になります、もちろん定義を作っただけなので、データは空です。

・ dwhorder [shell] [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhorder -e "show create table trn_jyucyu;" +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | trn_jyucyu | CREATE TABLE trn_jyucyu ( id int(11) DEFAULT NULL, j_cd int(11) DEFAULT NULL COMMENT '受注コード', j_date date DEFAULT NULL COMMENT '受注日', s_cd int(11) DEFAULT NULL COMMENT '商品コード', j_price int(11) DEFAULT NULL COMMENT '受注金額', user_id int(11) DEFAULT NULL COMMENT 'ユーザーコード', last_update datetime DEFAULT NULL COMMENT '最終更新日' ) ENGINE=InfiniDB DEFAULT CHARSET=utf8 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ [/shell] ・dwhuser [shell] [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhuser -e "show create table users;" +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users | CREATE TABLE users ( id int(11) DEFAULT NULL, user_name varchar(100) DEFAULT NULL COMMENT 'ユーザー名', user_mail varchar(100) DEFAULT NULL COMMENT 'メールアドレス', last_update datetime DEFAULT NULL COMMENT '最終更新日' ) ENGINE=InfiniDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ [/shell] ・dwhitems [shell] [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd dwhitem -e "show create table mst_item;" +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mst_item | CREATE TABLE mst_item ( id int(11) DEFAULT NULL, item_name varchar(100) DEFAULT NULL COMMENT '商品名', last_update datetime DEFAULT NULL COMMENT '最終更新日' ) ENGINE=InfiniDB DEFAULT CHARSET=utf8 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ [/shell]

5. データのエクスポート 「2. テーブルのダンプ」でリストアしました。ワークスキーマ上のテーブルをCSVファイルに出力します [shell] [root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd -e "SELECT * FROM wrkorder.trn_jyucyu INTO OUTFILE '/tmp/trn_jyucyu.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';"

[root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd -e "SELECT * FROM wrkuser.users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';"

[root@sqdwh01 tmp]# idbmysql -u sysadm -pmypasswd -e "SELECT * FROM wrkitem.mst_item INTO OUTFILE '/tmp/mst_item.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';"

[/shell]

6. データのロード 上記5にて、エクスポートしたデータを、InifiniDBのバルクローダーでロードします(非常に高速!!) [shell] [root@sqdwh01 tmp]# /usr/local/Calpont/bin/cpimport dwhorder trn_jyucyu '/tmp/trn_jyucyu.csv' -s , -E \"

Column delimiter : , Enclosed by Character : "

Using table OID 3000 as the default JOB ID Bulkload root directory : /usr/local/Calpont/data/bulk Job description file : /usr/local/Calpont/data/bulk/tmpjob/dwhorder_trn_jyucyu_D20120409_T163059_Job_3000.xml Log file for this job: /usr/local/Calpont/data/bulk/log/Job_3000.log 2012-04-09 16:30:59 (17790) INFO : successfully loaded job file /usr/local/Calpont/data/bulk/tmpjob/dwhorder_trn_jyucyu_D20120409_T163059_Job_3000.xml 2012-04-09 16:30:59 (17790) INFO : Job file loaded, run time for this step : 0.00211215 seconds 2012-04-09 16:30:59 (17790) INFO : PreProcessing check starts 2012-04-09 16:30:59 (17790) INFO : PreProcessing check completed 2012-04-09 16:30:59 (17790) INFO : preProcess completed, run time for this step : 0.112508 seconds 2012-04-09 16:30:59 (17790) INFO : No of Read Threads Spawned = 1 2012-04-09 16:30:59 (17790) INFO : No of Parse Threads Spawned = 3 2012-04-09 16:31:07 (17790) INFO : For table dwhorder.trn_jyucyu: 599167 rows processed and 599167 rows inserted. 2012-04-09 16:31:07 (17790) WARN : Column dwhorder.trn_jyucyu.j_date; Number of invalid dates replaced with null: 3010 2012-04-09 16:31:07 (17790) INFO : Bulk load completed, total run time : 8.298 seconds

  • 他のテーブルにつきましても同様の手順でロードします [root@sqdwh01 tmp]# /usr/local/Calpont/bin/cpimport dwhuser users '/tmp/users.csv' -s , -E \" [root@sqdwh01 tmp]# /usr/local/Calpont/bin/cpimport dwhitem mst_item '/tmp/mst_item.csv' -s , -E \"

[/shell] 以上で、初期データの作成は無事完了です!

 

▼ InfiniDBのパフォーマンス InfiniDBは本当に速いです!! InnoDBとInfiniDB、以下のようなSQLを発行し、InfiniDBと、InnoDBの速度比較を行いました。尚、当環境は、XEBのGUEST OSで検証しており、サーバのメモリは、2GBとしております。

●SQL1

1) InfiniDB [sql] select usr.id as user_id, usr.user_name , itm.id as s_cd, itm.item_name, sum(j_price) as sum_price from dwhuser.users usr inner join dwhorder.trn_jyucyu trn on usr.id = trn.user_id inner join dwhitem.mst_item itm on itm.id = trn.s_cd group by usr.id, itm.id, usr.user_name, itm.item_name order by sum(j_price) desc limit 100; [/sql]

2) InnoDB [sql] select usr.id as user_id, usr.user_name , itm.id as s_cd, itm.item_name, sum(j_price) as sum_price from wrkuser.users usr inner join wrkorder.trn_jyucyu trn on usr.id = trn.user_id inner join wrkitem.mst_item itm on itm.id = trn.s_cd group by usr.id, itm.id, usr.user_name, itm.item_name order by sum(j_price) desc limit 100; [/sql]

●SQL2

1) InfiniDB [sql] select usr.id as user_id, usr.user_name , count(trn.id) as cnt from dwhuser.users usr inner join dwhorder.trn_jyucyu trn on usr.id = trn.user_id group by usr.id, usr.user_name order by count(trn.id) desc; [/sql]

2) InnoDB [sql] select usr.id as user_id, usr.user_name , count(trn.id) as cnt from wrkuser.users usr inner join wrkorder.trn_jyucyu trn on usr.id = trn.user_id group by usr.id, usr.user_name order by count(trn.id) desc; [/sql]

●SQL3

1) InfiniDB [sql] select itm.id, itm.item_name, sum(trn.j_price) as sum_price from dwhorder.trn_jyucyu trn inner join dwhitem.mst_item itm on itm.id = trn.s_cd group by itm.id, itm.item_name order by sum(j_price) desc; [/sql]

2) InnoDB [sql] select itm.id, itm.item_name, sum(trn.j_price) as sum_price from wrkorder.trn_jyucyu trn inner join wrkitem.mst_item itm on itm.id = trn.s_cd group by itm.id, itm.item_name order by sum(j_price) desc; [/sql] ▼速度結果 下図の通りのなりました。バーが短いほど、応答速度が速いことになります。平均的に3倍程度の速度向上が見られます。