Analyzing air traffic performance with InfoBright and MonetDB

How come people from InfoBright did not offer you a full commercial edition right after your first post on InfoBright?

Hi Vadim!

thanks – really nice to see you guys looking into these analytical databases. I’m just writing to inform you that the link to the graph seems broken.

Vadim

“Any points on way into analytical databases”

no I have no particular on-topic points. I just added a comment as FYI re. the broken link, and used the opportunity to thank you for the comparison. I happen to do some BI/DWH for my job, but our volumes are so modest that I haven’t had the need for a special purpose product yet. I’m just interested and try to keep up with all those developments.

Thanks Vadim!
I was hoping you’d do something like this! I really appreciate the use of real world data because in my experience random, dummy data without any similarity to real world data just doesn’t give realistic info when it comes to large datasets.

I’m using ICE in production & the data compression is impressive (think green! as Brian Aker says). The query performance is impressive. But queries really have to be optimized and that is a challenge without explain. We’re not using joins (but unlike mysql you can have more columns) and we’re only using INTs, no varchars or date (tho my test with using date resulted in a very very slight increase in load time, not anything noticeable in query time). But if you mess up & run a query with curdate() in it, oh boy is that slow. Then do NOT kill it. That’s a bug (reported & they are working on it).

Thanks for doing these benchmarks.
erin

Hi Vadim,

We’ve written up a page with instructions on the LucidDB load (including experimental support for parallel load):

http://pub.eigenbase.org/wiki/LucidDbOtp

Best results will probably be achieved with one year per INSERT statement (theoretically you could use a massive UNION ALL to do them all at once, but I haven’t tested that).

We’re guessing that the performance degradation and eventual failures you hit may have been due to the blanks for number fields (which LucidDB is picky about); the cleansing CASE expressions are supplied to fix those. In case there are other problems, we’ve included a section on how to enable row rejection for debugging them.

Please let me know if you need any help with getting it working or tuned; we’ll be interested to see your results. LucidDB is targeted at complex schemas and queries, but we’d like to know where we stand even on simple table+query environments.

It’s great to see these independent tests of Infobright Community Edition (ICE) and other open source products. The massive compression you saw is typical of what ICE and IEE (enterprise edition) users see.

A couple of comments in regards to Infobright:

While we have lots of ICE users with relatively small databases (which we would consider under 200GB), the real power of the product can be seen when the database is considerably larger(hundreds of gigabytes to tens of terabytes.) That’s when the Knowledge Grid advantages really show, as it’s ability to eliminate the need to access large amounts of data results in great query performance versus other products. Typically we also see that the kinds of queries people run don’t have a where clause that encompasses half the rows in the databases. How about doing a test against a 500 GB database? Try it with putting in a typical report date range (a month?) and see what kind of results you get.

As far as getting access to IEE as mentioned by someone above, a free trial is available via download on the Infobright.com website, or we could send it to you if you want. The compression and query speed will be comparable to ICE, but there are more options for loading data and faster load speed.

Carl Gelbart
Sr. S.E.
Infobright

Vadim,

The best examples of high schema and query complexity I know of are locked away inside of the now-defunct LucidEra. CRM analytic schemas there involved numerous fact tables (e.g. leads, opportunities, orders, forecasts, quotas), joining on many conforming dimensions (e.g. time, lead owners, salesreps, territories, campaigns, prospects, customers, opportunity type/status, pricing, partners, discounts, on and on…), and in fact many of the fact and dimension “tables” were actually views joining underlying base tables in order to make the warehouse schema accessible via Mondrian’s dimensional model.

But if you want to see a reasonable amount of query complexity on a public dataset, use Mondrian’s FOODMART schema with LucidDB:

http://pub.eigenbase.org/wiki/LucidDbOlap

And then execute MDX queries involving lots of filtered dimensions, and trace the underlying SQL (you’ll see a lot of star joins). The data volume is piddling though.

For schema complexity, I’m not aware of any, but if you can find something that already has Mondrian set up with PostgreSQL or MySQL, we have an automated procedure for replicating that into LucidDB:

http://pub.eigenbase.org/wiki/LucidDbMondrianReplication

Dear Vadim,

