MariaDB Temporal Tables: Uncut & Uncensored (Part III)

Written by Pythian Marketing | Dec 9, 2019 5:00:00 AM

(This is the third of a series of blog posts covering MariaDB Temporal Tables. The first and second in the series introduced the topic.)

"Who are you?" said the Caterpillar. This was not an encouraging opening for a conversation. Alice replied, rather shyly, "I--I hardly know, sir, just at present-- at least I know who I was when I got up this morning, but I think I must have been changed several times since then."

Lewis Carroll - Alice in Wonderland

Welcome to the third installment of MariaDB Temporal Tables: Uncut & Uncensored. The good news is that you arrived here. The no-so-good news is that we are probably in the middle of our trip to understanding temporal tables. I promise that I'll try to make the journey as pleasant as possible. For newcomers to this blog series, if you arrived here via a search engine or an external link, probably you should look first at the two previous posts: the first one is an introduction to Temporal Tables, the second one goes a bit further and digs into the internals of System Versioned Tables. In this post, we will continue learning about temporal tables; specifically, how they are replicated, as well as some basic maintenance operations and partitioning.

System Versioned Tables Replication

Besides storing information about the changes that take place the data, databases are often replicated. It is a good idea to verify that the versioned tables are replicated properly and consistently. If our binary log is statement (or mixed) this is what gets replicated:

SET INSERT_ID=1/*!*/;
 # at 2631
 #190620 15:41:30 server id 1 end_log_pos 2756 CRC32 0x3944e1bf ... 
 SET TIMESTAMP=1561045290.679089/*!*/;
 insert into sv_table_ts values (null,1,'First row')
 /*!*/;

We see that the timestamp associated with the sentence is written into the binary logs. This means that the insert is processed using the correct timestamp. But what about row-based binary logging?

#190620 15:18:19 server id 1 end_log_pos 635 CRC32 0x406c16b2 Write_rows:
 table id 40 flags: STMT_END_F
 ### INSERT INTO `pythian`.`sv_table_ts`
 ### SET
 ### @1=54483
 ### @2=5
 ### @3='five'
 ### @4=1561043899.864106
 ### @5=2147483647.999999
 # Number of rows: 1

The whole row is replicated, including the timestamps. Looks like time-versioned tables are replication safe, at least the features we've seen so far. (SPOILER ALERT: In a later post, we will examine a feature that is not replication safe.)

System Versioned Tables Maintenance

In our first post, we saw that adding system versioning to a table is as easy as:

