TPC-H数据导入MySQL教程

0. TPC-H是啥

TPC-H是TPC提供的一个benchmark,用来模拟一个现实中的商业应用,可以生成一堆虚构的数据,且自带一些查询,可以导入到各种数据库中来模拟现实需求,检查性能。

具体是怎样的数据见:http://www.tpc.org/tpch/spec/tpch2.16.0.pdf

1. 获取文件

首先到官网 http://www.tpc.org/tpch/ 在右边的 DBGEN & Reference Data Set 下载到下面那个.zip,然后自己选一个路径解压过去。

2. 修改 makefile

在解压的文件夹下面cd到dbgen下,找到makefile.suite。

~/tpch_2_16_1$ cd dbgen
~/tpch_2_16_1/dbgen$ vim makefile.suite

把103~112行左右改成这个样子

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 = MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
#

改动的地方分别是:

  • 设定C语言编译器为gcc(如果你用的是其他的编译器就改成其他对应名字)
  • DATABASE设为MYSQL(注意注释里写的提供的数据库格式没有mysql,所以等一下要自己写一个格式,见步骤3)
  • MACHINE = LINUX 和 WORKLOAD = TPCH 就不用说啥意思了……

改好之后保存为makefile,这样才好用make命令。

3. 修改tpcd.h

// 其实这一步好像没什么用 = =
// 不过你上面把DATABASE设成了MYSQL的话就不要跳过这一步,不然编译不了。 
// 或者上面那步也可以设成SQLSERVER这步就不用管了

之前说了官方的生成程序没有mysql的格式,所以我们要自己写一个,打开tpcd.h,找一个空白的地方写上

#ifdef MYSQL
#define GEN_QUERY_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

这样就定义了一个MYSQL的脚本格式可以用了。

4. 生成dbgen

接下来make,生成数据生成脚本dbgen.

~/tpch_2_16_1/dbgen$ make

这个过程中会有一些关于数据类型的警告,一般可以无视。

make完dbgen目录下之后就会多出很多.o(等到你所有事都干完确定这些没有用了不想留着就make clean,或者直接整个文件夹删掉……)和一个叫dbgen的文件

5. 生成tbl数据文件

接下来要用dbgen生成数据,一共会生成8个表(.tbl)。

查看README里面有命令行参数解说,这里我们在dbgen目录下用

./dbgen -s 1

-s 1 表示生成1G的数据 (如果你之前曾经尝试过生成数据,最好先make clean,再重新make,接着到这步加上-f覆盖掉)

生成之后可以用head命令检查一下tbl们,会看到每一行都有一些用“|”隔开的字段。

 

6. 修改初始化脚本使mySQL可用

压缩包里自带两个脚本:

  • dss.ddl:用来建表
  • dss.ri:关联表中primary key和foreign key。

不过这些脚本不能直接在mySQL里用(看README就知道人家根本没考虑过mySQL……),要修改。

dss.ddl的开头需要加上一些给MySQL建立数据库连接用的指令。改完之后是这样:

https://gist.github.com/joyeec9h3/9617329

dss.ri 的情况复杂一些,因为MySQL里添加外键要指明键的名字,所以每个加外键的指令都需要改。前面也有几行MySQL不兼容的东西,改完之后是这样:

https://gist.github.com/joyeec9h3/9617302

如果要直接用这两个脚本请注意:

  1. dss.ddl开头的tpch是数据库的名字,你也可以换成其他名字,但如果改了,dss.ri里凡是提到“tpch”的地方也要改成对应的数据库名字。
  2. 这两个脚本用的表名和field名均和tpch文档一致,但tpch自带的测试貌似用了另外的表名。

以下可选,但本教程后面的步骤没有考虑这步

鉴于tpch自带的测试用的表名是小写的,而dss.ddl里面的表名是大写的,我们最好也改成小写……

用vim打开dss.ddl,执行

:%s/TABLE\(.*\)/TABLE\L\1

就可以了。

如果你要事后改,就进到mySQL里,USE tpch,然后用类似于

alter table CUSTOMER rename to customer;

这样的语句改就行了。嫌一个个打麻烦的话就写脚本吧~

 

7. 建表

打开mysql,执行

mysql> \. ~/tpch_2_16_1/dbgen/dss.ddl

后面那部分是dss.ddl的路径,要按照你的实际存放地址修改

然后可以用

mysql> SHOW DATABASES;

看到有一个叫tpch的数据库,像这样:

就说明建库成功了。(记得打分号,我老是忘记打分号……)

再执行

mysql> USE tpch;
mysql> SHOW TABLES;

可以看到生成的8个表就说明建表成功,像这样

 

下一步需要添加外键和主键,执行

mysql> \. ~/tpch_2_16_1/dbgen/dss.ri

同样记得dss.ri的路径要改成你的实际存放路径。