We appreciate your tryout of the system. We did not found the time yet to load the referenced data and re-run the experiments to shed some light on the questions raised. (Does RITA http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time has a (hidden) textual interface to quickly download the files? Can I download your cvs files?)

– we are aware that the documentation can be improved, especially in areas where confusion may arise due to SQL 2003 compliance.

– the parallel loading in 5.14 was turned off due to a design error that could materialize in COPY with initial offsets. The November release has it fixed. (on tpch-sf10 loading it is locally 3 x faster then the release you used)

– MonetDB does not rely heavily on value compression (the X100 project, now part of VectorWise/Ingres does)
MonetDB does, however, use the minimal data types required to store information.

– MonetDB automatically builds a dictionary system for strings
string heaps = 64KB are opportunistically (imperfect) duplicate eliminated

– The growth in database size most likely comes from the logical references (8bytes) into the dictionary(short strings)

– Q7 would be a target for us to run with the statement modifiers EXPLAIN (for plan) and TRACE (for detailed performance trace)
We would appreciate those for inspection.

– same for Q9. Note that MonetDB does not create any a priory (persistent) index, sorting or clustering to answer aggregate queries.
Everything is done at runtime and upon need. Including sampling for grouping.
I guess that Infobright benefits here from its partitioning information and compression over sorted tables (year).
Q3 is an example that highlights the differences in the ‘rough’.

regards,
Martin Kersten
The MonetDB Team

Matin,

Thank you for follow up.

you can download data by direct links like

http://www.transtats.bts.gov/Download/On_Time_On_Time_Performance_1988_1.zip

just change year and month in loop.

I am going to try another benchmark, suggested by John Sichi http://www.cs.umb.edu/~poneil/StarSchemaB.PDF, but on 500GB+ data size. Should I wait on your
November release, I guess parallel load will be useful. What is ETA for release ?

Can I try X100 project or it is not available yet ?

Thanks,
Vadim

Dear Vadim,
The csv files have been obtained without problem. Can you sent me the SQL schema used for MonetDB?
It appears that some input files have errors (or I used wrong column types).
thanks, Martin

Dear Vadim et.al.

This data warehouse is interesting, as it is one from the ‘wild’.
The dimension and fact tables have been combined into a single table.
Furthermore, the varchar(10) used often in the schema is an
overshoot for the actual values stored.

I have ran the load and queries on the MonetDB Nov 2009 RC.
The results can be seen here http://www.cwi.nl/~mk/ontimeReport

The results align with those reported here. However, we may consider
reviving an old manual technique applied in an older version of MonetDB,
which would bring the storage down to ca 10 GB.

regards, Martin

Martin,

I see you have comment about load time and datasize.
Is there something I should change to get better results ?

Thanks.
Vadim

There is comment from David Lutz, InfoBright:

Hello! I have been testing with this data set, as well, thanks to your suggestion. There are many differences in our two environments and I don’t want to post the whole test as it would be almost as big as your original post.

What I did want to offer is that I performed the test on 10 years of data using the current version of Infobright Enterprise Edition (3.2.2) which has a multi-threaded loader. I loaded different years and my total raw data was only ~27GB in size (~1.5GB compressed). My experience in loading was very consistent for each subsequent year at between 4m12s and 4m20s for each complete year (concatenated the months into a single file) or around 2,560 seconds. The total record count was 70,097,260 records.

Another way of saying that would be 273,817 records/second versus your experience of 13,244 records/second (117,023,290 records in 8,836 seconds) with the single-threaded loader provided in Infobright Community Edition, a ~20X improvement (on my server) and using the DDL below.

The DDL is the other thing. When appropriate I used the COMMENT ‘LOOKUP’ attribute offered by Infobright to transparently convert repetitive text strings to internal, integer lookup values. This improves load times, compression on those columns, and query performance when one of these columns is used for filtering, joining or aggretating on. This is apparent in the improvements in response time for several of the queries above.

The DDL I used is included below:

