HTAP Replication

MariaDB SkySQL provides MariaDB Platform for Smart Transactions service, delivering HTAP (Hybrid Transactional-Analytical Processing) capabilities.

MariaDB Platform for Smart Transactions service must be configured by establishing the desired replication between Transactional and Analytical components.

The instructions provided here are specific to MariaDB Platform for Smart Transactions, which delivers HTAP service. Information on HA (Primary/Replica) replication is provided separately.

Replication of INSERT with HTAP

Enable Replication for Smart Transactions (HTAP)

When using MariaDB Platform for Smart Transactions (HTAP), data can be replicated from InnoDB tables to ColumnStore tables. To enable replication, a replication filter must be created. The replication filter must match each table that requires replication.

Each MariaDB Platform for Smart Transactions service is limited to one replication filter, which can contain one source schema, one target schema, and multiple tables.

To create a replication filter, use the set_htap_replication() user-defined function (UDF):

SELECT set_htap_replication(
  '<fully_qualified_table_name_regex>',
  '<source_database>',
  '<target_database>');

The set_htap_replication() UDF's parameters have the following meanings:

  • <fully_qualified_table_name_regex> is a regular expression (regex) that uses the PCRE2 syntax. The regex must match the names of all source tables that need to be replicated, and it must match the table names with the database prefix, taking the form: database.table

  • <source_database> is the name of the source database.

  • <target_database> is the name of the target database.

Database and table names should take the SkySQL implementation guidance into account.

Example: Replication Setup

As an example, to replicate transactional tables from the innodb_db database to corresponding analytical tables in the columnstore_db database but only replicating transactional tables with a name that starts with the htap_ prefix:

  1. Set up the replication filter using the set_htap_replication() UDF:

    SELECT set_htap_replication(
      'innodb_db.htap_[[:word:]]+',
      'innodb_db',
      'columnstore_db');
    
  2. Create the transactional and analytical databases:

    CREATE DATABASE innodb_db;
    CREATE DATABASE columnstore_db;
    
  3. Create the transactional tables that will be replicated:

    CREATE TABLE innodb_db.htap_test1 (id INT) ENGINE = InnoDB;
    CREATE TABLE innodb_db.htap_test2 (id INT) ENGINE = InnoDB;
    
  4. Confirm that the corresponding analytical tables were created:

    SHOW TABLES
      FROM columnstore_db
      LIKE 'htap_%';
    
    +-----------------------------------+
    | Tables_in_columnstore_db (htap_%) |
    +-----------------------------------+
    | htap_test1                        |
    | htap_test2                        |
    +-----------------------------------+
    
  5. The replication initially creates empty InnoDB tables, which need to be transformed into ColumnStore tables and which need to be populated with the initial copy of the data:

    DROP TABLE IF EXISTS columnstore_db.htap_test1;
    
    CREATE TABLE columnstore_db.htap_test1
      ENGINE=COLUMNSTORE
      SELECT * FROM innodb_db.htap_test1;
    
    DROP TABLE IF EXISTS columnstore_db.htap_test2;
    
    CREATE TABLE columnstore_db.htap_test2
      ENGINE=COLUMNSTORE
      SELECT * FROM innodb_db.htap_test2;
    
  6. Insert some data into the transactional tables that will be replicated:

    INSERT INTO innodb_db.htap_test1 VALUES (100);
    INSERT INTO innodb_db.htap_test2 VALUES (200);
    
  7. Confirm that the data was replicated to the corresponding analytical tables:

    SELECT * FROM columnstore_db.htap_test1;
    
    +------+
    | id   |
    +------+
    |  100 |
    +------+
    
    SELECT * FROM columnstore_db.htap_test2;
    
    +------+
    | id   |
    +------+
    |  200 |
    +------+
    
  8. Create tables that will not be replicated:

    CREATE TABLE innodb_db.transactional_test1 (id INT) ENGINE = InnoDB;
    CREATE TABLE columnstore_db.analytical_test1 (id INT) ENGINE = ColumnStore;
    
  9. Confirm that these tables were not replicated:

    SHOW TABLES
      FROM innodb_db
      LIKE 'analytical_%';
    
    Empty set (0.02 sec)
    
    SHOW TABLES
      FROM columnstore_db
      LIKE 'transactional_%';
    
    Empty set (0.02 sec)
    

Implementation Guidance

