TPC-H gives a strict definition of the table structure and content, but leaves implementations some latitude in defining indices and data layout. A TPC-H run consists of a power test with a single user submitting queries in sequence, and of a throughput test where many concurrent users are simulated. An implementation may choose how many concurrent users are simulated, as long as this is more than a set minimum.
Tables do not always have to be stored in the order of their primary key. A clustered index is a structure where the index tree leaves contain the whole row of data. TPC-H explicitly forbids materializing multiple copies of a table in different sort orders for speeding up different queries. That said, an implementation may pick one primary order for each table which does not have to be the primary key order.
The present champion in core-for-core speed, Actian VectorWise, organizes the main tables, lineitem and orders, in date order. The Microsoft SQL Server implementations use clustered indices on lineitem and orders, where the table is stored in non-primary key order; in this case, the major ordering columns are l_shipdate and o_orderdate, respectively.
These index schemes create locality on the date dimension.
In the present discussion, we take a different tack: We keep lineitem and orders in primary key order, and sacrifice locality on date in favor of a fast merge join between these two tables, and for faster load and data maintenance.
TPC-H rules allow indices on foreign keys and dates. In this implementation, we only define one on o_custkey; lineitem is only indexed on its primary key. These are the two largest tables, and the only ones that change during the benchmark. Whether indices are defined on other tables makes little or no difference, since joins between these tend to perform better by hash than by index in any case.
All the runs are done at 100 GB scale. The test machine is a dual E5-2630 (2x6 cores, 2x12 threads, 2.3 GHz) with 192 GB of 1066 MHz RAM. Two Crucial 512 GB SSDs are used both for database and staging of the files. The disks are independent file systems with no RAID. (Note that RAID would be required for an official result.) The operating system is CentOS 6.2.
For cluster results, two machines with the above spec are used with a QDR InfiniBand interconnect.
The Virtuoso used is a current internal development version, to be available at the end of this series or by special request. This is neither the publicly available open nor closed source version.
About this entry:
Author: Orri Erling
Published: 11/13/2013 18:01 GMT
10/06/2014 13:57 GMT
Comment Status: 0 Comments