DROP TABLE IF EXISTS ontime;
CREATE TABLE ontime(
Year SMALLINT NOT NULL, — 1 Year
Quarter TINYINT NOT NULL, — 2 Quarter (1-4)
Month TINYINT NOT NULL, — 3 Month
DayofMonth TINYINT NOT NULL, — 4 Day of Month
DayOfWeek TINYINT NOT NULL, — 5 Day of Week
FlightDate DATE NOT NULL, — 6 Flight Date (YYYY-MM-DD) [not (yyyymmdd)]
UniqueCarrier CHAR(2) NOT NULL COMMENT ‘LOOKUP’, — 7 Unique Carrier Code. When the same code has been used by multiple …
— field for analysis across a range of years.
AirlineID SMALLINT NOT NULL, — 8 An identification number assigned by US DOT to identify a unique …
Carrier CHAR(2) NOT NULL COMMENT ‘LOOKUP’, — 9 Code assigned by IATA and commonly used to identify a carrier. As the …
TailNum CHAR(6) NOT NULL COMMENT ‘LOOKUP’, — 10 Tail Number
FlightNum SMALLINT NOT NULL, — 11 Flight Number
Origin CHAR(3) NOT NULL COMMENT ‘LOOKUP’, — 12 Origin Airport
OriginCityName VARCHAR(50) NOT NULL COMMENT ‘LOOKUP’, — 13 Origin Airport, City Name
OriginState CHAR(2) NOT NULL COMMENT ‘LOOKUP’, — 14 Origin Airport, State Code
OriginStateFips TINYINT NOT NULL, — 15 Origin Airport, State Fips
OriginStateName VARCHAR(50) NOT NULL COMMENT ‘LOOKUP’, — 16 Origin Airport, State Name
OriginWac TINYINT NOT NULL, — 17 Origin Airport, World Area Code
Dest CHAR(3) NOT NULL COMMENT ‘LOOKUP’, — 18 Destination Airport
DestCityName VARCHAR(50) NOT NULL COMMENT ‘LOOKUP’, — 19 Destination Airport, City Name
DestState CHAR(2) NOT NULL COMMENT ‘LOOKUP’, — 20 Destination Airport, State Code
DestStateFips TINYINT NOT NULL, — 21 Destination Airport, State Fips
DestStateName VARCHAR(50) NOT NULL COMMENT ‘LOOKUP’, — 22 Destination Airport, State Name
DestWac TINYINT NOT NULL, — 23 Destination Airport, World Area Code
CRSDepTime CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 24 CRS Departure Time (local time: hhmm)
DepTime CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 25 Actual Departure Time (local time: hhmm)
DepDelay DECIMAL(6,2) NOT NULL, — 26 Difference in minutes between scheduled and actual departure time. Early …
DepDelayMinutes DECIMAL(6,2) NOT NULL, — 27 Difference in minutes between scheduled and actual departure time. Early …
DepDel15 TINYINT NOT NULL, — 28 Departure Delay Indicator, 15 Minutes or More (1=Yes)
DepartureDelayGroups TINYINT NOT NULL, — 29 Departure Delay intervals, every (15 minutes from 180)
DepTimeBlk CHAR(9) NOT NULL, — 30 CRS Departure Time Block, Hourly Intervals
TaxiOut DECIMAL(6,2) NOT NULL, — 31 Taxi Out Time, in Minutes
WheelsOff CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 32 Wheels Off Time (local time: hhmm)
WheelsOn CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 33 Wheels On Time (local time: hhmm)
TaxiIn DECIMAL(6,2) NOT NULL, — 34 Taxi In Time, in Minutes
CRSArrTime CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 35 CRS Arrival Time (local time: hhmm)
ArrTime CHAR(4) NOT NULL COMMENT ‘LOOKUP’, — 36 Actual Arrival Time (local time: hhmm)
ArrDelay DECIMAL(6,2) NOT NULL, — 37 Difference in minutes between scheduled and actual arrival time. Early …
ArrDelayMinutes DECIMAL(6,2) NOT NULL, — 38 Difference in minutes between scheduled and actual arrival time. Early …
ArrDel15 TINYINT NOT NULL, — 39 Arrival Delay Indicator, 15 Minutes or More (1=Yes)
ArrivalDelayGroups TINYINT NOT NULL, — 40 Arrival Delay intervals, every (15-minutes from 180)
ArrTimeBlk CHAR(9) NOT NULL, — 41 CRS Arrival Time Block, Hourly Intervals
Cancelled TINYINT NOT NULL, — 42 Cancelled Flight Indicator (1=Yes)
CancellationCode VARCHAR(100), — 43 Specifies The Reason For Cancellation
Diverted TINYINT NOT NULL, — 44 Diverted Flight Indicator (1=Yes)
CRSElapsedTime DECIMAL(6,2) NOT NULL, — 45 CRS Elapsed Time of Flight, in Minutes
ActualElapsedTime DECIMAL(6,2) NOT NULL, — 46 Elapsed Time of Flight, in Minutes
AirTime DECIMAL(6,2) NOT NULL, — 47 Flight Time, in Minutes
Flights TINYINT NOT NULL, — 48 Number of Flights
Distance DECIMAL(6,2) NOT NULL, — 49 Distance between airports (miles)
DistanceGroup TINYINT NOT NULL, — 50 Distance Intervals, every 250 Miles, for Flight Segment
CarrierDelay DECIMAL(6,2), — 51 Carrier Delay, in Minutes
WeatherDelay DECIMAL(6,2), — 52 Weather Delay, in Minutes
NASDelay DECIMAL(6,2), — 53 NAS Delay, in Minutes
SecurityDelay DECIMAL(6,2), — 54 Security Delay, in Minutes
LateAircraftDelay DECIMAL(6,2), — 55 Late Aircraft Delay, in Minutes
FirstDepTime CHAR(4), — 56 First Gate Departure Time at Origin Airport
TotalAddGTime DECIMAL(6,2), — 57 Total Ground Time Away from Gate for Gate Return or Cancelled Flight
LongestAddGTime DECIMAL(6,2), — 58 Longest Time Away from Gate for Gate Return or Cancelled Flight
DivAirportLandings TINYINT, — 59 Number of Diverted Airport Landings
DivReachedDest TINYINT, — 60 Diverted Flight Reaching Scheduled Destination Indicator (1=Yes)
DivActualElapsedTime DECIMAL(6,2), — 61 Elapsed Time of Diverted Flight Reaching Scheduled Destination, in …
DivArrDelay DECIMAL(6,2), — 62 Difference in minutes between scheduled and actual arrival time for a …
DivDistance DECIMAL(6,2), — 63 Distance between scheduled destination and final diverted airport …
Div1Airport CHAR(3), — 64 Diverted Airport Code1
Div1WheelsOn CHAR(4), — 65 Wheels On Time (local time: hhmm) at Diverted Airport Code1
Div1TotalGTime DECIMAL(6,2), — 66 Total Ground Time Away from Gate at Diverted Airport Code1
Div1LongestGTime DECIMAL(6,2), — 67 Longest Ground Time Away from Gate at Diverted Airport Code1
Div1WheelsOff CHAR(4), — 68 Wheels Off Time (local time: hhmm) at Diverted Airport Code1
Div1TailNum CHAR(6), — 69 Aircraft Tail Number for Diverted Airport Code1
Div2Airport CHAR(3), — 70 Diverted Airport Code2
Div2WheelsOn CHAR(4), — 71 Wheels On Time (local time: hhmm) at Diverted Airport Code2
Div2TotalGTime DECIMAL(6,2), — 72 Total Ground Time Away from Gate at Diverted Airport Code2
Div2LongestGTime DECIMAL(6,2), — 73 Longest Ground Time Away from Gate at Diverted Airport Code2
Div2WheelsOff CHAR(4), — 74 Wheels Off Time (local time: hhmm) at Diverted Airport Code2
Div2TailNum CHAR(6), — 75 Aircraft Tail Number for Diverted Airport Code2
Div3Airport CHAR(3), — 76 Diverted Airport Code3
Div3WheelsOn CHAR(4), — 77 Wheels On Time (local time: hhmm) at Diverted Airport Code3
Div3TotalGTime DECIMAL(6,2), — 78 Total Ground Time Away from Gate at Diverted Airport Code3
Div3LongestGTime DECIMAL(6,2), — 79 Longest Ground Time Away from Gate at Diverted Airport Code3
Div3WheelsOff CHAR(4), — 80 Wheels Off Time (local time: hhmm) at Diverted Airport Code3
Div3TailNum CHAR(6), — 81 Aircraft Tail Number for Diverted Airport Code3
Div4Airport CHAR(3), — 82 Diverted Airport Code4
Div4WheelsOn CHAR(4), — 84 Wheels On Time (local time: hhmm) at Diverted Airport Code4
Div4TotalGTime DECIMAL(6,2), — 85 Total Ground Time Away from Gate at Diverted Airport Code4
Div4LongestGTime DECIMAL(6,2), — 86 Longest Ground Time Away from Gate at Diverted Airport Code4
Div4WheelsOff CHAR(4), — 87 Wheels Off Time (local time: hhmm) at Diverted Airport Code4
Div4TailNum CHAR(6), — 88 Aircraft Tail Number for Diverted Airport Code4
Div5Airport CHAR(3), — 89 Diverted Airport Code5
Div5WheelsOn CHAR(4), — 90 Wheels On Time (local time: hhmm) at Diverted Airport Code5
Div5TotalGTime DECIMAL(6,2), — 91 Total Ground Time Away from Gate at Diverted Airport Code5
Div5LongestGTime DECIMAL(6,2), — 92 Longest Ground Time Away from Gate at Diverted Airport Code5
Div5WheelsOff CHAR(4), — 93 Wheels Off Time (local time: hhmm) at Diverted Airport Code5
Div5TailNum CHAR(6) — 94 Aircraft Tail Number for Diverted Airport Code5
)
ENGINE=BRIGHTHOUSE
CHARACTER SET ascii
COLLATE ascii_bin
;

