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:
Load data
InfiniDB supports MySQL’s
|
1 |
LOAD DATA |
statement and it’s own
|
1 |
colxml / cpimport |
utilities. As
|
1 |
LOAD DATA |
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
|
1 |
ERROR 1 (HY000) at line 1: CAL0001: Insert Failed: St9bad_alloc |
Alright,
|
1 |
colxml / cpimport |
was more successful, however it has less flexibility in syntax than
|
1 |
LOAD DATA |
, so I had to transform the input files into a format that
|
1 |
cpimport |
could understand.
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.
Datasize
Size of database after loading is another confusing point. InfiniDB data directory has complex structure like
|
1 2 3 4 5 6 7 8 9 10 11 12 |
./000.dir/000.dir/003.dir/233.dir ./000.dir/000.dir/003.dir/233.dir/000.dir ./000.dir/000.dir/003.dir/233.dir/000.dir/FILE000.cdf ./000.dir/000.dir/003.dir/241.dir ./000.dir/000.dir/003.dir/241.dir/000.dir ./000.dir/000.dir/003.dir/241.dir/000.dir/FILE000.cdf ./000.dir/000.dir/003.dir/238.dir ./000.dir/000.dir/003.dir/238.dir/000.dir ./000.dir/000.dir/003.dir/238.dir/000.dir/FILE000.cdf ./000.dir/000.dir/003.dir/235.dir ./000.dir/000.dir/003.dir/235.dir/000.dir ./000.dir/000.dir/003.dir/235.dir/000.dir/FILE000.cdf |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Name: ontime Engine: InfiniDB Version: 10 Row_format: Dynamic Rows: 2000 Avg_row_length: 0 Data_length: 0 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: |
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.
Queries
First count start query
|
1 |
SELECT count(*) FROM ontime |
took 2.67 sec, which shows that InfiniDB does not store counter of records, however calculates it pretty fast.
Q0:
|
1 |
select avg(c1) from (select year,month,count(*) as c1 from ontime group by YEAR,month) t; |
Another bumper, on this query InfiniDB complains
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 |
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
|
1 |
infinidb_vtable_mode = 2 |
, which is:
|
1 2 |
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)
Query Q1:
|
1 |
mysql> SELECT |