As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment.
See my previous posts on this topic:
Analyzing air traffic performance with InfoBright and MonetDB
Air traffic queries in LucidDB
I could not run all queries against InfiniDB and I met some hiccups during my experiment, so it was less plain experience than with other databases.
So let’s go by the same steps:
InfiniDB supports MySQL’s
statement and it’s own
colxml / cpimport
is more familiar for me, I started with that, however after issuing LOAD DATA on 180MB file ( for 1989 year, 1st month) very soon it caused extensive swapping (my box has 4GB of RAM) and statement failed with
ERROR 1 (HY000) at line 1: CAL0001: Insert Failed: St9bad_alloc
colxml / cpimport
was more successful, however it has less flexibility in syntax than
, so I had to transform the input files into a format that
Total load time was 9747 sec or 2.7h (not counting time spent on files transformation)
I put summary data into on load data time, datasize and query time to Google Spreadsheet so you can easy compare with previous results. There are different sheets for queries, datasize and time of load.
Size of database after loading is another confusing point. InfiniDB data directory has complex structure like
so it’s hard to day what files are related to table. But after load, the size of 000.dir is 114G, which is as twice big as original data files. SHOW TABLE STATUS does not really help there, it shows
with totally misleading information.
So I put 114GB as size of data after load, until someone points me how to get real size, and also explains what takes so much space.
First count start query
SELECT count(*) FROM ontime
took 2.67 sec, which shows that InfiniDB does not store counter of records, however calculates it pretty fast.
select avg(c1) from (select year,month,count(*) as c1 from ontime group by YEAR,month) t;
Another bumper, on this query InfiniDB complains
ERROR 138 (HY000):
The query includes syntax that is not supported by InfiniDB. Use 'show warnings;' to get more information. Review the Calpont InfiniDB Syntax guide for additional information on supported distributed syntax or consider changing the InfiniDB Operating Mode (infinidb_vtable_mode).
mysql> show warnings;
| Level | Code | Message |
| Error | 9999 | Subselect in From clause is not supported in this release. |
Ok, so InfiniDB does not support DERIVED TABLES, which is big limitation from my point of view.
As workaround I tried to create temporary table, but got another error:
mysql> create temporary table tq2 as (select Year,Month,count(*) as c1 from ontime group by Year, Month);
ERROR 122 (HY000): Cannot open table handle for ontime.
As warning suggests I turned
infinidb_vtable_mode = 2
, which is:
2) auto-switch mode: InfiniDB will attempt to process the query internally, if it
cannot, it will automatically switch the query to run in row-by-row mode.
but query took 667 sec :
so I skip queries Q5, Q6, Q7 from consideration, which are also based on DERIVED TABLES, as not supported by InfiniDB.
Other queries: (again look on comparison with other engines in Google Spreadsheet or in summary table at the bottom)
mysql> SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC;
7 rows in set (6.79 sec)
mysql> SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC;
7 rows in set (4.59 sec)
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year BETWEEN 2000 AND 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
mysql> SELECT Carrier, count(*) FROM ontime WHERE DepDelay > 10 AND YearD=2007 GROUP BY Carrier ORDER BY 2 DESC;
I had another surprise with query, after 15 min it did not return results, I check system and it was totally idle, but query stuck. I killed query, restarted mysqld but could not connect to mysqld anymore. In processes I see that InfiniDB started couple external processes:
ExeMgr, DDLProc, PrimProc, controllernode fg, workernode DBRM_Worker1 fg
which cooperate each with other using IPC shared memory and semaphores. To clean system I rebooted server, and only after that mysqld was able to start.
After that query Q4 took 0.75 sec
Queries Q5-Q7 skipped.
SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE YearD BETWEEN 2008 and 2008 GROUP BY DestCityName ORDER BY 2 DESC LIMIT 10;
And times for InfiniDB:
1y: 8.13 sec
2y: 16.54 sec
3y: 24.46 sec
4y: 32.49 sec
10y: 1 min 10.35 sec
select Year ,count(*) as c1 from ontime group by Year;
Time: 9.54 sec
Ok, so there is summary table with queries times (in sec, less is better)
- InfiniDB server version shows , so I consider it as alpha release, and it is doing OK for alpha. I will wait for more stable release for further tests, as it took good amount of time to deal with different glitches.Shell1Server version: 5.1.39-community InfiniDB Community Edition 0.9.4.0-5-alpha (GPL)
- InfiniDB shows really good time for queries it can handle, quite often better than InfoBright.
- Inability to handle derived tables is significant drawback for me, I hope it will be fixed