LucidDbSqbmComparativeResults
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 |
