Analyzing air traffic performance with InfoBright and MonetDB

“Worth to note that MongoDB supports all INSERT / UPDATE / DELETE statements (and space is price for that as I understand), while InfoBright ICE edition allows you only LOAD DATA. InfoBright Enterprise allows INSERT / UPDATE but that also is not for online transactions processing.”

I guess, here you meant MonetDB, instead of MongoDB? 🙂

Anyway, good post.

Vadim, very interesting results.
You can also try sphinxsearch (http://www.sphinxsearch.com/) for some of those queries (it can now group by and order by). I’ve done some tests on the smaller dataset, and it gives very good results: http://www.arubin.org/blog/2009/10/01/reporting-queries-with-sphinx/
Will be interesting to see how sphinx performs on 55G

Vadim,

Infobright ICE does some serious optimisations on columns if they have less then 10,000 unique entries in the column and if you specify “COMMENT ‘lookup'” in the definition (they automatically generate and maintain an ENUM, I think). Think of them like indexes.

If you change the definitions for the key columns in the queries, does that change your results at all?

Good post, but why not try to run TPC-H test ?

Hey Vadim,

One more variation for InfoBright…

You might try using the FlightTime fields instead of the Year, Month, DayOfWeek stuff for your range queries. I found that the Knowledge Grid does some wonderful things with range queries over dates that it didn’t do for straight integers (even simple equality).

James.

John,

Thank you, I will try it in next couple days.

Do you have public examples with complex queries and schemas, beside TPC-H ?

Thanks,
Vadim

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.
[email protected]
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.