Création d'un index sur une grosse table [Fait] [Résolu]

Jester · 01/04/2008, 01h35

Bonjour,

J'ai une table de volumétrie importante (100 millions de lignes, 4Go en stockage InnoDB) au regard de la machine qui la gère (1Go de RAM).

J'ai une clé primaire sur deux colonnes de types INT (chaque ligne est composée de 3 INT et 1 DATE).

Le problème c'est que la génération de cet index est très très long.

J'aimerais savoir s'il y avait des moyen d'optimiser sa génération. Pour l'instant, j'ai fait un load data infile dans une table qui ne définit pas la clé primaire (25 minutes pour l'import). Si je rajoute la clé primaire au bout de 3 heures ce n'est pas fini.

J'ai fait des test sur une portion des données et j'ai constaté que le temps création de cet index est en O(n²). Soit pour ajouter l'index d'une ligne, il doit parcourir toutes les autres. Ca me semble très étrange.

Peut-on faire quelque chose pour accélérer cela? Augmenter des buffers en mémoire? Supprimer des vérification de contraintes inutiles?

Merci si vous avez des idées.

PS : J'ai remarquer que quand on ajoute une contrainte d'unicité sur le couple de la clé, la génération initiale de l'index prend beaucoup de temps, mais si on le supprime et qu'on le recrée, c'est beaucoup plus rapide grâce à cette clause d'unicité. Comment faire pour que ce soit rapide dès la première création, i.e. dire à MySQL de me croire quand je dis que c'est unique.

Jester · 01/04/2008, 04h19

Alors après quelques test, le problème est le suivant, la génération de l'index (du BTree) semble être itérative. Du coup rajouter une ligne au début est rapide, mais au fur et à mesure ça ralenti. Je commence à 20k par seconde et au bout de 10% je me retrouve déjà sur 1k par secondes. Inversement, les accès I/O augmentent (de 30% du temps processeur à 80%).

Car ma table me semble relativement ordonnées, ce qui déséquilibre probablement BTree. Y a-t'il un moyen de le remplir quitte à le laisser se déséquilibrer complètement et que Mysql ne le rééquilibre qu'à la fin?

Maximilian · 01/04/2008, 18h03

Salut,

Quelques liens sur le sujet :

http://www.mysqlperformanceblog.com/...ad-would-take/

http://www.mysql.de/news-and-events/...000000195.html

Il semble conseillé de trier les données à importer dans l'ordre de la clé primaire.

Par ailleurs 1 Go de RAM ça me parait vraiment peu pour ce volume de données...

Jester · 02/04/2008, 02h30

Je te remercie fortement pour le premier lien qui est de grande qualité. Je pensais pourtant avoir fait le tour de ce blog

La solution est donc de partitionner (voir ce tutorial) la table pour que les index de chaque tables puissent finalement tenir en mémoire.

Maximilian · 02/04/2008, 09h05

Effectivement ça peut être une solution mais par contre la 5.1 n'est pas encore une version de production, donc à prendre avec précaution même si les fonctionnalités sont assez stables...

Jester · 02/04/2008, 12h44

Oui c'est dommage que cette fonctionnalité n'existe pas déjà depuis un moment. Je me demande comment font les gens avec leurs data warehouse de plusieurs To.

Il y a aussi l'utilisation de table merge pour faire un partitionnement, mais c'est plus manuel.

