Dupliquer une base de données MySQL

Discussion dans 'Administration d'un site Web' créé par benjiman, 6 Septembre 2008.

  1. benjiman
    benjiman WRInaute discret
    Inscrit:
    9 Octobre 2005
    Messages:
    219
    J'aime reçus:
    0
    Bonjour à tous !

    J'ai à mon actif une base de données de plus de 25 Go et qui ne cesse de croitre ...
    Sauf qu'aujourd'hui, toute requête légèrement complexe sur une table de 8 Go demande un fou de chargement.
    Bien que tous les index soient corrects (C'est la commande Explain qui le dit :lol: ), il en reste que tout SELECT trop long bloque la table (Lock Table)... Normal me direz-vous ?
    Sauf que lorsqu'une requête (mise à jour Crontab) met 400 secondes (LEFT JOIN sur 9 très grosses tables et une réponse de 80 000 résultats), c'est 400 secondes de coupure pour les visiteurs !

    C'est très génant !

    Par conséquent, j'ai essayé d'analyser la situation et j'ai creusé ... jusqu'à trouver une solution : créer un petit script BASH me permettant de copier les tables que j'ai besoin en une table temporaire en passant directement par les fichiers (MYD, MYI et FRM) pour une copie de plusieurs Gigas en moins de 10 minutes. (beaucoup plus long par CREATE TABLE TEMPORARY)

    Sauf que ce système est assez contraignant !
    Certes, ca fonctionne à merveille ... les mises à jour sont beaucoup plus rapide ... sauf que c'est très lourd !
    Il faut à chaque fois copier les tables, avec une réparation de plusieurs heures à chaque fois car elles sont corrompus ...

    Bref, c'est un système qui fonctionne bien, mais qui est TROP lourd ...

    D'où ma question : connaissez-vous un système de duplication de tables ?

    Voici le principe :
    La base A est la principale. Les visiteurs lit tout dessus les "petites données".
    La base B est la secondaire. Une réplique parfaite de la base A est réalisée en temps réel, sauf que toutes les mises à jour se feront par l'intermédiaire de cette base B pour ne pas bloquer la base A.
    Par exemple :
    Je rajoute une ligne dans la table X de la base A. Il faut que la même ligne se rajoute à la table X de la base B.

    En fait, c'est un peu un système de RAID ... mais je ne connais pas bien son principe ...
    Quelqu'un aurait-il l'amabilité de m'expliquer ?

    Je vous remercie sincèrement de l'attention que vous portez à mon message ainsi qu'à toutes les réponses qui pourront suivre.

    PS : Merci de ne pas me dire qu'il faut revoir les requêtes de mise à jour ... je l'ai déjà fait et comme je l'ai expliqué au dessus : les EXPLAIN me donne de très bon résultats !
     
  2. Bool
    Bool WRInaute passionné
    Inscrit:
    26 Février 2004
    Messages:
    1 543
    J'aime reçus:
    0
    Hello,

    tu indiques que les EXPLAIN t'indiquent de "très bons résultats", mais pour que la table soit verrouillée c'est qu'il doit rester des select qui ont recours à des tables temporaires et/ou filesort, non ?

    Pour ton système de "duplication", il n'est évidement pas fiable : si MySQL verrouille la table c'est qu'il y a une raison ; sans ce verrou il pourrait y avoir corruption de données.

    Après si tu ne t'en sors pas, il y a plusieurs solutions :
    - utiliser InnoDB qui utilise des verrous par enregistrement et non par table
    - essayer avec le partitionnement de table, mais nécessite MySQL 5.1
    - séparer complètement les données en plusieurs tables (comme font certains forums), quitte à avoir une table en "MERGE".
    - utiliser un serveur MySQL en réplication, et y faire tes select
    - essayer de changer la configuration du serveur afin d'avoir moins souvent recours aux tables temporaires
     
  3. Topsitemaker
    Topsitemaker WRInaute impliqué
    Inscrit:
    19 Novembre 2006
    Messages:
    527
    J'aime reçus:
    0
    Bonjour,
    Tu as une demande précise, mais les détails ne sont pas assez clairs pour y répondre. Je pense que dans ta situation il y a des solutions, c'est évident.

    Le plus facile sans modification de ta structure c'est l'activation des logs binaires de la base A, mysql-bin-log, et puis tu éjectes ces logs binaires dans la base B pour reproduire ta base au fil de l'eau (ou en différé aussi).

    Les logs ça coute au niveau des UPDATE, INSERT ou DELETE.
    Tu devrais te focaliser plutot sur ces instructions , un SELECT renvoie un résultat différent, si un UPDATE change les données.
    Si ta requête bloque tes visiteurs il y a forcément un problème de structure, c'est plus de boulot malheureusement.

    Pourquoi ne pas avoir 2 tables identiques avec 2 UPDATE pour chaque changement de données, une table pour la consultation et une table pour ta requête ?
    Après est ce que tu as besoin de reproduire ta table entièrement ? à toi de voir

    Un moment, j'étais dans la même situation que toi, j'ai pris les gros moyens,
    schématiquement les "UPDATES" de la table sont stockés dans une table temporaire, par CRON cette table temporaire est dupliquée sur une table intermédiaire puis traités (aggrégation, calcul, alimentation table secondaire ...), enfin ils sont injectés depuis la table intermédiaire dans la table définitif. D'un tes requêtes n'embêteront plus tes visiteurs, et tes SELECT et UPDATE sont plus rapides, et de deux tu offres des moyens d'optimisations encore plus grande, en revanche il faut plus de place ( informations dupliqués = gain de temps de requêtes + taille plus grande sur le disque).
     
  4. benjiman
    benjiman WRInaute discret
    Inscrit:
    9 Octobre 2005
    Messages:
    219
    J'aime reçus:
    0
    Bonjour,

    Merci pour toutes ces réponses très intéressantes !
    Je ne sais pas si j'ai tout compris au message de Topsitemaker, mais si je comprend bien, le système serait de réutiliser les logs binaires pour modifier les tables secondaires ...

    Le problème dans tout ça, c'est qu'une mise à jour dure plus de 10h et elel doit mettre à jour parfois 10 000 lignes... parfois 100 000 !
    C'est en effet un petit "jeu en ligne" (environ 3 000 visiteurs par jour).

    Donc, dans ce contexte là, re-injecter des updates,insert,delete d'une table temporaire prendrait également plusieurs heures !

    Je vous donne un exemple pratique :
    J'ai une table X avec 18 000 000 de données.
    J'execute un SELECT qui devra lire minimum au minimum 300 000 lignes.
    Ce SELECT est une requête complexe, c'est-à-dire qu'en plus de devoir lire les 300 000 données de la table X, mysql devra rejoindre à chaque ligne le résultat de plus de 9 tables, dont 4 supérieurs à 4 Go.

    Le problème de faire 2 update, insert etc., c'est que cela semble être trop difficile pour le serveur ... Le chargement risque d'être plus long à chaque fois !

    Le changement en InnoDB ne m'oblige pas de laisser 1 requête simultanée ? Quel est l'avantage et l'inconvénient de InnoDB ? Je me rappel avoir lu des informations sur InnoDB qui m'avaient pas plu du tout.

    Je suis en Mysql 5.1. Qu'est ce que m'apportera de plus le partitionnement de table ?

    Le fait d'avoir une table MERGE ne risque pas d'empirer la situation ?

    Pour y faire une réplication, ne faut-il pas 2 serveurs distincts ?

    Qu'est ce que tu entends par "avoir moins souvent recurs aux tables temporaires" ?


    Enfin, après toutes ces questions, j'ai un autre problème ...
    Ma table X a 16 000 000 de données. mon auto_increment commence à me sortir des erreur du type :
    "Duplicate entry '76665668' for key 1"
    Que faire ?
    Ma clé primaire est : id int(250) UNSIGNED auto_increment
    Que dois-je faire ?
    Dois-je peut-être passer en type DOUBLE ?

    Je vous remercie encore une fois de votre aide !
     
  5. Topsitemaker
    Topsitemaker WRInaute impliqué
    Inscrit:
    19 Novembre 2006
    Messages:
    527
    J'aime reçus:
    0
    Vu les infos que tu donnes, je pense qu'il y a un problème, car un update qui fait plus de 10h c'est quand même louche.
    Une simple règle : plus tu as d'indexes sur ta table, plus long est ton update.

    Mon exemple de structure de tables peut ne pas être utile pour ton cas, il faut peut-être que tu crées ta propre structure car tu as des besoins bien spécifiques.

    As-tu essayé ?
    Pour arriver à ton but, tu fais un gros SELECT. Maintenant si tu dupliques tes infos pour simplifier le travail de ton SELECT, cette duplication sera faite au fil de l'eau étalé sur 24h, donc t'y gagneras.

    une réplication peux se faire sur le même serveur, avec 2 bases distinctes, mais les logs ça prend de la ressource. Je pene qu'il est préférable de changer la structure de tes tables plutot.
     
Chargement...
Similar Threads - Dupliquer base données Forum Date
Dupliquer le contenu d'une base Access Développement d'un site Web ou d'une appli mobile 17 Septembre 2007
Dupliquer son site pour le marché belge Référencement international (langues, pays) 9 Juin 2021
Dupliquer un e-shop sans Dupliquer le Contenu Rédaction web et référencement 25 Novembre 2020
Dupliquer en anglais le site pour UK, AU, USA... Référencement international (langues, pays) 25 Septembre 2020
Dupliquer site .fr, le traduire sur un .com et viser les USA? Référencement international (langues, pays) 20 Juin 2018
Comment dupliquer des mots clés en mots clés à exclure? AdWords 7 Décembre 2016
Dupliquer un dossier ou un fichier Développement d'un site Web ou d'une appli mobile 7 Novembre 2016
Contenu dupliquer pour les market places Problèmes de référencement spécifiques à vos sites 10 Septembre 2015
conseil pour dupliquer du contenu volontairement. Débuter en référencement 22 Septembre 2013
Dupliquer du contenu sans porter préjudice Rédaction web et référencement 29 Août 2013
Dupliquer une partie d'article Rédaction web et référencement 12 Octobre 2012
Dupliquer les liens de mon site... bonne idée ou pas? Débuter en référencement 13 Novembre 2011
Logiciel pour dupliquer et gérer des campagnes adwords ? AdWords 23 Septembre 2011
MySql : dupliquer une enregistrement Développement d'un site Web ou d'une appli mobile 29 Décembre 2010
dupliquer un site .fr en .eu pour clientèle europe ? Référencement Google 1 Décembre 2010
Dois je dupliquer la galerie photos ? Problèmes de référencement spécifiques à vos sites 9 Septembre 2009
Dupliquer sa boutique pour en fait un double, Je souhaiterai faire une deuxième boutique Débuter en référencement 27 Mars 2009
Dupliquer un site pour le rendre accéssible Problèmes de référencement spécifiques à vos sites 11 Février 2008
Dupliquer la balise h1 Débuter en référencement 29 Mars 2007
Refonte de site ou nouveau site pour repartir sur de bonnes bases ? Débuter en référencement 27 Juillet 2022