Infobright Community Edition version 3.3.2-beta
Calpont InfiniDB Community Edition 1.5
Percona Inc, 02 August 2010
Version 1.0 – Authored by Justin Swanhart
Table of Contents
Summary................................................................................................................................................................3Aspects evaluated.............................................................................................................................................3Software used....................................................................................................................................................3Evaluation Results .................................................................................................................................................4DDL and datatype support ................................................................................................................................4Loading Data.....................................................................................................................................................4Compression and data size...............................................................................................................................5Ease of installation and security evaluation.......................................................................................................5Ability to query over large data sets...................................................................................................................6Query Time Chart..............................................................................................................................................6Conclusions............................................................................................................................................................7Appendix – Query Times and Query list.................................................................................................................8q#1.....................................................................................................................................................................9q#2.....................................................................................................................................................................9q#3.....................................................................................................................................................................9q#4...................................................................................................................................................................10q#5...................................................................................................................................................................11q#6...................................................................................................................................................................12q#7...................................................................................................................................................................13q#8...................................................................................................................................................................14q#9...................................................................................................................................................................15q#10.................................................................................................................................................................15q#11.................................................................................................................................................................16q#12.................................................................................................................................................................16q#13.................................................................................................................................................................16q#14.................................................................................................................................................................16q#15.................................................................................................................................................................17q#16.................................................................................................................................................................17q#17.................................................................................................................................................................17q#18.................................................................................................................................................................17q#19.................................................................................................................................................................17q#20.................................................................................................................................................................17q#21.................................................................................................................................................................17q#22.................................................................................................................................................................18q#23.................................................................................................................................................................18q#24.................................................................................................................................................................18q#25.................................................................................................................................................................19q#26.................................................................................................................................................................19q#27.................................................................................................................................................................20q#28.................................................................................................................................................................21q#29.................................................................................................................................................................22
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
2
Summary
In accordance with the Infobright “statement of work”, Percona evaluated the functional characteristics of Infobright Community Edition.
Our work was performed in two different series of tests. The first test was an evaluation of the ICE functionality via a set of tests designed to execute the features which Infobright most wanted to test. These tests were provided to Todd and a number of bug reports were filed based on the results of the testing. These tests were done on a small synthetic dataset consisting of only 256 rows.
The second set of testing was done on a 900GB data set provided by Infobright. This data was loaded into both Infobright CE and InfiniDB CE. A series of 29 test queries, the bulk of which were provided by Infobright were executed against both MySQL databases using the 'mysqltest' MySQL testing binary.
This report is focused on the second phase of testing, comparing ICE with InfiniDB.
Aspects evaluated
●●●●●●
DDL and datatype support
Time to set up the database and load the data Size of the loaded database on disk and compressionEase of installation and security
Ability to query over large data sets without errors or crashes29 queries over the carsales database
Software used
●●●●
Infobright Community Edition 3.3.2-beta – LOAD DATA INFILE for loadingInfiniDB 1.5 GA – cpimport utility for loading
mysqltest 5.1.45 (not distributed with InfiniDB binaries)
PHP and bash scripts – wrappers for tests and for timing the data loading
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
3
Evaluation Results
DDL and datatype support
Infobright supports a number of MySQL data types which are not supported by InfiniDB. The data types below list the type supported by ICE, and what datatype was used on InfiniDB as substitute:
●●●●
Year – smallint was used insteadTime – char(10) was used insteadTinytext – varchar(255)
NOT NULL – NULL was used instead
InfiniDB does not support comments in DDL statements. All 'lookup' comments had to be stripped out of DDL before the table could be created in InfiniDB.
Neither database gives very good error messages when an unsupported datatype is used. The end user experience could be improved by listing the column name and data type that was not supported. Ideally each unsupported feature could be enumerated in warnings so that all the problems could be identified at once.
Loading Data
Infobright CE supports the MySQL “LOAD DATA INFILE” syntax, but it does not do so in a way that is compatible with the default MySQL settings, which can be confusing. I extracted each of the compressed files provided by Infobright and loading them individually with LOAD DATA INFILE.InfiniDB has a custom loader which is invoked using the 'cpimport' utility. This utility uses a 'job file' system and the jobs must be created by another utility called 'colxml'. Overall the complexity of loading is much higher on InfiniDB than on Infobright.
In both cases, the end user experience might be better if the loader was more compatible with MySQL, including support for escape characters and optional text enclosures.
Loading Times
Infobright Community 3.3.2-betaInfiniDB 1.5
28.07 hours total / avg of 1110 seconds per file19.01 hours total / avg of 7 seconds per file
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
4
Compression and data size
Infobright Community Edition compresses data significantly when compared to the size of the input data. InfiniDB does not appear to compress data. In fact, the size on disk of the InfiniDB database is slightly larger than the size of the source material.
Size of software and data on disk (905GB source data)
Infobright Community 3.3.2-betaInfiniDB 1.5
122GB934GB
Ease of installation and security evaluation
Since both database engines are distributed in both binary and source form. I tested the installation of the databases from RPM files. Both databases had to be installed as the 'root' user. Both databases install their files into a sub=directory of /usr/local. There were additional scripts to run to install the database on InfiniDB, but ICE worked out of the box. InfiniDB uses the standard MySQL port of 3306, but ICE runs on an alternate port by default which can be confusing.
Since ICE supports LOAD DATA INFILE, there is no additional security work which needs to be configured to give end users the ability to load data into the database. Any MySQL user with adequate permissions can load files. The ICE loader can read from named pipes.
While InfiniDB supports loading data with LOAD DATA INFILE, I found the performance to be unacceptable and instead chose to use the combination of tools: colxml and cpimport. The first utility creates an XML “job” file, and the second uses said files to load data. I was unable to get the loading utility to support loading more than one file into a table in a single job file. I used a symlink, a shell script and a hand crafted job file to load each file into the database. To further complicate matters, the job files must be placed into a particular directory, which complicates security from a unix perspective. InfiniDB requires the use of terminal aliases by default, which is inconvenient.
Comparison (all points are out of five)
Security
Infobright Community 3.3.2-betaInfiniDB 1.5
53
Ease of loading/use
43
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
5
Ability to query over large data sets
ICE was able to successfully execute all 29 of the test queries which were executed against it. InfiniDB could not execute a large number of the queries. InfiniDB does not support all of the MySQL aggregate functions, and it requires non-default values to run certain queries, or errors such as 'not enough memory for aggregation' can occur. Overall ICE appears to be able to run substantially more queries than InfiniDB due to two factors. First, ICE supports more data types and second, ICE supports more aggregate functions. The results on InfiniDB are questionable, as it gives wrong results for even a simple SELECT COUNT(*) with no where clause.
Query Time Chart
Query Time ComparisonICE vs InfiniDB CE250020001500Seconds100050001-500INFINI_SecondsICE_Seconds2345671011121314151617181920212223242526272829Note:Queries that could not be run on InfiniDB appear as negative values on the chart.Some queries may have taken longer than 2000 seconds. Please see chart below.
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
6
Conclusions
Overall, on the given data set and for the given queries, ICE performed better and could execute more queries than InfiniDB. Query results differed from ICE and InfiniDB due to bugs, data type differences and/or precision differences. In my opinion ICE is easier to use, performs better and returns more accurate results than InfiniDB at the current time.
We have not evaluated the newest release of ICE, version 3.4, but we look forward to doing so in the next round of testing.
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
7
Appendix – Query Times and Query list
QueryICE_TIMEINFINIDB_TIME1505.96.822460.3983.0732.061349.5.970531.572087.88627.7507824.3606.39101.1591.94.73103667.920118105.440120.960.49130.3618.42140.2522.45150.2422.4516144.401798.38018.601997.560200.3523.052197.9402298.702398.160241156.59253.662320.02036.1402753.082194.49283809.02029519.16723.07Time is in secondszero time means an error prevented the query from completing
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
8
Query List
q#1
select dim_dates.trans_year as c0, sum(sales_commission) as m0 from fact_sales, dim_dates
where fact_sales.trans_date=dim_dates.trans_date and dim_dates.trans_year=2005 group by c0;
q#2
select dim_dates.trans_year as c0, sum(sales_commission) as m0 from dim_dates, fact_sales
where fact_sales.trans_date=dim_dates.trans_date and dim_dates.trans_year=2005 group by c0;
q#3
select count(distinct(car_colour)) from fact_sales;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
9
q#4
-- sorted_resultselect
d.dealer_name, sum(f.dlr_trans_amt)from
fact_sales f, dim_dealers d, dim_msa mdwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
td.trans_month='JANUARY' and
td.trans_year=2001 )and
f.msa_id = md.msa_idand
md.msa_name in ('GREEN CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')group by
d.dealer_name;
BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
10
q#5
-- sorted_resultselect
d.dealer_name,
sum(f.dlr_trans_amt) as salefrom
fact_sales f, dim_dealers d, dim_cars mdwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
td.trans_month='JANUARY' and
td.trans_year=2001 )and
md.make_id = f.make_idand
md.make_name = 'ACURA'group by
d.dealer_name;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
11
q#6
select
d.dealer_name,
sum(f.dlr_trans_amt) as totalfrom
fact_sales f, dim_dealers d, dim_cars md, dim_msa mktwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
td.trans_month='JANUARY' and
td.trans_year=2001 )and
md.make_id = f.make_idand
mkt.msa_id = f.msa_idand
mkt.msa_name in ('GREEN CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')and
md.make_name = 'ACURA'group by
d.dealer_name;
BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
12
q#7
select
d.dealer_name,
sum(f.dlr_trans_amt) as totalfrom
fact_sales f, dim_dealers d, dim_cars md, dim_msa mktwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
td.trans_year = 2001 )and
md.make_id = f.make_idand
mkt.msa_id = f.msa_idand
mkt.msa_name in ('GREEN CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')and
md.make_name = 'ACURA'group by
d.dealer_name;
BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
13
q#8
-- sorted_resultselect
d.dealer_name,
sum(f.dlr_trans_amt) as totalfrom
fact_sales f, dim_dealers dwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
td.trans_year=2001 and
td.trans_month in ('JANUARY','FEBRUARY','MARCH') )and
d.dealer_state in ('OH','NY','MI','CA','NV')group by
d.dealer_name;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
14
q#9
select
d.dealer_name,
sum(f.dlr_trans_amt), sum(f.sales_commission), sum(f.sales_discount)from
fact_sales f, dim_dealers d, dim_cars cwhere
f.trans_date between '2001-01-01' and '2001-01-01'and
c.make_id = f.make_idand
d.dealer_id = f.dealer_idand
c.make_name = 'ACURA'group by
d.dealer_name;
q#10
select dealer_id, msa_id, dlr_trans_amt from fact_sales order by 1, 2, 3 limit 2000;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
15
q#11
SELECT car_year, car_colour, sales_person,
count(DISTINCT dim_dealers.dealer_id) AS dealer_cnt FROM fact_sales, dim_dealers
WHERE fact_sales.dealer_id = dim_dealers.dealer_id AND car_colour <> 'YELLOW' AND sales_person NOT LIKE 'RA%' AND car_year IN (2000, 2003, 2005)
AND fact_sales.make_id NOT IN (SELECT make_id FROM dim_cars
WHERE model_name LIKE 'E%X%')GROUP BY car_year, car_colour, sales_person
ORDER BY dealer_cnt DESC, car_year, car_colour, sales_person;
q#12
select AVG(sales_commission) from fact_sales where trans_year=2002;
q#13
select COUNT(sales_commission) from fact_sales where trans_year=2002;
q#14
select MAX(sales_commission) from fact_sales where trans_year=2002;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
16
q#15
select MIN(sales_commission) from fact_sales where trans_year=2002;
q#16
select STD(sales_commission) from fact_sales where trans_year=2002;
q#17
select STDDEV_POP(sales_commission) from fact_sales where trans_year=2002;
q#18
select STDDEV_SAMP(sales_commission) from fact_sales where trans_year=2002;
q#19
select STDDEV(sales_commission) from fact_sales where trans_year=2002;
q#20
select SUM(sales_commission) from fact_sales where trans_year=2002;
q#21
select VAR_POP(sales_commission) from fact_sales where trans_year=2002;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
17
q#22
select VAR_SAMP(sales_commission) from fact_sales where trans_year=2002;
q#23
select VARIANCE(sales_commission) from fact_sales where trans_year=2002;
q#24
-- sorted_resultselect
d.dealer_state, sum(f.dlr_trans_amt)from
fact_sales f, dim_dealers d, dim_dates ddwhere
f.dealer_id = d.dealer_idand
f.trans_date = dd.trans_dateand dd.trans_year = 2005group by
d.dealer_state;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
18
q#25
select AVG(sales_commission),COUNT(sales_commission),MAX(sales_commission),MIN(sales_commission),STD(sales_commission),
STDDEV_POP(sales_commission),STDDEV_SAMP(sales_commission),STDDEV(sales_commission),SUM(sales_commission),VAR_POP(sales_commission),VAR_SAMP(sales_commission),VARIANCE(sales_commission),COUNT(0) cnt_1,COUNT(*) cnt_2from fact_sales;
q#26
-- sorted_result
select year(f.trans_date), dealer_name, sum( sales_commission * dlr_trans_amt ) from fact_sales f
join dim_dealers using (dealer_id)where
(trans_year in (2004,2005))group by year(f.trans_date), dealer_name;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
19
q#27
select
d.dealer_name,
sum(f.dlr_trans_amt) as salefrom
fact_sales f, dim_dealers d, dim_cars md, dim_dates ddwhere
f.dealer_id = d.dealer_idand
f.trans_date = dd.trans_dateand
dd.trans_month='JANUARY'and
dd.trans_year=2001and
md.make_id = f.make_idand
md.make_name IN('HONDA','ACURA','OLDSMOBILE')group by
d.dealer_name;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
20
q#28
-- sorted_resultselect
mkt.msa_name, md.make_name, d.dealer_name,
sum(f.dlr_trans_amt) as totalfrom
fact_sales f, dim_dealers d, dim_cars md, dim_msa mkt, dim_dates ddwhere
f.dealer_id = d.dealer_idand
f.trans_date = dd.trans_dateand
dd.trans_year=2004and
md.make_id = f.make_idand
mkt.msa_id = f.msa_idand
mkt.msa_name not in ('GREEN CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')group by 1,2,3;
BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
21
q#29
select
d.dealer_name,
sum(f.dlr_trans_amt) as totalfrom
fact_sales f, dim_dealers dwhere
f.dealer_id = d.dealer_idand
f.trans_date in (
select
td.trans_date from
dim_dates td where
(td.trans_year=2001 or td.trans_year=2002 or td.trans_year < 2001) and
td.trans_month in ('JANUARY','FEBRUARY','MARCH') )and
d.dealer_state not in ('OH','NY','MI','CA','NV')group by
d.dealer_name;
Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA
+1 (888) 401-3401
info@percona.com
www.percona.com
22
因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- huatuo0.com 版权所有 湘ICP备2023021991号-1
违法及侵权请联系：TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务