Bonjour,

J'ai une problème de gestion avec mes bases de données, voici le topo :

J'ai deux serveurs :

- ServeurA avec la TableA qui possède 7000 lignes.

- ServeurB

La TableA augmente de 2 lignes par jours et est régulièrement modifier aléatoirement (exemple : modifier la ligne 12, puis 15min après la ligne 300 ...).

Mon problème : je dois avoir une copie de la TableA du ServeurA sur le ServeurB. Cette duplication doit à chaque fois être la copie exacte de l'autre. Contrainte : je ne peux pas faire de Federed.

Ma première solution est donc de delete/create toutes les 15mins : solution la plus simple et la plus rapide.

Maintenant je m'interroge sur les performances de celle-ci qui est très énergivore. Avez d'autres idées ? Si je fais un update, je vais devoir analyser chaque ligne pour savoir si elles ont été modifier ? 

Bref, il s'agit d'un problème d’optimisation ! Merci de votre aide.

Bonjour,

Quel SGBD utilises-tu ?

Seulement ces deux tables doivent être synchronisées ? Pas de réplication maître/esclave envisageable ?

Peux-tu nous en dire plus sur le contexte applicatif ?

Bonjour et merci de ta réponse.

J'utilise MYSQL, mais je n'ai aucune possibilité de changement (je ne maîtrise pas le serveur).

Les seuls outils que je puisse utiliser : SQL, PHP et créer un CRON pour lancer le script.  

Donc ça limite pas mal les possibilités d'actions. 

Effectivement seulement ces deux tables doivent être synchronisées. L'autre point, c'est que la TableB est légèrement modifier (j'ajoute une clé primaire sur un champs existant, d'ou l’impossibilité de faire un federated). Donc ce n'est pas vraiment une copie parfaite si on prend en compte la primary key que j'ajoute.

-
Edité par PhilipTruman 13 mars 2018 à 17:01:26

PhilipTruman a écrit:

je ne maîtrise pas le serveur

En effet, cela limite vraiment beaucoup les possibilités d'action

Tu n'as aucun moyen d'identifier ce qui a été modifié dans la tableA ? Date de modification, date de création, etc.

Le mieux est une réplication maître/esclave, c'est sûr.

Autrement, il n'y a pas de miracle, faire un export / ré-import complet de la table est la seule solution fiable.

Même modifier un programme pour qu'il fasse une double insertion (une sur chaque DB) n'est pas une bonne idée: en cas d'erreur d'insertion / modification sur un des serveur, on va avoir une désynchronisation.

Or récupérer d'une telle désynchro nécéssite... un export / réimport de la table. Retour au point de départ.

Autrement, il faut voir plus en amont: Pourquoi as-tu besoin de répliquer ces données ?

Merci pour vos réponses !

Pas d'infos sur les modifications malheureusement ...

Je pense que la solution delete/create reste ma meilleure option ...

Le besoin de répliquer ces données vient d'une demande métier, j'ai pas spécialement de marche de main d'oeuvre : en gros j'ai accès au ServeurA qu'en lecture, mais j'ai besoin de manipuler les données. Donc la seule solution est de copier la table en question sur mon serveur (le ServeurB).

Imaginons tout de même que la TableA contienne une colonne avec un ID qui permettrait d'identifier clairement (et de manière fiable) mes occurrences. 

La best solution, serait de : update if ID présent dans la TableB ou insert if ID pas présent ? Mais ça m'obligerai à parcourir toutes les lignes de la TableA tout de même ?

Comment ça, manipuler les données ? Tu veux dire les modifier ?

Exactement, j'ai besoin de les manipuler chez moi, sur mon ServeurB. (Lecture, modification, update ... bref tout ce que je ne peux pas faire sur le serveur en lecture seule)

Ce que je pige pas, c'est que tu vas perdre toutes tes modifs au moment où tu re-feras ton export / ré-import.

Et si tu ne veux pas perdre tes modifs, alors tu devras stocker tes données dans une autre table qui elle ne sera pas complètement effacée à chaque ré-import. Mais si du coup tu as une table qui te permet de faire ca, ca veut dire que tu n'auras pas besoin de modif ta TableB, et donc en soit elle ne sert plus à rien.

