Discussion :

[Théolude]

Bonjour à tou(te)s,

J'ai un serveur MySQL qui cafouille.

Il est installé sur une debian 6.1 (noyau 2.6.26-2-686, SMP activé), elle même est une VM installée sur une architecture VMWare 4.0. Il y a 4Go de RAM et 4 processeurs en 3Ghz.

Il s'agit de MySQL 5.0.51a-24+lenny1-log.

La VM n'indique aucune charge particulière, RAM et CPU ne plafonnent JAMAIS, le max est aux alentours de 30% de CPU, et 10% de RAM.

Je fais un test, je lance une très grosse requête qui sature MySQL. Quand je fais un top je ne vois toujours qu'un seul processus MySQL, et il sature à 100 ou un peu plus (116%) de RAM !!. Et toutes les autres demandes de connexions, toutes les autres requêtes SQL sont refusées.

J'ai l'impression que MySQL ne fonctionne que sur un seul processeur, qu'il n'y a pas de multithread mais qu'une seule très longue file d'attente.......

Voici le fichier de configuration de MySQL affiné pas à pas avec tuning-primer.sh.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
key_buffer              = 8M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
 
myisam-recover          = BACKUP
max_connections        = 200
connect_timeout         = 10
join_buffer_size        = 512K
key_buffer_size         = 300M
query_cache_size        = 8M
table_cache             = 560
thread_concurrency     = 10
query_cache_limit       = 1M

Merci de m'aider à trouver une solution pour redonner un peu de peps à mon serveur

Théo

[Théolude]

Bonjour,

Dans la même lignée que mon thread sur l'optimisation MySQL voici la requête qui rend mon serveur atone....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT f.fournisseur, f.nom, YEAR(c.date) AS annee,
SUM(b.quantite) / SUM(d.quantite) AS tauxRuptureQuantite,
SUM((b.prixNetUnitaire / b.quantite) * (b.quantite - d.quantite)) AS caPerdu,
SUM(b.prixNetUnitaire) AS caCommande,
SUM((b.prixNetUnitaire / b.quantite) * d.quantite) AS caLivre
FROM commandes a
INNER JOIN lignes_commandes b ON a.commande = b.commande AND b.annulee = 0
INNER JOIN bons_livraisons c ON a.commande = c.commande
INNER JOIN lignes_livraisons d ON c.bonLivraison = d.bonLivraison AND b.article = d.article AND b.collection = d.collection
INNER JOIN produits e ON b.article = e.codeProduit
INNER JOIN fournisseur f ON e.fournisseur = e.fournisseur AND f.inactif = 0
WHERE a.validee = 1
AND a.annule = 0
AND ((c.date <= STR_TO_DATE(CONCAT(YEAR(NOW()) - 1, "-", MONTH(NOW()), "-", DAY(NOW())), "%Y-%m-%d") AND c.date >= STR_TO_DATE(CONCAT(YEAR(NOW()) - 1, "-01-01"), "%Y-%m-%d")) 
OR (c.date <= NOW() AND c.date >= STR_TO_DATE(CONCAT(YEAR(NOW()), "-01-01"), "%Y-%m-%d")))
GROUP BY f.fournisseur, YEAR(c.date)

Un explain de cette requête me renvoie ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	f	ref	inactif	inactif	1	const	35	Using temporary; Using filesort
1	SIMPLE	c	range	PRIMARY,commande,date	date	3	\N	3490	Using where
1	SIMPLE	a	eq_ref	PRIMARY,validee,annulee	PRIMARY	8	terreetdecor.c.commande	1	Using where
1	SIMPLE	d	ref	PRIMARY	PRIMARY	8	terreetdecor.c.bonLivraison	22	
1	SIMPLE	e	eq_ref	codeProduit	codeProduit	17	terreetdecor.d.article	1	Using where
1	SIMPLE	b	ref	PRIMARY,collection,article	PRIMARY	8	terreetdecor.c.commande	23	Using where

Rien de très monstrueux.... et pourtant ça mouline des heures !!!!!!

Merci de me dire ce qui cloche et comment je pourrai améliorer la chose (tuning MySQL compris)...

