After my first post Analyzing air traffic performance with InfoBright and MonetDB where I was not able to finish task with LucidDB, John Sichi contacted me with help to setup. You can see instruction how to load data on LucidDB Wiki page
You can find the description of benchmark in original post, there I will show number I have for LucidDB vs previous systems.
Load time
To load data into LucidDB in single thread, it took for me 15273 sec or 4.24h. In difference with other systems LucidDB support multi-threaded load, with concurrency 2 (as I have only 2 cores on that box), the load time is 9955 sec or 2.76h. For comparison
for InforBright load time is 2.45h and for MonetDB it is 2.6h
DataSize
Another interesting metric is datasize after load. In LucidDB db file after load takes 9.3GB.
UPDATE 27-Oct-2009 From metadata table the actual size of data is 4.5GB, the 9.3GB is size of physical file db.dat, which probably was not truncated after several loads of data.
For InfoBright it is 1.6GB, and for MonetDB – 65GB. Obviously LucidDB uses some compression, but it is not so aggressive as in InfoBright case. As original dataset is 55GB, compression rate for LucidDB is somewhat 1:12
Queries time
Let me put list of queries and times for all systems.
– Lame query “count start”
LucidDB:
1 | SELECT count(*) FROM otp."ontime"; |
1 row selected (55.165 seconds)
Both InfoBright and MonetDB returned result immediately.
It seems LucidDB has to scan whole table to get result.
– Q0:
1 | select avg(c1) from (select "Year","Month",count(*) as c1 from otp."ontime" group by "Year","Month") t; |
LucidDB: 103.205 seconds
InfoBright: 4.19 sec
MonetDB: 29.9 sec
– Q1:
SELECT “DayOfWeek”, count(*) AS c FROM OTP.”ontime” WHERE “Year” BETWEEN 2000 AND 2008 GROUP BY “DayOfWeek” ORDER BY c DESC;
LucidDB: 49.17 seconds
InfoBright: 12.13 sec
MonetDB: 7.9 sec
– Q2:
SELECT “DayOfWeek”, count(*) AS c FROM otp.”ontime” WHERE “DepDelay”>10 AND “Year” BETWEEN 2000 AND 2008 GROUP BY “DayOfWeek” ORDER BY c DESC;
LucidDB: 27.131 seconds
InfoBright: 6.37 sec
MonetDB: 0.9 sec
– Q3:
!set rowlimit 10
SELECT “Origin”, count(*) AS c FROM otp.”ontime” WHERE “DepDelay”>10 AND “Year” BETWEEN 2000 AND 2008 GROUP BY “Origin” ORDER BY c DESC;
LucidDB: 27.664 seconds
InfoBright: 7.29 sec
MonetDB: 1.7 sec
– Q4:
SELECT “Carrier”, count(*) FROM otp.”ontime” WHERE “DepDelay”>10 AND “Year”=2007 GROUP BY “Carrier” ORDER BY 2 DESC;
LucidDB: 2.338 seconds
InfoBright: 0.99 sec
MonetDB: 0.27 sec
– Q5:
SELECT t.”Carrier”, c, c2, c*1000/c2 as c3 FROM (SELECT “Carrier”, count(*) AS c FROM OTP.”ontime” WHERE “DepDelay”>10 AND “Year”=2007 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS c2 FROM OTP.”ontime” WHERE “Year”=2007 GROUP BY “Carrier”) t2 ON (t.”Carrier”=t2.”Carrier”) ORDER BY c3 DESC;
LucidDB: 7.351 seconds
InfoBright: 2.92 sec
MonetDB: 0.5 sec
– Q6:
SELECT t.”Carrier”, c, c2, c*1000/c2 as c3 FROM (SELECT “Carrier”, count(*) AS c FROM OTP.”ontime” WHERE “DepDelay”>10 AND “Year” BETWEEN 2000 AND 2008 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS c2 FROM OTP.”ontime” WHERE “Year” BETWEEN 2000 AND 2008 GROUP BY “Carrier”) t2 ON (t.”Carrier”=t2.”Carrier”) ORDER BY c3 DESC;
LucidDB: 78.423 seconds
InfoBright: 21.83 sec
MonetDB: 12.5 sec
– Q7:
SELECT t.”Year”, c1/c2 FROM (select “Year”, count(*)*1000 as c1 from OTP.”ontime” WHERE “DepDelay”>10 GROUP BY “Year”) t JOIN (select “Year”, count(*) as c2 from OTP.”ontime” GROUP BY “Year”) t2 ON (t.”Year”=t2.”Year”);
LucidDB: 106.374 seconds
InfoBright: 8.59 sec
MonetDB: 27.9 sec
– Q8:
SELECT “DestCityName”, COUNT( DISTINCT “OriginCityName”) FROM “ontime” WHERE “Year” BETWEEN 2008 and 2008 GROUP BY “DestCityName” ORDER BY 2 DESC;
Years, LucidDB, InfoBright, MonetDB
1y, 6.76s, 1.74s, 0.55s
2y, 28.82s, 3.68s, 1.10s
3y, 35.37s, 5.44s, 1.69s
4y, 41.66s, 7.22s, 2.12s
10y, 72.67s, 17.42s, 29.14s
– Q9:
select “Year” ,count(*) as c1 from “ontime” group by “Year”;
LucidDB: 76.121 seconds
InfoBright: 0.31 sec
MonetDB: 6.3 sec
As you see LucidDB is not showing best results. However on good side about LucidDB I can mention it is very reach featured, with full support of DML statement. ETL features is also very impressive, you can extract, filter, transform external data (there is even access to MySQL via JDBC driver) just in SQL queries (compare with single LOAD DATA statement in InfoBright ICE edition). Also I am not so much in Java, but as I understood LucidDB can be easily integrated with Java applications, which is important if your development is Java based.
Worth to mention that in LucidDB single query execution takes 100% of user time in single CPU, which may signal that there some low-hanging fruits for optimization. OProfile can show clear places to fix.
Thanks Vadim, glad you got it successfully running with LucidDB.
In the comments on your earlier post, I noted that LucidDB is optimized for complex schema/query patterns, so we’re not surprised to see it burning CPU on these ones. But the ball is in our court now to provide some representative examples for another round of comparison.
Regarding tuning, there’s a well-known technique for pushing aggregations down into filtered column scans (since the compressed representation in each block already has the list of distinct values as well as a count for each, from which the filtering can decrement for each row), but it hasn’t been implemented yet for LucidDB. I think that will provide an order of magnitude improvement for this query pattern (i.e. it’s an entirely different algorithmic as opposed to CPU tuning).
Regarding compression, Nick got a number under 4GB for the stored database size, so there may be some discrepancy there (perhaps he used a different set of indexes).
John,
Thanks for comment. I am going to look more in direction you are working.
Would be “APB-1 OLAP Benchmark”
http://www.olapcouncil.org/research/bmarkly.htm
interesting for you ?
The company, that developed it, seems dead, but the ideas of benchmark is worth to consider.
Thanks Vadim!
Much informative! I happened to look for some examples on database benchmarks among infobright, innodb etc. Thanks for the nice post!
Vadim,
Thanks for posting your results. I saw a similar profile of single CPU 100% when I was testing myself and agree there may be some “low hanging” fruit there for tuning. As a community driven open source project, we welcome any opportunity and help to improve any feature/performance results. As you noted in your blog, LucidDB is built for BI which means fast (er?) 🙂 but also includes the transparent data access(JDBC/custom), ability to do inline ELT, warehouse labels (for read consistent reporting while doing ETL).
re: Loading of data and size. I loaded years 1998 to 2008 since your queries where limited to that (although looking above it looks like the queries may have changed slightly). So, the 3GB I got was for the dataset we were querying on but it appears as if you loaded another 10 years? However, even there, I’d only expect to see the size up to 5-6GB.
Can you run / post the following storage report? This way we can see the size of the storage (idxes)
select table_schem as schema, table_name, index_name, pages*32/1024 as “SizeInMB” from sys_boot.jdbc_metadata.index_info_internal where table_schem = ‘OTP’ order by 1,2,4 desc;
Nicholas,
I think I understand where size difference comes from. In post I mentioned I loaded data in single thread, and after that in two-threads (I dropped schema between runs). Could it be that LucidDB did not reuse space after drop schema after first run?
The output you are asking is:
select table_schem as schema, table_name, index_name, pages*32/1024 from sys_boot.jdbc_metadata.index_info_internal where table_schem = ‘OTP’ order by 1,2,4 desc;
+———+————-+————————————————–+————-+
| SCHEMA | TABLE_NAME | INDEX_NAME | EXPR$3 |
+———+————-+————————————————–+————-+
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrTime | 224.250000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepTime | 215.781250 |
| OTP | ontime | DEPDELAY_IDX | 176.375000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$FlightNum | 169.281250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$TailNum | 168.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$WheelsOn | 159.000000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$WheelsOff | 156.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$CRSArrTime | 149.781250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ActualElapsedTime | 143.718750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$CRSDepTime | 136.000000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrDelay | 133.312500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepDelay | 128.906250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrDelayMinutes | 127.843750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepDelayMinutes | 126.843750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$CRSElapsedTime | 120.187500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Distance | 114.281250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$OriginCityName | 103.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Origin | 100.093750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$AirTime | 99.656250 |
| OTP | ontime | DAYOFWEEK_IDX | 94.000000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DestCityName | 86.750000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Dest | 83.937500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$TaxiOut | 82.906250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$OriginStateName | 79.531250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$OriginStateFips | 78.625000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$OriginState | 78.625000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$OriginWac | 78.437500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrTimeBlk | 75.031250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepTimeBlk | 74.656250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$FlightDate | 71.343750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DayofMonth | 70.687500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DestStateName | 66.437500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DestStateFips | 65.906250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DestState | 65.906250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DestWac | 65.718750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$TaxiIn | 64.968750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrivalDelayGroups | 60.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepartureDelayGroups | 60.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DistanceGroup | 53.687500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DayOfWeek | 43.437500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$CarrierDelay | 39.375000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$LateAircraftDelay | 39.375000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$NASDelay | 38.687500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$ArrDel15 | 30.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DepDel15 | 30.468750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$WeatherDelay | 30.187500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$UniqueCarrier | 17.125000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Carrier | 17.093750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$AirlineID | 17.093750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Cancelled | 15.656250 |
| OTP | ontime | YEAR_IDX | 15.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Diverted | 15.250000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$SecurityDelay | 14.156250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$CancellationCode | 9.937500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$FirstDepTime | 1.593750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$LongestAddGTime | 1.468750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$TotalAddGTime | 1.468750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1WheelsOn | 1.312500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1TotalGTime | 1.250000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1TailNum | 1.218750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1WheelsOff | 1.218750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1LongestGTime | 1.218750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div1Airport | 1.218750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DivArrDelay | 1.187500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DivActualElapsedTime | 1.187500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DivDistance | 0.968750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Month | 0.906250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DivReachedDest | 0.843750 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$DivAirportLandings | 0.812500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Quarter | 0.625000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2Airport | 0.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2WheelsOn | 0.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2TotalGTime | 0.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2LongestGTime | 0.562500 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Year | 0.531250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2TailNum | 0.531250 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4WheelsOn | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4Airport | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3TailNum | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3WheelsOff | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3LongestGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div2WheelsOff | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3TotalGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3WheelsOn | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div3Airport | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Flights | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4LongestGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4TotalGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4WheelsOff | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div4TailNum | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5Airport | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5WheelsOn | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5TotalGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5LongestGTime | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5WheelsOff | 0.500000 |
| OTP | ontime | SYS$CLUSTERED_INDEX$ontime$Div5TailNum | 0.500000 |
| OTP | ontime | SYS$DELETION_INDEX$ontime | 0.031250 |
+———+————-+————————————————–+————-+
97 rows selected (0.171 seconds)
0: jdbc:luciddb:rmi://localhost> select sum(pages*32/1024) from sys_boot.jdbc_metadata.index_info_internal where table_schem = ‘OTP’;
+————–+
| EXPR$0 |
+————–+
| 4617.843750 |
+————–+
1 row selected (0.841 seconds)
So it’s 4.5GB, but size of db.dat is 9486M. I will update post.
Vadim,
LucidDB does not reclaim space from dropped objects until the ALTER SYSTEM DEALLOCATE OLD is run. Had this of been done between runs the db.dat would be close to the 4.5 you observed. Sometimes this is beneficial as part of a batch ETL process, but it would be nice if it were automatic (or configurable).
Look forward seeing the new LucidDB release with better performance!
Vadim: for a more-up-to-date benchmark, see the Star Schema Benchmark. It’s a simplification of TPC-H which removes some of the joins in order to create a proper star. Still synthetic though.
http://www.cs.umb.edu/~poneil/StarSchemaB.PDF
John,
Are there utils around Star Schema Benchmark ? I mean datagen or something ?
Hm, LucidDB doesn’t look good in this benchmark. Was the test done with LucidDB 0.9.4 or some earlier version?
Otis,
It was 0.9.1.
I see latest available release 0.9.2
You are right, 0.9.2 is the latest.
Are you planning on doing another test with LucidDB? I’d be interested in seeing the comparison of LucidDB, MonetDB, and InfiniDB (they just had a release).
Thanks.
Otis,
I just run SSB benchmark, but results are not officially published yet.
You can see results here
https://www.percona.com/docs/wiki/benchmark:ssb:start
Also more benchmarks coming.
Thanks for the pointer, Vadim, I can’t wait to see the final results!
When doing these comparisons, are you getting experts from each project to tune/optimize their DB?
Otis,
I have review of the results from experts from each vendor.
There are some comments from them, but nothing significant that could change final results noticeably.
Excellent!
This is good, because benchmarks done in a vacuum are often not realistic, unless the person preparing the benchmark is truly an expert in all tools being benchmarked, which is very rarely the case. I know I’ve seen such expert-advice/review-free benchmarks in search engine land (Lucene, Solr, Sphinx, Xapian…) and, as a Lucene/Solr guy, found them frustrating and worthless.
Is all the code/setup used for benchmarking going to be available, so individuals and other organizations can make use of it, possibly improving it, and even maintaining it, so that it is easy to re-run the benchmark with newer releases of the software?
Putting everything on Github, where it can be easily forked, may make sense here.
We’re very happy to have a well-respected independent organization like Percona running these. As one of the “experts” involved, one thing I can say is that it would be great if we could be given direct access to the box during the setup phase to help with tuning, and then release it to Vadim for exclusive access/auditing of the final runs. Doing everything by proxy adds enough overhead that it’s not always possible to try as many configurations as one would like. Working with big data sets increases the challenge due to the times involved (particularly for loading), so starting with a small validation set first can help a lot too before moving on to full scale. I realize there are other issues like security and multi-user contention on shared hardware that may not make direct access practical.
What John wrote above makes sense: +1
Without that, the experts will always be a little handicapped and the benchmark quality will suffer.
Thank you for doing this valuable work!
Otis, John,
That’s OK. I can’t spend more time on SSB + ontime queries benchmarks, but I will be able
provide hardware access for next benchmark.