如果想看看外键和主键是否添加成功,有没有添加对,可以执行 SHOW CREATE TABLE 表名(觉得默认格式很碍眼可以试试看加上\G垂直打印),像这样:

可以看到有哪些限制加进去了。

做完以上步骤之后光有一堆表和限制,里面是没有数据的,需要从之前生成的.tbl里将实际的数据导入进来。

8. 写个导入tbl文件的脚本

你也可以进到mysql然后写一堆类似于

LOAD DATA LOCAL INFILE '/home/joyeecheung/tpch_2_16_1/dbgen/supplier.tbl' INTO TABLE SUPPLIER
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';

的东西,不过实在太龟毛了……所以还是写一个bash脚本自动生成这些语句好了。

 

// 懒得运行脚本生成指令or需要添加外键主键的请往后跳一点直接看成品……

// 不懂bash的人可以看下面,懂的大牛看了这么弱的脚本请别来pia我……

dbgen目录下新建一个load.sh(或者随便什么名字),把这些东西复制进去

https://gist.github.com/joyeec9h3/9619766

以load.sh为名保存到dbgen目录下,然后执行

sh load.sh

同目录下就会生成一个loaddata.sql,里面是从8个tbl里导入数据的sql指令。

 

注意如果需要导入外键和主键,要根据各个表的依赖关系调整一下导入各个文件的顺序。(其实就是拓扑排序口桀口桀……不过也就是把八个代码块调整一下顺序而已,这么点工作量就不写代码了)

成品看这里,可以直接复制保存到dbgen目录下进行下一步(要直接用记得替换成你的保存路径)

https://gist.github.com/joyeec9h3/9619336

 

9. 执行脚本导入数据

在dbgen目录下运行

mysql --local-infile -u root -p < loaddata.sql

记得要加那个 --local-infile,因为MySQL为了安全默认是把从外部文件导入数据的功能关掉的,要导入的话就必须在启动MySQL的时候打开这个功能。

接下来可以喝杯茶等导入。如果你觉得它好像死在那里了,可以再开一个终端,登陆MySQL,用SHOW processlist;看看它在干嘛。

如果你好奇导入多少了,可以用SHOW TABLE STATUS FROM tpch; 看看目前每个表都导入了多少行,对照一下文档里写的1G数据量每个表总共会有几行数据,心里就有谱了。

10. 检查导入结果

按照前面的步骤,如果你不加foreign key和primary key关联的话,导入还是挺快的……

我的本本CPU是i7-2670QM@2.20GHz,虚拟机分配了2个核和2G内存,日立的硬盘分了20G给它(现在一看貌似不够用啊囧),大概7分钟左右就能导入完的样子。如果加了外键和主键速度会显著降低,大约是60多分钟,而且貌似越到后面速度越慢。

导入完了之后可以用

SHOW TABLE STATUS FROM tpch;

看看是不是都导入进去了,对照一下文档的13页,看看每个表行数有没有差太远,我测试是都导进去了,查询结果如下:

mysql> SHOW TABLE STATUS FROM tpch\G;
*************************** 1. row ***************************
           Name: CUSTOMER
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 149842
 Avg_row_length: 192
    Data_length: 28884992
Max_data_length: 0
   Index_length: 3686400
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: LINEITEM
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6009452
 Avg_row_length: 147
    Data_length: 883949568
Max_data_length: 0
   Index_length: 218103808
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: NATION
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 4. row ***************************
           Name: ORDERS
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1491770
 Avg_row_length: 131
    Data_length: 196804608
Max_data_length: 0
   Index_length: 39403520
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 5. row ***************************
           Name: PART
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 200806
 Avg_row_length: 164
    Data_length: 33095680
Max_data_length: 0
   Index_length: 0
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 6. row ***************************
           Name: PARTSUPP
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 778800
 Avg_row_length: 267
    Data_length: 208388096
Max_data_length: 0
   Index_length: 20496384
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 7. row ***************************
           Name: REGION
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 8. row ***************************
           Name: SUPPLIER
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9594
 Avg_row_length: 274
    Data_length: 2637824
Max_data_length: 0
   Index_length: 278528
      Data_free: 130023424
 Auto_increment: NULL
    Create_time: 2014-03-18 18:43:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
8 rows in set (0.81 sec)

 

还可以用 SELECT * FROM 表名 LIMIT 数量; 来从表抓几个数据看:

 

参考链接

http://www.pilhokim.com/index.php?title=Project/EFIM/TPC-H&oldid=90509#Setup_MySQL

http://imysql.cn/2012/12/21/tpch-for-mysql-manual.html

http://my2iu.blogspot.com/2009/01/running-tpc-h-queries-on-mysql.html

posted @ 2014-03-14 02:30  Joyee  阅读(...)  评论(...编辑  收藏