TPC-H数据库性能基准测试规范2.17.1版

需积分: 25 116 浏览量更新于2024-07-22 收藏 2.32MB PDF 举报

"TPC-BenchmarkTMHStandardSpecificationRevision2.17.1是数据库评测的专业文档，主要关注决策支持（DecisionSupport）方面的性能标准。该文档由TransactionProcessingPerformanceCouncil（TPC）发布，是一个衡量交易处理性能的组织。TPC-H是基于TPC-D版本2发展而来的，涉及多家知名IT公司的贡献，包括Compaq, Data General, Dell, EMC, HP, IBM, Informix, Microsoft, NCR, Oracle, Sequent, SGI, Sun, Sybase, 和 Unisys。" TPC-H是一个广泛使用的决策支持系统（Decision Support System, DSS）基准测试套件，特别针对大数据量分析查询的性能进行评估。它包含了一组复杂的SQL查询，模拟了商业智能应用中的常见操作，如数据分析、报告生成和数据挖掘。这些查询涵盖了多种数据处理模式，包括聚合、排序、过滤、连接等，旨在全面反映一个数据库在处理复杂分析任务时的性能。 TPC-H测试的规模通常用每小时可以处理的事务数（Queries per Hour, QphH）来衡量，这是一个标准化的度量，使得不同系统之间的性能比较成为可能。测试的规模可以从1GB到数PB不等，以确保结果对不同大小的数据库都具有代表性。 TPC-H标准规范包括了以下几个关键部分： 1. **数据生成器（DBGEN）**：DBGEN是一个工具，用于生成模拟业务数据，这些数据符合TPC-H定义的8个表结构和相应的业务场景。数据量可以根据需求调整，以适应不同的测试规模。 2. **查询集**：TPC-H包含22个预定义的SQL查询，这些查询具有各种复杂性和执行时间，旨在涵盖不同的查询类型和工作负载。 3. **性能指标**：除了QphH之外，还包括查询响应时间、系统资源利用率（如CPU、内存和I/O）等，这些指标帮助全面评估系统的整体性能。 4. **合规性**：任何宣称符合TPC-H标准的系统都必须遵循严格的规则和审计过程，以确保测试结果的公正性和可比性。 5. **硬件和软件环境**：测试环境需要详细记录，包括操作系统、数据库管理系统（DBMS）、网络配置等，以便其他人可以复现和验证测试结果。 TPC-H基准测试对于数据库供应商和用户来说都是重要的工具，它可以帮助数据库厂商优化其产品，同时帮助潜在客户了解不同系统在实际业务场景下的表现。通过TPC-H，数据库业界可以持续推动技术进步，提升大数据处理的效率和能力。

TPC Benchmark

H Standard Specification Revision 2.17.1 Page 16

C_NAME

variable text, size 25

C_ADDRESS

variable text, size 40

C_NATIONKEY

Identifier

Foreign Key to N_NATIONKEY

C_PHONE

fixed text, size 15

C_ACCTBAL

Decimal

C_MKTSEGMENT

fixed text, size 10

C_COMMENT

variable text, size 117

Primary Key: C_CUSTKEY

ORDERS Table Layout

Column Name

Datatype Requirements

Comment

O_ORDERKEY

Identifier

SF*1,500,000 are sparsely populated

O_CUSTKEY

Identifier

Foreign Key to C_CUSTKEY

O_ORDERSTATUS

fixed text, size 1

O_TOTALPRICE

Decimal

O_ORDERDATE

Date

O_ORDERPRIORITY

fixed text, size 15

O_CLERK

fixed text, size 15

O_SHIPPRIORITY

Integer

O_COMMENT

variable text, size 79

Primary Key: O_ORDERKEY

Comment: Orders are not present for all customers. In fact, one-third of the customers do not have any order in

the database. The orders are assigned at random to two-thirds of the customers (see Clause 4: ). The purpose of

this is to exercise the capabilities of the DBMS to handle "dead data" when joining two or more tables.

LINEITEM Table Layout

Column Name

Datatype Requirements

Comment

L_ORDERKEY

identifier

Foreign Key to O_ORDERKEY

L_PARTKEY

identifier

Foreign key to P_PARTKEY, first part of the

compound Foreign Key to (PS_PARTKEY,

PS_SUPPKEY) with L_SUPPKEY

L_SUPPKEY

Identifier

