4.2 Data Warehouse Scale-Out with MySQL

4.2 Data Warehouse Scale-Out with MySQL

One of the most successful technical strategies used by MySQL customers is “scale out”. The term refers to an architectural design where, rather than “scale up” a single server with additional CPU’s and memory to handle greater data management loads, a data-driven application scales out by separating and spreading a database’s workload across commodity hardware, thus achieving much better fault tolerance and performance. Many of the most heavily-trafficked Web sites use MySQL and a scale-out infrastructure to achieve their high levels of uptime and performance.

The scale-out blueprint can also be utilized for data warehousing/business intelligence, with the practice helping to overcome the mixed workload performance problem identified by Gartner as the biggest performance obstacle coming in data warehousing. Rather than trying to scale-up with expensive additions to monolithic hardware and/or utilize different database vendors to match each data warehousing use case,

IT architects can standardize on MySQL, choose the right MySQL storage engine for the right data warehouse use case, and employ scale out to separate the various data warehouse workloads so that performance meets user’s expectations.

For example, the architects of a corporate business intelligence infrastructure can create ETL and data cleansing flows from the various OLTP/operational systems and feed the various “shards” (individual analytic MySQL data stores on commodity hardware) that target a particular BI subject area or meet a specific BI need. A small data mart that serves, for example, a financial analyst business unit may use MySQL with the MyISAM and memory storage engines that make up one shard. A marketing team may be served by another MySQL data warehousing shard that mines tons of historical data from the Bright House storage engine,while executive BI dashboards are being fed with real-time data by another MySQL shard using either the NitroEDB or MyISAM storage engines (see Figure 2).