Dear Vadim,
I run the same benchmark against MonetDB5, the execution time is very short, however, after I run this same query for 10000 times, the mserver5 process used up to 1.5G memory and crashed. I can see some crash reports in the monetdb forum, but no memory leak report, could you give me some advices? Did you encouter this problem when you run the benchmark?
Thanks

Dear Frank,
Sounds as an error I would like to reconfirm (and solve if correct).
Can you provide more information on this experiment on the MonetDB users’ list.
monetdb-users@lists.sourceforge.net
In particular, version used, platform characteristics, and experiment script.

Dear all,

This benchmark was a good exercise to re-prioritize some of our development efforts towards improvements for daily BI use.
Although the original results were quite promising, we knew that low hanging fruit in the MonetDB stack was not harvested.

A peek preview on the performance results for the next feature release can be found in http://www.cwi.nl/~mk/ontimeReport

Happy newyear. Martin

First of all, I would like to thank Vadim and mysqlperformanceblog team for very interesting posts – I am a regular visitor of your web-site.

In our company we are looking for D/W solution which will allow us to do a real-time (10-15 sec) queries on 1B+ row tables. InfoBright, MonetDB, InfiniDB are in my short lists. Having read your article first time I was impressed by performance numbers of columnar databases, but now … I think that there are a lot of room for query optimization inside both MonetDB and Infobright.

