mo_tpch_open Tool Guide¶
mo_tpch_open is a tool that implements TPCH testing for MatrixOne.
Note
The mo_tpch_open tool is currently only supported for deployment on Linux system x86 architectures.
Pre-dependency¶
Finished installing and starting MatrixOne
Set environment encoding to UTF-8
Installed wget
The bc command is installed
Install mo_tpch_open¶
wget https://github.com/matrixorigin/mo_tpch_open/archive/refs/tags/v1.0.1.zip unzip v1.0.1.zip
Generate Data Set¶
Generate the dataset using the following command:
cd mo_tpch_open-1.0.1 ./bin/gen-tpch-data.sh -s 1 -c 5
Parameter interpretation
-s: means generating a dataset of about 1GB, no parameters specified, 100G data generated by default,
-c: Indicates the number of threads generating table data. Default is 10 threads.
Generating a complete data set may take some time. When you are done, you can see the result file in the mo_tpch_open-1.0.1/bin/tpch-data directory.
root@host-10-222-4-8:~/soft/tpch/tpch-tools/bin/tpch-data# ls -l
root@host-10-222-4-8:~/soft/tpch/mo_tpch_open-1.0.1/bin/tpch-data# ls -l
total 1074936
-rw-r--r-- 1 root root 24346144 Jun 7 03:16 customer.tbl
-rw-r--r-- 1 root root 151051198 Jun 7 03:16 lineitem.tbl.1
-rw-r--r-- 1 root root 152129724 Jun 7 03:16 lineitem.tbl.2
-rw-r--r-- 1 root root 152344710 Jun 7 03:16 lineitem.tbl.3
-rw-r--r-- 1 root root 152123661 Jun 7 03:16 lineitem.tbl.4
-rw-r--r-- 1 root root 152213994 Jun 7 03:16 lineitem.tbl.5
-rw-r--r-- 1 root root 2224 Jun 7 03:16 nation.tbl
-rw-r--r-- 1 root root 34175478 Jun 7 03:16 orders.tbl.1
-rw-r--r-- 1 root root 34463858 Jun 7 03:16 orders.tbl.2
-rw-r--r-- 1 root root 34437453 Jun 7 03:16 orders.tbl.3
-rw-r--r-- 1 root root 34445732 Jun 7 03:16 orders.tbl.4
-rw-r--r-- 1 root root 34429640 Jun 7 03:16 orders.tbl.5
-rw-r--r-- 1 root root 24135125 Jun 7 03:16 part.tbl
-rw-r--r-- 1 root root 23677134 Jun 7 03:16 partsupp.tbl.1
-rw-r--r-- 1 root root 23721079 Jun 7 03:16 partsupp.tbl.2
-rw-r--r-- 1 root root 23808550 Jun 7 03:16 partsupp.tbl.3
-rw-r--r-- 1 root root 23894802 Jun 7 03:16 partsupp.tbl.4
-rw-r--r-- 1 root root 23883051 Jun 7 03:16 partsupp.tbl.5
-rw-r--r-- 1 root root 389 Jun 7 03:16 region.tbl
-rw-r--r-- 1 root root 1409184 Jun 7 03:16 supplier.tbl
Building tables in MatrixOne¶
Modify the configuration file conf/matrxione.conf to specify the address, username, password for MatrixOne. An example configuration file is shown below
# MatrixOne host
export HOST='127.0.0.1'
# MatrixOne port
export PORT=6001
# MatrixOne username
export USER='root'
# MatrixOne password
export PASSWORD='111'
# The database where TPC-H tables located
export DB='tpch'
Then execute the following script to build the table.
./bin/create-tpch-tables.sh
Connect to MatrixOne to view and build table successfully.
mysql> show tables;
+----------------+
| Tables_in_tpch |
+----------------+
| customer |
| lineitem |
| nation |
| orders |
| part |
| partsupp |
| region |
| revenue0 |
| supplier |
+----------------+
9 rows in set (0.00 sec)
Import Data¶
Execute the following script to import the data required for the TPC-H test:
./bin/load-tpch-data.sh -c 10
Parameter interpretation
-c: You can specify the number of threads to perform the import, which defaults to 5.
Once loaded, you can query the data in MatrixOne using the created table.
Run the query command¶
Execute the following command to query:
root@host-10-222-4-8:~/soft/tpch/mo_tpch_open-1.0.1# ./bin/run-tpch-queries.sh
mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)
HOST: 127.0.0.1
PORT: 6001
USER: root
DB: tpch
Time Unit: ms
q1 836 715 691 691
q2 111 80 88 80
q3 325 235 212 212
q4 221 181 177 177
q5 240 236 295 236
q6 215 292 350 292
q7 373 327 299 299
q8 236 238 243 238
q9 443 406 413 406
q10 375 390 422 390
q11 201 237 231 231
q12 461 460 400 400
q13 321 294 301 294
q14 289 261 282 261
q15 391 285 294 285
q16 222 288 255 255
q17 333 247 243 243
q18 275 262 317 262
q19 513 479 511 479
q20 240 244 198 198
q21 1503 1746 1786 1746
q22 138 122 126 122
Total cold run time: 8262 ms
Total hot run time: 7797 ms
Finish tpch queries.
The query results correspond to: query statement, first query result, second query result, third query result, and fastest result in ms.
Note
You can view specific query statements in the mo_tpch_open-1.0.1/queries directory.