Optimisation MySQL pour gros volumes

Bonjour à tous,

J'ai un gros problème d'optimisation de base de données. Pour une application de cartographie, j'ai deux tables, une de 1.5 millions d'enregistrements et l'autre de 6 millions d'enregistrements qui montera à 26 millions.
Les requetes s'effetuent sur 4 champs integer( 8 ) indexés.

Le temps de requete sur la 1ere table se situe aux environs de 6 secondes, pour la deuxième, environ 20 secondes.

La première optimisation a été de passer les tables en InnoDB (obligé pour la taille).

Si qqun a déja rencontré ce type de problématique et veut bien partager son experience, je suis preneur !!!

Merci,

Frédéric

Bonjour,

Pour t'aider il nous faudrait plus d'infos : structure exacte des tables, requêtes envoyées, caractéristiques de la machine sur laquelle s'exécute le serveur...

C'est très sympa de bien vouloir me donner un coup de main !

Il faut savoir que l'architecture est sous-dimensionnée aujourd'hui et sera revue lors de la mise en production du site vers la mi-août (2 serveurs).
Aujourd'hui, le serveur est un bi-xeon 2.5 Ghz avec 2 Go de mémoire, 2 disque 73 Go 10000 tm en raid 1, hébergé chez un prestataire spécialisé sur une debian.

La particularité du site est qu'il tourne entièrement autour d'une cartographie dynamique (ma spécialité), mais chose qui n'était pas prévue au départ du projet, le client s'est tourné vers l'achat du produit "georoute Raster" (volumétrie délirante!):
Pour avoir une idée:
-> Toute la France au 5000eme (équivalent mappy) au format image.
-> 70 Go de données images georéférencées (images avec des coordonnées terrain).

Donc, pour pouvoir afficher tout ce joli monde dans un client Flash de manière fluide, il faut référencer toutes les images (entre temps découpées en tuiles de petite taille) dans la base de données. Et là, les problèmes commencent à se faire sentir.

1/ Au niveau du nombre de fichiers (30 millions)
2/ Au niveau de la base de données qui doit référencer ces 30 millions d'images.

Je n'avais jamais bossé avec plus de 2 millions d'objets graphiques en base.

Pour les tables, elles sont au nombre de 8, avec une même structure, correspondant au différentes échelles cartographiques (du 4 000 000eme au 5000eme). Le problème d'optimisation ne se pose que pour la couche la plus volumineuse: 5000eme (26 millions d'objets).

Structure:
id_elemgraph int(8 )
nom_fichier varchar(100)
seuilzoom int(8 )
id_geo int(8 )
CM1x int(11)
CM2x int(11)
CM1y int(11)
CM2y int(11)
defgeo
id_tab
ssrep varchar(50)

Certains champs ne sont pas utilisés dans cette appli car j'ai gardé à peu près la structure des tables de notre application (Actigis): seuilzoom, id_geo.

