Wednesday, December 19, 2018

Exchange Partition: An Archiving Strategy


 As applications mature, tables tend to grow infinitely, specially the time series ones. They have tens of millions of rows and run up to a few TBs in size. Even though the tables have so much data, applications only need to access data that was recently saved. (Say within, the last year or so). A trivial task of adding a new index or changing the type of a column for such a humungous table, becomes a very painful task. I was charged with such a task recently and that got me thinking about archiving strategies.

Archiving is a good practice to keep the data to a required working set, while at the same time, maintaining the data that might be needed for legal or compliance reasons, or for certain less frequent workflows in the application, like looking at the 10 year purchase history of a customer.

Though archiving seems like a database problem, it cannot be done in vacuum. It needs buy in from application, legal and compliance to know what the archiving boundaries are, what are the access patterns and availability requirements for the archived data.

But solving for the database problem is something right up my alley, so I thought of testing a few approaches. One of my favorites for the ease of use is the "EXCHANGE PARTITION" feature for 5.7.

In MySQL 5.7, it is possible to exchange a table partition or subpartition with a table using –
ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

where
pt is the partitioned table
p is the partition of pt to be exchanged
and nt is a non partitioned table.

Privileges needed for the statement is the combination of ALTER TABLE and
TRUNCATE TABLE on both tables.

This provides a great opportunity for archival of partitioned tables.

Consider an online retail store, that logs its invoices or orders in a table. Here is an over simplified invoice table for the store.

CREATE TABLE `invoice` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_num` int(10) unsigned NOT NULL,
  `stockcode` int(10) unsigned NOT NULL,
  `invoice_date` datetime NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`,`invoice_date`)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(invoice_date))
(PARTITION p2009 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2010 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION p2011 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p2012 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2013 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

The queries on this table only need data from a year ago, to be available for queries, however for compliance and other non-frequent workflow we need to maintain older data. The EXCHANGE PARTITION gives us an opportunity to archive partitions, with quick DDL operations.

Consider the need to archive 2010 data, which is in p2010 partition.

mysql> select count(*) from invoice PARTITION (p2010);
+----------+
| count(*) |
+----------+
|  1111215 |
+----------+
1 row in set (0.38 sec)

To use EXCHANGE PARTITION the table to be partitioned pt and the non-partitioned table that the data will be archived into nt need to meet a few requirements - 
-       The non-partitioned table needs to have the same structure as the partitioned table.
-       It cannot be a temporary table
-       It cannot have foreign keys, or have any foreign keys that refer to it.
-       There are no rows in nt that lie beyond the boundaries of the partition p.


Lets create the table invoice_2010 which will archive all invoices from 2010.

mysql> create table invoice_2010 like invoice;
Query OK, 0 rows affected (0.44 sec)

mysql> alter table invoice_2010 remove partitioning;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table invoice_2010 \G
 Table: invoice_2010
Create Table: CREATE TABLE `invoice_2010` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_num` int(10) unsigned NOT NULL,
  `stockcode` int(10) unsigned NOT NULL,
  `invoice_date` datetime NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`,`invoice_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Archiving the partition using EXCHANGE partition, can be done as –

mysql> select count(*) from invoice PARTITION (p2010);
+----------+
| count(*) |
+----------+
|  1111215 |
+----------+
1 row in set (0.38 sec)

mysql> ALTER TABLE  invoice EXCHANGE PARTITION p2010 WITH TABLE invoice_2010;
Query OK, 0 rows affected (0.14 sec)

mysql> select count(*) from invoice PARTITION (p2010);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from invoice_2010;
+----------+
| count(*) |
+----------+
|  1111215 |
+----------+
1 row in set (0.21 sec)

If needed the partition can also be moved back.

mysql> ALTER TABLE  invoice EXCHANGE PARTITION p2010 WITH TABLE invoice_2010;
Query OK, 0 rows affected (0.73 sec)

mysql> select count(*) from invoice_2010;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from invoice PARTITION (p2010);
+----------+
| count(*) |
+----------+
|  1111215 |
+----------+
1 row in set (0.23 sec)

If any of the rows in the non partitioned table violate the partitioning rule for the partition it is being exchanged with, you would get an error.

mysql> update invoice_2010 set invoice_date='2012-01-01' where id=10000009;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ALTER TABLE  invoice EXCHANGE PARTITION p2010 WITH TABLE invoice_2010;
ERROR 1737 (HY000): Found a row that does not match the partition


In its current implementation a row by row validation, does a full table scan on the non partitioned table to evaluate if there is a row in the table that violates the partitioning rule. From the open worklog, it seems like there are plans to have the command use index instead of a full table scan but it isn't implemented yet. A workaround to this is using WITHOUT VALIDATION.

To avoid time consuming validation when exchanging a partition with a table that has many rows, it is possible to skip the row-by-row validation step by appending WITHOUT VALIDATION to the ALTER TABLE ... EXCHANGE PARTITION statement. However with this the onus lies on the engineer to verify that no partitioning rules are getting violated.

mysql> ALTER TABLE  invoice EXCHANGE PARTITION p2010 WITH TABLE invoice_2010 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.13 sec)

From a 20,000 ft view, the high level architecture for this involves
-       Taking a upgradable metadata lock on both tables
-       Verifying that the metadata matches (i.e both tables have the same structure)
-       If WITHOUT VALIDATION is not used, verifying data in the non partitioned table
-       Upgrading to a exclusive metadata lock for both tables
-       Rename non-partitioned table to partition and partition to non-partitioned table.
-       Releasing the metadata locks


It would have been nice  if it was possible to append to the non partitioned table, rather than a exchange, but my guess is then it wouldn't be a metadata operation. The same can be achieved by an exchange to an intermediate table, followed by copy into the archive table.

As pertains to previous example, this would involve creating a table invoice_2010_intermediate. Exchanging p2010 from invoice table with invoice_2010_intermediate and doing a copy from invoice_2010_intermediate to invoice_2010.

But for what it does, I think it is a delightful approach to archiving data that you still need on your database server, for reading and archiving, but it doesn't change.

Resources :
Script for populating data into invoice table - https://gist.github.com/dontstopbelieveing/3c42338c8f5c756a526ab2f7bef5525e
-->

MySQL storing time

Storing time in databases seems like a daunting task to many. The general principle with storing time in databases is - ALWAYS STORE TIME ...