[djjnet]

bonjour,
vu ce que vous indiquez, je ne suis pas sur que ce soit la configuration mysql le problème.
avez vous fait un :
explain requete_sql \G
pour voir s'il ne manquait pas un indexe à votre requête ?

sur vos paramètres, le cache de requête est très faible, donc mysql ne pourra pas mettre beaucoup de requête dans celui-ci

[djjnet]

bonjour,
votre requête utilise les tables temporaires, essayez :
max_heap_table_size = 32M
tmp_table_size = 32M

si les valeurs sont à l'origine 16M.

après c'est difficile sans l'état du serveur, j'explique sur mon site comment optimiser mysql :
http://www.dj-j.net/waka/Linux:Administration_MySQL

[Théolude]

Bonjour,

Votre site est très intéressant
J'ai remodifié la configuration de MySQL en m'inspirant et de vos conseils et du résultat de tuning-primer.sh.

Le contexte est le suivant:
Un applicatif sur une base LAMP, peu d'utilisateurs (une quarantaine), de très nombreux SELECT, quelques INSERT, UPDATE et REPLACE (oui je sais c'est pas propre mais très pratique), et quelques fois de TRES GROS SELECT. La base de donnée est conséquente (230 tables), et la taille des tables peut être considérable (1 900 000 tuples sur 7 champs actuellement pour la plus grosse).

Voici la nouvelle configuration:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
key_buffer              = 8M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
max_connections         = 200
connect_timeout         = 10
join_buffer_size        = 512K
key_buffer_size         = 100M
query_cache_size        = 1G
query_cache_limit       = 20M
sort_buffer_size        = 10M
read_buffer_size        = 8M
table_cache             = 560
open_files_limit        = 1120
thread_concurrency      = 30
query_cache_limit       = 12M
max_heap_table_size     = 64M
tmp_table_size          = 64M

J'ai lancé tuning-primer.sh après avoir rebooté le serveur. Je dispose je le rappel de 4Go de RAM alloué sur ma VM.

Voici un des résultats de tuning-primer.sh qui m'a laissé pantois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 1 G
Configured Max Memory Limit : 4 G
Physical Memory : 2.96 G
 
Max memory limit exceeds 90% of physical memory

Je ne comprends pas pourquoi il me dit ça...
Par ailleurs que pensez-vous de ma nouvelle configuration ???

Je suis en train de me dire que j'ai peut être intérêt à créer un cluster, de manière à ne pas asphyxier le serveur quand une grosse requête est lancée (quelques fois par semaine), c'est un peu le char pour tuer la mouche, mais je ne vois pas d'autres solutions à aujourd'hui.

Merci de vos conseils, commentaires et retours d'expériences, Théo

[djjnet]

max_connections = 200
join_buffer_size = 512K
sort_buffer_size = 10M
read_buffer_size = 8M

Avec ces paramètres, vous pouvez allouer jusqu'à 4Go de RAM pour les caches de sessions, d'où le message de tuning primer.
Il faut soit diminuer le max_connections soit les caches.

Mais encore une fois, je ne suis pas sur que votre problème sur la grosse requête soit du aux paramètres mysql mais plutôt à la requête en elle même surtout si elle travaille sur des tables avec des millions de lignes.

Là il faut voir si les disques supportent de gros I/O ou si vous ne pouvez pas faire de l'archivage sur vos grosses tables.

Si vous souhaitez, je peux vous proposer un audit de votre serveur.

[CinePhil]

Il y a effectivement quelque chose qui me dérange dans cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INNER JOIN lignes_livraisons d ON c.bonLivraison = d.bonLivraison 
    AND b.article = d.article 
    AND b.collection = d.collection

Vous avez trois conditions de jointures avec deux tables différentes pour une seule instance de la table lignes_livraisons, laquelle n'est pas la table située derrière le FROM.

Il vaudrait mieux faire une autre instance de lignes_commandes je pense :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INNER JOIN lignes_livraisons d ON c.bonLivraison = d.bonLivraison 
    INNER JOIN lignes_commandes b2 ON b2.article = d.article 
      AND b2.collection = d.collection

