背景

複雑なデータ分析を行う際には、専用のデータウェアハウスを導入し、OLTPのデータベースからデータを同期する必要があるため、ハードルが高い(2つのデータベースシステムを使用する必要があり、アーキテクチャの複雑さ、運用作業量、およびコストが大幅に増加)と感じられるでしょう。
PolarDBをOLAPとしても利用できるかどうかを検証する目的で、通常のPolarDB(デフォルト状態/row based)とIMCIを試してみました。

1. テスト環境の準備

前提条件：

テストに使用されるECSインスタンスとPolarDBクラスタは同じregionの同じAZにあります。
ECSインスタンスとPolarDBクラスタは同じVPC内に存在している

ECSインスタンスの新規作成：

インスタンスのスペック：ecs.c5.4xlarge
cloud disk容量：1000 GB ESSD
OSイメージ：CentOS 7.7 64ビット

必要なライブラリはベンチマークのデータをインターネットからdownloadするから、 ECSインスタンスにPublic IP Addressを付与する必要があります。

PolarDBクラスタの新規作成：

2. データ生成ツールdbgenのコンパイルとデータ生成

TPC-Hとは

TPC-H は RDBMS ベンチマーク仕様の一つで、売上分析データウェアハウスのデータモデリングを使い、意思決定支援システムの性能を測定、計測するもので、データウェアハウスの分析型クエリ能力を評価するためによく使われています。
8つのデータテーブルと22の複雑なSQLクエリが含まれて、ほとんどのクエリには複数のテーブルのJoin、サブクエリ、およびGroup byの集約など複雑な検索クエリが含まれています。
その中でnation（国家）とregion（地域）の2つのテーブルのデータ量は固定です。残りの6つのテーブルのデータ件数はSF（Scale Factor）に関連し、1、100、1000などと指定することができます。それぞれ1GB、100GB、1000GBを表しています。指定されたSFに基づいて各テーブルのデータ量を決定します。たとえば、tpch1gのpartテーブルの場合、そのデータ量は基本データ量200,000×1=200,000（行）です。また、tpch100gのpartテーブルの場合、データ量は200,000×100=20,000,000（行）となります。
TPC-H の詳細に関しては、下記 URL を参照してください。

また、以下のようなデータモデリングを使用しています。

以下からdownloadして、そのまま利用することはできなくもないんのですが、 ECSから大量のデータをPolarDBにloadするにはかなり時間かかります。

時間短縮のためには以下の並行loadできるscriptツールを利用します。

ECSインスタンスにログインした上で、以下のコマンドを実行する

# mkdir workspace && cd $_
# wget https://github.com/yubinr/benchtpch/archive/refs/heads/master.zip
# ls -al
drwxr-xr-x  5 root root     4096 Sep  1 14:20 master.zip
# yum -y install unzip
# unzip master.zip
# ls -al
drwxr-xr-x  5 root root     4096 Sep  1 14:20 benchtpch-master
-rw-r--r--  1 root root   257045 Sep  1 10:48 master.zip
# mv benchtpch-master benchtpch
# ls -al
drwxr-xr-x  5 root root     4096 Sep  1 14:20 benchtpch
-rw-r--r--  1 root root   257045 Sep  1 10:48 master.zip
# cd benchtpch/dbgen-src
# cp makefile.suite makefile

makefileファイル中のCC、DATABASE、MACHINE、WORKLOADなどのパラメーターの修正：

vim makefileでmakefile ファイルを開いて、中身を修正

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC= gcc
# Current values for DATABASE are: INFORMIX, DB2, ORACLE,
#                                  SQLSERVER, SYBASE, TDAT (Teradata)
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

tpcd.hファイルを修正し、database portability defineの部分に以下の新たなmicroを追加

vim tpcd.h

#ifdef MYSQL
#define GEN_QUERY_PLAN "EXPLAIN PLAN"
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif

buildする
```
# sudo yum install gcc
# make
```

dbgenとqgenという２つのバイナリファイルが生成されれば、成功です

# ls -al dbgen qgen
-rwxr-xr-x 1 root root 106520 Sep  1 11:57 dbgen
-rwxr-xr-x 1 root root  98608 Sep  1 12:03 qgen

mysqlクライントがない場合は、以下を参考して、mysqlクライントをインストールしておきます。

# rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# yum search mysql-community-client
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
================================================================================================== N/S matched: mysql-community-client ===================================================================================================
mysql-community-client.i686 : MySQL database client applications and tools
mysql-community-client.x86_64 : MySQL database client applications and tools
mysql-community-client-plugins.i686 : Shared plugins for MySQL client applications
mysql-community-client-plugins.x86_64 : Shared plugins for MySQL client applications

  Name and summary matches only, use "search all" for everything.

# yum repolist enabled | grep mysql
!mysql-connectors-community/x86_64 MySQL Connectors Community                227
!mysql-tools-community/x86_64      MySQL Tools Community                     100
!mysql80-community/x86_64          MySQL 8.0 Community Server                426

# yum install -y mysql-community-client

もし以下のエラーが発生した場合は、新しいGPGキーをインポートしてから、MySQLをインストールすることで解決できる。

Public key for mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm is not installed
 Failing package is: mysql-community-client-plugins-8.0.34-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# yum install -y mysql-community-client

ローカルのデータファイルをPolarDBへloadさせるために、以下の設定をEC2インスタンスに実施します。
```
vim /etc/my.cnf
[client]
loose-local-infile=1
```

上記の設定がないと、データをロードする時には以下のエラーが発生します。
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

data_kit.shというスクリプトを実行して、dataの作成、table、indexのddlファイル作成、databaseにschemaの適用、そしてデータのロードは全部一気に実施します。

bash /root/workspace/benchtpch/tpch/data_kit.sh --parallel 20  -g /root/workspace/benchtpch/dbgen-src  -s 100 -c 400 --data /root/workspace/benchtpch/100G400 --database tpch100g --host polardbhostname  --ddl /root/workspace/benchtpch/tpch/innodb_fk.ddl --port 3306 -u test_user -p yourpassword all

polardbhostnameとyourpasswordを実際のpolardbのVPC endpointとtest_userアカウントのpasswordに置き換えてください。
data_kit.shスクリプトの使い方：
--parallel: Specify number of threads to load data in parallel
-g | --generator=: Specify directory of dbgen binary
-d | --data=: Specify directory to store generated data → 事前にディレクトリを作成しておく必要がなくて、パスを指定すれば、自動的に作成してくれる。すでに存在している場合は、自動的に削除されて、同じ名前のディレクトリが自動的に作成してくれます。
-s | --scale-factor=: Specify value for scale factor　→　例えば1000GB程度のデータを出力するためには -s を 1000に指定すればよい
-c | --chunks=: Specify value for chunks, same as PARALLEL_THREADS by default
-H | --host=: Specify hostname/ip of mysql database server to load data, localhost by default
-P | --port=: Specify port on which mysql server run
-u | --username=: Specify username to connect database server
-p | --password=: Specify password to connect database server
--database=: Specify name of database to be loaded (will be created during prepare)\n\　　→　事前に手動で作成しておかなくても自動的に作成してくれる
--ddl=: Specify path for DDL SQL commands
COMMAND all: dataの作成、table,indexのddlファイル作成、databaseにschemaの適用とデータのロード全部一気にやってくれる。

指定していた100G400ディレクトリが自動的に作られて、中にはにたくさん分割されているデータファイルがあります。

[~/workspace/benchtpch]# ls -al
drwxr-xr-x 2 root root 81920 Sep  1 14:30 100G400
drwxr-xr-x 8 root root 86016 Sep  1 14:30 dbgen-src
-rw-r--r-- 1 root root    47 May  9  2022 .gitignore
-rw-r--r-- 1 root root    84 May  9  2022 README.txt
drwxr-xr-x 2 root root  4096 Sep  1 17:06 tpch

[root@iZ6wefl8o790llv3332rvoZ benchtpch]# ls -alh 100G400 | head -n 20
total 107G
-rw-r--r-- 1 root root 5.8M Sep  4 15:14 customer.tbl.1
-rw-r--r-- 1 root root 5.9M Sep  4 15:15 customer.tbl.10
-rw-r--r-- 1 root root 5.9M Sep  4 15:16 customer.tbl.100
-rw-r--r-- 1 root root 5.9M Sep  4 15:16 customer.tbl.101
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.102
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.103
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.104
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.105
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.106
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.107
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.108
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.109
-rw-r--r-- 1 root root 5.9M Sep  4 15:15 customer.tbl.11
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.110
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.111
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.112
-rw-r--r-- 1 root root 5.9M Sep  4 15:17 customer.tbl.113
...

dataの作成には10分程度かかったことが分かります。
dataの作成完了したら、すぐにpolardbに並行としてロードされます。

以下のコマンドでLOAD DATAコマンドが並行として実行されていることが分かります。