Désolé, c'est vrai que c'est flou.

J'ai pas besoin de modifier la TableB. La TableB doit être la copie parfaite de la TableA. Je ne fais aucune modification dans la TableB (je fais des modification, mais dans une autre table justement pour éviter le problème que tu as soulevé).

Du coup tu peux me demander : Pourquoi tu as besoin d'avoir une copie de la TableA dans la TableB si tu ne fais pas de changement ? 

Réponse : Pour le problème de clé primaire, ma TableB est identique à ma table TableA sauf que j'y ajoute une clé primaire. 

Bref c'est vraiment bordélique je vous l'accorde, mais j'ai pas le choix et cette question de "logique" n'est pas mon problème principal

Pour résumé : je dois trouver un moyen performant de copier régulièrement ma TableA dans ma TableB, en sachant que j'ai comme outil : PHP, SQL et un serveur LINUX.

Merci de vos réponses.

-
Edité par PhilipTruman 14 mars 2018 à 10:45:17

Et pourquoi ne pas travailler tout bêtement directement avec les données de la table A ?

Avec PHP tu fais un SELECT des données de la table A avec tous les "calculs" / modification que tu dois y apporter, y compris une pseudo clé primaire ...

Dans ton code PHP tu réalises les opérations que tu souhaites et tu affiches le résultat et ce, chaque fois que tu en as besoin, sans chercher à "copier" les données quelques part ...

Je rejoins Benzouye, j'arrive pas à comprendre en quoi cette table est absolument indispensable.

Certes, tu aurais une clef primaire. J'ai envie de dire : et alors ?

Elle t'apporte quoi la clef primaire en l’occurrence ?

Une contrainte d'unicité ? Sauf que vu que tu changes pas les données, ça ne change rien.

Une liaison avec une FK d'une autre table ? Sauf que si tu purges et recrées les données sans arrêt, c'est inexploitable. 

-
Edité par Tiffado 14 mars 2018 à 11:15:59

Effectivement, l'idée de faire les calculs au niveau de petits scripts PHP peut être une solution.

Mais l'ajout de clé primaire c'était pour gagner en performance, et je gagne facilement plusieurs secondes.

Select sur TableA sans clé primaire = 5 secondes

Select sur TableB en ajoutant clé primaire = 1 secondes

C'est surtout pour ça que j'avais pour idée de copier les données de la TableA dans une TableB qui contiendrait une clé primaire.

Si je fais des select en PHP sur la TableA je ne gagne pas en performance malheureusement.

Donc clé primaire = gain de performance

-
Edité par PhilipTruman 14 mars 2018 à 11:30:04

Tu n'as vraiment aucun pouvoir sur la table A ?

Genre demander à l'entité qui la gère de rajouter un index, c'est pas plus simple ?

PhilipTruman a écrit:

Select sur TableA sans clé primaire = 5 secondes

Select sur TableB en ajoutant clé primaire = 1 secondes

Alors là ! Mystère ... Je ne vois pas d'explication à cela ...

Les requêtes ne sont vraisemblablement pas les mêmes, l'environnement de travail également ... Tu ne peux pas comparer des carottes et des choux ...

Surtout que qu'importe la requête, 5 secondes pour 7000 lignes, ca craint un max.

@Benzouye : les carottes, c'est meilleur que les choux

Benzouye a écrit:

PhilipTruman a écrit:

Select sur TableA sans clé primaire = 5 secondes

Select sur TableB en ajoutant clé primaire = 1 secondes

Alors là ! Mystère ... Je ne vois pas d'explication à cela ...

Les requêtes ne sont vraisemblablement pas les mêmes, l'environnement de travail également ... Tu ne peux pas comparer des carottes et des choux ...

Pourquoi tu ne vois pas d'explication à cela ? Sur deux mêmes requêtes identiques, celle avec l'index est plus rapide que l'autre ? C'est pas une légende quand même Le moteur se base sur les index non ?

L'entité qui gère ... est hermétique au dialogue !

