accélérer la comparaison de tables

Bonjour,


j 'ai développé un pogramme en VB qui fait un back up quotidien de certaines bases sur plusieurs serveurs.
Pour ce faire je dois comparer le contenu des tables que je vais répliquer avec celui des tables "back upés" afin de voir si les tables ont été modifiées durant la journée.
Si c est le cas, alors le back up commence.

tout d abord je decoupe ma table en "sections" d'enreg

je fais donc un

Code:

---

SELECT INTO MonFichier FROM ... where enreg < X and enreg > X

---

sur le serveur source et idem sur le serveur cible.

ensuite je calcule le hash de ces fichiers. si ils sont identiques, je passe à la section suivante. Sinon cette section d enregistrement sera back upée.

Mon programme marche correctement, mais la comparaison des données est parfois lente (surtout pour les tables de plusieurs millions d enreg...)

ma question:
connaissez vous une méthode plus rapide pour comparer et isolé les enregistrements?????


par avance merci.

Qu'est-ce que tu penses de ça? :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT matableA.* INTO fichier.txt FROM matableA LEFT OUTER JOIN matableB ON (matableA.id=matableB.id) WHERE matableA.champ1 != matableB.champ1 OR matableA.champ2 != matableB.champ2 OR ...   LIMIT 0, 100

---

Ici, on ne relève que les enregistrements qui ont changé et on fait un découpage de 100 en 100 (condition du LIMIT)

est ce que ca marche si mes bases sont sur 2 serveurs differents??

Dans 2 bases différentes, c'est jouable (tu fais mabaseA.matable et mabaseB.matable), mais sur 2 serveurs différents, ça me parait chaud.

Après, il doit y avoir des méthodes de réplication de base (bases maître/esclave), mais il faut que je me renseigne (quelqu'un d'autre aura peut-être la solution).

Edit: Visiblement, il faut s'en référer au chapitre 6 du manuel de MySQL (http://dev.mysql.com/doc/refman/5.0/en/replication.html). Je pense que ce sera beaucoup plus propre que de faire des scripts à la main.

j ai deja etudié la possibilité de la méthode maitre/esclave, mais elle a été écarté par mon boss...

Citation:

---

Envoyé par mick84m

j ai deja etudié la possibilité de la méthode maitre/esclave, mais elle a été écarté par mon boss...

---

Pourquoi?

car elle est trop compliquée a gérer parfaitement car il y a beaucoup de serveurs.
de plus il est délicat de vérifier l avancement des réplications.

Parce que tu trouves que de gérer à la main la synchronisation et les accès concurrents en section critique c'est plus simple?

Ah et puis j'oublais, il faut considérer 3 cas:
- les lignes modifées
- les lignes ajoutées
- les lignes supprimées

Pour l'instant, on ne s'occuppe que des lignes modifiées et ajoutées. Et puis de toute façon, vu que c'est sur deux serveurs différents, pour faire une jointure, ça me parait difficile...

je trouve que oui, car je sait exactementce qui se passe, où ca se passe et quand ca se passe. j'ai un controle total des replications.
de plus, les réplications ne se font pas toujours dans le meme sens selon nos besoins, donc pas de "maitres" fixes, de meme pour les "esclaves".
je gère tres bien les enreg ajoutés, modifiés et supprimés. le probleme est que ma methode est parfois lente.

T'as pensé à une synchronisation avec des horloges logiques?

- Tu créés une table pour stocker une horloge logique (un entier).

