MySQL in Data Warehousing & Business Intelligence

According to Forrester Research, the amount of data businesses retain for analytic purposes is growing at a rate of 50% per year, and in some industries such as Web, ecommerce, retail, telecommunications and government, the growth rate is even higher. Just a few years ago, the data used for business intelligence purposes was stored in a centralized data warehouse and a few departmental data marts. But now, the skyrocketing demand for better business intelligence data has created a vast array of distributed data repositories that run throughout organizations, which has resulted in increased complexity and costs for businesses wishing to maximize their use of analytic data.

To mitigate these issues, leading modern businesses such as Los Alamos National Labs, MIT Lincoln Lab, Cox Communications, and others have selected MySQL to power their growing data warehouse infrastructure. The growth of MySQL in the area of data warehousing recently prompted Gartner Group to include MySQL in their 2006 Magic Quadrant for Data Warehouse DBMS Servers.

MySQL is uniquely designed to easily handle the most common data warehousing use cases:

• Data Marts
• Traditional Data Warehouses
• Large Historical/Archive Data Warehouses
• Real Time Data Warehouses

Native Storage Engines

MySQL's pluggable storage engine architecture enables you to leverage specific engines to improve performance, reduce storage costs, and meet compliance mandates. MySQL does this with both internally developed storage engines and externally developed engines built by MySQL partners. Some of MySQL's internal engines that enable data warehousing include:

• MyISAM: A high performance non transactional storage engine originally developed for data warehousing applications. MyISAM is the default MySQL storage engine and the one most commonly used by MySQL customers for their data warehouse & reporting applications.
• Archive: Government compliance mandates such as Sarbanes Oxley, Basel II, and others have forced many corporations to retain huge volumes of historical information on their database servers. Companies are looking for ways to reduce the amount of space this type of seldom-referenced data consumes. MySQL's Archive storage engine automatically compresses table data down to a very small footprint, with typical reductions being 15 to 20 % of the data's original size.
• Memory: The memory engine is ideal for data warehouse dimension tables. All data is kept 100% of the time in main memory, which equates to extremely fast lookup and join times.
• CSV: Allows flat files in comma delimited format to be instantly loaded into MySQL and referenced via SQL. Data in CSV tables can be edited via normal DML as well as through external file editors.
• Merge: The merge engine allows for tables to be grouped into a single logical unit and accessed/inserted into as one object.

Partner Storage Engines

• Infobright's BrightHouse engine: BrightHouse delivers high performance on complex queries across terabytes of data for analytic data warehouse solutions. Quick to install, no indexes to create and deployable on commodity hardware, BrightHouse delivers unrivaled TCO. BrightHouse also features 10:1 data compression, storing 10TB of raw data as 1TB in BrightHouse.

Core Features for Data Warehousing

In addition to MySQL's various storage engines, the MySQL database server contains a number of core features that enable data warehousing. These include:

• Data/Index partitioning (range, hash, key, list, composite) in MySQL 5.1 and above
• No practical storage limits (1 tablespace=110TB) with automatic storage management
• Built-in Replication
• Strong indexing support (B-tree, fulltext, clustered, hash, GIS)
• Multiple, configurable data/index caches
• Pre-loading of data into caches
• Unique query cache (caches result set + query; not just data)
• Parallel data load
• Multi-insert DML
• Read-only tables
• Cost-based optimizer
• Wide platform support

Additional Resources:

Technology Partners

• MySQL AB partners with ETL, reporting and BI vendors such as Business Objects, MicroStrategy, Jaspersoft, Pentaho, Talend, and Informatica.

Whitepapers

• Enterprise Data Warehousing with MySQL
• Inside MySQL 5.1: A DBA's Perspective
• Inside MySQL 5.0, a DBA's Perspective

Customer Stories

• Los Alamos National Labs Relies on MySQL to Scale with 7 Terabytes of Data
• Cox Communications Powers Massive Data Warehouse with MySQL
• Fortum Relies on MySQL to Monitor Power Plant in Real Time

On demand Webinars

• A Case Study: Click Stream Analysis on Terabytes of Data with MySQL and Infobright