Quoi qu'il en soit, j'ai l'impression qu'on a épuiser les idées. 

Donc

1 = soit calcul directement dans les scripts PHP, pas de TableB

2 = soit delete/create à chaque fois

Merci à vous pour votre patience en tout cas

-
Edité par PhilipTruman 14 mars 2018 à 11:56:05

PhilipTruman a écrit:

Sur deux mêmes requêtes identiques, celle avec l'index est plus rapide que l'autre ?

Si une requête utilise un index et pas l'autre c'est que les requêtes sont différentes ...

Imaginons cette requête :

SELECT *
FROM tableA
WHERE colonne1 = 'toto'

Si la table B est identique en tout point à la table A sauf l'ajout d'une colonne clé primaire indexée, alors :

SELECT *
FROM tableB
WHERE colonne1 = 'toto'

Cette requête n'utilisera pas d'index puisque n'en a pas besoin ...

L'index sera utilisé sur la table B si il est impliqué dans la requête ...

PhilipTruman a écrit:

L'entité qui gère ... est hermétique au dialogue !

Je ne saisi pas ...

PhilipTruman a écrit:

Donc

1 = soit calcul directement dans les scripts PHP, pas de TableB

2 = soit delete/create à chaque fois

On est d'accord sur ce point en tout cas ...

-
Edité par Benzouye 14 mars 2018 à 13:37:59

J'ai fais le test sur mon ServeurB (oublions la TableA et le ServeurA)

ServeurB, TableB sans clé primaire et TableB-bis avec clé primaire

Quand je fais :

Select * from TableB = 5 secondes

Select * from TableB-bis = 0,1 secondes

Comment tu expliques cela, si ce n'est que la clé primaire permet au moteur d'accélérer le traitement ? C'est ce qu'on m'enseignait en école d'ingé je suis pas fou 

(Entité hermétique, je répondais à Tiffado)

Je viens de faire le test sur une table de 100k lignes ... Le plan d'exécution montre que l'index n'est pas utilisé sur une requête ne l'impliquant pas ... et les temps de réponse avec et sans index sont sensiblement identiques ... je ne comprends pas ...

Peux-tu poster le résultat de :

EXPLAIN Select * from TableB;

EXPLAIN Select * from TableB-bis;

PhilipTruman a écrit:

Comment tu expliques cela, si ce n'est que la clé primaire permet au moteur d'accélérer le traitement ? C'est ce qu'on m'enseignait en école d'ingé je suis pas fou 

(Entité hermétique, je répondais à Tiffado)

Je crois que sur le fond, c'est simplement que la présence de la clef primaire force la création d'un index sur cette clef. La seule différence, c'est donc la présence d'un index en plus.

De plus, ta table de base, 5 secondes pour 7000 lignes ? Ça veut clairement dire qu'il n'y a aucun index, sur aucune colonne, donc ça me choque pas que remonter les données soit plus long car ton SGBD doit s'y prendre comme un manche pour tout remonter.

Après, ça commence à dépasser mes compétences x)

Heu... Y a pas un truc qui vous choque ? Select sur TableA sans clé primaire

Une table sans clef primaire, ça ne choque vraiment personne ?

Pour revenir à la question des performances, ça n'a aucun sens. A partir du moment où l'on fait un "SELECT * FROM xxx" sans aucun WHERE, il y aura un scan complet de la table. Exception de l'utilisation d'un index couvrant, mais on remplace juste un full scan de la table par un full scan de l'index, ce qui revient au même. Et de toute façon MySQL ne gère pas les index couvrant...

Pour simplifier, comme il n'y a pas de recherche (pas de clause WHERE), il n'y pas d’intérêt à chercher dans un index.

La perte de performance (si les deux tables sont absolument identiques) doit s'expliquer par d'autres critères :

- Quelle est la quantité de RAM utilisée / disponible ?

- Quelle est l'utilisation du disque ? vitesse, volume disponible et utilisé...

- Fragmentation du fichier / de la table

Bah, sur le fond ca me choque, mais vu les projets que j'ai déjà pu visiter, ce sot des choses qui arrivent.

