Modification d'une grosse table

[fred078]

Bonjour,

Nous avons une table relativement grosse dans notre base de donnée mysql (600 000 enregistrements, 4.5 Go).

A chaque modification de la structure de cette table (par exemple l'ajout d'un simple champ varchar), le serveur mouline et rend le site inaccessible pendant plusieurs dizaines de minutes.

Y-a-t-il une méthode saine pour ajouter un champ à ce type de table, sans faire planter le serveur ?

Merci d'avance pour votre aide !

[captain_torche]

Pourquoi ajoutez-vous régulièrement des champs à cette table ?

Sans plus d'infos, je pense que ça vient d'un problème de conception de votre base de données.

[jcaron]

C'est aussi ma première réaction, mais bon, ça peut arriver qu'on veuille étendre un schéma déjà existant.

Je ne sais pas comment ça se passe avec mysql, mais avec postgresql un ajout de colonne est rapide tant qu'on ne met pas de valeur par défaut à la nouvelle colonne. Comme l'ajout de la colonne lui-même est bloquant, la solution consiste alors à ajouter la colonne sans défaut, puis mettre le défaut sur la colonne, puis initialiser cette nouvelle colonne avec cette valeur par défaut. Evidemment, cette dernière opération peut être un peu longuette suivant la taille de la table, et avec mysql le type de base (MyISAM ou Innodb) va donc influencer le type de lock et donc le fait que ça bloque ou pas.

Autre solution, créer une autre table qui sera utilisée avec une jointure avec la première, éventuellement avec une vue pour simplifier les choses en lecture.

Jacques.

[fred078]

Bonjour,

Merci pour vos réponses

Nous devons ajouter un champs suite à l'évolution de notre site, nécessité de stocker de nouvelles infos.

Lors de l'ajout de ce champs, aucune valeur par défaut n'est créée :

ALTER TABLE `matable` ADD `monchamp` VARCHAR(10) NOT NULL ;

La solution pourrait effectivement être de créer une autre table jointe, mais j'aurais préféré ajouter ce champs dans la table existante.

Modifié 29 Septembre 2011 par fred078

[jcaron]

Euh... Comment est-ce-que tu peux ajouter une colonne not null sans valeur par défaut?

Jacques.

[captain_torche]

La phrase "à chaque modification de la structure de cette table" semble dire que c'est relativement fréquent : à quelle fréquence le faites-vous ?

[fred078]

_AT_jcaron:

En fait je la met a NOT NULL pour pouvoir faire mes tests sur champs vide (... WHERE monchamp='').

Par "pas de valeur par défaut" j'entendais, pas de :

ALTER TABLE `matable` ADD `monchamps` VARCHAR(10) NOT NULL DEFAULT 'valeur';

_AT_captain_torche :

Ca arrive moins de 1 fois par an, et c'est probablement une des dernieres fois que ca arrive.

Modifié 29 Septembre 2011 par fred078

[jcaron]

Je ne comprends pas comment mysql peut t'autoriser à ajouter une colonne "not null" sans valeur par défaut: la colonne en question est vide donc elle ne respecte pas la contrainte. Donc soit il y a un "raccourci" dans mysql qui dit que si tu mets not null alors il lui met une valeur de chaîne vide plutôt que null par défaut (et donc tu as une valeur par défaut), soit mysql se moque de toi :-) Avec un vrai serveur SQL, pour ajouter une colonne avec une contrainte NOT NULL à une table déjà remplie, soit il faut mettre une valeur par défaut explicite, soit il faut faire ça en plusieurs temps (ajout de la colonne sans valeur par défaut ni contrainte, modif de la colonne pour ajouter la valeur par défaut, remplissage de la colonne, modif de la colonne pour ajouter la contrainte).

Note aussi que le comportement peut éventuellement différer entre une table myIsam et une table InnoDB.

