Experience incrementally updateable materialized views, on demand table level replication and transformation, and columnar storage.
Materialized views are excellent for dashboards and reporting servers. Columnar storage option for ad-hoc querying of large data marts.
WarpSQL is an enhanced version of MySQL that remains 100% compatible with Oracle MySQL Server 8.0. The LeapDB AWS AMI appliance is a marketplace hosted instance of WarpSQL with the additional closed source feature of incrementally refreshable materialiazed views.
LeapDB supports traditional MySQL replication and also on-demand table level replication from existing MySQL data sources. LeapDB features asynchronously and incrementally refreshable materialized views (for inner joins), standard materialized views (for all queries), and WARP columnar storage with automatic bitmap indexing for ad-hoc querying of data marts.
Of course LeapDB can simply be used as a stand-alone database appliance and is 100% compatible with Oracle MySQL while providing SQL language enhancements for materialized views and columnar storage.
Create materialized views for dashboard and reporting queries and access them directly for optimal reporting performance. Materialized views can be refreshed automatically on a periodic basis or can be refreshed on demand. All materialized data is stored on disk and survives database restarts.
The WARP storage engine features columnar storage, automatic bitmap indexing, and parallel query optimizations for fast access to data marts that are terabytes or more.
LeapDB can replicate data from MySQL 8 using standard MySQL replication options. Advanced per-table replication (remote single table materialized views) is supported for MySQL 5.6+.
This is an example of creating a materialized view for a dashboard style query. This is query 4.1 of the Star Schema Benchmark. The data set is scale factor 30, or approximately 22GB of data in the 'lineorder' table. The database is configured with a 12GB innodb_buffer_pool_size.
The query initially takes over 11 minutes. Once the materialized view is created, access to the view is instant (35 rows). After modifying 50,000 rows, it takes only 7.31 seconds to update the view to reflect those changes. This means it takes only 7.31 seconds to run the query now, instead of 11.5 minutes! Refresh times are proportional to the numbers of underlying rows changed in the tables that define the view.
The query is now 100X faster!
LeapDB is based on WarpSQL which includes the beta WARP storage engine. The following query is being executed over scale factor 400, which is 276GB. This is significantly larger than the size of the above example used for the materialized view, which was being executed over InnoDB data. InnoDB at 22GB of data took 11 minutes. In this example, WARP queries 13X the data (276GB) yet is still 3X faster than the InnoDB query on 22GB of data! This example used a 32 core machine with 64GB of ram.
When using the WARP storage engine, query parallelization and columnar storage improve performance significantly.