Not the answer you need?
Register and ask your own question!

Replicate data to a joined table for Reporting

DereksurfsDereksurfs EntrantCurrent User Role Novice
Hello,

I would to copy OLTP data from original tables to a Reporting table which is join of 5 tables. I know Percona doesn't offer materialized views like Oracle or PostgreSQL. But I am wondering about other methods to perform a similar kind of data copy, though possibly more of a workaround. Long term we'll most likely move reporting tables to a separate database/warehouse. In both cases we still need an efficient method to replicate this data to Reporting tables which are not a one for one copy in terms of schema. Regarding lag time or eventual consistency, we could allow ~ 3-5 minutes for data replication to occur.

Is there any way to use Percona's built-in replication or other tools to replicate to a table of different names and schema structures? Let's say using a query to define what will be replicated and to where?

If not possible, what other ideas or workarounds might be recommended. I can think of creating some triggers on master tables (onInsert, onUpdate, onDelete). But I'm looking for other options beyond trigger based transactions, if available. And ideas?

Thanks,
Derek

Comments

  • Federico RazzoliFederico Razzoli Contributor Current User Role Patron
    These are the ways I know to do that:
    1. Justin Swanhart's FlexViews
    2. MariaDB allows to run triggers on a slave
      1. With Percona Server (or vanilla MySQL) and replication, yes you can theoretically do that on a slave, but you would need to use statement based replication, which is slow and unsafe.
    3. You can however use triggers on the master with any MySQL flavor.
  • DereksurfsDereksurfs Entrant Current User Role Novice
    Thank you, Federico. Something like FlexViews is the type of functionality I'm looking for. I'm just not sure it would be Production grade/capable or not. There is not a lot of activity on that particular project.

    One of the challenges to using triggers with statements in an HA environment is that we are running in a multi-master replicated cluster using Percona XtraDB. So we don't want to fire duplicate triggers from those master tables within the cluster. It looks like MariaDB had to address that somehow with their slave triggers when dealing with clusters. That's propbably a moot point for us since we're running Percona rather MariaDB.
  • Federico RazzoliFederico Razzoli Contributor Current User Role Patron
    I think FlexViews has been used in production, and probably is. My only problem with that project is that it requires a lot of different technologies.

    About triggers, I really don't recommend to use statement-based replication. It's unsafe and inherently slow (because each statement is re-executed in the slave). MariaDB allows to run triggers for RBR events without logging their effects in the binlog.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.