Autre alternative si la jointure ne te convient pas: créer une nouvelle table en copiant l'ancienne (un truc genre create table nouvelle as select *,cast(nouvelle_valeur_par_défaut as type) as nouvelle colonne from ancienne), puis remplacer l'ancienne table par la nouvelle (2 x alter table rename). Le tout avec une transaction bien placée suivant tes circonstances (si tu peux empêcher les écritures dans la table pendant l'opération, une courte transaction autour des deux alter table rename suffira).

Jacques.

[fred078]

A ok, j'ignorais que le "vide" était en fait une valeur (si quelqu'un d'aussi inculte que moi passe par là : http://sqlpro.developpez.com/cours/null ). je pensais que ne pas passer par NULL me simplifierais la vie mais je comprends que j'ai pris une très mauvaise habitude...

Donc si je comprends bien, mes tables seront moins lourdes si a partir du moment où un champs est vide, je le met en NULL plutôt que de le laisser vide ?

Je vais essayer d'ajouter ce champs en spécifiant le NULL plutôt qu'un champs vide car si je peux éviter les jointures ça m'arrange (trop de mauvais souvenirs de mauvaises performances avec les jointures...).

Modifié 29 Septembre 2011 par fred078

[fred078]

Au final, je pense que je vais contourner le problème en divisant cette table en plusieurs tables :

clients commençant par A dans la table "matable_a", clients qui commencent par B dans la table "matable_b", etc... * 26 tables.

Je penses que je vais en faire hurler quelques un mais c'est ce qui me parait le plus simple et le moins générateur de problèmes sur le long terme (multiplication du volume de données : +30% chaque année)...

[captain_torche]

C'est effectivement ... glurps ... absolument horrible.

Quels sont les champs que vous ajoutez ? Des informations non prévues ?

Si c'est le cas, il faudrait envisager une seconde table 'informations', ainsi qu'une jointure.

[Arlette]

Si c'est pour le site : "Merci facteur" ... Autant faire les choses proprement. Vu la progression de CA d'une année sur l'autre , vaut mieux perdre un peu de temps à bien peaufiner son code, que de faire du rafistolage !

Voilà encore une idée géniale qui a de l'avenir.

[fred078]

_AT_captain_torche :

Oui, ce sont des infos supplémentaires nécessaire suite à l'évolution de certaines fonctionnalités.

En fait cette table est composée de 2 "gros" champs contenant 99% du volume, et une 15ène d'autres "petits" champs.

J'ai testé les jointures il y a quelques temps mais j'ai été confronté à de nombreux problèmes de performances. Donc, même si notre infra à beaucoup évolué depuis (et que les structures de nos tables ont été optimisées via des INDEX plus judicieux), j'hésite à me relancer la dedans...

_AT_Arlette :

Non, il ne s'agit pas de Merci Facteur, c'est pour un autre site. Mais merci pour ton compliment (comment sais-tu que je fais partie de merci facteur ? je ne pensais pas l'avoir spécifié pour être plus libre dans mes propos dans le cas où un concurrent tomberait sur mes messages.)

Modifié 29 Septembre 2011 par fred078

[jcaron]

Le partitionnement de table c'est bien gentil, mais ça veut dire que tu ne fais jamais de recherches que par le nom complet (ou au moins le début du nom), c'est un peu limitant, non?

Je pense qu'une bonne jointure bien faite (i.e. avec les bons index) ça devrait être nettement plus simple.

Jacques.

[captain_torche]

Je peux te garantir qu'une table de jointure bien rédigée te solutionnerait pas mal de soucis.

Quant à merci facteur (Qui est effectivement un excellent concept), les modérateurs et administrateurs ont accès à ton mail d'inscription

[fred078]

Non, jamais de recherches globales parmi tous les clients, les recherches se font que pour les informations d'un même client (hors dans le cas d'un partitionnement alphabétiques les données d'un seul et même client seront toujours dans la même table).

Mais bon, si vous me déconseillez aussi nettement cette méthode je vais vous suivre et faire quelques tests de jointures pour voir ce que ça donne.

[AdvertEasy]

Bonjour,

Nous avons une table relativement grosse dans notre base de donnée mysql (600 000 enregistrements, 4.5 Go).

A chaque modification de la structure de cette table (par exemple l'ajout d'un simple champ varchar), le serveur mouline et rend le site inaccessible pendant plusieurs dizaines de minutes.

Y-a-t-il une méthode saine pour ajouter un champ à ce type de table, sans faire planter le serveur ?

Merci d'avance pour votre aide !

C'est sous phpmyadmin que ça plante ? En ssh, ça donne quoi ?

[fred078]

Bonjour,

Je n'ai pas essayé sous ssh. Je vais me pencher dessus.

Modifié 1 Octobre 2011 par fred078

[fred078]

Bonjour,

Simplement pour vous tenir au courant, nous avons finalement choisi de scinder notre table en plusieurs table.

Cela a résolu nos problèmes.

Merci pour vos conseils.

[AdvertEasy]

Bonjour,

Simplement pour vous tenir au courant, nous avons finalement choisi de scinder notre table en plusieurs table.

Cela a résolu nos problèmes.

Merci pour vos conseils.

Quand on a une table très grosse et qui en plus grossit au fil du temps, le plus simple est en effet souvent de la scinder en créant 1 nouvelle table par semaine ou par mois ou sur une autre périodicité adaptée aux données qu'on traite.

C'est bien que tu aies pu résoudre ton problème.