01/04/2008, 01h35	#1
Jester Membre chevronné Inscription : septembre 2003 Messages : 625 Détails du profil Informations forums : Inscription : septembre 2003 Messages : 625 Points : 634 Points : 634	Création d'un index sur une grosse table Bonjour, J'ai une table de volumétrie importante (100 millions de lignes, 4Go en stockage InnoDB) au regard de la machine qui la gère (1Go de RAM). J'ai une clé primaire sur deux colonnes de types INT (chaque ligne est composée de 3 INT et 1 DATE). Le problème c'est que la génération de cet index est très très long. J'aimerais savoir s'il y avait des moyen d'optimiser sa génération. Pour l'instant, j'ai fait un load data infile dans une table qui ne définit pas la clé primaire (25 minutes pour l'import). Si je rajoute la clé primaire au bout de 3 heures ce n'est pas fini. J'ai fait des test sur une portion des données et j'ai constaté que le temps création de cet index est en O(n²). Soit pour ajouter l'index d'une ligne, il doit parcourir toutes les autres. Ca me semble très étrange. Peut-on faire quelque chose pour accélérer cela? Augmenter des buffers en mémoire? Supprimer des vérification de contraintes inutiles? Merci si vous avez des idées. PS : J'ai remarquer que quand on ajoute une contrainte d'unicité sur le couple de la clé, la génération initiale de l'index prend beaucoup de temps, mais si on le supprime et qu'on le recrée, c'est beaucoup plus rapide grâce à cette clause d'unicité. Comment faire pour que ce soit rapide dès la première création, i.e. dire à MySQL de me croire quand je dis que c'est unique.
	00

01/04/2008, 18h03	#3
Maximilian Provisoirement toléré Inscription : juin 2003 Messages : 2 622 Détails du profil Informations forums : Inscription : juin 2003 Messages : 2 622 Points : 2 505 Points : 2 505	Salut, Quelques liens sur le sujet : http://www.mysqlperformanceblog.com/...ad-would-take/ http://www.mysql.de/news-and-events/...000000195.html Il semble conseillé de trier les données à importer dans l'ordre de la clé primaire. Par ailleurs 1 Go de RAM ça me parait vraiment peu pour ce volume de données... __________________ Pensez au bouton
	00

02/04/2008, 09h05	#5
Maximilian Provisoirement toléré Inscription : juin 2003 Messages : 2 622 Détails du profil Informations forums : Inscription : juin 2003 Messages : 2 622 Points : 2 505 Points : 2 505	Effectivement ça peut être une solution mais par contre la 5.1 n'est pas encore une version de production, donc à prendre avec précaution même si les fonctionnalités sont assez stables... __________________ Pensez au bouton
	00

01/04/2008, 04h19	#2
Jester Membre chevronné Inscription : septembre 2003 Messages : 625 Détails du profil Informations forums : Inscription : septembre 2003 Messages : 625 Points : 634 Points : 634	Alors après quelques test, le problème est le suivant, la génération de l'index (du BTree) semble être itérative. Du coup rajouter une ligne au début est rapide, mais au fur et à mesure ça ralenti. Je commence à 20k par seconde et au bout de 10% je me retrouve déjà sur 1k par secondes. Inversement, les accès I/O augmentent (de 30% du temps processeur à 80%). Car ma table me semble relativement ordonnées, ce qui déséquilibre probablement BTree. Y a-t'il un moyen de le remplir quitte à le laisser se déséquilibrer complètement et que Mysql ne le rééquilibre qu'à la fin?
	00

02/04/2008, 02h30	#4
Jester Membre chevronné Inscription : septembre 2003 Messages : 625 Détails du profil Informations forums : Inscription : septembre 2003 Messages : 625 Points : 634 Points : 634	Je te remercie fortement pour le premier lien qui est de grande qualité. Je pensais pourtant avoir fait le tour de ce blog La solution est donc de partitionner (voir ce tutorial) la table pour que les index de chaque tables puissent finalement tenir en mémoire.
	00

02/04/2008, 12h44	#6
Jester Membre chevronné Inscription : septembre 2003 Messages : 625 Détails du profil Informations forums : Inscription : septembre 2003 Messages : 625 Points : 634 Points : 634	Oui c'est dommage que cette fonctionnalité n'existe pas déjà depuis un moment. Je me demande comment font les gens avec leurs data warehouse de plusieurs To. Il y a aussi l'utilisation de table merge pour faire un partitionnement, mais c'est plus manuel.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email