Foreign key to S_SUPPKEY, second part of the

compound Foreign Key to (PS_PARTKEY,

TPC Benchmark

H Standard Specification Revision 2.17.1 Page 18

1.4.2 Constraints

The use of constraints is optional and limited to primary key, foreign key, check, and not null constraints. If

constraints are used, they must satisfy the following requirements:

 They must be specified using SQL. There is no specific implementation requirement. For example,

CREATE TABLE, ALTER TABLE, CREATE UNIQUE INDEX, and CREATE TRIGGER are all valid

statements;

 Constraints must be enforced either at the statement level or at the transaction level;

 All defined constraints must be enforced and validated before the load test is complete (see Clause 5.1.1.2);

1.4.2.1 The NOT NULL attribute may be used for any column.

1.4.2.2 The following columns or set of columns listed in Clause 1.4.1 as ‘Primary Key’ may be defined as primary key

constraints (using the PRIMARY KEY clause or other equivalent syntax):

 P_PARTKEY;

 S_SUPPKEY;

 PS_PARTKEY, PS_SUPPKEY;

 C_CUSTKEY;

 O_ORDERKEY;

 L_ORDERKEY, L_LINENUMBER;

 N_NATIONKEY;

 R_REGIONKEY.

Defining a primary key constraint can only be done for the columns listed above.

1.4.2.3 Columns listed in the comments of Clause 1.4.1 as ‘Foreign Key’ may be defined as foreign key constraints. There

is no specific requirement to use referential actions (e.g., RESTRICT, CASCADE, NO ACTION, etc.). If any

foreign key constraint is defined by an implementation, then all the foreign key constraints listed below must be

defined by the implementation (using the FOREIGN KEY clause or other equivalent syntax):S_NATIONKEY

(referencing N_NATIONKEY);

 PS_PARTKEY (referencing P_PARTKEY);

 PS_SUPPKEY (referencing S_SUPPKEY);

 C_NATIONKEY (referencing N_NATIONKEY);

 O_CUSTKEY (referencing C_CUSTKEY);

 L_ORDERKEY (referencing O_ORDERKEY);

 L_PARTKEY (referencing P_PARTKEY);

 L_SUPPKEY (referencing S_SUPPKEY);

 L_PARTKEY, L_SUPPKEY (referencing PS_PARTKEY, PS_SUPPKEY);

 N_REGIONKEY (referencing R_REGIONKEY);

Defining a foreign key constraint can only be done for the columns listed above.

1.4.2.4 Check Constraints: Check constraints may be defined to restrict the database contents. In order to support

evolutionary change, the check constraints must not rely on knowledge of the enumerated domains of each column.

The following list of expressions defines permissible check constraints:

1. Positive Keys

P_PARTKEY >= 0

S_SUPPKEY >= 0

C_CUSTKEY >= 0

TPC Benchmark

H Standard Specification Revision 2.17.1 Page 19

PS_PARTKEY >= 0

R_REGIONKEY >= 0

N_NATIONKEY >= 0

2. Open-interval constraints

P_SIZE >= 0

P_RETAILPRICE >= 0

PS_AVAILQTY >= 0

PS_SUPPLYCOST >= 0

O_TOTALPRICE >= 0

L_QUANTITY >= 0

L_EXTENDEDPRICE >= 0

L_TAX >= 0

3. Closed-interval constraints

L_DISCOUNT between 0.00 and 1.00

4. Multi-column constraints

L_SHIPDATE <= L_RECEIPTDATE

Comment: The constraints rely solely on the diagram provided in Clause 1.2and the description in Clause 1.4. They

are not derived from explicit knowledge of the data population specified in Clause 4.2.

1.5 Implementation Rules

1.5.1 The database shall be implemented using a commercially available database management system (DBMS).

1.5.2 The physical clustering of records within the database is allowed as long as this clustering does not alter the logical

independence of each table.

Comment: The intent of this clause is to permit flexibility in the physical design of a database while preserving a

strict logical view of all the tables.

1.5.3 At the end of the Load Test, all tables must have exactly the number of rows defined for the scale factor, SF, and the

database population, both specified in Clause 4: .

1.5.4 Horizontal partitioning of base tables or auxiliary structures created by database directives (see Clause 1.5.7) is

allowed. Groups of rows from a table or auxiliary structure may be assigned to different files, disks, or areas. If this

assignment is a function of data in the table or auxiliary structure, the assignment must be based on the value of a

partitioning field. A partitioning field must be one and only one of the following:

 A column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary key

constraint;

 A column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;

 A column having a date datatype as defined in Clause 1.3.

Some partitioning schemes require the use of directives that specify explicit values for the partitioning field. If such

directives are used they must satisfy the following conditions:

 They may not rely on any knowledge of the data stored in the table except the minimum and maximum

values of columns used for the partitioning field. The minimum and maximum values of columns are

specified in Clause 4.2.3

 Within the limitations of integer division, they must define each partition to accept an equal portion of the

range between the minimum and maximum values of the partitioning column(s). For date-based partitions,

it is permissible to partition into equally sized domains based upon an integer granularity of days, weeks,

months, or years (e.g., 30 days, 4 weeks, 1 month, 1 year, etc.). For date-based partition granularities other

TPC Benchmark

H Standard Specification Revision 2.17.1 Page 20

than days, a partition boundary may extend beyond the minimum or maximum boundaries as established in

that table’s data characteristics as defined in Clause 4.2.3.

 The directives must allow the insertion of values of the partitioning column(s) outside the range covered by

the minimum and maximum values, as required by Clause 1.5.13.

Multiple-level partitioning of base tables or auxiliary structures is allowed only if each level of partitioning satisfies

the conditions stated above and each level references only one partitioning field as defined above. If implemented,

the details of such partitioning must be disclosed.

1.5.5 Physical placement of data on durable media is not auditable. SQL DDL that explicitly partitions data vertically is

prohibited. The row must be logically presented as an atomic set of columns.

Comment: This implies that vertical partitioning which does not rely upon explicit partitioning directives is

allowed. Explicit partitioning directives are those that assign groups of columns of one row to files, disks or areas

different from those storing the other columns in that row.

1.5.6 Except as provided in Clause 1.5.7, logical replication of database objects (i.e., tables, rows, or columns) is not

allowed. The physical implementation of auxiliary data structures to the tables may involve data replication of

selected data from the tables provided that:

 All replicated data are managed by the DBMS, the operating system, or the hardware;

 All replications are transparent to all data manipulation operations;

 Data modifications are reflected in all logical copies of the replicated data by the time the updating

transaction is committed;

 All copies of replicated data maintain full ACID properties (see Clause 3: ) at all times.

1.5.7 Auxiliary data structures that constitute logical replications of data from one or more columns of a base table (e.g.,

indexes, materialized views, summary tables, structures used to enforce relational integrity constraints) must

conform to the provisions of Clause 1.5.6. The directives defining and creating these structures are subject to the

following limitations:

 Each directive may reference no more than one base table, and may not reference other auxiliary structures.

 Each directive may reference one and only one of the following:

o A column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary key

constraint;

o A column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;

o A column having a date datatype as defined in Clause 1.3.

 Each directive may contain functions or expressions on explicitly permitted columns

No directives (e.g. DDL, session options, global configuration parameters) are permitted in TPC-H scripts whose

effect is to cause the materialization of columns (or functions on columns) in auxiliary data structures other than

those columns explicitly permitted by the above limitations. Further, no directives are permitted whose effect is to

cause the materialization of columns in auxiliary data structures derived from more than one table.

Comment: Database implementations of auxiliary structures generated as a result of compliant directives usually

contain embedded pointers or references to corresponding base table rows. Database implementations that

transparently employ either ‘row IDs’ or embedded base table ‘Primary Key’ values for this purpose are equally

acceptable.

In particular, the generation of transparently embedded ‘Primary Key’ values required by auxiliary structures is a

permitted materialization of the ‘Primary Key’ column(s). ‘Primary Key’ and ‘Foreign Key’ columns are listed in

Clause 1.4.1.

剩余135页未读，继续阅读

wwwlmlskycom

粉丝: 0
资源: 3

TPC-H数据库性能基准测试规范2.17.1版

TPCH性能测试数据集

TPC-H_Tools_v2.18.0.zip

oracle测试

TPCH数据库创建与表定义详解

建立TPCH数据库的查询语句

怎么在SQL SERVER中建立TPCH数据库

lab-2_tpch_数据库_tpch建表_

数据库MySQL与TPCH

基于TPCH基准的内存数据库性能测试方案研究.pdf

数据库系统实验：TPCH模式构建

最新资源