Dupliquer une base de données MySQL

[benjiman]

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 !

 

[Bool]

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

 

[Topsitemaker]

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).

 

[benjiman]

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 !

 

[Topsitemaker]

Bonjour,

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).

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.

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 !

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.