Sorry for the double reply Jay, I meant to send to the entire list. On Sat, Jul 18, 2009 at 3:47 PM, Justin Swanhart <[email protected]>wrote:
> Hi Jay, > > Materialized views can also be useful for improving OLTP join performance. > You can materialize the primary keys of all the tables involved in a complex > join. This avoids secondary index lookups on InnoDB backed tables, which > reduces IO. If you "push down" filter columns into the materialized view, > you can filter on multiple tables in a "meta join index" of sorts, which has > great performance implications. Ideally a parser rewrite plugin would know > this sort of join index exists and use it when requested. > > I'd like thoughts on abstracting Flexviews into an API which could be used > by multiple RDBMS interfaces. I'd like to make Flexviews work in such a way > that it can simply issue SQL to use changelogs collected by triggers or > other methods where applicable. It would be nice if Flexviews could be used > to provide view aggregation to multiple open source databases through on > open API. The API would generate SQL statements so this need not be > implemented in a Drizzle specific way. > > Once parser plugins are implemented it could call the Flexviews API to get > SQL to create and maintain the aggregations. > > As for collecting table change logs I think two different, but equally > useful approaches should be taken. > > a) a generic trigger plugin system for before/after plugins > i) plugins are registered as before/after plugins for a table, a > database, or all tables > ii) this is the easiest way to support a synchronous algorithm > iii) eventually this could be extended to pass the row changes to an > "external stored procedure", but a trigger will have to be written in C for > now, and be generic with respect to the tables being modified. Triggers in > C would not be useful for "business logic" type triggers, but are perfect > for implementing a fixed algorithm like synch. materialized view refresh > algorithms. > > and > > b) a change data capture plugin which can > i) serialize log data to local change capture tables (which themselves > would not be logged) > ii) send data as INSERT/UPDATE/DELETE via XA to a remote server for > synchronous replication. > iii) log into an external log as your plugin currently does > iv) log into an external SQL CSV tuple changes, per table. > > I'm thinking that for Flexviews, type B is more appropriate, because it > requires workload serialization, which shouldn't be implemented using > triggers. > > > On Sat, Jul 18, 2009 at 2:44 PM, Jay Pipes <[email protected]> wrote: > >> Hi Justin! >> >> I've read through all of your 3 posts. Very interesting stuff. I can see >> how flexviews would be useful to a number of environments, including classic >> data warehousing and also as cached aggregate tables for OLTP/web type >> environments. >> >> It's pointless to discuss concerns over the performance of a solution >> built on top of MySQL triggers and stored procedures, since Drizzle has >> neither, so I will talk about what needs to be accomplished by Drizzle >> contributors *before* a flexviews plugin can be developed for Drizzle (and >> of course, it would be a plugin, as this would not be a feature central to >> the kernel...). >> >> The following things need to happen in Drizzle: >> >> 1) Ability for plugins to add new tokens and grammar rules to the parser >> in order to add new syntax (for instance, for the MATERIALIZED and VIEW >> keywords). >> >> Without additions to the parser, flexviews would have to be implemented as >> an awkward set of UDFs... >> >> This needs to happen for a variety of upcoming plugins, actually, >> including some of the replication plugins (publisher and subscriber coming >> shortly). Without this functionality, plugins are stuck using only the SQL >> syntax currently implemented in Drizzle. >> >> A bit of an aside...the Drizzle parser, though less than a third the size >> of MySQL's, is still a bit of a rat's nest of code. To make it extensible, >> we need to add hooks that enable a plugin's own parser methods to be called >> when the kernel's parser hits a token it does not understand. So, instead >> of bombing a YYABORT(), it needs to call plugins' registered parse routines, >> passing in the token stream. >> >> 2) Simple table-specific trigger plugin >> >> Right now, there is only a very simple trigger mechanism which allows >> other plugins to be fed a Command message when *anything* changes on *any* >> table in Drizzle. This is called the default replicator plugin and is set >> to be merged (hopefully today or tomorrow) into trunk. You can see this >> plugin (and how ridiculously simple it actually is) in my replication >> branch: >> >> http://code.launchpad.net/~jaypipes/drizzle/replication<http://code.launchpad.net/%7Ejaypipes/drizzle/replication> >> >> Look at >> >> /plugin/default_replicator/default_replicator.cc|.h >> /plugin/command_log/command_log.cc|.h >> >> This plugin does exactly what it implies: it replicates all data >> modification and definition events which occur in the kernel to *any* >> registered "Applier" plugin. >> >> The only example of an Applier plugin so far is the command_log plugin >> (also in the same branch above), which takes Command messages it receives >> from the default replicator plugin and "applies" (writes) them to a simple >> log file. >> >> The Command Log plugin can be used as an example of how to work with >> Command messages, which contain all the information that a Flexview >> "trigger" would need in order to process the log of deltas you describe in >> your ML posts. >> >> Cheers! >> >> Jay >> >> <snip>
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