Il y a une erreur d'alias dans cette jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INNER JOIN fournisseur f ON e.fournisseur = e.fournisseur

Il faudrait plutôt écrire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INNER JOIN fournisseur f ON e.fournisseur = f.fournisseur

Ceci explique peut-être cela :

id select_type TABLE type possible_keys KEY key_len ref rows Extra
1 SIMPLE f ref inactif inactif 1 const 35 USING TEMPORARY; USING filesort

Je crois comprendre, au format indiqué dans la fonction STR_TO_DATE, que la colonne commande.date (mauvaise idée d'appeler une colonne 'date', c'est un mot du langage SQL !) est déjà de type DATE ?
Dès lors, ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.date <= STR_TO_DATE(CONCAT(YEAR(NOW()) - 1, "-", MONTH(NOW()), "-", DAY(NOW())), "%Y-%m-%d")

Peut être simplifié en cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.date <= DATE_ADD(CURRENT_DATE - INTERVAL 1 YEAR)

Ça ne doit pas influer sur les performances de manière significative et MySQL est permissif mais les valeurs textuelles doivent être écrites entre parenthèses et non pas entre guillemets.

Les identifiants, et par conséquent les clés étrangères, utilisés dans les conditions de jointures sont-ils bien de type entier ?
Les clés étrangères sont-elles indexées ?

[Théolude]

Il y a effectivement quelque chose qui me dérange dans cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INNER JOIN lignes_livraisons d ON c.bonLivraison = d.bonLivraison 
    AND b.article = d.article 
    AND b.collection = d.collection

Vous avez trois conditions de jointures avec deux tables différentes pour une seule instance de la table lignes_livraisons, laquelle n'est pas la table située derrière le FROM.

Il vaudrait mieux faire une autre instance de lignes_commandes je pense :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INNER JOIN lignes_livraisons d ON c.bonLivraison = d.bonLivraison 
    INNER JOIN lignes_commandes b2 ON b2.article = d.article 
      AND b2.collection = d.collection

Oui mais dans ce cas je suis dépendant de la structure de la BDD. Il faut que je retrouve une ligne de commande équivalente dans le bon de livraison, or la clef du bon de livraison n'est pas reprise dans lignes_livraisons... par contre article et collection y sont, et je veux être sur du match entre la ligne livraison et la ligne commande... Je sais c'est tordu

Il y a une erreur d'alias dans cette jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INNER JOIN fournisseur f ON e.fournisseur = e.fournisseur

Il faudrait plutôt écrire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INNER JOIN fournisseur f ON e.fournisseur = f.fournisseur

Ceci explique peut-être cela :

Ouppppppppppppppppppppppps
Comme quoi un regard neuf Merci !!

Je crois comprendre, au format indiqué dans la fonction STR_TO_DATE, que la colonne commande.date (mauvaise idée d'appeler une colonne 'date', c'est un mot du langage SQL !) est déjà de type DATE ?
Dès lors, ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.date <= STR_TO_DATE(CONCAT(YEAR(NOW()) - 1, "-", MONTH(NOW()), "-", DAY(NOW())), "%Y-%m-%d")

Peut être simplifié en cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.date <= DATE_ADD(CURRENT_DATE - INTERVAL 1 YEAR)

Merci de l'astuce Je ne connaissais pas CURRENT_DATE.
Sur le nom du champ, oui je me suis un peu laisser aller ....

Les identifiants, et par conséquent les clés étrangères, utilisés dans les conditions de jointures sont-ils bien de type entier ?
Les clés étrangères sont-elles indexées ?

Oui c'est la première chose que j'ai vérifié.... et ajouter un index au passage. Mais c'était avant d'aller sur le forum, l'EXPLAIN afficher est correct et up to date.

Merci pour votre aide, j'essaye avec vos corrections.

Théo

[Théolude]

La seule correction sur l'erreur de jointure a tout résolu !!!!! Temps de requête divisé par 10 !!!

La leçon de tout ça:
1. Relire et relire et relire encore.... et relire toujours
2. EXPLAIN c'est très bien, mais encore faut il être en mesure de le lire finement et de faire attention aux détails

Bonne journée et merci !!