Comment archiver automatiquement les données froides d'une table partitionnée au format CSV - PolarDB

La fonctionnalité de gestion du cycle de vie des données (DLM) réduit les coûts de stockage et améliore l'efficacité. Elle archive automatiquement et périodiquement les données froides peu consultées de PolarStore vers un support de stockage économique, tel que Object Storage Service (OSS).

Prérequis

Votre cluster doit exécuter PolarDB for MySQL 8.0.2, révision 8.0.2.2.34.1 ou ultérieure.
Remarque
- Pour vérifier la version de votre cluster, consultez Interroger la version du moteur.
- Si votre cluster exécute PolarDB for MySQL 8.0.2, révision 8.0.2.2.11.1 ou ultérieure, la fonctionnalité DLM n'enregistre pas les opérations dans le journal binaire.
Activez l'archivage des données froides avant d'utiliser les politiques DLM. Pour plus d'informations, consultez Activer l'archivage des données froides.
Remarque
Si vous n'activez pas la fonctionnalité d'archivage des données froides, l'erreur suivante s'affiche :
```
ERROR 8158 (HY000): [Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF.
```

Limites

La fonctionnalité DLM prend uniquement en charge les tables partitionnées sans sous-partitions. La méthode de partitionnement doit être RANGE COLUMN.
Vous ne pouvez pas utiliser la fonctionnalité DLM sur une table partitionnée possédant un index secondaire global (GSI).
PolarDB for MySQL ne permet pas de modifier une politique DLM. Pour changer une politique, supprimez d'abord la politique existante, puis créez-en une nouvelle.
Si une table possède une politique DLM, évitez d'exécuter des opérations DDL entraînant des incohérences de schéma entre la table source et la table d'archive (ajout ou suppression de colonnes, modification des types de données). De telles incohérences peuvent empêcher l'analyse des données archivées ultérieurement. Avant d'exécuter ces opérations DDL, supprimez la politique DLM de la table. Pour reprendre l'archivage automatique, créez une nouvelle politique DLM et spécifiez un nouveau nom pour la table d'archive. Ce nom doit différer de tous les noms de tables d'archive précédemment utilisés.
Utilisez le partitionnement INTERVAL RANGE pour étendre automatiquement les partitions et la fonctionnalité DLM afin d'archiver les données des partitions peu utilisées vers OSS.

Remarque
Le partitionnement INTERVAL RANGE est pris en charge uniquement pour les clusters exécutant PolarDB for MySQL 8.0.2, révision 8.0.2.2.0 ou ultérieure.

Spécifiez une politique DLM lors de l'exécution de l'instruction CREATE TABLE ou ALTER TABLE.
L'instruction SHOW CREATE TABLE n'affiche pas les politiques DLM. Consultez la table mysql.dlm_policies pour afficher toutes les politiques DLM.

Précautions

Une fois les données froides archivées, la table d'archive dans OSS devient accessible en lecture seule et les performances des requêtes peuvent diminuer. Effectuez des tests préalables pour vous assurer que les performances répondent à vos exigences.
Après l'archivage d'une partition vers OSS, les données de cette partition deviennent accessibles en lecture seule. Les opérations DDL sur la table partitionnée ne sont plus possibles.
Les sauvegardes n'incluent pas les données archivées vers OSS. Les données stockées dans OSS ne permettent pas la restauration à un point précis dans le temps.

Syntaxe

Créer une politique

Créer une politique DLM avec CREATE TABLE

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
    [dlm_add_options]

dlm_add_options:
    DLM ADD
        [(dlm_policy_definition [, dlm_policy_definition] ...)]

dlm_policy_definition:
    POLICY policy_name
    [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
    [ENGINE [=] engine_name]
    [STORAGE SCHEMA_NAME [=] storage_schema_name]
    [STORAGE TABLE_NAME [=] storage_table_name]
    [STORAGE [=] OSS]
    [READ ONLY]
    [COMMENT 'comment_string']
    [EXTRA_INFO 'extra_info']
    ON [(PARTITIONS OVER num)]

Créer une politique DLM avec ALTER TABLE

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]
    [dlm_add_options]

dlm_add_options:
    DLM ADD
        [(dlm_policy_definition [, dlm_policy_definition] ...)]

