LucidDbSqbmComparativeResults

From Eigenpedia

Jump to: navigation, search

Contents

Overview

This page is under construction.

This page provides an unscientific, informal performance analysis of LucidDB in terms of the Set Query Benchmark (using MySQL as a standard reference point). It is not an official benchmark execution report and should not be interpreted as such. Readers of this page are encouraged to execute their own benchmark configurations, since environment and performance requirements vary greatly by application.

Environment

Test machine was a 3Ghz 4-CPU machine with 8 gigabytes of RAM (Dell PowerEdge 2950), running Red Hat Enterprise Linux 4. Disk configuration was RAID-1. No parallelism was used by the tests, so only one CPU was active.

In order to simulate a typical data warehouse environment in which only a small portion of the database can fit in memory, the amount of physical memory made available to the DBMS was constrained to 64MB by setting the buffer cache size and requesting usage of direct I/O (disabling the file system cache). For example, MySQL index joins perform very well as long as the database fits in memory, but beyond that may degrade depending on locality of reference during the join.

LucidDB Setup

LucidDB 0.7 prerelease codebase as of eigenchange 9187.

alter system set "cachePagesInit"=2000;

(LucidDB page size is 32K, so 2000*32K = 64MB.)

MySQL Setup

MySQL version 5.0.22.

Relevant lines from my.cnf:

default-storage-engine=INNODB
key_buffer_size=64M
innodb_buffer_pool_size=64M
innodb_log_file_size=64M
innodb_log_buffer_size=8M
sort_buffer_size=64M
innodb_flush_method=O_DIRECT

The InnoDB storage engine was used in order to match a standard production configuration with fault tolerance (and because MyISAM does not support O_DIRECT).

The following command was used to load data from the flatfile into the database (as with LucidDB, indexes other than the primary key were not created until after the load).

LOAD DATA LOCAL INFILE '/path/to/luciddb-sqbm-testdata/bench1M.csv'
INTO TABLE BENCH1M   FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(KSEQ,K2,K4,K5,K10,K25,K100,K1K,K10K,K40K,K100K,K250K,K500K)
SET 
S1='12345678',
S2='12345678900987654321', 
S3='12345678900987654321', 
S4='12345678900987654321', 
S5='12345678900987654321',
S6='12345678900987654321', 
S7='12345678900987654321', 
S8='12345678900987654321';

Storage

  • Source data size (uncompressed .csv): 54.3MB
  • Source data size (compressed .bz2): 20.8MB
  • LucidDB database size unindexed: 98.4MB
  • LucidDB database size after indexing: 163.9MB
  • MySQL database size unindexed: 278MB
  • MySQL database size after indexing: 1176MB (can this be right?!?)

Timing

Operation LucidDB Time MySQL Time (seconds)
Load from flatfile 60.3 45.1
CREATE INDEX on K2 2.1 69.6
CREATE INDEX on K4 1.4 69.4
CREATE INDEX on K5 1.3 77.2
CREATE INDEX on K10 1.4 86.3
CREATE INDEX on K25 1.6 93.7
CREATE INDEX on K100 2.0 110.4
CREATE INDEX on K1K 3.4 126.3
CREATE INDEX on K10K 13.5 142.0
CREATE INDEX on K40K 12.7 213.2
CREATE INDEX on K100K 12.3 449.5
CREATE INDEX on K250K 13.1 807.4
CREATE INDEX on K500K 14.6 1217.1
CREATE INDEX on (K2,K100) 16.8 1498.0
CREATE INDEX on (K4,K25) 15.3 1557.9
CREATE INDEX on (K10,K25) 16.2 1810.4
ANALYZE TABLE 55.8 0.7
Q1(KSEQ) 1.6 0.8
Q1(K100K) 0.7 0.1
Q1(K10K) 0.6 0.1
Q1(K1K) 0.7 0.1
Q1(K100) 0.7 0.1
Q1(K25) 0.8 0.2
Q1(K10) 0.8 0.4
Q1(K5) 0.8 0.6
Q1(K4) 1.0 0.6
Q1(K2) 1.1 0.6
Q2A(KSEQ) 0.9 0.1
Q2A(K100K) 0.9 0.1
Q2A(K10K) 0.8 0.4
Q2A(K1K) 0.8 0.8
Q2A(K100) 0.9 0.1
Q2A(K25) 0.9 0.6
Q2A(K10) 1.1 0.8
Q2A(K5) 1.0 0.8
Q2A(K4) 1.2 0.9
Q2B(KSEQ) 1.9 0.3
Q2B(K100K) 1.8 1545.9
Q2B(K10K) 1.9 1547.4
Q2B(K1K) 1.6 1547.2
Q2B(K100) 1.4 1.8
Q2B(K25) 1.3 1548.0
Q2B(K10) 1.4 1547.8
Q2B(K5) 1.3 1549.5
Q2B(K4) 1.4 1548.1
Q3A(K100K) 0.4 0.1
Q3A(K10K) 0.4 0.1
Q3A(K100) 0.5 1.1
Q3A(K25) 0.5 5.7
Q3A(K10) 0.7 6.2
Q3A(K5) 0.6 7.4
Q3A(K4) 0.7 8.0
Q3B(K100K) 0.5 0.1
Q3B(K10K) 0.5 0.1
Q3B(K100) 0.5 0.1
Q3B(K25) 0.6 0.2
Q3B(K10) 0.7 0.5
Q3B(K5) 0.7 0.9
Q3B(K4) 0.8 0.9
Q4.1 3.5 310.9
Q4.2 1.2 7.4
Q4.3 1.0 7.5
Q4.4 1.8 23.4
Q4.5 2.2 23.1
Q4.6 2.3 7.9
Q4.7 1.6 6.4
Q4.8 1.6 27.1
Q4.9 7.7 1544.2
Q4.10 7.9 22.9
Q4.11 7.6 23.1
Q4.12 8.1 23.1
Q4.13 19.0 8.2
Q4.14 17.1 8.2
Q4.15 17.2 26.8
Q5.1 4.5 2.4
Q5.2 4.0 2.2
Q5.3 4.5 2.6
Q6A (K100K) 1.1 0.1
Q6A (K40K) 1.1 0.3
Q6A (K10K) 1.3 0.7
Q6A (K1K) 1.4 5.1
Q6A (K100) 1.3 12.3
Q6B (K40K) 1.1 0.2
Q6B (K10K) 1.1 1.0
Q6B (K1K) 0.8 10.4
Q6B (K100) 1.0 83.1
Personal tools