LucidDbTpch

From Eigenpedia

Revision as of 06:22, 30 July 2008 by Jvs (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

Contents

Disclaimers

Important Notice #1: the information on this page refers to the TPC-H benchmark tables and queries, but the results described are not official TPC-H results and should not be interpreted as such; nor should they be compared to official TPC-H results in any way. TPC-H is a trademark of the Transaction Processing Performance Council.

Important Notice #2: this page contains comparative results for LucidDB and MySQL, but the tests were carried out without expertise in MySQL performance tuning, and so the results should not be interpreted as what could be expected from a system tuned by an expert. Suggestions for configuration improvements are welcome! MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

  • --Jvs 18:29, 29 July 2008 (PDT): Peter Zaitsev has pointed out that MySQL load performance would be better if indexes were predefined before data is loaded (rather than created individually after), so we'll be trying that on the next run. He also recommened using the rewritten queries with joins instead of subqueries against MySQL (not just LucidDB).

Overview

This wiki page presents some LucidDB performance results based on the table and query definitions from the TPC-H benchmark. For the purposes of comparison between LucidDB's column store architecture and a typical row store, results from MySQL running on the same machine are also presented for the identical analytic workload. Scale factor 10 (10GB of source flatfile data) was used for the cross-DBMS comparison. Some information on how to run the tests yourself is also provided. Join the luciddb-users mailing list at sourceforge.net if you need help.

Additional information was presented at OSCON 2008; here are the slides.

Scale Factor 10 Benchmark Machine Specifications

The machine used to run the scale factor 10 benchmark for both LucidDB and MySQL had the following hardware:

  • AMD64
  • 8GB RAM
  • 2GHz dual-core
  • 1MB L2 cache
  • Seagate ST3300555SS SATA drive, 10K RPM

All files (source flatfiles, data files, and temp files) were stored on the same ext3 filesystem on the single drive (no RAID). The Linux I/O scheduler was changed from the default (cfq) to deadline.

The operating system was 64-bit RHEL 5.

For LucidDB, the JRockit R27.4 JVM was used.

LucidDB Test Execution

LucidDB Version

The LucidDB version used for testing was a prerelease of 0.7.4. If you're going to run the test yourself, download the full 0.7.4 source tarball and unpack it. http://p4web.eigenbase.org/open/dev/thirdparty/tpch.tar.gz contains the dbgen benchmark code from the TPC website, plus some LucidDB-specifics helper scripts, while luciddb/test/sql/tpch from the source tarball contains all LucidDB-specific scripts.

LucidDB Storage Preallocation

For optimal load performance, LucidDB data and temp files were preallocated via the lucidDbAllocFile utility. The allocSpace.10G.sh script can be used after editing the path for LUCIDDB_DIR to point to your installed LucidDB bin directory. Make sure LucidDB is not running when you execute the utility, otherwise you'll encounter a file lock error.

LucidDB Buffer Pool Size

The LucidDB buffer pool was set to 6GB. See LucidDbBufferPoolSizing for the procedure.

LucidDB I/O Scheduler

The LucidDB I/O Scheduler was set to aioLinux (forcing usage of libaio and O_DIRECT):

alter system set "deviceSchedulerType" = 'aioLinux';

Restart LucidDB after changing this setting and make sure that it comes back up successfully; if it doesn't, you probably need to install the libaio package on your machine.

LucidDB Prefetch

A prefetch queue size of 50 pages was found to be the optimal setting for the disk configuration used:

alter system set "prefetchPagesMax" = 50;

Changing this setting also requires a restart in order to take effect. The optimal setting is dependent on the disk configuration.

(The numbers reported below were with the default setting of 12.)

LucidDB Concurrency

For benchmark loads and queries run without any concurrency, LucidDB can be configured to give each statement the maximum possible memory:

alter system set "expectedConcurrentStatements" = 1;

Changing this setting does not require a restart.

(The numbers reported below were with the default setting of 4.)

Data Generation

The dbgen utility provided on the TPC website was used to generate the datafiles. The utility can't generate files larger than 2GB, so at scale factor 10, it's necessary to generate the files split up into smaller pieces. Large tables were split up into 10 parts, and then reconcatenated back into a single file per table (since the limit is in the utility, not in LucidDB's flatfile reader, and it's more efficient for LucidDB to load each table in one shot).

The createdbMultiProcess script in tpch.tar.gz takes care of the generation step above, as well as some additional munging needed to make the files parsable by LucidDB.

$ ./createdbMultiProcess 10G 10

Reconcatentation can be performed with the Unix cat command.

LucidDB Initialization SQL

The template script init.sql.tmpl executes DDL to create some of the necessary objects such as the flatfile links. It also selects row counts from the flatfile links to verify that the files are accessible. Edit it to match your site-specifics, and then execute it. You can ignore the commented-out portions (they are obsolete leftovers from Broadbase).

LucidDB Table Definitions

The script create_tables.sql defines the TPC-H base tables. The definitions include the primary keys, but no other indexes.

(Note that the flat file external tables are quoted as lowercase, e.g. "lineitem", whereas the corresponding base tables are unquoted and hence ALLCAPS, e.g. LINEITEM.)

LucidDB Data Load

The script load_tables.sql copies data from the flatfile links into the base tables. Besides loading the data, this also updates the primary key index.

LucidDB Index Creation

Non-primary-key indexes are created by script create_indexes.sql. LucidDB does not support foreign key constraints, but indexes on the foreign key columns are still needed for efficient query processing. Additional indexes useful to the optimizer are also created.

This script also runs ANALYZE to collect statistics needed by the optimizer.

LucidDB Queries

Queries are in individual script files named according to query number, e.g. 1.sql.

  • Queries 2, 4, 11, 15, 16, and 17 are rewritten into an equivalent form which avoids subqueries, since LucidDB's optimizer does not yet handle all subquery decorrelation efficiently by itself.
  • Queries 19, 21, and 22 are omitted since LucidDB's optimizer cannot yet handle them, and no one has come up with an equivalent rewrite.
  • Queries 2, 3, 10, and 18 should be using !set rowlimit to control the result set size, but the scripts have not been updated yet; doing so may reduce the result rendering time.
  • Queries which perform date arithmetic after parameter substitution in the original spec have been rewritten with the result of the arithmetic; LucidDB's optimizer is capable of doing the equivalent constant reduction rewrite (the scripts can be edited to verify this).

After the server was restarted post-load, all queries were run through exactly once in sequence in the same session.

LucidDB Storage

LucidDB storage breakdowns were obtained with this query:

select table_schem as schema_name, table_name, index_name, pages as page_count
from sys_boot.jdbc_metadata.index_info_internal;

MySQL Test Execution

MySQL Version

The MySQL version used was 5.0.22, with the MyISAM storage engine.

MySQL Configuration

For the data load and indexing, the following my.cnf settings were used (after some experimentation to attempt to find the best ones):

skip-locking
key_buffer = 3000M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
bulk_insert_buffer_size = 2M

However, for queries, the default my.cnf settings were used, since attempts to use large-memory settings seemed to hurt query times in most cases.

MySQL Scripts

The script Media:mysql_load.sql was used to create the schema, load the data, create the indexes, and analyze statistics. PRIMARY KEY and index definitions were identical to those used for LucidDB. However, unlike LucidDB, primary keys were added as a separate step (instead of being loaded together with the data).

The script Media:mysql_query.sql was used to run queries; the queries were not modified from the TPC-H specification. As with LucidDB, queries were run in sequence in a single session after restarting the server post-load.

Query 11 returned incorrect results (empty result set). Later investigation showed that the query was parameterized incorrectly for both MySQL and LucidDB, so the results for both should be ignored. (This will be corrected for a future run.)

During a trial run, query 18 never came back after hours, and the machine was thrashing, so it was omitted from the real run.

MySQL Storage

Storage was measured by shutting the server down and examining data and index file sizes:

# ls -l
total 16377720
-rw-rw---- 1 mysql mysql       8850 Apr 13 23:28 customer.frm
-rw-rw---- 1 mysql mysql  238173452 Apr 13 23:28 customer.MYD
-rw-rw---- 1 mysql mysql   38496256 Apr 13 23:28 customer.MYI
-rw-rw---- 1 mysql mysql         65 Apr 13 18:16 db.opt
-rw-rw---- 1 mysql mysql       9226 Apr 14 04:50 lineitem.frm
-rw-rw---- 1 mysql mysql 5296436176 Apr 14 05:01 lineitem.MYD
-rw-rw---- 1 mysql mysql 7186396160 Apr 14 06:01 lineitem.MYI
-rw-rw---- 1 mysql mysql       8692 Apr 14 06:06 nation.frm
-rw-rw---- 1 mysql mysql       2316 Apr 14 06:06 nation.MYD
-rw-rw---- 1 mysql mysql       4096 Apr 14 06:06 nation.MYI
-rw-rw---- 1 mysql mysql       8928 Apr 14 06:07 orders.frm
-rw-rw---- 1 mysql mysql 1472903276 Apr 14 06:09 orders.MYD
-rw-rw---- 1 mysql mysql  585344000 Apr 14 06:14 orders.MYI
-rw-rw---- 1 mysql mysql       8874 Apr 14 06:15 part.frm
-rw-rw---- 1 mysql mysql  234451704 Apr 14 06:15 part.MYD
-rw-rw---- 1 mysql mysql  156191744 Apr 14 06:16 part.MYI
-rw-rw---- 1 mysql mysql       8748 Apr 14 06:03 partsupp.frm
-rw-rw---- 1 mysql mysql 1160039052 Apr 14 06:04 partsupp.MYD
-rw-rw---- 1 mysql mysql  370024448 Apr 14 06:06 partsupp.MYI
-rw-rw---- 1 mysql mysql       8648 Apr 13 23:33 region.frm
-rw-rw---- 1 mysql mysql        412 Apr 13 23:33 region.MYD
-rw-rw---- 1 mysql mysql       3072 Apr 13 23:33 region.MYI
-rw-rw---- 1 mysql mysql       8804 Apr 13 19:20 supplier.frm
-rw-rw---- 1 mysql mysql   13888184 Apr 13 19:20 supplier.MYD
-rw-rw---- 1 mysql mysql    1848320 Apr 13 19:20 supplier.MYI

Scale Factor 10 Results

All times are in seconds; all storage sizes are in bytes. Index storage includes PRIMARY KEY.

Queries

Results for queries 1-18 are graphed here (times in seconds):

Image:tpch_sf10_queries.png

(As noted above, results for query 11 should be ignored due to a problem with the runs.)

Load Time

This is how long it took to load the data (ANALYZE time not included):

Image:tpch_sf10_load.png

LucidDB takes longer to load the base data, but is able to take advantage of the compression results later while building indexes.

Storage

Storage comparison for the LINEITEM table (which dominates the database) is graphed here (measured in bytes):

Image:tpch_sf10_storage.png

If we drill into the LucidDB storage by column for this table, we find something interesting:

Image:tpch_sf10_column_storage.png

Most of the storage is eaten up by the comment text field, whereas the other columns compress quite well. With column store, the comment column is only accessed if the query actually needs it, whereas with row store, the I/O cost is always paid up front.

The LucidDB index storage breakdown for this table is also enlightening:

Image:tpch_sf10_index_storage.png

Most of the storage is eaten up by the primary key index, which is hard to compress; the other index bitmaps compress quite well. The primary key on the fact table is not accessed much except at load time, so in terms of query I/O, it isn't relevant.

SF 10 Numbers


MySQL LucidDB
QUERIES seconds seconds
1 464.6 372.5
2 26 18.0
3 3301.4 74.2
4 269.3 86.4
5 1724.3 64.7
6 63.6 21.8
7 94.8 278.8
8 42.8 18.9
9 533 169.0
10 2730.9 119.6
11 bad run bad run
12 1388.7 48.5
13 571.2 180.0
14 487 46.9
15 357.1 70.0
16 132.2 69.7
17 49.1 138.1
18 thrash 249.3
19 68.3
20 248 83.8
21 1787.3
22 6.7



LOAD TABLE seconds (does not include primary key creation) seconds (includes primary key creation)
NATION 0.1 1.6
REGION 0.1 1.4
PART 15.1 42.1
SUPPLIER 0.8 3.0
CUSTOMER 12.7 32.2
PARTSUPP 60.4 210.8
ORDERS 135 584.7
LINEITEM 665.7 4841.4



PRIMARY KEY seconds seconds
NATION 0.1
REGION 0.5
PART 21.3
SUPPLIER 1.2
CUSTOMER 19.9
PARTSUPP 100.7
ORDERS 158.4
LINEITEM 741.7



CREATE INDEX seconds seconds
NATION(N_REGIONKEY) 1.1 0.4
SUPPLIER(S_NATIONKEY) 1.5 0.6
PARTSUPP(PS_PARTKEY) 82.3 32.0
PARTSUPP(PS_SUPPKEY) 120 53.1
CUSTOMER(C_NATIONKEY) 24.4 1.2
ORDERS(O_CUSTKEY) 142.6 98.1
LINEITEM(L_ORDERKEY) 1002.7 168.7
LINEITEM(L_PARTKEY,L_SUPPKEY) 1370 452.6
LINEITEM(L_SHIPDATE) 1993.9 599.0
CUSTOMER(C_MKTSEGMENT) 27.9 1.1
ORDERS(O_ORDERDATE) 281.1 149.2
LINEITEM(L_SUPPKEY) 1691.1 523.4
REGION(R_NAME) 0.7 0.5
LINEITEM(L_DISCOUNT) 2334.1 15.3
LINEITEM(L_QUANTITY) 2528.7 24.5
LINEITEM(L_RETURNFLAG) 2981.2 11.7
LINEITEM(L_SHIPMODE) 3475.8 14.9
LINEITEM(L_COMMITDATE) 3733.9 565.3
LINEITEM(L_RECEIPTDATE) 3968 557.3
LINEITEM(L_PARTKEY) 4300.6 457.8
PART(P_SIZE) 27 1.5
PART(P_TYPE) 43.2 2.4
PARTSUPP(PS_SUPPLYCOST) 163.9 62.4
NATION(N_NAME) 0.2 0.6
PART(P_NAME) 57 21.4
ORDERS(O_CLERK) 437.2 181.3
PART(P_BRAND) 71.2 1.6
PART(P_CONTAINER) 76.5 1.3



ANALYZE TABLE seconds seconds
NATION 0.1 2.1
REGION 0.1 1.4
PART 0.1 4.5
SUPPLIER N/A 2.9
PARTSUPP 15.9 6.8
CUSTOMER N/A 3.5
ORDERS N/A 10.6
LINEITEM 290 36.3



STORAGE (DATA) bytes bytes
NATION 2,316 262,144
REGION 412 196,608
PART 234,451,704 126,353,408
SUPPLIER 13,888,184 14,155,776
PARTSUPP 1,160,039,052 1,156,907,008
CUSTOMER 238,173,452 224,034,816
ORDERS 1,472,903,276 1,288,536,064
LINEITEM 5,296,436,176 3,668,377,600



STORAGE (INDEXES) bytes bytes
NATION 4,096 131,072
REGION 3,072 98,304
PART 156,191,744 176,553,984
SUPPLIER 1,848,320 2,883,584
PARTSUPP 370,024,448 329,875,456
CUSTOMER 38,496,256 42,696,704
ORDERS 585,344,000 595,722,240
LINEITEM 7,186,396,160 3,918,462,976

Scale Factor 30 Results

To test LucidDB scalability, a separate test was carried out comparing LucidDB SF 10 against LucidDB SF 30. (No MySQL run was attempted at SF 30.) These test runs used a different machine with a larger filesystem mounted on SAN (which is why the SF 10 numbers are better than the ones above); other than that, the hardware and LucidDB configuration were similar to the runs described above (in particular, still 8 GB RAM).

Image:tpch_sf10_vs_30.png

Left-hand bars show the SF 10 results; right-hand bars show the SF 30 results. The middle bars are computed as three times the SF 10 results (for comparing the expected results given perfect linear scaleup vs the actual SF 30 results).

SF 30 Numbers


LucidDB SF 10 LucidDB SF 30

Time (sec) Time (sec)
GRAND TOTAL 10216 33703
LOAD TABLE 4820 14976
NATION 2 1
REGION 1 1
PART 38 110
SUPPLIER 3 6
CUSTOMER 29 85
PARTSUPP 122 387
ORDERS 495 1519
LINEITEM 4131 12867



CREATE INDEX 3779 13509
NATION(N_REGIONKEY) 1 1
SUPPLIER(S_NATIONKEY) 1 1
PARTSUPP(PS_PARTKEY) 25 76
PARTSUPP(PS_SUPPKEY) 45 138
CUSTOMER(C_NATIONKEY) 1 2
ORDERS(O_CUSTKEY) 89 295
LINEITEM(L_ORDERKEY) 142 426
LINEITEM(L_PARTKEY,L_SUPPKEY) 429 1644
LINEITEM(L_SHIPDATE) 574 1999
CUSTOMER(C_MKTSEGMENT) 1 2
ORDERS(O_ORDERDATE) 132 426
LINEITEM(L_SUPPKEY) 519 1979
REGION(R_NAME) 1 1
LINEITEM(L_DISCOUNT) 12 37
LINEITEM(L_QUANTITY) 17 51
LINEITEM(L_RETURNFLAG) 10 28
LINEITEM(L_SHIPMODE) 12 34
LINEITEM(L_COMMITDATE) 565 2054
LINEITEM(L_RECEIPTDATE) 559 2052
LINEITEM(L_PARTKEY) 422 1551
PART(P_SIZE) 2 3
PART(P_TYPE) 2 4
PARTSUPP(PS_SUPPLYCOST) 55 188
NATION(N_NAME) 1 1
PART(P_NAME) 15 47
ORDERS(O_CLERK) 149 466
PART(P_BRAND) 1 2
PART(P_CONTAINER) 1 2



ANALYZE TABLE (ESTIMATE STAT) 64 228
NATION 2 2
REGION 1 1
PART 3 5
SUPPLIER 2 2
PARTSUPP 6 14
CUSTOMER 3 5
ORDERS 10 28
LINEITEM 37 171



QUERIES 1552 4991
1 287 842
2 13 48
3 60 207
4 74 231
5 63 168
6 19 57
7 273 837
8 17 70
9 105 462
10 88 184
11 10 30
12 35 138
13 2 9
14 36 117
15 68 192
16 53 171
17 116 375
18 170 653
19

20 67 200

Attachments

Personal tools