Calpont InfiniDB 2.0 and BI QuickStarts

The 2.0 release of Calpont InfiniDB
is ready for download. New features for the columnar database storage engine for MySQL include data compression, fully parallelized & scalable UDFs, and partition drop has been added to the automatic vertical & horizontal data partitioning.

Benefits of InfiniDB Enterprise 2.0
• 20-50% query performance improvement when reading from disk
• Distributed in-database calculations provide greater flexibility to the data analyst, and enable faster performance for deep analytics
• Removing obsolete data from the database quickly frees up disk storage and improves query response

And for those of you new to data warehousing and business intelligence, there are QuickStart for data reporting tools from Japersoft, BIRT, and Pentaho plus a guide for using Pentaho spoon with the Calpont Infinidb bulk loader.

Open Source BI -- Pentaho and Jaspersoft Part I

Hey DBAs! Are you seeking more efficient ways of shifting through your data to aid your business operations? Two popular Business Intelligence products have community Open Source software are Pentaho and JasperSoft. And both work with MySQL.

Both are easy to download and install. Both will use a JDBC connector to connect to MySQL. But how easy are the two to configure and run a simple report against a running instance of MySQL?


Setting up a JDBC connection with JasperSoft or Pentaho is pretty much like using any other JDBC connection.

The next step is to setup a query like SELECT name, job_title, department FROM employees, departments WHERE employees.emp_id = departments.emp_id. Either package will let you pick a variety of output templates. Then you have the BI software merge your query with the template. I honestly think an average MySQL DBA could fairly quickly generate a nice looking report from their instance and that JasperSoft would be just a little bit faster.

In part two of this series, the steps will be more detailed and documented. There will also being comparing and contrasting of the two products. Both products are part of larger projects and there are many useful tools that work with the BI software that you will want to investigate. More on those in later posts.

And in a short time you should be able to download a Virtual Box image with both community BI programs and a InfiniDB instance with some data sets. This way you can test all three simply. I would also consider doing a VMWare version if there is demand for it.

Doing your own on-time flight time analysis Part III

In the last post, the data from the on-time flight database was loaded in a column-orientated storage engine. Now the numbers can be crunched.

The original goal of this exercise was to find the flight from Los Angeles International Airport, LAX, to Dallas Fort Worth International Airport, DFW, that was the most likely to arrive on-time.

The data is 'opportunity rich' in that there is a lot information in there. It is easy to start wondering about the various nuggets of information in there. Are their certain aircraft (tail numbers) that are routinely bad performers? Are some days of the week better than others? Do national holidays have an effect on the on-time performance? If you are delayed, is there a 'regular amount' of delay? Does early departure make for an early arrival? Can the flight crew make up for a late departure? How much time is usually spend on runways?

But to look for the flight from LAX to DFW with the lowest arrival delay¹.

SELECT FlightNum, SUM(ArrDelayMinutes) as Delay
FROM flight
WHERE Origin = 'LAX'
AND Dest = 'DFW'
GROUP BY FlighNum
ORDER BY FlightNum;

So there are lots of good questions to ask with this data but it ends this series on doing your own on-time flight analysis. If anyone digs into the flight data further, please let the rest of us know what you are finding.

1. For the data on hand the answer is American Airlines flight 2463.

Anybody have a few millions lines of Apache Log files they can share?

Anybody have a few millions lines of Apache Log files they can share? I am working on a lab/demo on InfiniDB and need a few millions lines of an Apache HTTPD log file. I no longer run any large websites and would rather use real data over creating something. I will sanitize your URL so you will be anonymous, so www.YourCompanyHere.com will end up as www.abc123.com or something similar.

The demo/lab will show how to load data into the columnar InfiniDB storage engine and run some analytics against the data. Please let me know if you can help.

InfinDB Extent Maps saving I/O

InfiniDB uses Extent Maps to retrieve data. Data is stored in extents and each extent is a logical block of space that holds the same number of rows.

The Extent Map catalogs all extents and their corresponding blocks. The Extent Map also maintains minimum and maximum values for a column’s data within an extent.

The Extent Map lets InfiniDB to retrieve only the blocks needed to satisfy a query. There is another benefit – that of logical range partitioning. This is accomplished via the minimum and maximum values for each extent that are contained within the Extent Map. Extent elimination is first accomplished in InfiniDB via the column-oriented architecture (only needed columns are scanned), but the process is accelerated because of the logical horizontal partitioning that is implemented in the Extent Map. So only the needed sections of the column are read. So if only the data from Col1 between 105 and 180 is requested, InfiniDB will return data from Extent 2, skipping Extents 1, 3 and 4. That saves a 75% of I/O compared to a row based storage engine.

The savings in I/O also carries over to more complex queries. If a column WHERE filter of “col1 BETWEEN 220 AND 250 AND col2 < 10000” is specified, InfiniDB will eliminate extents 1, 2 and 4 from the first column filter, then, looking at just the matching extents for col2 (i.e. just extent 3), it will determine that no extents match and return zero rows without doing any I/O at all.