mysql> show processlist;
+-----------+-----------------+---------------------+----------+----------------+--------+--------------------------------+------------------------------------------------------------------------------------------------------+
| Id        | User            | Host                | db       | Command        | Time   | State                          | Info                                                                                                 |
+-----------+-----------------+---------------------+----------+----------------+--------+--------------------------------+------------------------------------------------------------------------------------------------------+
                                                           |
| 268617858 | test_user       | 192.168.2.24:55266  | tpch100g | Query          |      3 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.167' INTO TABLE part FIELD |
| 268617859 | test_user       | 192.168.2.24:55270  | tpch100g | Query          |      3 | waiting for handler commit     | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.161' INTO TABLE part FIELD |
| 268617860 | test_user       | 192.168.2.24:55274  | tpch100g | Query          |      3 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.169' INTO TABLE part FIELD |
| 268617863 | test_user       | 192.168.2.24:55282  | tpch100g | Query          |      2 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.155' INTO TABLE part FIELD |
| 268617868 | test_user       | 192.168.2.24:55296  | tpch100g | Query          |      2 | Receiving from client          | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.168' INTO TABLE part FIELD |
| 268617869 | test_user       | 192.168.2.24:55306  | tpch100g | Query          |      1 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.216' INTO TABLE part FIELD |
| 268617870 | test_user       | 192.168.2.24:55308  | tpch100g | Query          |      1 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.162' INTO TABLE part FIELD |
| 268617871 | test_user       | 192.168.2.24:55314  | tpch100g | Query          |      1 | Receiving from client          | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.185' INTO TABLE part FIELD |
| 268617872 | test_user       | 192.168.2.24:55316  | tpch100g | Query          |      1 | Receiving from client          | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.197' INTO TABLE part FIELD |
| 268617873 | test_user       | 192.168.2.24:55320  | tpch100g | Query          |      0 | executing                      | LOAD DATA LOCAL INFILE '/root/workspace/benchtpch/100G400/part.tbl.219' INTO TABLE part FIELD |

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

以下のFinish loading data for database tpch100g with 400 chunks in 20 threadsメッセージが表示されましたら、全部のデータはECSのローカルからPolarDBに全部ロードできたということになります。データの作成からロードの完了まではだいたい１時間半かかりました。
各テーブル件数の確認(データ量合計100G)：

table名	件数
customer	15,000,000
lineitem	600,037,902
nation	25
orders	150,000,000
part	20,000,000
partsupp	80,000,000
region	5
supplier	10,00,000

3. クエリを実行

必要なクエリの準備

qgenでクエリも作れますが、テスト結果再現しやすくために、以下のように準備していたクエリをdownloaｄしてご利用ください。

[~/workspace/benchtpch]# ls -al
drwxr-xr-x 2 root root 81920 Sep  1 14:30 100G400
drwxr-xr-x 8 root root 86016 Sep  1 14:30 dbgen-src
-rw-r--r-- 1 root root    47 May  9  2022 .gitignore
-rw-r--r-- 1 root root    84 May  9  2022 README.txt
drwxr-xr-x 2 root root  4096 Sep  1 17:06 tpch

[~/workspace/benchtpch]# mkdir queries && cd $_

[~/workspace/benchtpch/queries]# wget https://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/145463/cn_zh/1575017874050/22%E4%B8%AA%E6%9F%A5%E8%AF%A2.zip

[~/workspace/benchtpch/queries]# ls -al
-rw-r--r-- 1 root root 9865 Nov 29  2019 22个查询.zip

[~/workspace/benchtpch/queries]# unzip 22个查询.zip

[~/workspace/benchtpch/queries]# mv 22个查询/* .

