Actions

Journaux











Travailler avec une grosse table dans MySQL

Écrit le 28/09/2006 @ 15:24 par Drizzt

Informatique - ProgrammationJe travaille sur un problème de MySQL depuis quelque temps. J'ai une base de données avec 39 millions d'entrées dans une table de 15GB, et les données ne sortent pas aussi vite que je le voudrais. J'ai fouillé sur le 'Net pour trouver une solution à mon problème. Si jamais vous avez le temps et vous trouvez une idée, faites-le savoir, je vais l'essayer! Wink

La première chose que j'ai faite, c'est de mettre la table à une taille fixe. Elle est passée de 5 à 15GB, mais les requêtes prennent environ 7 minutes chaques (avant c'était variable et pouvais être plus long). J'aimerais pouvoir diviser celà en deux..

Commentaires

Commentaire par Tomastan  Score: 2
Écrit le: 28/09/2006 @ 15:33

As-tu optimisé tes indexs?

Les indexs peuvent être une cause de ralentissement lorsque mal créés.

Si tu n'as pas d'index, alors je comprends un peu plus la longueur. Crée toi un index avec les champs les plus consultés, et ce en ayant le moins de colonne possible... Par exemple:

Pour une table de 15 millions d'occurence, avec les colonnes Nom, prenom, no. ass. sociale, no téléphone, code inventaire. Tu sais que la majorité des conditions que tu mets dans la table concerne les colonne ass cociale et code investaire, alors crée un index avec ces deux colonnes... ca risque d'etre long, mais c une job qui economisera le temps de toute les prochaines...

Si tu fais un simple select * sans condition, et bien il n'y a aucun index qui accélérera ton temps réponse...

C'était une simple idée... Roll Eyes
Dernière modification le 28/09/2006 @ 15:41

Commentaire par Drizzt  Score: 2
Écrit le: 28/09/2006 @ 15:43

Oui j'ai un index (qui fait seulement 1.5GB), sauf que j'ai trop de données à sortir, je crois que le temps qu'il prend est fonction de la position dans le fichier (et je crois aussi que je vais revenir sur le backup avec les tailles dynamiques).

Va rester à essayer d'autres formats de storage, pis demander une meilleur machine.

Commentaire par Tomastan  Score: 2
Écrit le: 28/09/2006 @ 16:00

Je sais qu'il y a d'autres moyens, mais je suis beaucoup moins familier... Est-ce que la notion de tablespace existe en MySql. Je sais que changer la taille des blocs des tablespace pour une BD oracle change le temps réponse.

Commentaire par Drizzt  Score: 2
Écrit le: 28/09/2006 @ 16:04

Explique le concept, peut-être que je vais répondre oui! Razz

Commentaire par Tomastan  Score: 2
Écrit le: 29/09/2006 @ 07:59

Le tablespace dans Oracle est un espace réservé pour tes tables sur le disque. Tu peux fixer la taille des blocs utilisés dans ces fichiers. Ces grosso modo ce que je pourrais en dire. Si je serais certifié Oracle, je pourrais sans doute te donner la vrai définition... Sourire sans les dents

Commentaire par Drizzt  Score: 2
Écrit le: 29/09/2006 @ 08:41

Pas de concept équivalent..

Commentaire par Drizzt  Score: 2
Écrit le: 29/09/2006 @ 10:36

Je crois que j'ai trouvé ma solution...

Ce serait de mettre à jour MySQL à la version 5.1, ensuite d'appliquer et d'automatiser la gestion de partitions horizontales! Ça semble faire exactement ce que j'ai besoin, i.e. les données seraient regroupées par mois, il y en aurait donc moins à chercher, et pour trouver les lignes du mois de septembre, MySQL n'aura pas besoin de scanner tout juin/juillet/août!

Commentaire par Drizzt  Score: 2
Écrit le: 29/09/2006 @ 13:35

Je ne réussis par à faire mes partitions, mais bon.. pour le fun, je suis en train de faire un nouvel index avec les colonnes que j'utilises, en espérant que ça marche!

Commentaire par Drizzt  Score: 2
Écrit le: 29/09/2006 @ 14:10

Il semblerait que si tu veux utiliser un autre champ que la clé primaire pour créer les conditions de partition, ça chie. Il faut donc droper la clé primaire, ou attendre une autre version qui va éliminer cette condition.

Commentaire par Drizzt  Score: 2
Écrit le: 29/09/2006 @ 17:51

J'ai réussi à diviser les tables par semaine. Ça semble fonctionner très bien, les temps de requêtes sont passés de 7-12 minutes à 2 minutes Yeah!

Je réajoute l'index personnalisé, juste au cas où Smart!

Commentaire par Drizzt  Score: 2
Écrit le: 02/10/2006 @ 07:40

Ça marche!

Je suis rendu à environ 20 secondes / 10 000 entrées.

Je crois que les 2 méthodes employées ensemble aident à la vitesse. Par contre, je vais encore avoir un problème avec les requêtes qui ont un "like" et un wildcard, car ça lance une recherche sur toutes les partitions.

Commentaire par Drizzt  Score: 2
Écrit le: 20/10/2006 @ 14:39

J'avais ce problème parce que j'utilisais une fonction qui retournait un string comme clé de partitionnement, et que je castais ce string en integer.

La solution, refaire le partitionnement en utilisant to_days() ou to_years()!

Ça devrait fonctionner comme un charme, le serveur est en train de modifier le partitionnement sur la table contenant maintenant plus de 70 000 000 d'entrées!

Commentaire par Drizzt  Score: 2
Écrit le: 20/10/2006 @ 15:19

Finalement, ça n'a rien changé! Un like dans une requête prend encore énormément de temps parce que toutes les partitions sont scannées. Si je ne me trompes pas, c'e l'une des améliorations sur laquelle ils travaillent.

Commentaire par Drizzt  Score: 2
Écrit le: 17/11/2006 @ 09:59

J'utilise maintenant la fonction yearweek() pour séparer mes tables en utilisant la colone de date comme argument. Présentement, cette fonction n'a pas été optimisée pour le partition pruning.

Ce n'est pas bien grâve, parce que les performances sont encore très bonnes, malgré le fait que la base de données dépasse maintenant les 122 millions d'entrées. J'espère seulement que MySQL vont amener l'optimisation des requêtes select avec cette fonction.

Commentaire par Drizzt  Score: 2
Écrit le: 31/01/2007 @ 17:01

La base de données fait environ 227 000 000 d'entrées. Je suis en train de modifier la structure de la table, pour permettre de faire des recherches plus intelligentes dans celles-ci (une erreur au niveau de la conception originale).

Une requête SQL lancée hier, vers 5h00, s'exécute encore. Ça devrait être fini d'ici 1h ou 2.. Laughing out loud

J'ai remodifié le partitionnement, j'ai maintenant 4 sous-partitions à chaque partition, basées sur la journée dans la semaine. Ça fonctionne très bien et ça rend les recherches plus rapide. En plus, depuis MySQL 5.1.14 yearweek() est bien compris comme méthode de partitionnement et le database pruning fonctionne! Smart!

Commentaire par Drizzt  Score: 2
Écrit le: 01/02/2007 @ 07:15

Ça a pris 31h environ. Par mal content que ce soit fini! Laughing out loud

Commentaire par Drizzt  Score: 2
Écrit le: 01/05/2007 @ 13:03

Petit suivi de l'histoire..

La base de données fait maintenant plus de 400000000 entrées..

Oui oui! C'est 400M d'entrées!

La vitesse n'a pas réellement changé (il faut prendre en considération que le serveur n'est pas une bombe), elle pèse maintenant 63GB, 9.1GB une fois compressée pour le backup.