For example, this query takes 7.9 sec for MonetDB and 12 sec for Infobright.

-Q1: Count flights per day from 2000 to 2008 years
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE YearD BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC

This is a full column scan query. Group BY column has very low cardinality (7) => do grouping and aggregation in memory.
DayOfWeek requires only 3 bits per row => for 117M row table, it will need ~30Mb to store the whole column.

Loading column from disk (30MB), decompressing it and aggregating 117M numbers should not take more than 1 sec (actually, I think I can do it on my laptop in less than 0.5 sec).

Vadim, can you forward my e-mail address and question to MonetDB and Infobright guys?

This is query:

SELECT T.C FROM T WHERE (up to 4 simple filter conditions ANDed) ORDER BY T.B DESC LIMIT N;

The good selectivity is not guaranteed. It may require almost full table scan in a worst case. Can they do this type of query under 15 sec in a worst case on 1B table which is similar to the table from this post (120 columns – most of them with low cardinality – no strings only numbers)

Thanks, Martin

I will definitely give MonetDB a try.

For anyone who’s interested in this data and/or in these queries as a benchmark, I’ve created a github repository:

https://github.com/eyalroz/usdt-ontime-tools

for automating the download of the CSVs and the loading of the data – at least into MonetDB. The numbers have changed significantly since 2009 – but there are also schema changes because of what BTS provides; some make the schema ‘tighter’ – e.g. the fact that the decimal values are not really decimal, they always end with .00 and are really ints, so my loading script cleans them up; but on the other hand, you can’t rely on having exactly 6 chars in the tailnum, and sometimes it’s even missing entirely.

I’ll be using this to try out some GPU decompression primitives.