クエリ一括実行できるシェルスクリプトの用意：

    [~/workspace/benchtpch/queries]# vim sql.sh
    
    #!/usr/bin/env bash
    host=**
    port=**
    user=**
    password=**
    database=**
    resfile=resfile
    echo "start test run at "`date "+%Y-%m-%d %H:%M:%S"`|tee -a ${resfile}.out
    for (( i=1; i<=22;i=i+1 ))
    do
    queryfile="Q"${i}".sql"
    start_time=`date "+%s.%N"`
    echo "run query ${i}"|tee -a ${resfile}.out
    mysql -h ${host}  -P${port} -u${user} -p${password} $database -e" source $queryfile;" |tee -a ${resfile}.out
    end_time=`date "+%s.%N"`
    start_s=${start_time%.*}
    start_nanos=${start_time#*.}
    end_s=${end_time%.*}
    end_nanos=${end_time#*.}
    if [ "$end_nanos" -lt "$start_nanos" ];then
            end_s=$(( 10#$end_s -1 ))
            end_nanos=$(( 10#$end_nanos + 10 ** 9))
    fi
    time=$(( 10#$end_s - 10#$start_s )).`printf "%03d\n" $(( (10#$end_nanos - 10#$start_nanos)/10**6 ))`
    echo ${queryfile} "the "${j}" run cost "${time}" second start at "`date -d @$start_time "+%Y-%m-%d %H:%M:%S"`" stop at "`date -d @$end_time "+%Y-%m-%d %H:%M:%S"` >> ${resfile}.time
    done

クエリ一括実行できるシェルスクリプトの実行

sql.shファイル中のhost,port,user,password,databaseを全部実際の情報に置き換えてください。
```
[~/workspace/benchtpch/queries]# bash sql.sh
start test run at2023-09-01 18:31:41
run query 1
```

以下の２つのファイルが生成されます。

resfile.out：　クエリ実行の結果
resfile.time：　各クエリ実行でかかった時間

[~/workspace/benchtpch/queries]# ls -al resfile*
-rw-r--r-- 1 root root 18574 Oct 17 10:42 resfile.out
-rw-r--r-- 1 root root   649 Oct 17 10:42 resfile.time

時間確認

cat resfile.time

Q1.sql the  run cost 1359.181 second start at 2023-09-05 09:32:29 stop at 2023-09-05 09:55:08
Q2.sql the  run cost 36.620 second start at 2023-09-05 09:55:08 stop at 2023-09-05 09:55:44
Q3.sql the  run cost 289.391 second start at 2023-09-05 09:55:44 stop at 2023-09-05 10:00:34
Q4.sql the  run cost 85.091 second start at 2023-09-05 10:00:34 stop at 2023-09-05 10:01:59
Q5.sql the  run cost 269.742 second start at 2023-09-05 10:01:59 stop at 2023-09-05 10:06:29
Q6.sql the  run cost 257.311 second start at 2023-09-05 10:06:29 stop at 2023-09-05 10:10:46
Q7.sql the  run cost 185.985 second start at 2023-09-05 10:10:46 stop at 2023-09-05 10:13:52
Q8.sql the  run cost 603.937 second start at 2023-09-05 10:13:52 stop at 2023-09-05 10:23:56
Q9.sql the  run cost 477.451 second start at 2023-09-05 10:23:56 stop at 2023-09-05 10:31:53
Q10.sql the  run cost 937.594 second start at 2023-09-05 10:31:53 stop at 2023-09-05 10:47:31
Q11.sql the  run cost 32.973 second start at 2023-09-05 10:47:31 stop at 2023-09-05 10:48:04
Q12.sql the  run cost 961.232 second start at 2023-09-05 10:48:04 stop at 2023-09-05 11:04:05
Q13.sql the  run cost 810.000 second start at 2023-09-05 11:04:05 stop at 2023-09-05 11:17:35
Q14.sql the  run cost 266.808 second start at 2023-09-05 11:17:35 stop at 2023-09-05 11:22:02
Q15.sql the  run cost 310.005 second start at 2023-09-05 11:22:02 stop at 2023-09-05 11:27:12
Q16.sql the  run cost 45.379 second start at 2023-09-05 11:27:12 stop at 2023-09-05 11:27:57
Q17.sql the  run cost 62.935 second start at 2023-09-05 11:27:57 stop at 2023-09-05 11:29:00
Q18.sql the  run cost 280.144 second start at 2023-09-05 11:29:00 stop at 2023-09-05 11:33:40
Q19.sql the  run cost 20.029 second start at 2023-09-05 11:33:40 stop at 2023-09-05 11:34:00
Q20.sql the  run cost 53.048 second start at 2023-09-05 11:34:00 stop at 2023-09-05 11:34:54

4. テスト結果

query番号	かかる時間(秒)
Q1	1359.181
Q2	36.620
Q3	289.391
Q4	85.091
Q5	269.742
Q6	257.311
Q7	185.985
Q8	603.937
Q9	477.451
Q10	937.594
Q11	32.973
Q12	961.232
Q13	810.000
Q14	266.808
Q15	310.005
Q16	45.379
Q17	62.935
Q18	280.144
Q19	20.029
Q20	53.048
Q21	2016.559
Q22	21.130

IMCIについては以下の記事をご参考ください。