La base de données est maintenant partitionnée par semaine, puis sous-partitionnée par jour.

Commentaire par Drizzt  Score: 2
Écrit le: 19/06/2007 @ 08:53

Nous venons de passer la barrière psychologique du demi milliard d'entrées! Yeah!

Commentaire par Drizzt  Score: 2
Écrit le: 13/10/2007 @ 13:58

0.6G d'entrées cette semaine.. la vitesse n'a pas changée! Nous avons donc une méthode gagnante! Smart!

Commentaire par Drizzt  Score: 2
Écrit le: 28/11/2007 @ 08:56

0.7G d'entrées et ça va toujours bien Rock on!

Commentaire par Drizzt  Score: 2
Écrit le: 22/04/2008 @ 14:25

On a eu notre milliard d'entrées vendredi matin!

Malheureusement, MySQL ont modifié les règles du database pruning concernant les dates avec la version 5.1.24... Les recherches faites avec mes scripts PHP n'utilisent plus les partitions, ce qui est la cata! Reste à savoir ce que je vais faire avec ça.. modifier les scripts ou espérer que la prochaine version m'apporte de la vitesse Roll Eyes

Commentaire par Drizzt  Score: 2
Écrit le: 01/06/2010 @ 09:32

Ça intéresse probablement personne d'autre que moi, mais bon.. voici quand même où s'en est rendu!

J'ai modifié, il y a longtemps, le partitionnement et les requêtes SQL de recherche. C'est partitionné selon la fonction to_days(date) par année, puis repartitionné par HASH.

La table a été modifiée en type ARCHIVE, ce qui veut dire qu'elle est compressée à la volée et qu'elle n'a pas d'index. Les performances ont été un peu atteintes, mais le gain en espace disque en valait le coup!

Nous avons maintenant 2.5 milliards d'entrées occupant 70GB d'espace disque. Il est possible de sortir environ 40000 lignes d'une recherche en moins de 20 secondes.


Tous les blogs
<< Messenger 6.0:Mac | Retour aux blogs | Après Apple et Dell, IBM et Lenovo >>
Blogs de la même catégorie
<< Le computer stupidities de la programmation | RealBasic maintenant UB >>