TPC-H

신예환·2023년 3월 23일
1

TPC 란?

  • 트랜잭션 처리 성능 평의회(Transaction Processing Performance Council)로 비영리 단체
  • OLTP(OnLine Trancation Processing) 시스템 처리 성능 측정
  • 벤치마크 테스트 모델로 TPC-alphabet으로 표기
  • 1989년 TPC-A를 시작으로 여러 벤치마크 도구 개발
  • 정리
    • 일반적으로 TPC는 주어진 시스템 및 데이터베이스가 단위 시간당 수행 할 수 있는 트랜잭션(초 당 트랜잭션 또는 분당 트랜잭션)의 관점에서 트랜잭션 처리 및 데이터베이스 성능을 측정 하는 것.

WHY TPC-H

  • TPC-H 벤치마크 테스트는 의사결정 지원 시스템의 성능 테스트를 위해 개발
  • 의사 결정 용도의 시스템에 대한 성능 측정을 위해 참조되며, Business를 위한 Ad Hoc Query 위주로 구성
  • TPC-H 테스트 측정 방법은 8개의 Table로 이루어진 데이터베이스에 대해 22개의 사전 정의된 Query를 갖는 여러 개의 Query Scheme을 동시에 수행하여 소요 시간을 측정

동일한 문제를 푸는 방법은 여러 가지가 있을 수 있지만, 해법들이 풀 수 있는 규모는 다를 수 있다. 소규모 Data에서는 아무 문제 없이 수행되는 질의이지만 Big Data에서는 시간이 오래 걸릴 수 있다. 대부분의 SQL은 탐색 시간이 오래 걸리는 것이 문제인데 효율적인 SQL의 시간 복잡도 (Time Complexity)는 n*Log(n)인 반면 비효율적인 SQL의 시간 복잡도는 n2보다 크기 때문에 DB 크기가 10배증가하더라도 속도는 10보다 훨씬 느려질 수 있다.

Stackoverflow에는 효율적인 SQL을 물어보는 질문이 많다. 이 경우 질문자가 사용하는 실제 데이터를 확인하기 어렵기 때문에 몇 가지 패턴을 이용하여 답변을 해 주는 편인데, (예를 들어 ‘NOT IN 대신LEFT JOIN을 써라’ 등) 실제 얼마나 빨라졌는지 구체적인 수치를 알 수 없으니 답변 후에 얻는 쾌감(?)이 적을 뿐 더러, ‘당신의 SQL이 정말 좋은 것이냐’라는 논란이 생길 때 대응하기가 어렵다. 따라서 남들도 동의할 수 있으면서 적당히 크기도 큰 Data가 필요한데 이는 역시 공인된 벤치마크, 그 중에서도 TPC-H가 좋다고 생각되었다.

TPC-H 벤치마크 도구 설치(dbgen)

1.소스코드 다운
2.dbgen 컴파일
3.TPC-H용 텍스트 파일 생성

  • dbgen은 TPC-H용 텍스트 파일을 생성해 주는 프로그램이다. 이 텍스트 파일을 MySQL의 LOAD DATA 구문을 이용하여 테이블에 로딩할 것이다. 우선 다음과 같이 텍스트 파일을 생성해 보자.
$ time ./dbgen
TPC-H Population Generator (Version 2.16.0)
Copyright Transaction Processing Performance Council 1994 - 2010
 
real    0m39.142s
user    0m38.165s
sys     0m0.903s

보통 -s 옵션을 통해 scale을 지정해준다. 많은 데이터 사이즈가 필요하지 않기 때문에
$ make
$ ./dbgen -s 1

 
8개의 테이블이 나오고, 총 860만건 레코드(튜플)
$ wc -l *.tbl
    150000 customer.tbl (고객 테이블용 데이터)
   6001215 lineitem.tbl
        25 nation.tbl
   1500000 orders.tbl (주문 테이블용 데이터)
    800000 partsupp.tbl
    200000 part.tbl
         5 region.tbl
     10000 supplier.tbl
   8661245 total
  • 아무런 옵션도 없이 ./dbgen을 실행하면 약 860만건의 레코드를 생성한다. TPC-H는 총 8개의 테이블 규정하는데 customer.tbl은 고객 테이블용 데이터이고, orders.tbl는 주문 테이블용 데이터이다. 즉, 15만명의 고객, 150만건의 주문을 생성했다고 보면 된다.

  • dbgen의 -s 옵션은 scale을 의미하는데 기본 값은 1이다. 이 값을 2로 지정하면 1일 때보다 2배 더 많은 데이터를 생성한다.

4.테이블 생성

  • FOREIGN KEY 관련 오류를 배제 하기 위해 테이블을 먼저 생성하고, INDEX, 함수종속 등 제약 조건을 설정해 준다.
    5.LOAD DATA

Unzip the downloaded file Navigate through the command line to DBGEN folder

cd Downloads/project/dbgen/

Make a copy of the dummy makefile

cp makefile.suite makefile

In dbgen folder find the created makefile and insert highlighted values (bold) to this file.

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

In dbgen folder find the tpcd.h file and edit higlighted (bold) values for SQLSERVER.

...
#ifdef  SQLSERVER
#define GEN_QUERY_PLAN  "set showplan on\nset noexec on\ngo\n"
#define START_TRAN      "**BEGIN WORK;**"
#define END_TRAN        "**COMMIT WORK;**"
#define SET_OUTPUT      ""
#define SET_ROWCOUNT    "limit %d;\n\n"
#define SET_DBASE       "use %s;\n"
#endif
...

Run make command.

$ make

Generate the files for population. (The last numeric parametr determines the volume of data with which will be your database then populated - I decided that 0.1 (=100MB) is fine for my purposes, since I am not interested in the database benchmark tests.

$ ./dbgen -s 0.1

Connect to SQL server with permission to reach local files, create database and connect to schema.

$ mysql -u root -p --local-infile (mysql에서 현재디렉토리에 있는 dbgen로컬파일을 사용한다.)
$ mysql> CREATE DATABASE tpch;
$ mysql> USE tpch;

Run following queries in SQL console uploaded in this repository.

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152));

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152));

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL );

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL);

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

Populate tables with generated dummy data.

LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';

Alter the schema dependencies (The original statement can be found in dss.ri. This is my modified version in order to work with MySQL.)

ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION(R_REGIONKEY);
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);
ALTER TABLE SUPPLIER  
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION(N_NATIONKEY);
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION(N_NATIONKEY);
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER(S_SUPPKEY);
ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART(P_PARTKEY);
ALTER TABLE ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER(C_CUSTKEY);
ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references ORDERS(O_ORDERKEY);
ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references PARTSUPP(PS_PARTKEY, PS_SUPPKEY);

Now you can run your test the queries uploaded in this repository. Hope that's useful!

mysql> show global variables like 'local_infile';
mysql> set global local_infile=true;
내용이 잘들어갔는지 확인!
mysql> desc NATION;
mysql> select * from NATION;
mysqsl –u 유저 –p 데이터베이스명 < 파일명.sql
$ cd project/dbgen/queries
$ mysql –u root –p tpch < 1.sql

mysql> source 파일명.sql 
mysql> source queries/1.sql

여기서 각각의 칼럼(attribute)들에 대해서 설명하면...

참고 http://jason-heo.github.io/mysql/2014/03/05/mysql-tpc-h.html

profile
더디가도 함께가자

0개의 댓글