MariaDB [pythian]> alter table sv_table_ts add system versioning;
 Query OK, 10003 rows affected (0.115 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

So we can expect that dropping system versioning is as easy as replacing add by drop.

MariaDB [pythian]> alter table sv_table_ts drop system versioning;
 Query OK, 0 rows affected (0.081 sec)
 Records: 0 Duplicates: 0 Warnings: 0

It is important to keep in mind that both adding and dropping system versioning can have an impact on the online queries on that table, and can take a long time for large tables. When we add system versioning, we change the primary key and add two columns. And when we drop system versioning, we change the primary key, remove two columns and delete the history rows. Also, do not expect to be able to use online schema change tools like pt-osc or gh-ost on a system-versioned table, only the MariaDB SQL sentences that support online changes should be used, and as we're changing the primary key, the change will take some time and is not lock-free. But what if I just want to reclaim the space allocated by changes? It is possible to purge the history data, both completely or for changes before a certain time using delete history from <table_name> [before system_time '<timestamp>']

MariaDB [pythian]> DELETE HISTORY FROM sv_table_ts;
 Query OK, 1 row affected (0.013 sec)
 
 MariaDB [pythian]> DELETE HISTORY FROM sv_table_ts BEFORE SYSTEM_TIME '2019-06-18 13:46:28';
 Query OK, 0 rows affected (0.001 sec)

Partitioning

When we talked about performance, we found that having all the history data mixed with current data could become a performance issue. To avoid these problems, we can add partitioning to move history data out of the primary table:

MariaDB [pythian]> alter table sv_table_ts partition by system_time (partition
  -> p_hist HISTORY, partition p_curr current);
 Query OK, 10003 rows affected (0.135 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

We can also use more than one partition for the history data by limiting the number of changes to store per partition:

MariaDB [pythian]> alter table sv_table_ts
  -> partition by system_time limit 100000 (
  -> partition p0 history,
  -> partition p1 history,
  -> partition pcur current
  -> );
 Query OK, 10003 rows affected (0.122 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

Or we can have partitions using time slices. For example, one week:

MariaDB [pythian]> alter table sv_table_ts
  -> partition by system_time
  -> interval 1 week (
  -> partition p0 history,
  -> partition p1 history,
  -> partition p2 history,
  -> partition pcur current
  -> );
 Query OK, 10003 rows affected (0.122 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

And obviously, it is possible to add or drop history partitions:

MariaDB [pythian]> alter table sv_table_ts
  -> add partition (partition p3 history);
 Query OK, 0 rows affected (0.015 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 MariaDB [pythian]> alter table sv_table_ts
  -> drop partition p0;
 Query OK, 0 rows affected (0.024 sec)
 Records: 0 Duplicates: 0 Warnings: 0

Or remove partitioning completely:

MariaDB [pythian]> alter table sv_table_ts remove partitioning;
 Query OK, 10003 rows affected (0.127 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

It is also possible to subpartition the table, but there is a syntactic restriction: the main partition must use system_versioning, subpartition by system_time is not supported:

MariaDB [pythian]> alter table sv_table_ts
  -> partition by system_time
  -> subpartition by key (id)
  -> subpartitions 4 (
  -> partition ph history,
  -> partition pc current
  -> );
 Query OK, 10003 rows affected (0.186 sec)
 Records: 10003 Duplicates: 0 Warnings: 0

Altering System-Versioned Tables

The purpose of system-versioned tables is to keep a reliable history of data changes. This means that altering the structure of the table becomes a problem, as we have to decide if the changes are applied also to the history view or we allow having different table definitions for the history and for current data. Keeping different table definitions is not so simple: a table can be modified many times. MariaDB uses the former approach which is also straightforward, as the history data is stored in the same table. However, it introduces session variables to protect the table from accidental changes: system_versioning_alter_history. This variable has two possible values: error and keep. Error raises an error when you try to alter the table and keep allows the change to be processed.

MariaDB [pythian]> alter table sv_table_ts add x2 int(11) not null;
 ERROR 4119 (HY000): Not allowed for system-versioned `pythian`.`sv_table_ts`. Change @@system_versioning_alter_history to proceed with ALTER.
 MariaDB [pythian]> set system_versioning_alter_history=keep;
 Query OK, 0 rows affected (0.000 sec)
 
 MariaDB [pythian]> alter table sv_table_ts add x2 int(11) not null;
 Query OK, 0 rows affected (0.007 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 MariaDB [pythian]> select count(*) from sv_table_ts for system_time all;
 +----------+
 | count(*) |
 +----------+
 | 10003 |
 +----------+
 1 row in set (0.002 sec)
 
 MariaDB [pythian]> select distinct x2 from sv_table_ts;
 +----+
 | x2 |
 +----+
 | 0 |
 +----+
 1 row in set (0.005 sec)
 
 MariaDB [pythian]> alter table sv_table_ts add x3 int(11);
 Query OK, 0 rows affected (0.005 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 MariaDB [pythian]> select distinct x3 from sv_table_ts;
 +------+
 | x3 |
 +------+
 | NULL |
 +------+
 1 row in set (0.004 sec)
 
 MariaDB [pythian]> select count(*) from sv_table_ts for system_time all;
 +----------+
 | count(*) |
 +----------+
 | 10003 |
 +----------+
 1 row in set (0.002 sec)

It's worth mentioning that defining a default value to the new column doesn't trigger any change in the history view. Table alters that do not change column definition will not be affected by system_versioning_alter_history:

MariaDB [pythian]> set system_versioning_alter_history=error;
 Query OK, 0 rows affected (0.000 sec)
 
 MariaDB [pythian]> alter table sv_table_ts add key(x3);
 Query OK, 0 rows affected (0.041 sec)
 Records: 0 Duplicates: 0 Warnings: 0

This has been a long post and, like Alice in the quote from Alice in Wonderland, I feel a bit stunned, so it is time to let you go. In the next post we will finish our journey into System Versioned tables in MariaDB, we will learn about the extended syntax and an interesting MariaDB feature that allows using transaction data instead of timestamps to store the history. Sounds good, doesn't it?

MySQL Database Consulting

Looking to optimize your MySQL use?

View full post