Exemple : un bon gros MDD de 1500 tables sans la moindre FK. Alors il en faut plus pour me choquer.

Merci sebajuste ... Je commençais vraiment à me poser des questions ...

Sebajuste a écrit:

Une table sans clef primaire, ça ne choque vraiment personne ?

Vu qu'il n'a aucun moyen d'agir sur cette table je n'avais même pas soulevé

Sebajuste a écrit:

Heu... Y a pas un truc qui vous choque ? Select sur TableA sans clé primaire

Une table sans clef primaire, ça ne choque vraiment personne ?

Si tu savais ce qu'on trouve en entreprise ... et je parle de boite top 10 .... mon problème concerne d'ailleurs une banque française ...

Enfin bref, visiblement vous avez l'air d'avoir raison - je vais pas polémiquer pendant des heures. Sachez tout de même, que sur deux tables identiques - sur un même serveur - sur plusieurs tests - et que seule le clé primaire change, le select * from a été plus rapide pour la table avec clé primaire. Faites ce que vous voulez de cette info, de toute façon je suis pas payé pour comprendre ce genre de truc  

Merci à vous 

PhilipTruman a écrit:

Faites ce que vous voulez de cette info, de toute façon je suis pas payé pour comprendre ce genre de truc  

J'ai l'impression que tu le prend mal, et ça ne devrait pas être le cas. Je ne remet en aucun cas en cause tes observations ! Mais il faut bien comprendre que pour un même effets, il peut y avoir plusieurs causes. Et en l'occurence, je pense sincérement que tu te trompes de cause.

Car ce n'est pas l'ajout d'une clef de tri (car une clef primaire cré automatiquement un index) qui peut accelerer une requête. Si tu dois lire l'intégralité d'un roman, à quoi peu bien te servir de faire uen recherche dans le sommaire ?  Puisque tu dois tout lire de toute façon.

D'expérience, (car j'ai déjà été confronté à ce genre de problème) il s'agit d'un problème de fragmentation du fichier. Ce qui accèlère la requête n'est donc pas la création d'un index sur la nouvelle table, mais simplement la création d'une nouvelle table identique à la première.

Car en créant une nouelle table, et en la remplissant (peu importe le moyen, que ce soit par un dump, ou par un CREATE TABLE suivi de tous les INSERT) un nouveau ficher sera créé sans fragmentation.

Il faut bien comprendre qu'il y a deux type de fragmentation dans une base de données :

- la fragmentation "naturelle" d'un fichier, issue du système de fichier (FAT, NTFS, ext...)

- la fragmentation de page, qui peuvent intervenir quand on fait des UPDATE / DELETE. Ce qu'il faut savoir, c'est qu'un SGBD organise ses données par page (MySQL avec InnoDB utilise des page de 16KB). Faire un DELETE laisse donc des trous dans une page. Et un UPDATE aussi ! Car avec un système de transaction (et la possibilité de revenir en arrière), un UPDATE réalise en fait un nouvel enregistrement, et invalide l'ancien (pour le rendre indisponible) mais sans le supprimer !

Et pour finir, MySQL ne dispose pas d'outil pour faire de la défragementation au fil de l'eau, pour réordonner / récupérer l'espace inutilisé dans les page (et c'est là que je place mon "Utilise PostgreSQL à la place de MySQL", qui lui a des outils d'auto vacuum xD )

Pour défragmenter MySQL, il faut faire un dump de la table, détuire l'originale et la recréer. C'est exactement ce que tu fais, et comme par magie, ça accélère la lecture.

La création d'un index n'a je pense, rien à voir.

Je le prends pas mal, au contraire ! Je précise juste mes observations car c'est loin d'être mon domaine de compétence, et vous semblez (j'ai toujours eu du mal avec l'affirmatif ^^) plus compétent que moi sur ce sujet (j'aurais pas poser ma question ici autrement)

En tout cas, merci pour ton explication sur la fragmentation : ça pourrait potentiellement venir de là effectivement !

Merci encore pour vos interventions qui m'ont été fortes utiles