Optimisation d'un serveur mysql (table avec 130 millions de données) [Résolu]

[frite333]

Bonjour,

Actuellement en possession d'une base de données comprenant une table d'environ 20Go(130 millions de records), je cherche un moyen d'optimiser le serveur mysql (ses paramètres mais aussi ceux de l'OS sur lequel il est : Linux (Centos 5)).

Car actuellement une simple requête avec une jointure sur une table de 2Go (10 millions de records) avec un GROUP BY prend 5 minutes.
Les champs de la clause WHERE ont été indexés et la requête et les tables ont été optimisés au maximum.

Je réalise des tests actuellement pour savoir quels seraient les optimisations à faire, mais elle sont longues a tester sur une telle base de données.
Pouvez vous m'indiquer quelles sont les réglages les plus influent sur la rapidité d'exécution d'une requête ?

Si ca continue je vais devoir tester sous d'autres SGBD, comme postgreSQL, pour de meilleurs performances

Merci pour votre aide !

[overider]

bonjour.

De ce que j'ai identifié, les paramètres qui influence le plus les performances sont ceux ci:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# pour agir sur les performances c'est celle la qu'il faut changer
max_connections = 25
key_buffer_size = 128M
max_allowed_packet = 50*1024*1024
table_cache = 1024
sort_buffer_size = 6M
join_buffer_size = 6M
net_buffer_length = 8K
read_buffer_size = 6M
#read_rnd_buffer_size = 1M
#myisam_sort_buffer_size = 224M
query_cache_size = 80M
query_cache_type = 1
query_cache_min_res_unit = 1k
#open_files_limit = 2048
#max_tmp_table = 128
#thread_concurrency = 4
tmp_table_size = 48M
max_heap_table_size = 48M
wait_timeout = 86400
table_open_cache = 600
thread_concurrency = 3

.
NB : ne pas tenir compte des valeurs, les optimiser pour votre utilisation

Je m'aide souvent des script décrit dans ce post pour régler finement le serveur.

Au niveau matériel, ceux qui influe le plus est la rapidité des disques et beaucoup de mémoire vive.

Au niveau de l'OS, je ne connais rien à optimiser. Si vous trouvez ça m'interresse.

Concernant les requetes, il faut bien faire attention car parfois l'optimiseur se perds. Surtout je pense quand beaucoup de champs sont index. Il commence pas forcément par la table ququel on s'attends. Un moyen de controler l'optimiseur est l'utilisation du

Code :

USE INDEX

[frite333]

C'est a peu près les paramètres que j'avais identifié mais d'autres me sont encore inconnus, je vais étudié cela.

Je vais aussi de ce pas regarder la page de script.

En tout cas merci de ta réponse. Je reposterais ici, si je trouve une quelconque optimisation pour l'OS

A bientôt

[frite333]

Excusez moi, j'ai une autre question.

J'ai modifier les paramètres du serveur donc je suis passé pour l'exécution de ma requête de 360 secondes à 240 secondes. A partir de là, je ne descend pas plus bas.

Je précise que je teste pour l'instant uniquement UNE seule requête, il n'y a aucune autre connection sur mon serveur pendant ce temps.

J'ai même essayé d'augmenter à un point déraisonnable les valeurs, rien n'y fait.

Comment puis-je réduire le temps d'exécution de cette unique requête juste avec la configuration du serveur ?

[qi130]

Envoyé par frite333

Les champs de la clause WHERE ont été indexés

Et les autres champs qui participent à un order éventuel, ceux des jointures, du group by ?

[overider]

il est également possible d'indexer sur deux champs.

Code :

CREATE INDEX index_nom_prenom ON utilisateur (nom,prenom)

Cela peut être plus performants cela depends vraiment de l'utilisation .

[patic]

Bonjour,
Je vais peut être dire un truc bête, mais si la situation le permet, c'est à dire si vous pouvez vous permettre d'avoir des données à J+1, pourquoi ne pas créer une table qui regroupe toutes vos colonnes nécessaires, en gros dénormaliser, puis indexer les bonnes colonnes ? Vous mettrez cela à jour chaque nuit.
Une autre idée, ne serait il pas possible de créer une vue qui regroupe ces tables, sur certaines bdd je sais qu'on peut indexer les colonnes d'une vue, ce serait peut être plus rapide.
Sinon, est ce que le faire via une simple procédure stockée n'améliore pas un peu le temps.
Bon courage