dlm_policy_definition:
    POLICY policy_name
    [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
    [ENGINE [=] engine_name]
    [STORAGE SCHEMA_NAME [=] storage_schema_name]
    [STORAGE TABLE_NAME [=] storage_table_name]
    [STORAGE [=] OSS]
    [READ ONLY]
    [COMMENT 'comment_string']
    [EXTRA_INFO 'extra_info']
    ON [(PARTITIONS OVER num)]

Paramètres de la politique DLM

Paramètre	Obligatoire	Description
tbl_name	Oui	Nom de la table.
policy_name	Oui	Nom de la politique.
TIER TO TABLE	Oui	Archive les données vers une nouvelle table externe OSS.
TIER TO PARTITION	Oui	Convertit les partitions de données chaudes en partitions de données froides stockées dans OSS au sein de la même table, créant ainsi une table partitionnée hybride. Remarque Cette fonctionnalité est en version canary. Pour l'utiliser, accédez à Quota Center, recherchez le nom du quota correspondant à l'ID de quota `polardb_mysql_hybrid_partition`, puis cliquez sur Apply dans la colonne Actions. L'archivage des partitions d'une table partitionnée vers OSS nécessite que votre cluster exécute PolarDB for MySQL 8.0.2, révision 8.0.2.2.17 ou ultérieure. Lorsque vous utilisez cette fonctionnalité, assurez-vous que le nombre total de partitions dans la table partitionnée ne dépasse pas 8 192.
TIER TO NONE	Oui	Supprime les données des partitions les plus anciennes au lieu de les archiver.
engine_name	Non	Moteur de stockage pour les données archivées. Actuellement, seul le moteur CSV est pris en charge pour l'archivage.
storage_schema_name	Non	Base de données pour la table d'archive. Par défaut, il s'agit de la base de données de la table source.
storage_table_name	Non	Nom de la table d'archive. S'il n'est pas spécifié, la valeur par défaut est `<source_table_name>_<dlm_policy_name>`.
STORAGE [=] OSS	Non	Stocke les données archivées dans OSS. Il s'agit de la valeur par défaut.
READ ONLY	Non	Rend les données archivées accessibles en lecture seule. Il s'agit de la valeur par défaut.
comment_string	Non	Commentaire associé à la politique DLM.
extra_info	Non	Spécifie les informations OSS_FILE_FILTER pour la table OSS de destination. Remarque L'archivage de partitions vers OSS requiert que votre cluster exécute l'édition Enterprise de PolarDB for MySQL 8.0.2, révision 8.0.2.2.25 ou ultérieure. Cette fonctionnalité ne prend effet que si la table de destination n'existe pas. Dans ce cas, le système génère automatiquement l'attribut FILE_FILTER basé sur le paramètre OSS_FILE_FILTER dans EXTRA_INFO lors de la création de la table OSS de destination. Si la table de destination existe déjà, le filtre de fichiers existant est utilisé. Le format de `EXTRA_INFO` est `{"oss_file_filter":"field_filter[,field_filter]"}`, où `field_filter` est défini comme suit : `field_filter := field_name[:filter_type] filter_type := bloom`
ON (PARTITIONS OVER num)	Oui	Archive les données lorsque le nombre de partitions est supérieur à `num`.

Gérer une politique

Activez une politique DLM.

ALTER TABLE table_name DLM ENABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

Désactivez une politique DLM.

ALTER TABLE table_name DLM DISABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

Supprimez une politique DLM.

ALTER TABLE table_name DLM DROP POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

Dans ces instructions, table_name correspond au nom de la table, et dlm_policy_name au nom de la politique à gérer. Vous pouvez spécifier plusieurs noms de politiques.

Exécuter une politique

Exécutez toutes les politiques DLM sur toutes les tables du cluster actuel.
```
CALL dbms_dlm.execute_all_dlm_policies();
```
Exécutez les politiques DLM sur une seule table.
```
CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
```
Dans cette instruction, database_name désigne le nom de la base de données contenant la table, et table_name le nom de la table.

La fonctionnalité MySQL event permet d'exécuter des politiques DLM pendant la fenêtre de maintenance de votre cluster. Cette méthode évite d'impacter les performances de la base de données pendant les heures de pointe et permet de déplacer périodiquement les données expirées pour réduire les coûts de stockage. Utilisez la syntaxe suivante pour exécuter une politique DLM avec un événement :

CREATE
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [COMMENT 'comment']
    DO event_body;

schedule: {
  EVERY interval
  [STARTS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_body: {
      CALL dbms_dlm.execute_all_dlm_policies();
    | CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
}

Le tableau suivant décrit les paramètres.

Paramètre	Obligatoire	Description
event_name	Oui	Nom de l'événement.
schedule	Oui	Heure et fréquence d'exécution de l'événement.
comment	Non	Commentaire associé à l'événement.
event_body	Oui	Contenu exécuté par l'événement. Il doit s'agir d'une instruction exécutant une politique DLM. Remarque Si vous utilisez `CALL dbms_dlm.execute_all_dlm_policies()`, l'événement exécute toutes les politiques DLM du cluster. Par conséquent, créez un seul événement de ce type par cluster. Si vous utilisez `CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');`, l'événement exécute toutes les politiques DLM uniquement sur une table spécifique. Créez donc un événement pour chaque table nécessitant un archivage planifié.
interval	Oui	Fréquence d'exécution de l'événement.
timestamp	Oui	Heure de début d'exécution de l'événement.
database_name	Oui	Nom de la base de données.
table_name	Oui	Nom de la table.

Pour plus d'informations sur la fonctionnalité MySQL EVENT, consultez la documentation officielle MySQL pour CREATE EVENT.

Pour des exemples d'utilisation, consultez Exemples d'archivage de données froides vers OSS.

Exemples

Archiver des données vers une table externe

Créer une politique DLM

L'exemple suivant crée une table partitionnée nommée sales qui utilise la colonne order_time comme clé de partitionnement. La table possède une politique INTERVAL et une politique DLM :

Politique INTERVAL : Lorsque les données insérées sortent de la plage de partitions existante, une nouvelle partition est automatiquement créée avec un intervalle de temps d'un an.
Politique DLM : La table est configurée pour conserver uniquement trois partitions. Lorsque le nombre de partitions dépasse trois, la politique DLM se déclenche et effectue l'une des actions suivantes :
- Si la table externe OSS sales_history n'existe pas, une nouvelle table externe OSS nommée sales_history est créée, et les données froides sont archivées dans la table sales_history.
- Si la table externe sales_history existe et que la table sales_history réside sur l'OSS intégré, les données froides sont directement archivées dans la table externe sales_history.

Remarque

Pour créer une table avec un partitionnement INTERVAL RANGE, assurez-vous que tous les prérequis sont remplis. Pour plus d'informations sur INTERVAL, consultez Partitionnement INTERVAL RANGE.

Créez la table sales avec une politique DLM.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' EXTRA_INFO '{"oss_file_filter":"id,name:bloom"}' ON (PARTITIONS OVER 3);

La politique DLM de la table est nommée test_policy. Lorsque le nombre de partitions dépasse trois, la politique archive les données froides de la table source au format CSV vers OSS. La table d'archive résultante est nommée sales_history et est en lecture seule. Si la table d'archive OSS n'existe pas, le système la crée automatiquement et ajoute un OSS_FILE_FILTER aux colonnes id et name.

Les politiques DLM de la table actuelle sont stockées dans la table système mysql.dlm_policies. Interrogez cette table pour afficher les détails des politiques DLM. Pour plus d'informations sur la table mysql.dlm_policies, consultez Description de la structure de la table. Affichez la structure de la table mysql.dlm_policies.

mysql> SELECT * FROM mysql.dlm_policies\G

Exemple de sortie :

*************************** 1. row ***************************
                   Id: 3
         Table_schema: test
           Table_name: sales
          Policy_name: test_policy
          Policy_type: TABLE
         Archive_type: PARTITION COUNT
         Storage_mode: READ ONLY
       Storage_engine: CSV
        Storage_media: OSS
  Storage_schema_name: test
   Storage_table_name: sales_history
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 10300
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: {"oss_file_filter": "id,name:bloom,order_time"}
              Comment: NULL
1 row in set (0.03 sec)

Actuellement, la table sales comporte trois partitions, donc aucune donnée n'est archivée.

Insérez 3 000 lignes de données de test dans la table partitionnée sales. Cela garantit que les données dépassent la plage de partitions définie et déclenche la politique INTERVAL pour créer automatiquement de nouvelles partitions.

DROP PROCEDURE IF EXISTS proc_batch_insert;
delimiter $$
CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
BEGIN
SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
PREPARE stmt from @insert_stmt;
WHILE begin <= end DO
SET @ID1 = begin;
SET @NAME = CONCAT(begin+begin*281313, '@stiven');
SET @TIME = from_days(begin + 737600);
EXECUTE stmt using @ID1, @NAME, @TIME;
SET begin = begin + 1;
END WHILE;
END;
$$
delimiter ;
CALL proc_batch_insert(1, 3000, 'sales');

La politique INTERVAL se déclenche, ajoutant de nouvelles partitions à la table sales. La structure de la table est désormais la suivante :

mysql> SHOW CREATE TABLE sales\G

Exemple de sortie :

*************************** 1. row ***************************       
      Table: sales
Create Table: CREATE TABLE `sales` (  
  `id` int(11) DEFAULT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  `order_time` datetime NOT NULL,  
   PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ 
/*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

Les nouvelles partitions portent le nombre total de partitions à plus de trois. Cela satisfait la condition de la politique DLM, et les données sont maintenant prêtes à être archivées.

Exécuter la politique DLM

Exécutez la politique DLM directement via une instruction SQL, ou planifiez son exécution périodique à l'aide de la fonctionnalité MySQL EVENT. Par exemple, si votre fenêtre de maintenance commence à 01:00 chaque jour, à partir du 11 octobre 2022, créez l'événement suivant pour exécuter la politique DLM quotidiennement à 01:00.
```
CREATE EVENT dlm_system_base_event
       ON SCHEDULE EVERY 1 DAY
    STARTS '2022-10-11 01:00:00'
    do CALL 
dbms_dlm.execute_all_dlm_policies();
```
Après 01:00, cet événement exécute toutes les politiques DLM sur toutes les tables.

Exécutez la commande suivante pour afficher la structure de la table sales :

mysql> SHOW CREATE TABLE sales\G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (  
  `id` int(11) DEFAULT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  `order_time` datetime NOT NULL,  
   PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ 
/*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB, 
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

La table ne comporte plus que trois partitions.

Interrogez la table mysql.dlm_progress pour consulter l'historique d'exécution de la politique DLM. Pour plus d'informations sur la table dlm_progress, consultez Structures des tables. Exécutez la commande suivante pour interroger la table mysql.dlm_progress :

mysql> SELECT * FROM mysql.dlm_progress\G

Exemple de sortie :

*************************** 1. row ***************************
                  Id: 1
        Table_schema: test
          Table_name: sales
         Policy_name: test_policy
         Policy_type: TABLE
      Archive_option: PARTITIONS OVER 3
      Storage_engine: CSV
       Storage_media: OSS
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 0
  Archived_file_info: null
          Start_time: 2024-07-26 17:56:20
            End_time: 2024-07-26 17:56:50
          Extra_info: null
1 row in set (0.00 sec)

Les partitions contenant des données froides peu consultées, notamment p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 et _p20250101000000, ont été archivées dans la table externe OSS.

Exécutez la commande suivante pour afficher la structure de la table externe OSS :

mysql> SHOW CREATE TABLE sales_history\G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales_history
Create Table: CREATE TABLE `sales_history` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ /*!99990 800020224 OSS_FILE_FILTER='id,name:bloom,order_time' */
1 row in set (0.15 sec)

La table est désormais une table CSV utilisant le moteur OSS pour le stockage. Interrogez-la de la même manière qu'une table locale. Les colonnes spécifiées ont été ajoutées à l'OSS_FILE_FILTER. Comme order_time est une clé de partition, un OSS_FILE_FILTER est également créé automatiquement pour celle-ci.

Interrogez séparément les données des tables sales et sales_history.

SELECT COUNT(*) FROM sales;
+----------+
| count(*) |
+----------+
|      984 |
+----------+
1 row in set (0.01 sec)

SELECT COUNT(*) FROM sales_history;
+----------+
| count(*) |
+----------+
|     2016 |
+----------+
1 row in set (0.57 sec)

Le nombre total de lignes est de 3 000, ce qui correspond au nombre de lignes initialement insérées dans la table sales.

Interrogez la table externe OSS à l'aide de l'OSS_FILE_FILTER. Le commutateur OSS_FILE_FILTER doit être activé.

mysql> explain select * from sales_history where id = 9;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
|  1 | SIMPLE      | sales_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2016 |    10.00 | Using where; With pushed engine condition (`test`.`sales_history`.`id` = 9) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
1 row in set, 1 warning (0.59 sec)

mysql>  select * from sales_history where id = 9;
+------+----------------+---------------------+
| id   | name           | order_time          |
+------+----------------+---------------------+
|    9 | 2531826@stiven | 2019-07-04 00:00:00 |
+------+----------------+---------------------+
1 row in set (0.19 sec)

Archiver des partitions vers OSS

Créer une politique DLM

L'exemple suivant crée une table partitionnée nommée sales qui utilise la colonne order_time comme clé de partitionnement. La table possède une politique INTERVAL et une politique DLM :

Politique INTERVAL : Lorsque les données insérées sortent de la plage de partitions existante, une nouvelle partition est automatiquement créée avec un intervalle de temps d'un an.
Politique DLM : La table est configurée pour conserver uniquement trois partitions. Lorsque le nombre de partitions dépasse trois, la politique DLM se déclenche et archive directement les partitions les plus anciennes vers OSS.

Créez la table sales.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);

La politique DLM de la table est nommée policy_part2part. Lorsque le nombre de partitions dépasse trois, les partitions les plus anciennes sont archivées vers OSS.

Consultez la politique DLM dans la table mysql.dlm_policies.

SELECT * FROM mysql.dlm_policies\G

Exemple de sortie :

*************************** 1. row ***************************
                   Id: 2
         Table_schema: test
           Table_name: sales
          Policy_name: policy_part2part
          Policy_type: PARTITION
         Archive_type: PARTITION COUNT
         Storage_mode: READ ONLY
       Storage_engine: CSV
        Storage_media: OSS
  Storage_schema_name: NULL
   Storage_table_name: NULL
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 10300
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: null
              Comment: NULL
1 row in set (0.03 sec)

Utilisez la procédure stockée proc_batch_insert pour insérer des données de test dans la table partitionnée sales. Cette action déclenche la politique INTERVAL, qui crée automatiquement de nouvelles partitions.
```
CALL proc_batch_insert(1, 3000, 'sales');
```
Le résultat suivant indique que les données ont été insérées avec succès :
```
Query OK, 1 row affected, 1 warning (0.99 sec)
```

Exécutez la commande suivante pour afficher la structure de la table sales :

SHOW CREATE TABLE sales \G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

Exécuter la politique DLM

Exécutez la commande suivante pour lancer la politique DLM :
```
CALL dbms_dlm.execute_all_dlm_policies();
```

Interrogez la table mysql.dlm_progress pour consulter l'historique d'exécution DLM.

SELECT * FROM mysql.dlm_progress \G

Exemple de sortie :

*************************** 1. row ***************************
                  Id: 4
        Table_schema: test
          Table_name: sales
         Policy_name: policy_part2part
         Policy_type: PARTITION
      Archive_option: PARTITIONS OVER 3
      Storage_engine: CSV
       Storage_media: OSS
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: null
          Start_time: 2023-09-11 18:04:39
            End_time: 2023-09-11 18:04:40
          Extra_info: null
1 row in set (0.02 sec)

Exécutez la commande suivante pour afficher la structure de la table sales :

SHOW CREATE TABLE sales \G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION='default_oss_server'
/*!99990 800020205 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

La sortie montre que les partitions p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 et _p20250101000000 de la table partitionnée sales sont archivées vers OSS. Seules les trois partitions de données chaudes, _p20260101000000, _p20270101000000 et _p20280101000000, sont conservées dans le moteur InnoDB. La table sales est désormais une table partitionnée hybride. Pour savoir comment interroger les données d'une table partitionnée hybride, consultez Interroger une table partitionnée hybride.

Supprimer des données froides

Créer une politique DLM

L'exemple suivant crée une table partitionnée nommée sales qui utilise la colonne order_time comme clé de partitionnement. La table possède une politique INTERVAL et une politique DLM :

Politique INTERVAL : Lorsque les données insérées sortent de la plage de partitions existante, une nouvelle partition est automatiquement créée avec un intervalle de temps d'un an.
Politique DLM : La table est configurée pour conserver uniquement trois partitions. Lorsque le nombre de partitions dépasse trois, la politique DLM se déclenche pour supprimer les données froides.

Créez la table sales avec une politique DLM.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO NONE ON (PARTITIONS OVER 3);

La politique DLM de la table est nommée test_policy. Elle se déclenche lorsque le nombre de partitions dépasse trois. Lors de son exécution, la politique supprime les données froides.

Exécutez la commande suivante pour interroger la table mysql.dlm_policies :

SELECT * FROM mysql.dlm_policies\G

Exemple de sortie :

*************************** 1. row ***************************
                   Id: 4
         Table_schema: test
           Table_name: sales
          Policy_name: test_policy
          Policy_type: NONE
         Archive_type: PARTITION COUNT
         Storage_mode: NULL
       Storage_engine: NULL
        Storage_media: NULL
  Storage_schema_name: NULL
   Storage_table_name: NULL
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 50000
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: null
              Comment: NULL
1 row in set (0.01 sec)

Utilisez la procédure stockée proc_batch_insert pour insérer des données de test dans la table partitionnée sales. Cette action déclenche la politique INTERVAL, qui crée automatiquement de nouvelles partitions.
```
CALL proc_batch_insert(1, 3000, 'sales');
Query OK, 1 row affected, 1 warning (0.99 sec)
```

Exécutez la commande suivante pour afficher la structure de la table sales :

SHOW CREATE TABLE sales \G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

Exécuter la politique DLM

Exécutez la commande suivante pour lancer directement la politique DLM :
```
CALL dbms_dlm.execute_all_dlm_policies();
```

Pendant l'exécution de la politique DLM, interrogez les données de la table mysql.dlm_progress.

SELECT * FROM mysql.dlm_progress \G

Les résultats dans la table sont les suivants :

*************************** 1. row ***************************
                  Id: 1
        Table_schema: test
          Table_name: sales
         Policy_name: test_policy
         Policy_type: NONE
      Archive_option: PARTITIONS OVER 3
      Storage_engine: NULL
       Storage_media: NULL
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: null
          Start_time: 2023-01-09 17:31:24
            End_time: 2023-01-09 17:31:24
          Extra_info: null
1 row in set (0.03 sec)

Les partitions contenant des données froides peu consultées, notamment p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 et _p20250101000000, ont été supprimées.

La structure de la table sales est désormais la suivante :

SHOW CREATE TABLE sales \G

Exemple de sortie :

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.02 sec)

Gérer les politiques avec ALTER TABLE

Créez une politique DLM à l'aide de l'instruction ALTER TABLE.
```
ALTER TABLE t DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);
```
La politique DLM de la table t est nommée test_policy. Elle se déclenche lorsque le nombre de partitions dépasse trois. Lors de son exécution, cette politique archive les données des partitions les plus anciennes de la table t vers une table OSS nommée sales_history.
Activez la politique DLM test_policy sur la table t.
```
ALTER TABLE t DLM ENABLE POLICY test_policy;
```
Désactivez la politique DLM test_policy sur la table t.
```
ALTER TABLE t DLM DISABLE POLICY test_policy;
```
Supprimez la politique DLM test_policy de la table t.
```
ALTER TABLE t DLM DROP POLICY test_policy;
```

Résoudre les erreurs d'exécution

Des problèmes de configuration peuvent entraîner l'échec de l'exécution des politiques DLM. Les enregistrements d'erreurs sont stockés dans la table mysql.dlm_progress. Exécutez la commande suivante pour consulter les enregistrements d'erreurs :

SELECT * FROM mysql.dlm_progress WHERE Archive_stage = "ARCHIVE_ERROR";

Recherchez les détails de l'erreur dans le champ Extra_info. Après avoir identifié et résolu la cause de l'erreur, supprimez l'enregistrement ou mettez à jour son Archive_stage à ARCHIVE_COMPLETE. Exécutez ensuite la commande call dbms_dlm.execute_all_dlm_policies; pour exécuter manuellement la politique, ou attendez la prochaine exécution planifiée.

Remarque

Pour des raisons de sécurité des données, si un enregistrement d'exécution de politique présente l'état ARCHIVE_ERROR, le planificateur n'exécutera plus la politique automatiquement. Une fois la cause de l'échec confirmée et l'enregistrement mis à jour, la politique reprend son exécution planifiée.