Archivier das mal!

mysql_blo_20191203-093221_1

Immer wieder kommt es bei Kunden vor, dass Datenbanken wachsen, da historische Inhalte nicht gelöscht oder archiviert werden. Oftmals handelt es sich bei diesen Daten um Zeitreihenwerte. Dies können beispielsweise erstellte Rechnungen oder erzeugte und gespeicherte Messwerte über mehrere Monate und Jahre sein. Ein mögliches Archivierungskonzept kann die beiden von MySQL unterstützten Funktionalitäten „Partitionierung" und „Replikation" verwenden.

Im Folgenden wird ein  an einen Kunden angelehntes  Archivierungskonzept erläutert, welches exakt diese Technologien implementiert hat.

Zahlen bitte! 

Im konkreten Fall hat ein Kunde in einem Warenwirtschaftssystem Rechnungen für Kunden gespeichert.
mysql> select * from rechnungen;
+--------+----------+--------+------------+------+-------+
| rechnr | kundennr | betrag | datum      | jahr | monat |
+--------+----------+--------+------------+------+-------+
|      4 |      987 |   4.25 | 2018-06-10 | 2018 |     6 |
|      1 |      123 |   7.50 | 2019-04-15 | 2019 |     4 |
|      2 |      456 |   5.75 | 2020-02-03 | 2020 |     2 |
|      3 |      789 |   4.50 | 2020-01-12 | 2020 |     1 |
|      5 |      123 |   6.50 | 2021-09-17 | 2021 |     9 |
+--------+----------+--------+------------+------+-------+
5 rows in set (0,00 sec) 
Da die Anzahl der Rechnungen (anders als im Beispiel) über die vergangenen Jahren stark angewachsen sind, wurden diese Daten partitioniert

Teile und herrsche!

Aktuell werden die entsprechenden Rechnungen über das Datenmodell nach Jahren partitioniert.
CREATE TABLE `rechnungen` (
  `rechnr` int(11) NOT NULL AUTO_INCREMENT,
  `kundenr` int(11) NOT NULL,
  `betrag` decimal(10,2) NOT NULL,
  `datum` date NOT NULL,
  `jahr` int(11) GENERATED ALWAYS AS (year(`datum`)) VIRTUAL,
  `monat` int(11) GENERATED ALWAYS AS (month(`datum`)) VIRTUAL,
  PRIMARY KEY (`rechnr`,`datum`)
) PARTITION BY RANGE (year(`datum`))
(PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB); 
Bei Zugriffen nach bestimmten Jahren, kann sich die Datenbank gezielt auf einzelne Partitionen konzentrieren. So wird beispielsweise bei bestimmten SELECTs aus einem Full-Table-Scan „nur" ein Full-Partition-Scan:
mysql> explain select * from rechnungen where datum between '2018-01-01' and '2018-12-31';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | rechnungen | p2018      | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec) 
Da auf dem produktiven System aus unterschiedlichen Gründen jedoch nicht alle Daten gehalten werden sollen, werden regelmäßig alte Daten gelöscht. Diese müssen aber zuvor archiviert werden (z.B. aus regulatorischen Gründen). Anstatt diese Daten klassisch zu entladen (Export), um sie dann ggfs. auf einem anderen System (Reporting-Datenbank / Datawarehouse) wieder zu laden, wird hier eine Replikation genutzt.

Bitte (nicht) nachmachen!

Generell ist es die Aufgabe eines Replikations-Systems (wird bei MySQL Slave genannt), die Kommandos (Transaktionen) des führenden Systems (Master genannt) analog durchzuführen. Dazu werden die Daten (Transaktionen) aus einem Logfile vom Master auf den Slave kopiert und dort wiederholt.

Vor dem Jahreswechsel wird auf dem Master eine neue Partition für die Rechnungen hinzugefügt. Auch dieses Kommando vollzieht der Slave nach, um für die kommenden Rechnungen gewappnet zu sein.

mysql> alter table rechnungen add partition (partition p2021 values less than (2022))
Query OK, 0 rows affected (0,06 sec)
Records: 0  Duplicates: 0  Warnings: 0 

Nach dem Jahreswechsel wird im Gegenzug eine alte Partition auf dem Master gelöscht, um den Datenbestand zu verrringern. Dies soll auf dem Slave jedoch nicht passieren. Um die Replikation dieses Kommandos zu unterdrücken, wird in der entsprechenden Session das Loggen auf dem Master (für dieses eine Kommando) deaktiviert.

mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0,00 sec)
 
mysql> alter table rechnungen drop partition p2018;
Query OK, 0 rows affected (0,08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0,00 sec) 

Das Löschen der Partition wird somit vom Slave nicht durchgeführt. Die Daten bleiben also dort erhalten.

Im Normalfall sollte dieses „Schieflage" zu keinen Problem bei der Replikation führen. Auf dem Master können ja keine Daten manipuliert werden, die zu einem Abbruch der Replikation führen können. Natürlich gibt es Ausnahmen von diesem „Normalfall". Würde man versuchen auf dem Master eine „historische" Partition wieder anzulegen (die auf dem Slave noch existiert), so würde dies zu einem Fehler führen. Der Slave würde ja ebenfalls versuchen diese Partition anzulegen. Dies würde jedoch nicht funktionieren, da eine gleichnamige Partition bei ihm ja noch existiert.

Wenn Sie Fragen zur MySQL Replikation, zur Partitionierung, Archivierungslösungen oder großen Datenmengen im Umfeld von MySQL (oder MariaDB) haben, dann sprechen Sie uns gerne an.

 

Kommentare

Derzeit gibt es keine Kommentare. Schreibe den ersten Kommentar!
Gäste
Sonntag, 08. Dezember 2019

Sicherheitscode (Captcha)