Discussion :

[GyZmoO]

Bonjour à tous !

Je me tourne vers vous pour quelques avis et/où retours d'exp.

Voici notre problématique, nous possédons une appli Java/J2EE qui attaque une BD MySQL (full table innoDB).

Sur cette BD, se trouve une table qui peut attendre les 100 millions d'enregistrement (voire plus). Cette table est attaquée la plupart du temps en écriture (70% du temps) et le reste en lecture (en fait c'est une table qui stocke les résultats de notre application).

Une de nos obligations est de fournir au client graphique de notre appli la possibilité de purger/archive/restaurer certaines lignes de cette table. (Ces actions se font selon une plage de temps précise, par exemple : je veux détruire tous les résultats entre les dates D1 & D2).

En résumé le client peut :

- Détruire une partie des résultats sans faire de sauvegarde
- Détruire une partie des résultats en faisant une sauvegarde (sous forme de dump réalisé avec mysqldump)
- Restaurer une partie des résultats grâce à une sauvegarde faite précédemment avec mysqldump.

Le problème étant que ces opérations (surtout la destruction) prennent énormément de temps.

- Nous avions donc mis en place un index sur la colonne des dates : ça a améliorait les perfos mais bon pas assez au goût du client

- Nous avons essayé une autre façon de faire, à savoir récupérer les identifiants primaires des résultats en fonction des dates saisies par le client (comme la colonne des primary key est auto incrémentée, on prend les 2 bornes des dates, on récupère les primary id correspondants, et on fait un DELETE FROM ... WHERE PRIMARY_KEY BETWEEN V1 & V2) : toujours pas top.

- Donc, nouvelle piste, et c'est là que j'ai besoin de vos conseils, nous pensions réalisé un partitionnement horizontal (partition en fonction de cette fameuse colonne de date).

Cependant, nous avons pu constater que MySQL souffrait d'une limitation assez pénible, à savoir qu'un table partitionnée ne supporte pas les foreign keys, et comme par hasard, cette table en question en possède un certain nombre.

J'ai pu lire des articles sur le sujet qui indiquent des contournements, comme par exemple, placer des triggers pré-insertion pour vérifier les contraintes de foreign keys...

- Pensez-vous que cela vaille le coup d'établir un partitionnement horizontal sur cette table, sachant qu'il va falloir mettre en place ce système de triggers pour vérifier les contraintes de keys?

- Sinon, avez-vous des tuyaux concernant la suppression massive de lignes sur une table InnoDB (je sais que, InnoDB n'est pas forcément prévu pour ça mais bon, la structure était déjà en place que nous n'étions pas nés...) ?

Merci pour vos futurs conseils

[SQLpro]

Ce n'est certainement pas avec MySQL que vous améliorerer ce genre de chose, sauf à augmenter drastiquement les ressources du serveur notamment en multipliant le nombre de disques physique et en choisissant un RAID approprié.
En effet, MySQL ne sait pas gérer finement les espaces de stockage et son partitionnement est un leurre qui provoque généralement plus de dégâts qu'autre choses (complexification des plans de requêtes, absence de contraintes...).
Vous êtes sans doute aux limite de MySQL et AMHA il serait souhaitable de changer pour un SGBDR plus costaud, capable de bien gérer les espaces de stockage et le journal de transaction.
Voir les articles que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p8...plexes-dans-l/
http://blog.developpez.com/sqlpro/p8...t-le-stocakge/

A +

[GyZmoO]

Bonjour et merci pour votre réponse.

Je sais bien que MySQL n'est pas forcément le SGBD le plus adapté pour faire des suppressions massives (surtout avec des tables InnoDB), malheureusement, il nous est impossible de changer de SGBD ...

Donc nous cherchons avant tout, à "limiter la casse" :/

J'ai lu les 2 articles, et celui qui décrit comment faire une suppression logique à travers des vues me semble pertinent.

Nous allons donc tenter cette approche... Le fait de faire la suppression réelle des lignes aux heures creuses semble très approprié dans notre cas.

[StephaneC.]

Bonjour,

Tu peux essayer d'utiliser Maatkit avec mk-archiver qui est spécialement fait pour répondre à ce genre de problème.

Tu verras dans la partie Description de l'outil un lien extrêmement intéressant sur des stratégies possibles d'archivage qui devraient t'ouvrir des horizons , et ce d'autant plus qu'apparemment tu te trouves dans le cas le plus favorable, qui est d'avoir des plages où ta base est inactive ou peu active.

En tout cas, si ta stratégie est bonne, si ton modèle de données est bon et si tes requêtes sont bonnes, l'opération est largement réalisable, même avec MySQL comme diraient certains

Stéphane

[GyZmoO]

Bonjour.

Je viens de réaliser qu'il y'avait une nouvelle réponse !!

Merci pour ce retour

Je vais enquêter et je vous tiens au courant