Mettre/archiver les vieilles données sur le côté [Résolu]

[Sergejack]

Bonjour.

J'ai une DB qui évolue pas mal chaque jour.
Il y a dans cette DB beaucoup de données qui n'ont quasiment que pour seule valeur de servir d'historique.
Ainsi je me disais qu'il serait peut-être intéressant pour les performances si je mettais à intervalle ponctuel (tous les jours à minuit par exemple) ces données sur le côté afin de ne conserver dans ma DB que les données nécessaires au fonctionnement en cours et à venir.

Je ne m'y connais pas sur le sujet. J'ignore par exemple si les partitions seraient une piste illusoire à suivre, etc.

Donc, j'aimerais que vous me conseilliez un peu si vous le pouvez.
Merci.

[NicoD.]

Déplacer les enregistrements de la table courante vers la table archive est une bonne idée.

Ceci peut être fait par l'intermédiaire d'un script dans un langage tiers (ex: PHP) ou même par création d'une procédure stockée.

Cependant, avant d'optimiser à fond, combien y a-t-il d'enregistrements dans ta table et combien peux-tu en espérer au maximym sans faire de ménage (dans un scénario raisonnablement pessimiste) ?

[Sergejack]

J'ignore à quel moment j'aurais de gros problèmes de performance.
Mais quotidiennement, on peut estimer que 95% des nouveaux enregistrements peuvent/devraient être archivés et donc ça augmente vite.

[NicoD.]

Si on prend une procédure stockée, voici un exemple de code à exécuter à 0h15.

On ajoutera une colonne booléenne to_be_archived à la table courante afin d'être sûr de supprimer les enregistrements de la table qui ont été copiés dans l'archive.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
 
CREATE PROCEDURE nettoie_table() 
BEGIN
 
  UPDATE table_courante SET to_be_archived = true WHERE date_insertion <= DATE_ADD(CURDATE, INTERVAL -1 SECOND) ;
 
  INSERT INTO table_archive SELECT * FROM table_courante WHERE to_be_archived = true ;
 
  DELETE FROM table_courante WHERE to_be_archived = true
 
END //

Un petit LOCK TABLES pourra même être un peu plus sécurisant si jamais tu n'es pas sûr des processes qui ont accès à la base de données.

[Sergejack]

La seule méthode suggérée est de dupliquer les tables ?

[NicoD.]

Si c'est pour des soucis de performance, c'est le système que j'emploierais effectivement en priorité.

Après tu avais évoqué le partitionnement. C'est également une piste à suivre mais cela dépend de la structure existante de la table et de la manière qu'on a de l'interroger.

Il faut trouver un bon "angle de partitionnement" pour pouvoir séparer les données (Ex : l'heure d'insertion pour des logs). Mais il ne faut pas que cela handicape des requêtes SELECT qui chercheraient des enregistrements sur plusieurs partitionnement (Perfs légèrement dégradées par rapport à l'absence de partitionnement).

[Sergejack]

Pour partitionner à heures fixes, je dois forcément prévoir une colonne rien que pour cet usage (genre "SET partition = 1" à 00:00) ?
Où est-ce que je peux garder la structure de mes tables intact et choisir selon mes propres règles/envies sur quelle partition va quelle ligne ?

[NicoD.]

En fait, tu définis dans la définition de ta table les partitions que tu veux utiliser.

J'ai tiré du tutoriel http://krierjon.developpez.com/mysql/partitionnement/ l'exemple ci-après qui pourrait t'aider à construire tes partitions par date.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE TABLE transac_part
(
   id INT UNSIGNED NOT NULL,
   montant INT UNSIGNED NOT NULL,
   jour DATE NOT NULL,
   codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
) PARTITION BY RANGE(YEAR(jour))
(
   PARTITION p1 VALUES LESS THAN(1997),
   PARTITION p2 VALUES LESS THAN(1998),
   PARTITION p3 VALUES LESS THAN(1999),
   PARTITION p4 VALUES LESS THAN(2000),
   PARTITION p5 VALUES LESS THAN(2001),
   PARTITION p6 VALUES LESS THAN(2002),
   PARTITION p7 VALUES LESS THAN(2003),
   PARTITION p8 VALUES LESS THAN(2004),
   PARTITION p9 VALUES LESS THAN(2005),
   PARTITION p10 VALUES LESS THAN(2006),
   PARTITION p11 VALUES LESS THAN MAXVALUE
);

[Sergejack]

Je me suis pas mal renseigné sur les différentes possibilités évoquées.

Mais je n'ai pas encore trouvé de méthode pour partitionner une table en fonction d'une autre.
J'ai peur que ce ne soit pas faisable mais peut-être l'est-ce.

Pr exemple si j'ai ces table
PARENT : champ1, champ2, ..., champsn, estArchive
ENFANT : cleParent, champ1, champ2, ..., champsn.
AUTREENFANT : cleParent, champ1, champ2, ..., champsn.

Et que je partionne PARENT sur estArchive.
Comment je fais pour que les lignes respectives de ENFANT et AUTREENFANT soient dans la même partition que PARENT ?
En effet, si des lignes de la table PARENT ne me sont plus fréquement utiles, il en va de même pour les lignes liés de ENFANT et AUTREENFANT.

[NicoD.]

Dans l'état actuel de mes connaissances sur le sujet, un partitionnement MySQL ne peut concerner qu'une table à la fois.

A moins également de mettre en place un champ estArchive sur les table ENFANT et AUTREENFANT puis les partitionner, je ne vois pas d'autre solution.

[Sergejack]

Je vais sans doute utiliser deux bases alors.

Du coup, dommage aussi qu'il n'y ait pas de trigger sur les ddl en MySql...

Merci Nico