When replicating between transactional and analytical tables, use the following guidelines:

  • Replicated transactional tables must be in different databases than their corresponding analytical tables.

  • Replicated transactional tables must have the same names as their corresponding analytical tables.

  • Include a common prefix to the names of any replicated tables. This will simplify the regular expressions in the next step. For example, the names of replicated tables could use the prefix htap_.

  • Force a minimum database and/or table name length. This will reduce the chance of a false positive regular expression match.

  • Never use table and database names that fully or partially match SQL keywords. This will ensure that the regular expression cannot match some irrelevant text in a query string, instead of matching the intended table names. For example, database and table names should probably not match keywords like select, from, table, database, or various others.

  • Avoid table and database names that include words that will be found in the data. This will ensure that the regular expression cannot fully or partially match the data itself, instead of matching the intended table names.

List Existing Replication

To list the current replication filter, use the show_htap_replication() user-defined function (UDF):

SELECT show_htap_replication();

The result will look like this:

+------------------------------------------------------------------------------------+
| show_htap_replication()                                                            |
+------------------------------------------------------------------------------------+
|
    === replication_filter ===
    table: innodb_db.htap_[[:word:]]+
    source database: innodb_db
    target database: columnstore_db
|
+------------------------------------------------------------------------------------+

Edit Existing Replication

To edit the current replication filter, use the set_htap_replication() user-defined function (UDF). This is the same UDF that is used to create a new replication filter. However, if one already exists, the new replication filter will override the old one.

Each MariaDB Platform for Smart Transactions service is limited to one replication filter, which can contain one source schema, one target schema, and multiple tables.

SELECT set_htap_replication(
  '<fully_qualified_table_name_regex>',
  '<source_database>',
  '<target_database>');

The set_htap_replication() UDF's parameters have the following meanings:

  • <fully_qualified_table_name_regex> is a regular expression (regex) that uses the PCRE2 syntax. The regex must match the names of all source tables that need to be replicated, and it must match the table names with the database prefix, taking the form: database.table

  • <source_database> is the name of the source database.

  • <target_database> is the name of the target database.

Database and table names should take the SkySQL implementation guidance into account.

Example: View and Confirm Replication Filter

For example, to view the current replication filter and ensure that innodb_db.big_tab is replicated, in addition to the current replicated tables:

  1. View the current replication filter with the show_htap_replication() UDF:

    +------------------------------------------------------------------------------------+
    | show_htap_replication()                                                            |
    +------------------------------------------------------------------------------------+
    |
        === replication_filter ===
        table: innodb_db.htap_[[:word:]]+
        source database: innodb_db
        target database: columnstore_db
    |
    +------------------------------------------------------------------------------------+
    
  2. Edit the replication filter using the set_htap_replication() UDF:

    SELECT set_htap_replication(
      'innodb_db.htap_[[:word:]]+|innodb_db.big_tab',
      'innodb_db',
      'columnstore_db');
    
  3. Confirm that the replication filter changed:

    +------------------------------------------------------------------------------------+
    | show_htap_replication()                                                            |
    +------------------------------------------------------------------------------------+
    |
        === replication_filter ===
        table: innodb_db.htap_[[:word:]]+|innodb_db.big_tab
        source database: innodb_db
        target database: columnstore_db
    |
    +------------------------------------------------------------------------------------+
    
  4. Create the transactional table that will be replicated:

    CREATE TABLE innodb_db.big_tab (id INT) ENGINE = InnoDB;
    
  5. Confirm that the corresponding analytical table was created:

    SHOW TABLES
      FROM columnstore_db
      LIKE 'big_tab';
    
    +------------------------------------+
    | Tables_in_columnstore_db (big_tab) |
    +------------------------------------+
    | big_tab                            |
    +------------------------------------+
    
  6. The replication initially creates empty InnoDB tables, which need to be transformed into ColumnStore tables and which need to be populated with the initial copy of the data:

    CREATE OR REPLACE TABLE columnstore_db.big_tab
      ENGINE=COLUMNSTORE
      SELECT * FROM innodb_db.big_tab;
    
  7. Insert some data into the transactional table that will be replicated:

    INSERT INTO innodb_db.big_tab VALUES (300);
    
  8. Confirm that the data was replicated to the corresponding analytical table:

    SELECT * FROM columnstore_db.big_tab;
    
    +------+
    | id   |
    +------+
    |  300 |
    +------+
    

Delete Existing Replication

To delete the current replication filter, use the set_htap_replication() user-defined function (UDF). This is the same UDF that is used to create a new replication filter. However, if one already exists, it can be deleted by calling the UDF with an empty string in place of the table name parameter:

SELECT set_htap_replication(
  '',
  'innodb_db',
  'columnstore_db');

If a replication filter only exists for a single target database, you can call the UDF with empty strings for all parameters to delete it:

SELECT set_htap_replication(
  '',
  '',
  '');

Note

Using the set_htap_replication() user-defined function (UDF) will implicitly call STOP SLAVE; START SLAVE;

This is needed for the new configuration to take effect, but can cause side effects if there is data currently being replicated.