MariaDB Temporal Tables: Uncut & Uncensored (Part II)
(This is the second of a series of blog posts covering MariaDB Temporal Tables. The first blog post can be found here.)
There are really four dimensions, three which we call the three planes of Space, and a fourth, Time. There is, however, a tendency to draw an unreal distinction between the former three dimensions and the latter, because it happens that our consciousness moves intermittently in one direction along the latter from the beginning to the end of our lives.
H.G. Wells - The time machine
In my previous post, we introduced the concept of temporal tables and started to work with system-versioned tables as implemented in MariaDB 10.4. All the SQL sentences should work for any engine that supports system-versioned tables, but in this post, we will focus only on the InnoDB engine. We will try to discover how it is implemented and the performance issues you should consider.System-Versioned Tables table structure
To keep the history of data, the RDBMS needs to store it somewhere, but where? In the previous post, we saw that updates generate both update and insert operations, suggesting that the history is stored in the table itself. Let's try to find out.MariaDB [pythian]> select column_name from information_schema.columns where -> table_name='sv_table_ts'; +-------------+ | column_name | +-------------+ | id | | x | | text | +-------------+ 3 rows in set (0.001 sec)No additional columns? Maybe these are just invisible columns.
MariaDB [pythian]> select ist.name, isc.name -> from -> information_schema.innodb_sys_columns isc -> inner join -> information_schema.innodb_sys_tables ist -> on ist.table_id=isc.table_id -> where ist.name = 'pythian/sv_table_ts'; +---------------------+-----------+ | name | name | +---------------------+-----------+ | pythian/sv_table_ts | id | | pythian/sv_table_ts | x | | pythian/sv_table_ts | text | | pythian/sv_table_ts | row_start | | pythian/sv_table_ts | row_end | +---------------------+-----------+ 5 rows in set (0.001 sec)Gotcha, we have two additional columns. What is the content of these columns? We will specify the name of these hidden columns in a select to retrieve their content:
MariaDB [pythian]> select id,x,row_start,row_end from sv_table_ts for system_time all; +----+------+----------------------------+----------------------------+ | id | x | row_start | row_end | +----+------+----------------------------+----------------------------+ | 1 | 1 | 2019-11-28 20:55:17.628237 | 2019-11-28 20:55:50.095010 | | 1 | 3 | 2019-11-28 20:55:50.095010 | 2038-01-19 03:14:07.999999 | | 2 | 2 | 2019-11-28 20:55:33.097465 | 2019-11-28 20:56:14.383640 | +----+------+----------------------------+----------------------------+ 3 rows in set (0.001 sec)There are two rows with id = 1, one has row_end = 2038-01-19 03:14:07.999999 (in brighter yellow) and has the same row_start than the other row with id one: 2019-11-28 20:55:50.095010 (in red). This timestamp looks pretty much like the time when id one was updated in the previous post. The row with id = 2 was deleted and has also a row end that seems to be the time the row was deleted (in blue). One thing to note is that 2038-01-19 03:14:07.999999 is currently the maximum value supported for timestamps in MariaDB/MySQL and other systems. The life of a row is defined by row_start and row_end values. For any row, if row_end value is the maximum value for timestamps, then the row is currently alive. If it has a different value, either it has been removed or has been updated, then another row with the same id will have that value as row_start. By following the row_start/row_end pairs for a certain primary key, we can follow the history of a row. Actually, we can follow the history of a row if the primary key hasn't been changed, in this case we would see the old row deleted and the new one inserted. But we all know that updating the primary key value is not recommended ;)
System-Versioned Tables key structure
We've seen that in the versioned table we can have several rows with the same old primary key value. This means that the primary key definition has to change somehow. Let's try to find how the primary key is defined now.MariaDB [pythian]> select ist.name, -> isi.name, -> isf.pos, -> isf.name -> from information_schema.innodb_sys_indexes isi -> inner join -> information_schema.innodb_sys_tables ist -> on ist.table_id=isi.table_id -> inner join -> information_schema.innodb_sys_fields isf -> on isi.index_id = isf.index_id -> where ist.name = 'pythian/sv_table_ts' -> order by isi.name, isf.pos; +---------------------+---------+-----+---------+ | name | name | pos | name | +---------------------+---------+-----+---------+ | pythian/sv_table_ts | PRIMARY | 0 | id | | pythian/sv_table_ts | PRIMARY | 1 | row_end | +---------------------+---------+-----+---------+ 2 rows in set (0.000 sec)The primary key is now a composite key that uses both the former primary key and adds the row_end at the end. This makes sense. For every query, we only need to add the row_end = timestamp maxvalue filter condition to implement the standard behavior. If we need to retrieve all the versions, then no conditions are added and if we need to access old versions using a timestamp, row_end must be greater than this timestamp and row_start must be smaller.
System-Versioned Tables performance considerations
The primary key will use a bit more space to store the row_end. This means a bit larger tables and indexes. This doesn't seem to make a difference in terms of performance, but it is important to keep in mind that we can reach the index prefix maximum size when enabling system versioning. Another issue to consider is that the table will require a lot of extra space, if changes are frequent, and all the versions of a row are stored together generating some memory overhead in the buffer pool. SPOILER ALERT: This can be fixed using partitioning, but we will discuss that in a later post. How efficient is retrieving rows for a certain moment in the past? We are going to run some explain plans to find out. We will test some different access patterns to learn how these queries are processed by the optimizer.MariaDB [pythian]> explain select * from sv_table_ts for system_time all\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sv_table_ts type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: 1 row in set (0.000 sec) MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sv_table_ts type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where 1 row in set (0.000 sec) MariaDB [pythian]> explain select * from sv_table_ts for system_time all where id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sv_table_ts type: ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 2 Extra: 1 row in set (0.000 sec) MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854' where id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sv_table_ts type: range possible_keys: PRIMARY key: PRIMARY key_len: 15 ref: NULL rows: 1 Extra: Using where 1 row in set (0.000 sec)As expected, retrieving all versions of all rows performs a full scan. But retrieving a snapshot for a certain moment of time also performs a full scan, this could possibly be optimized. We will go after that in a minute. The last two queries, using system versioning and where conditions use the primary key. Note that the where condition must be specified after the system version clauses. If you need to query the contents of the table for a defined time often and want to avoid the full scan, you can add an index using row_start and row_end as keys.
MariaDB [pythian]> alter table sv_table_ts add index sv_index(row_start,row_end); Query OK, 0 rows affected (0.016 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sv_table_ts type: range possible_keys: sv_index key: sv_index key_len: 7 ref: NULL rows: 3 Extra: Using index condition 1 row in set (0.000 sec)In this second post, we have discovered the internals of system versioning for InnoDB and analyzed the performance impact of enabling system versioning. In the next post, we will look at replication, maintenance operations, extended syntax, and partitioning. Epilogue: For the sake of correctness I have to confess that some color names were used incorrectly: red is actually crimson, blue is deepskyblue and brigher yellow is gold, as defined in the W3C CSS3 standard.
