Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 16

Displaying posts with tag: flexviews (reset)

Flexviews 1.8.0 beta 1 released
+2 Vote Up -0Vote Down
You can get Flexviews at the project page:
Flexviews project page

This release includes the following new features:

Table change logs now include a new column: fv$gsn. This column carries the "global sequence number" which uniquely identifies the order in which to make changes in the database. This was required to fix some problems identified in updating views that did not contain aggregate functions.

Updated refresh algorithm takes GSN into account and applies changes in GSN order.

All reported bugs have been fixed.

FlexCDC now supports all MySQL data types properly.
Flexviews 1.7.0 GA released
+3 Vote Up -0Vote Down
I am happy to announce the GA release of Flexviews. I've numbered the release 1.7.0 GA.
There are a small number of bug fixes and enhancements:

  • Fixes for views which have aggregate functions but no GROUP BY expressions could have had problems during incremental update.
  • GA support for all aggregate functions except GROUP_CONCAT and AVG(distinct).
  • The PERCENTILE_XX (ie PERCENTILE_90 for a 90th percentile calculation) is now stable too

FlexCDC got a number of fixes and improvements:

  • TIMESTAMP columns are now properly supported
  • Now mysqlbinlog errors are detected, and the program exits gracefully
  • FlexCDC now logs to a log file instead of writing to STDOUT/STDERR
  • There are new PHP scripts for adding and removing table changelogs
  • Also, a new wrapper script (consumer_safe.sh)

You can get it here: http://flexvie.ws
Using Flexviews – part one, introduction to materialized views
+2 Vote Up -0Vote Down

If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and maintain incrementally refreshable materialized views.

You might be asking yourself “what is an incrementally refreshable materialized view?”. If so, then keep reading. This is the first in a multi-part series describing Flexviews.

You can find part 2 of the series here:

  [Read more...]
Flexviews 1.6.0-RC2 is released
+1 Vote Up -0Vote Down
Available immediately Flexviews 1.6.0-RC2.

This release is a bugfix release.  This is the second Flexviews release candidate.  If no major bugs are discovered the next release will be the GA release.

Flexviews is a stored procedure managed materialized view system for MySQL 5.1 or greater. 

What is fixed in Flexviews 1.6.0-RC2?
  • Numerous performance fixes. 
    • Flexviews uses fewer temporary tables and subqueries
    • A full table scan of the view is no longer required (only changed GB keys are scanned)
    • Dead code has been removed
  • Bug fixes
    • Removing tables and adding them to a view again could result in the WHERE clause being generated in the wrong order
    • Fix a problem with applying deltas to views which use PERCENTILE
    • Improved error messages
  • FlexCDC Bug

  [Read more...]
Flexviews 1.6.0-RC1 is released
+2 Vote Up -0Vote Down
Whats new in Flexviews 1.6.0RC1
  • This is the first release candidate before the final release.  If no major bugs are uncovered, then the next release will be the first GA release. 
  • Flexviews now has a test suite for all major features.  The creation of these tests uncovered a number of issues which have been resolved in this release. 
  • All MySQL aggregate functions except GROUP_CONCAT are now supported. 
  • A special aggregate function called PERCENTILE is now also supported.  The calculation uses a modified version of the GROUP_CONCAT based solution suggested by Roland Bouman for percentiles.  This function should be considered experimental.  Please report bugs if you find any.
  • You can add indexes to enabled materialized views using SQL_API/add_expr
  • Adding PRIMARY KEY indexes is no longer
  [Read more...]
Check out my Flexviews talk in the Open Space today at 3PM.
+0 Vote Up -0Vote Down
Flexviews allows you to cache (materialize) SQL statements and then quickly apply only the changes to underlying tables to update the cache. This can phenomenally increase the performance of your application and allow you to more easily maintain summary tables automatically.

I'll be demonstrating the usage of Flexviews and how to create 'fast refresh' materialized views.

I'll be showing off:
FlexCDC - Flexible change data capture for MySQL
Flexviews - Uses FlexCDC and stored procedures to incrementally maintain mateialized views, including those with inner JOINS and aggregation.
A handy regular expression for 'tokenizing' a SQL statement
+5 Vote Up -0Vote Down
Often times I find myself having to handle a particular portion of a SQL statement via a script. I've written a lot of specialized regular expressions over time to handle these tasks, but the one that I've had to write the most is a basic 'tokenizer' which understands the quoting semantics of a MySQL statement. That is, it understands `BackTick`, 'Single Quote' and "Double Quoted" strings.

#PHP Heredoc syntax
$regex = <<< END_OF_REGEX
  [^ \"'`(),]*\([^)]*\)    #match functions like concat(x,"y",`a`.`z`) or sum(`xyz`);
  |\([^)]*?\)              #match grouped items
  |"[^"]*?"                #match double quoted items
  |'[^']*?'                #match single quoted items
  |`[A-Za-z0-9_ .'\"()+\\-&^%\$+?%\\/\\\\!`]+`  #match backtick mysql names
  |[^ ,]+                  #match keywords, operators and aliases

EDIT: After some

  [Read more...]
How to support COUNT(DISTINCT expression) expressions with Flexviews.
+1 Vote Up -0Vote Down
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.
+0 Vote Up -0Vote Down
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

  [Read more...]
Flexviews for MySQL 1.0.3-alpha released
+0 Vote Up -0Vote Down
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 http://flexviews.sourceforge.net
Learn more about Flexviews and see a demonstration at the July 14th Silicon Valley MySQL Meetup
+0 Vote Up -0Vote Down
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
+0 Vote Up -0Vote Down
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!)
+0 Vote Up -0Vote Down
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

  [Read more...]
Flexviews - minor improvements, further plans
+0 Vote Up -0Vote Down
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

  [Read more...]
Flexviews for MySQL 1.0.1-alpha released
+0 Vote Up -0Vote Down
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
+0 Vote Up -0Vote Down
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 1 to 16

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.