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.

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?

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

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.

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

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.