Upon system startup, the Extent Map file is read into memory and updated automatically as needed.

InfiniDB Alpha 1.1.2 on 64-bit Windows and MySQL Workbench

This week has been rough on my computers and I had to reinstall Vista on my main Windows system. But being a 'glass half full' guy, I took it as an opportunity to load the new alphas for both InfiniDB and MySQl Workbench.

I downloaded the InfiniDB software from http://infinidb.org/downloads/cat_view/40-binary-release/137-112-alpha-binary-releases and Workbench from http://dev.mysql.com/downloads/workbench/ respectively. In stalled them in that order and started the database and then Workbench.

I was expecting to have to fiddle with configuring one or the other to get them to work together. But I received a delightful surprise. Both worked out of the chute with no customization. Congratulations to the folks writing the install scripts at both companies!

Getting started with InfiniDB Part I

My new job with Calpont has me scrambling to learn all the facets of the InfiniDB storage engine. For those of you new to the scene, InfiniDB is column-orientated as opposed to row-orientated and is designed for data warehouses and business intelligence. Most MySQL DBAs are probably not used to thinking sideways and hopefully this series of postings will change that.

To make things interesting, I am using real data from BP's Statistical Review of World Energy 2009. The data is a historical information on various forms of energy. And it is free to download and comes in Excel workbook format. I wanted data that was unfamiliar to me and in a format that would be commonly used in a BI setting.

The first step is to obtain and install the InfiniDB community software. Since the install instructions are extremely clear, I will not go over those steps.

Next I exported the data from the Crude oil prices 1861 to 2008 worksheet into a CSV file. Now you have choices on how to load the data into your table. The first is the good old MySQL LOAD DATA INFILE statement.

mysql>USE energy;
LOAD DATA INFILE '/tmp/oil.csv'
INTO TABLE oil 
FIELDS TERMINATED BY ',';

Or you can use the supplied bulk loading tool. In this example the table is simple and there are only 148 rows. LOAD DATA INFLE works fine for small sets. I copied CSV file into the directory InfiniDB used for the bulk loading tool -- /usr/local/Calpont/data/bulk/data/import --, changed the commas to pipes (s/,/|/g) preferred by the bulk loader, and prepare it for the columns tructure:

# /usr/local/Calpont/bin/colxml energy -t oil -j04

Bulkload root directory : /usr/local/Calpont/data/bulk
job description file : Job_04.xml
2010-05-04 12:42:21 (5364) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_04.xml
2010-05-04 12:42:21 (5364) INFO : PreProcessing check starts
2010-05-04 12:42:21 (5364) INFO : PreProcessing check completed
2010-05-04 12:42:21 (5364) INFO : preProcess completed, total run time : 0 seconds
2010-05-04 12:42:21 (5364) INFO : No of Read Threads Spawned = 1
2010-05-04 12:42:21 (5364) INFO : No of Parse Threads Spawned = 3
2010-05-04 12:42:21 (5364) INFO : For table energy.oil: 148 rows processed and 148 rows inserted.
2010-05-04 12:42:22 (5364) INFO : Bulk load completed, total run time : 1 seconds

[root@localhost import]# 

Now to load the data:

 /usr/local/Calpont/bin/cpimport -j 04
Running colxml with the following parameters:
2010-05-04 12:42:13 (5312) INFO : 
 Schema: energy
 Tables: oil  
 Load Files: 
 -b 0
 -c 1048576
 -d |
 -e 10
 -f CSV
 -j 04
 -n 
 -p /usr/local/Calpont/data/bulk/job/
 -r 5
 -s 
 -u
 -w 10485760
 -x tbl

File completed for tables:
 energy.oil
 
Normal exit.

Almost forgot to show you the table!
CREATE TABLE oil (`year` INT(4), price_historical DECIMAL(5,2), price_current(5,2)) ENGINE=InfiniDB;

So now we can ask for the historical average price of a barrel of oil.

mysql>SELECT AVG(price_historical) AS 'Historical Avg Price' FROM oil\g

+----------------------+
| Historical Avg Price |
+----------------------+
|             7.772297 |
+----------------------+

Many of you reading this are probably muttering 'I could have done this with any storage engine except Blackhole! and you are right. The beauty behind the column orientated approach does not appear until you are working with large sets of data. In the next post we will get into large sets of data and compare speeds with other storage engines.

No North Texas Users Group Meeting in May, new job

There will not be a May meeting of the North Texas MySQL Users Group but I am scrambling to get us a great location for the June meeting. I have left my Job with MySQL/Sun/Oracle and am no longer responsible for MySQL Certifications. That means my access to the Sun office were we have been meeting is ended also.

I am now the Customer Service Manager for Calpont and the InfiniDB storage engine. InfiniDB is column based, multi-threaded, and the community edition is open source. If you run intensive reports against InnoDB or MyIsam databases, you need to investigate this product. And if you regularly run massive queries in data warehousing or business analytic operation that you need to see how the massively parallel process architecture of the enterprise product can make life easier.