Les requetes lancées sont du type (requete permettant de déterminer quelles sont les images présentes dans un rectangle d'encombrement défini par $xmin, $xmax, $ymin, $ymax):

SELECT * FROM spo_raster_5k fndVec WHERE
(
((fndVec.CM1x>=$xmin and fndVec.CM1x<=$xmax) or (fndVec.CM2x>=$xmin and fndVec.CM2x<=$xmax) or (fndVec.CM1x<=$xmin and fndVec.CM2x>=$xmax))
and
((fndVec.CM1y>=$ymin and fndVec.CM1y<=$ymax) or (fndVec.CM2y>=$ymin and fndVec.CM2y<=$ymax) or (fndVec.CM1y<=$ymin and fndVec.CM2y>=$ymax))
)

Voila, je pense que tout est là.
Je ne peux pas donner l'adresse du site avant son lancement sur le forum mais je peux la donner en privé si nécessaire.


Merci d'avance pour votre aide !

Frédéric

Effectivement, la requête combine des opérateurs d'inégalité et des OR / AND donc l'exécution peut être longue.
Entre parenthèses, je ne comprends pas trop le

Code:

---

or (fndVec.CM1x<=$xmin and fndVec.CM2x>=$xmax)

---

Comme j'imagine qu'il y aura beaucoup de lectures et très peu d'écritures dans la base, les index vont être prépondérants.
Tu peux jouer sur les différents buffers et autres réglages système pour gagner en performance. Cf http://florian.developpez.com/mysql/page2.php et http://dev.mysql.com/doc/mysql/en/optimizing-the-server.html

Avec un serveur plus puissant et les réglages qui vont bien, tu devrais gagner pas mal en performance...

Cette ligne de la clause Where sert à retourner les images qui seraient plus grande que l'encombrement demandé:
Imaginons, j'ai une image qui mesure 10, de la coordonnée 10 à 20 puis je demande dans ma requete les images comprises entre 8 et 22.
Sans cette clause, l'image ne sort pas...

J'espère que j'ai été clair, je n'en suis pas convaincu !



En effet, il n'y aura aucune écriture dans ces tables une fois l'import des images effectué.
MySQL ne comporte-t-il qu'un seul type d'index ?

Je réfléchi mais je ne pense pas que l'on puisse réaliser cette requete sans croiser les OR et les AND... A moins peut-etre d'utiliser des checksum...

Si vous avez des idées !

Merci de la réponse,

Frédéric

Citation:

---

Envoyé par barns

J'espère que j'ai été clair, je n'en suis pas convaincu !

---

Si si, en fait c'est juste que ça m'étonne de stocker 2 coins du tile, ils n'ont pas une taille fixe ?
Peut-être qu'en stockant juste un coin et éventuellement une longueur les requêtes seraient moins lourdes.

Citation:

---

MySQL ne comporte-t-il qu'un seul type d'index ?

---

Non, il y a les hash index mais ils ne fonctionnent qu'avec des comparateurs d'égalité et des tables stockées en mémoire. Tu peux aussi mettre un index multiple sur les 4 colonnes mais je ne pense pas que ça apporte grand chose.

PS : regarde tes MP...

il faut que tu partitionnes tes tables.

Vu ta volumétrie cible, tu ne pourras pas tout laisser dans une meme table et obtenir un temps de réponse correct. :)

Je ne sais pas comment on partitionnes en Mysql... Aucune idée si c'est possible deja.. :?

1/ Essaie de faire un explain sur tes requetes pour voir par ou elles passent et ensuite tu pourras entreprendre des actions de Tuning SQL..

2/ En parrallèle, regarde pour le partitionnement de tes tables. En partitionné, ca doit passer. Je bosse (sous DB2) avec une table partitionné de presque 1 milliard de lignes et ca passe sans problème.

3/ Analyse tes paramètres de serveur MySql

Honnetement je pense qu'une partie de la solution se trouve dans le partitionnement.

Bon courage :D

Comme te le suggere gregory.broissard, le partitionnement te permettrais un bon gain de performance. A condition qu'il soit mis en place de façon efficace.
Envisage une architecture en cluster, tu obtiendras un partionnement à répartition physique. Tu ne pourras pas trouver plus performant.

Salut,

Ben voilà, l'appli est en test de prod et va être lancée le mois prochain (je passerai l'adresse). Ca marche nikel et l'architecture n'a pas encore évoluée.

La solution:
-> Type de table innoDb
-> Requete uniquement avec des AND dans la clause W (merci !) (tps de requete 24 secondes -> 0.2 secondes)

MySQL se débrouille très très bien avec une table de 28 000 000 d'enregistrements... rien à redire !

Le défi technique suivant a été de stoker et et afficher rapidement parmis les 28 000 000 d'imagettes jpg qui vont avec mais pour ça, notre bon linux s'en débrouille tout aussi bien !

Merci à tous.