Skip to content

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.