[frite333]

Merci pour vos réponses !

Tout d'abord, au niveau des index tout a été fait, dans les requêtes ainsi que dans les sous requêtes et bien sur aussi sur plusieurs champs.

@patic : Ta solution n'est pas réalisable. Par contre les vues et les procédures stockés ont déjà été testés mais n'améliorent pas du tout le temps d'exécution sur une telle masse de données.

Je reprécise par ailleurs que je recherche plutôt une solution au niveau de la configuration du serveur (et de l'OS si c'est possible).

Pour l'instant grâce à la configuration j'ai pu atteindre une amélioration globale de mes requête de 20% sur le temps d'exécution.
Je crois comprendre que lors du traitement des requêtes, la création de tables temporaire ne peut se faire en mémoire (dû à la taille des tables concernés) et se fait donc sur le disque.
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 7 |
Cependant ces données issus d'un SHOW STATUS après 3 requêtes identiques montrent qu'il n'a pas fait de tables temporaires sur le disque mais par contre à créé des fichiers temporaires (es ce qu'ils contiennent des tables temporaires ?).
Si c'est le cas, alors le temps d'exécution est dû à la rapidité de lecture/écriture du disque dur ? Et simplement cela ?

Que ce soit le cas ou non, connaissez-vous une méthode/technique/astuce pour optimiser le traitement de mes requêtes ?

Je vous remercie d'avance, pour toute l'aide que vous m'apportez.

[ddoumeche]

Bonjour,

ta question est encore trop vague. Il nous faudrait savoir :
- la version du serveur Mysql utilisé
- le SHOW CREATE TABLE de ta table
- un exemple de requete très lente.

En ce qui concerne le fichier my.cnf cité en exemple :
- le key_buffer_size est trop petit, il ne faut pas hésiter a réserver 1/3 de la RAM pour celui-ci
- le innodb_buffer_pool_size joue le même rôle que key_buffer_size mais pour les tables innodb. Ne pas hésiter à lui réserver beaucoup de mémoire.

[SQLpro]

Envoyé par frite333

Si ca continue je vais devoir tester sous d'autres SGBD, comme postgreSQL, pour de meilleurs performances

Dans votre cas et compte tenu que vous faites des requêtes d'agrégation, si vous voulez des performances il faut un SGBDR qui sache faire des vues indexées.
Seules solutions : Oracle (mais les vues matérialisées sont asynchrone et le serveur est cher) ou MS SQL Server (vues indexées synchrones).
Pour démo, voir l'étude que j'ai mené sur l'amélioration d'une requête portant sur 1 250 000 lignes, faisant passé le temps de réponse que quelques seconds à non mesurables....
http://sqlpro.developpez.com/optimisation/indexation/

Enfin sur les performances de MySQL, ce SGBD (pseudo relationnel) n'a jamais été conçu pour les performances.... contrairement à une légende...
Lisez les articles que j'ai écrit à ce sujet :
MySQL un ersatz de SGBDR : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/
Benchmark SQL Server / PostGreSQL / MySQL : http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/

A +

[frite333]

Encore une fois merci pour vos réponses

@ddoumeche
Encore une fois ma question ne porte ni la structure des tables, ni la requête.
Au niveau du key_buffer_size, vous avez raison et je pense que le mien est bien réglé.

@SQLpro
Je pense que c'est exactement la réponse que j'attendais. Le problème c'est qu'il est difficilement réalisable dans le cas présent d'envisager un changement de SGBD.
Quoi qu'il en soit, je pense être arrivé au maximum des possibilités de MySQL et au bout de ma question. Je passe mon post en résolu.
Je vais donc lire votre étude, et faire mes propres test.

Je vous remercie tous pour votre aide. A bientôt !

[patic]

Bonjour,
Vous y gagnerez peut être un peu avec une mise à jour de mysql vers la dernière version (5.6) ... Je ne pense pas que cela impactera beaucoup vos développements en cours.

[frite333]

Je ne pense pas que ce sera influant.
Certaines des plus grosses requêtes prennent plusieurs heures, je ne pense pas qu'un mise a jour de mysql permettrait une grande amélioration du temps de traitement :s

[ericd69]

Salut,

Une solution, peut-être, est de voir si tu peux scinder tes données grâce à un système de hash et donc te retrouver à brasser des tables plus petites... diviser pour mieux règner