Should I use the Infobright database or is there a better aggregation solution?

Question

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log.

We need to be able to aggregate counts of this data (cube) vis a vi ad-hoc queries. We are currently sending the data to CouchDB. This is very fast, once a view has mapped completely, but our database size is only 31GB and CouchDB is taking FOREVER to map a new view ( 2+hours ). Retrieving JSON formatted data has worked well for us. But we dread creating new view documents. I'm thinking all Map-Reduce systems may have this problem.

We are evaluating Infobright as they claim to provide superior aggregation performance for ad-hoc queries and are a MySQL fork.

We have evaluated Mondrian and it's not going to work for us.

MySQL/InnoDB is too slow.

We will be around 500GB of log data by the end of the year.

Is Infobright the right solution or should we be also evaluating something else?

Answer 1

I think if you're reporting aggregate statistics you should probably look into an OLAP solution. Mondrian is a HOLAP system with a relational back-end; the cheapest widely available system supporting MOLAP with a leading ROLAP partition is Microsoft Analysis Services.

For web log data of the size you describe you might be able to get away with the Business Intelligence Edition of SQL Server 2012, which is not all that expensive at a list price of ~8,500 USD per server + CALs. It is not necessary to use a SQL Server back end for SSAS - I've built cubes loading from Oracle sources and I see no reason why this could not be done with MySQL if you need to do that.

Most of the SSAS features of Enterprise Edition are available in Business Intelligence Edition, including cube partitioning, ROLAP and MOLAP storage and semi-additive measures.

This article discusses the difference between the two editions in more depth. If you need a low-latency analytic capability this article discusses some of the ins and outs of that.

Answer 2

Almost always (in my experience), the following is the solution to performance with large, log-like, data and semi ad hoc queries.

Characteristics of the data and the application:

• continually arriving data
• no updates to 'old' data
• optionally purging old data (if so use PARTITION BY RANGE(TO_DAYS()))
• queries tend to have a date range in the WHERE clause

Solution: Build and maintain "summary tables".

• pick a timeframe (usually day or hour)
• after midnight (or top of hour), summarize yesterday's data from the raw (Fact) table into the Summary table.
• PK of the Summary table usually includes some dimension(s) and the rounded-down date/hour.
• rest of fields include aggregates like COUNT(), SUM(), etc, but not AVG().
• AVG is calculated as SUM(sum_foo)/SUM(ct)
• "reports" hit a summary table, not the fact table.
• 1-10 summary tables usually suffice for a given application
• Querying the summary table usually gives you 10x performance improvement; I have seen 1000x in rare cases.

I say "semi ad hoc" because you will find out that users don't really query on everything. Even if they do, you can have summary table(s) that at least help.

I mentioned the straight-forward way of completely summarizing "yesterday". There are many other flavors. One is "as needed", with the aid of INSERT ... ON DUPLICATE KEY UPDATE.

Answer 3

I recommend you to use Infobright as opposed to Microsoft SQL Server or MySQL.

• Very good Load speed; up to 2TB/Hour with DLP and 150GB/Hour using IB Load
• Very good query performance; for example SUM/COUNT DISTINCT for 25 million events in a table of 600 million events in less than 4 seconds.
• Very good compression; average compression rate is 1:10. For example, if you have 100GB data then on-disk storage will be 10GB, although certain data types can achieve even higher compression rates.

Note: Community Edition vs Enterprise Edition:

1. Significant Query Performance Differences: IEE is about 50 -500% Faster
2. IEE supports the SQL Data Manipulation Language allowing the ability to INSERT, UPDATE, DELETE data in an IEE database. ICE only Data Load infile.
3. IEE supports Faster Data Loading and More Options
4. IEE supports Replication and High Availability
5. IEE supports Concurrent Query While Load / DML
6. IEE supports Alter table
7. IEE supports Temp tables.

Answer 4

I think Infobright would be a good fit for the situation you describe. I have found that Infobright can often quickly report aggregates without the need for MOLAP.

I have been using Infobright Community Edition for two years and I have not worked for Infobright.

If your "evaluating Infobright" includes loading a Brighthouse data engine table with representative data and running representative queries against it, you should discover for yourself if it is a good fit. If you have a problem developing such a pilot and you contact me I would be happy to try and help.