Discussion :

[jvanbouc]

Bonjour tout le monde,

Cela fait quelques temps que je m'acharne à trouver un moyen de rendre des opérations de type "ALTER TABLE" possibles sur une table de grande taille sans succès.

Je vous explique le topo :
La table concernée a plus de 1 800 000 entrées, avec de nombreux champs TEXT pour un total de plus de 5 GB de données (moteur de stockage InnoDB).

Le serveur est configuré plus ou moins à la my-huge.cnf (avec quelques buffers plus grands), est entièrement dédié à MySQL mais n'a qu'1 GB de RAM. En soi, quelque soit la requête, sur une petite ou large partition de données, le serveur est assez rapide à répondre (compte tenu qu'on sait quand utiliser les index ou pas...). Ce sont les opérations "ALTER TABLE" qui prennent une éternité.

Particulièrement hier je tentais de retirer une colonne (tinyint(1)) de la table en question (enfin... Une copie de backup), après environ 20 à 30 minutes le client se déconnecte :
ERROR 2013 (HY000) at line 3: Lost connection to MySQL server during query

J'ai essayé de verrouiller les tables auparavant, c'est sans effet.
Il y a quelques mois j'ai pu supprimer quelques index de cette table. L'opération a pris plus de 2h mais elle a abouti.

Je m'en remet donc à votre expertise... Est-ce que quelqu'un sait comment mener à bien ces opérations sur une table aussi volumineuse ?

Un grand merci,
John.

[CinePhil]

ALTER TABLE en principe c'est pas un truc qu'on fait souvent !

Plusieurs pistes :
1) La BDD est-elle normalisée ?
Une grande table avec beaucoup de colonnes peut être le signe d'une modélisation des données non optimum.

2) Si la reconception de la BDD n'est pas envisageable à court terme, ou si tu penses que ta BDD est normalisée, essaie de désactiver les index puis de les réactiver après avoir fait les modifs nécessaires.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE laTable
DISABLE KEYS

La reconstruction des index se fera en une seule fois après toutes les modification au lieu de se faire à chaque ALTER TABLE.

3) Tu peux lancer un OPTIMIZE TABLE pour réorganiser la table. En principe ça améliore les performances.

[jvanbouc]

Je vais essayer ces quelques suggestions dans l'instant, merci beaucoup.

J'avais lancé un OPTIMIZE TABLE il y a quelques mois, il a mis plus de 2h en bloquant les INSERT.
Je préfèrerais éviter ce genre de downtime, et je ne pense pas qu'on puisse accélérer un OPTIMIZE TABLE.

Une solution (enfin je pense ) serait de copier la table, effectuer l'OPTIMIZE sur la copie, et faire un rename.

Concernant la normalisation, la table est relativement standard : clé primaire BIGINT, pas de clés étrangères, plusieurs gros champs TEXT et quelques "flags" (j'utilise des tinyint(1) pour ça). Les queries sont très rapides sur cette table quand ils sont bien faits. Par exemple, les count(*) sur toute la table sont quasi immédiats.

[CinePhil]

On peut avoir la structure de la table pour voir ?
Et une info sur ce qu'elle est censée contenir ?

[jvanbouc]

Cette table reçoit des informations de plusieurs systèmes de filtrage email.
Environ 20000 entrées par jour sont concises dans cette table (les vieilles entrées sont historisées).

Voici la structure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLE `maillog` (
  `mail_id` bigint(20) unsigned NOT NULL auto_increment,
  `id` text,
  `size` bigint(20) default '0',
  `from_address` text,
  `from_domain` text,
  `to_address` text,
  `to_domain` text,
  `subject` text,
  `clientip` varchar(30) default NULL,
  `isspam` tinyint(1) default '0',
  `ishighspam` tinyint(1) default '0',
  `issaspam` tinyint(1) default '0',
  `isrblspam` tinyint(1) default '0',
  `spamwhitelisted` tinyint(1) default '0',
  `spamblacklisted` tinyint(1) default '0',
  `sascore` decimal(7,2) default '0.00',
  `spamreport` text,
  `virusinfected` tinyint(1) default '0',
  `nameinfected` tinyint(1) default '0',
  `otherinfected` tinyint(1) default '0',
  `report` text,
  `hostname` text,
  `date` date default NULL,
  `time` time default NULL,
  `headers` text,
  `quarantined` Char(1) NULL,
  PRIMARY KEY  (`mail_id`),
  KEY `maillog_datetime_idx` (`date`,`time`),
  KEY `maillog_quarantined` (`quarantined`),
  KEY `mail_id_idx` USING BTREE (`id`(20)),
  KEY `from_address_idx` USING BTREE (`from_address`(200)),
  KEY `to_address_idx` USING BTREE (`to_address`(200)),
  KEY `from_domain_idx` USING BTREE (`from_domain`(50)),
  KEY `to_domain_idx` USING BTREE (`to_domain`(50)),
) ENGINE=InnoDB;

J'ai essayé DISABLE KEYS, ça ne fonctionne qu'avec le moteur MyISAM.
Je commence à envisager de dumper entièrement la table, la recréer à mon goût, et la repeupler ensuite en effaçant les colonnes inutiles du dump avec un script.

J'avoue que je préfèrerais un moyen qui me coûterais moins de temps et de café

EDIT : En fait, les colonnes que je veux supprimer ne sont pas dans ce CREATE TABLE... Ce sont 6 champs tinyint.

[CinePhil]

Plusieurs choses à optimiser :

1) Les colonnes de type text sont à éviter.
Les colonnes from_address, from_domain, to_address, to_domain, subject, hostname peuvent sans doute être limitées à un VARCHAR(x) correpondant à la norme des données qu'elles contiennent.

Les index seront ainsi optimisés et leur utilisation plus rapide.

2) Quel est cet id de type texte en plus de la clé primaire mail_id ?

3) Le BIGINT est-il indispensable ?
C'est quand même très grand :

BIGINT 8 octets / de -9 223 372 036 854 775 808 à + 9 223 372 036 854 775 807

Des millions de millliards !

4) Clientip en VARCHAR(30) : IP V6 ?
Parce que l'IP V5 ne demande que 15 caractères : 123.123.123.123

5) Plutôt que d'avoir plusieurs colonnes pour le type de spam, une seule colonne indiquant le type en clé étrangère référençant une table des types serait préférable.
Ainsi on peut ajouter de nouveaux types sans changer la structure de la table.

6) La partie virus devrait aussi être externalisée.

7) Nommer des colonnes date et time est une mauvaise idée parce que ce sont des mots réservés du SQL et ça peut créer des confusions, voire des erreurs.