- Dans tes tables, tu ajoutes un champs qui correspond à une horloge logique (ou alors tu fais une table séparée, l'important étant de savoir quelles lignes de quelle table ont été modifiées depuis la dernière horloge logique). A chaque insertion/modification/suppression, tu mets également à jour la valeur de l'horloge à laquelle la modif a été effectuée.

- Au moment du backup entre 2 serveurs, tu sors la liste de lignes qui ont été insérées/modifiées/supprimées depuis la dernière horloge logique.

- Une fois le back up effectué, tu mets à jour l'horloge logique des deux serveurs (nouvelleHorlogeLogique := MAX(HL1, HL2)+1 ).

Bon, ce n'est qu'une idée mais ça pourrait te permettre de simplifier la synchronisation et de pouvoir redéfinir facilement les maîtres et esclaves en fonction des besoins.

Après, il faut creuser un petit peu du coté de l'algorithmique distribuée.

Petite précision (j'ai peut-être pas été clair): sur chaque serveur, il faut que tu aies une table avec les horloges logiques des autres serveurs (pour savoir à quand remonte la dernière synchro) :

Code:

---

1 2 3 4 5 6 7 8 9

Serveur | HL ----------------- Fry | 56 Leila | 58 Bender | 62 Hermes | 60 Farnsworth | 48 Amy | 62 Zoidberg | 57

---

Lors de la synchro entre Fry et Bender, tu compares leurs horloges logiques pour savoir qui est le plus à jour.

je ne connaissait pas cette methode de table avec horloge logique.
ca a l air interessant, j vais me renseigner là dessus.

merci pcaboch :wink:


Si quelqu'un a d autre idées, cela m interesse aussi...

J'avais un peu d'algorithmique distribuée au programme de dernière année, mais j'ai pas eu l'occasion de mettre en pratique. J'ai un peu adapté à ton problème.

Concernant ton problème, j'ai poussé la reflexion un peu plus loin:

Pour la suite, nous appelerons "hl" la table contenant les horloges logiques pour cahque serveur. On a une table "hl" sur chaque serveur.

Imaginons que l'on ait une table "table", avec comme identifiant un champ "id". On créé une table "hl_table" pour contenir les horloges logiques de modification de ligne:

Code:

---

1 2 3 4 5

CREATE TABLE hl_table ( id UNSIGNED INT NOT NULL, hl UNSIGNED INT NOT NULL, # Horloge logique PRIMARY KEY(id) ) ;

---

A chaque modification/ajout d'une ligne de "table", on modifie l'enregistrement correspondant dans "hl_table". En cas de suppression de ligne, on supprime l'enregistrement de "table" et on met à jour "hl_table" (pas de suppression dans hl_table).

***

Maintenant, imaginons que "Bender" veuille enregistrer ses modifications sur "Fry". La table "hl" de Bender nous indique que la dernière synchro avec Fry remonte à l'horloge logique 56. On sort la liste des lignes modifiées depuis cette l'horloge logique.

Sur "Bender", on fait:

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT hl_table.id, table.champ1, ... INTO outfile.txt   FROM hl INNER JOIN hl_table ON (hl_table.hl > hl.hl)   LEFT OUTER JOIN table ON (hl_table.id=table.id)   WHERE (hl.serveur = "Fry")

---

Logiquement, avec cette requête, les lignes qui ont été supprimées devraient avoir leurs champs à NULL (à cause du LEFT OUTER JOIN).

Ensuite, on met à jour les horloges logiques de Fry et Bender, qui est égale à MAX(hlFry, hlBender) + 1 = 62+1 = 63.

***

Maintenant, la plus petite horloge logique est 48. Il est donc inutile de garder la liste des lignes qui ont été modifiées avant cette date. Donc régulièrement, on fait:

Code:

---

1 2 3 4 5

SELECT @minHL:=MIN(hl) from hl ;   DELETE FROM hl_table WHERE hl<@minHL

---

***
Comme tu auras pu le remarquer, toutes les tables servant à la gestion d'horloge logique ont leur nom commençant par "hl", ce qui permet de mieux les retrouver.

***
Après, il reste un autre problème: quand les 2 serveurs modifient les mêmes données chacun de leur côté, laquelle prendre? Mais ça, ça dépend entièrement de ton problème (dont on n'a pas connaissance).

Qu'est-ce que tu penses de tout cela?

merci beaucoup pour cette explication, je comprend mieux ce que tu appelles horloge logique !!

c est tres interessant mais je ne sais pas si il va m etre possible de le mettre en place...car en faite chacune de mes bases de données correspond à une application client. nous avons plus de 75 bases...je ne sais pas si il va etre possible d integrer cette methode dans tous nos projets clients.

en tout cas cette methode métire vraiment d etre étudier. je vais faire quelques tests et voir si c est envisageable.


MERCI !! 8)

Ok, tiens nous au courant.

Citation:

---

Envoyé par mick84m

c est tres interessant mais je ne sais pas si il va m etre possible de le mettre en place...car en faite chacune de mes bases de données correspond à une application client. nous avons plus de 75 bases...je ne sais pas si il va etre possible d integrer cette methode dans tous nos projets clients.

---

C'est vrai que c'est un énorme problème: à chaque fois qu'une valeur dans une table est modifiée, il faut aussi modifier l'horloge logique correspondante. Cela implique une modification de l'appli qui utilise la base de données, et ce pour tes 75 clients. Bref, c'est assez ingérable.

Il m'est venu une idée pour résoudre ce problème sans toucher à l'application: si tu es en MySQL 5.x, alors tu peux utiliser des triggers:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

DELIMITER |   CREATE TRIGGER table__before_insert BEFORE INSERT ON table FOR EACH ROW BEGIN SELECT @hl:=hl FROM hl WHERE serveur="self" ; REPLACE INTO hl_table VALUES (NEW.id, @hl) ; END |   #Autres triggers (BEFORE UPDATE, BEFORE DELETE) ...   DELIMITER ;

---

Dans la table "hl", on identifie le serveur sur lequel on est par la valeur "self" (comme ça, ça évite d'adapter le script pour chaque machine).

Le REPLACE marche de la façon suivante:
- si la ligne existe, elle est remplaçée
- si la ligne n'existe pas, REPLACE se comporte exactement comme INSERT
(Cela évite de tester l'existance de la ligne pour savoir si on fait un INSERT ou un UPDATE)

Ce qui me chiffone, c'est qu'on récupère l'horloge logique courante à chaque insertion (problème de perfs). Peut-être qu'on pourrait mettre celle-ci directement dans une variable globale (à tester...).

***
Maintenant, le problème, c'est qu'il faut passer en MySQL 5 (frayeurs garanties!).

En guise de compensation, après, tu pourras être sûr que toutes tes applications clients tournent sur du MySQL 5 (ce qui évite de se poser la question: "Pourquoi ça tourne sur ma version de developpement mais ça ne marche pas sur le client? Merde, un problème de version! Comment le résoudre maintenant?").

Voilà, j'essaye de trouver des solutions simples à ton problème (ou au moins de donner des idées et d'échanger des points de vue).