Justin Swanhart

Materialized Views store the results of a MySQL query in a table. When data in the database changes, Flexviews materialized views can be updated efficiently using the row change information that has been captured, instead of building the whole view again.

Materialized Views store the results of a MySQL query in a table. When data in the database changes, Flexviews materialized views can be updated efficiently using the row change information that has been captured, instead of building the whole view again. The row change information is captured using FlexCDC, a change data capture tool for MySQL. Since the contents of the view are stored in tables, indexes can be added to the views and they can be accessed in milliseconds instead of potentially minutes or hours to run the SQL statement that underlies the views.

Flexviews is part of Swanhart-Tools and includes both the change data capture utility, and a small SQL API for creating and maintaining the views.

This talk will introduce Flexviews, the SQL API and FlexCDC.

The MySQL RDBMS is traditionally tuned for OLTP workloads. OLAP (analytic) workloads have traditionally been sub-optimal. While it is true that Amazon EC2 and other cloud providers now provide large machines with SSD disks, these are still best for OLTP workloads.

The largest EC2 server is limited to 64GB. MySQL queries are single threaded. Perhaps spreading your data over eight 17.1GB servers might cost the same(or less) and perform significantly better. So, the question is, is one big server with lots of very fast storage the best option for analytic queries?

This talk will introduce Shard-Query which can spread data over many servers but treat the set as one big server. Basic features will be discussed, but the focus is on performance, not how Shard-Query works.

The talk will compare the price/performance difference of OLAP queries on one ""Quadruple Extra Large High-IO"" server compared with eight ""Extra Large High Memory"" servers. While eight servers increase operational complexity, the performance improvement trade-off may very well be acceptable.

Shard-Query is an open source (bsd licensed) MPP query engine for MySQL: http://code.google.com/p/shard-query