Showing entries 11 to 19
« 10 Newer Entries
Displaying posts with tag: flexviews (reset)
How to support COUNT(DISTINCT expression) expressions with Flexviews.

Unlike COUNT(expression), COUNT(DISTINCT expression) is not a distributable function. That is, the expression can not be computed by looking at only the changed rows in the table change logs.

The following view can not be FAST refreshed with Flexviews today:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       COUNT(DISTINCT d) d_alias
 GROUP BY a, b, c;

However, a dependent child materialization could be created to support the value for COUNT(DISTINCT d):

-- child materialization, dependent subview
--There will be one row for each DISTINCT value of d

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       count(*) d_alias_cnt
 GROUP BY a, b, c, d;

The original view could then be rewritten as:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, …
[Read more]
Can't wait for table change logs.

At today's keynote by Mark Callaghan one of the new options he talked about are table change logs. He mentioned they might be of use to external applications, like Flexviews, which he mentioned but not directly. He asked if the guy who wrote it was in the audience, so I got to wave my hand and yell 'Flexviews!'.

I caught up with Mark at the Facebook party this evening. I had a chance to talk to him not only about the change logs, but also about Kickfire and the SQL chip. He asked me what I thought about working at Kickfire and I smiled and said I love it. I think I said "I've never been able to join a billion row table to a hundred million row table, sort, group and get results back in less than a minute" and I'm sure the smile never left my face.

As far as the table change logs, he verified:

  • The global transaction id will be stored in the table
  • OLD and NEW …
[Read more]
Flexviews for MySQL 1.0.3-alpha released

Make sure you set sp_max_recursion_depth - see the instructions file.

functions/procedures now take parameters in DB,TABLE order.
demo database removed from sourceforge and added into SVN/release tarball.
GRANT USAGE on *.* to flexviews; is now the default permission level of the flexviews user
I removed the mview_ prefix from filenames.
flexviews.add_table() now checks for the existence of the table and the table's mvlog

get it at

Learn more about Flexviews and see a demonstration at the July 14th Silicon Valley MySQL Meetup

I will be demonstrating the features of Flexviews as well as talking about how you can improve performance by:

  • pre-aggregating important tables for improved query performance
  • pre-calculating joins on InnoDB using materialized views
  • nesting incrementally refreshed materialized views to further improve performance

Flexviews-1.0.2-alpha released

available now: Flexviews for MySQL 1.0.2-alpha

bug fixes:
ensure_validity() was failing when an incremental refresh mview did not contain an AVG()
the delta application phase of refresh was not cleaning up the delta log
removed serialization overhead, improving dml performance. needs further improvement.

Get it at:

Flexviews - A performance overview (incremental refresh is 30x faster!)

I keep talking about Flexviews, but I figured I'd step back and explain exactly what it does and how it can help you maintain aggregate tables cheaply. Read that again. It really is important. If you have a BI or DW running MySQL then you probably spend a lot of your time creating aggregate tables with cron jobs or some other ETL tool.

These aggregate generating queries might take many hours to run, and running multiple aggregations concurrently likely severely impacts performance. The more data you have the longer the aggregations take to run. If you are attempting to answer real-time business questions this presents a big challenge. Flexviews to the rescue. It gets rid of those cron jobs and custom aggregation scripts and replaces them with an API for creating materialized views (a fancy name for those aggregate tables). It supports two kinds of views: COMPLETE and INCREMENTAL refresh.

A COMPLETE materialized …

[Read more]
Flexviews - minor improvements, further plans

I've made a few fixes to the SVN version of Flexviews:
flexviews.enable() would return an error when trying to create an incremental refresh materialized view without an AVG() aggregate expression in the select clause.

Performance of DML is now improved substantially. Flexviews is designed to work in an environment with serialized DML (such as a MySQL slave). I removed extra serialization enforcement overhead. Previously two tables were involved in coordinating the 'uow_id' generation to ensure serialization in the logs. Now only one table is utilized. I need to add a procedure to auto-cleanup this new table.

There and there are a lot more to make. On my list of things to do:
1) code cleanup
flexviews went through several iterations and there are still 'mview' references all over the

some of the code is encased in comments because it was extracted by …

[Read more]
Flexviews for MySQL 1.0.1-alpha released

There were a number of problems in the installer script and in support for COMPLETE refresh materialized views. These problems have been rectified and an updated version is available on sourceforge. Also, you can pull the latest source from the svn repository:

Here is a little example for complete refresh:

CALL flexviews.create('mv2', 'test', 'COMPLETE', 1);
CALL flexviews.set_definition(@MVID, 'SELECT 1');
CALL flexviews.enable(@MVID);
CALL flexviews.refresh(@MVID, 'COMPLETE');

Flexviews for MySQL 1.0.0-alpha released

I am proud to announce the availability of Flexviews for MySQL 1.0.0-alpha under LGPLv3!

The documentation needs quite a bit of work. When you run into problems (and I'm sure you will) you can ask for help on the sourceforge support tracker. Please download the example database for some examples of incremental refreshable views.

I haven't tested COMPLETE refresh views in quite a while, but I think they break when you try to enable them. I'll fix this today or tomorrow and upload a new version. INCREMENTAL refresh is what is interesting anyway :)

Please report bugs (of which I'm sure there are quite a few), as well as feature requests, etc, on the sourceforge bug tracker.

Showing entries 11 to 19
« 10 Newer Entries