LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@dennis_wang(dennis wang)

PolarDBをOLAPとして利用してみる(1) デフォルト状態(row based)

Last updated at Posted at 2023-10-17

背景

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

image.png

1. テスト環境の準備

前提条件：

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

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

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

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

PolarDBクラスタの新規作成：

  • ノードのスペック： polar.mysql.x8.4xlarge（32コア256 GB）

  • バージョン： MySQL 8.0.1
    image.png
    image.png

  • database アカウントtest_userを新規作成
    image.png
    image.png

  • ECSからPolarDBにアクセスできるようになるために、PolarDB側のWhitelistsにECSのPrivate IPを登録
    image.png

  • ノードの数：

    • 並行クエリのテストに使用されるノードの数は2つです（1つのprimay node + 1つの read-only node）。
    • IMCI性能テストで使用されるノードの数は3つです（1つのprimay node + 1つの read-only node + 1つのread-only column stored node）。

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 を参照してください。

また、以下のようなデータモデリングを使用しています。
image.png
以下から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

image.png

  • 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分程度かかったことが分かります。
    image.png

  • dataの作成完了したら、すぐにpolardbに並行としてロードされます。
    image.png

  • 以下のコマンドで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に全部ロードできたということになります。 データの作成からロードの完了まではだいたい１時間半かかりました。
    image.png

  • 各テーブル件数の確認(データ量合計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については以下の記事をご参考ください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?