Discussion :

[eratox]

Bonjour,

Voilà mon problème.
Pour une même requêtes, le résulta de EXPLAIN est différent quand il y a une entrée supplémentaire dans la table FC_poster.


Structure de la DB :

Code :

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

1
2
3
4
 
FC_name      3 074	MyISAM	utf8_unicode_ci	448,6 Kio
FC_poster	61 200	MyISAM	utf8_unicode_ci	4,7 Mio
FC_ratings	61 168	MyISAM	utf8_unicode_ci	9,0 Mio

Structure de la table FC_poster :

Code :

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

1
2
3
4
5
6
7
 
Champ	Type
id	mediumint(5)
file	varchar(80)
dater	datetime
id_name	smallint(5)
id_account	mediumint(6)

Profilage - EXPLAIN - résult

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
Profilage
État	Durée
(initialization)	0.000007
Opening tables	0.000015
System lock	0.000004
Table lock	0.000005
init	0.000004
optimizing	0.000003
statistics	0.000004
preparing	0.000004
executing	0.000014
Sending data	0.000008
end	0.000004
query end	0.000007
freeing items	0.000004
closing tables	0.000003
removing tmp table	0.000005
closing tables	0.000004
logging slow query	0.000003
 
requête SQL
requête SQL:
EXPLAIN SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <= '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45
 
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	T_poster	index	id_name	dater	8	NULL	61200	 
1	SIMPLE	FC_name	eq_ref	PRIMARY,born	PRIMARY	2	cfake.T_poster.id_name	1	Using where
1	SIMPLE	FC_ratings	eq_ref	id	id	3	cfake.T_poster.id	1

Et voilà le résulta avec un enregistrement supplémentaire dans la table FC_poster (donc 61201 entrées)

Profilage - EXPLAIN - résult

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Profilage
État	Durée
(initialization)	0.000037
Opening tables	0.000074
System lock	0.000009
Table lock	0.000014
init	0.000016
optimizing	0.00001
statistics	0.000016
preparing	0.000013
executing	0.008459
Sending data	0.000214
end	0.00001
query end	0.00001
freeing items	0.000013
closing tables	0.000013
removing tmp table	0.000023
closing tables	0.000292
logging slow query	0.000011
 
requête SQL
requête SQL:
EXPLAIN SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <= '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	FC_name	ALL	PRIMARY,born	NULL	NULL	NULL	3074	Using where; Using temporary; Using filesort
1	SIMPLE	T_poster	ref	id_name	id_name	2	cfake.FC_name.id	20	 
1	SIMPLE	FC_ratings	eq_ref	id	id	3	cfake.T_poster.id	1

On notera que dans le profilage l'exécution prend bcp de temps (puis mysqld rame à fond).

Ce post fait suite à ce 1er post : http://www.developpez.net/forums/d85...ration/entree/

[ionesco]

Il faut que tu saches que MySQL n'exécute pas une requête de la même manière selon le nombre d'enregistrements des tables concernées par la requête.

Tu vois les indexes possèdent des cardinalités, ces mêmes cardinalités qui sont utilisées par MySQL pour faire son choix d'employé tel ou tel index.
MySQL prendra l'indexe qui aura le plus petit 'ROW' dans le explain.

Ainsi ton enregistrement supplémentaire boulverse les choses.


Si tu n'as pas compris quelque chose, dis le moi.

[eratox]

J'ai bien compris et je te remercie.
Je ne connaissais pas cet élément de fonctionnement.

Mais malheureusement pour moi cela veux dire que mon probleme ne viens pas de là.

Car je ne sais si tu as vu le lien, qui est mon problème.

Je ne peux plus effectuer de nouvelle enregistrement, car le process mysqld sature RAM et CPU.

[ionesco]

1) le profilage est-il fait sur la requête "EXPLAIN SELECT ...." ou bien sur la requête "SELECT ...."? Car je vois une bizarrerie: le 2ème EXPLAIN indique que la requête utilise une table temporaire, or cette étape n'apparait pas dans le profilage!

2) Le nouvel enregistrement fait que MySQL va écrire sur disque dur car il crée une table temporaire pour résoudre la requête et le tri sur la date en rajoute une couche. Quels sont les caractéristiques de tes DDUR? Les I/O sont mis à rude épreuve. Renseigne toi de ce côté là si tes ddur sont performant.

Sinon peux-tu essayer cela et donner le résultat?

Code :

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

1
2
3
4
5
6
7
EXPLAIN SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name FORCE INDEX(PRIMARY) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <= '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45

[eratox]

1) Les 2 profilage sont effectués sur les requêtes "EXPLAIN SELECT .. "

2) Je me suis jamais trop intéresser au I/O HD. J'ai utilisé le prog iostat, voilà le résulta :

Code :

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

1
2
3
4
5
6
7
8
9
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          19,76    0,00    8,80   13,50    0,00   57,95
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              97,44      5469,69       452,86 7041652991  583014002
sda1              4,85        17,89        71,65   23027210   92241352
sda2             92,33      5446,55       370,85 7011864731  477430938
sda3              0,26         5,25        10,36    6760890   13341712

Et voilà le df -h :

Code :

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

1
2
3
4
5
6
7
8
 
Sys. de fich.            Tail. Occ. Disp. %Occ. Monté sur
/dev/sda1             9,7G  2,9G  6,3G  32% /
varrun                3,9G   72K  3,9G   1% /var/run
varlock               3,9G     0  3,9G   0% /var/lock
udev                  3,9G   20K  3,9G   1% /dev
devshm                3,9G     0  3,9G   0% /dev/shm
/dev/sda2             677G   49G  594G   8% /home

Mais pour ma part ce que je ne comprend pas c'est que ça marche bien en l'état et qu'en ajoutant 1 entrée plus rien ne marche le process mysqld sature tout, jusqu'à ne plus pouvoir utiliser ssh (very low). Voir même un reboot machine pour reprendre la main.

Comment une seule entrée peut modifier autant les performances du SGBD, de la RAM, du CPU et du system tout entier !!?

Je suis dans le flou, le brouille, le noir complet !

Voilà l'exécution de la requête que tu proposais :

Avant l'ajout de "l'entrée tueuse".

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Profilage
État	Durée
(initialization)	0.000023
Opening tables	0.000037
System lock	0.000003
Table lock	0.000006
init	0.000004
optimizing	0.000003
statistics	0.000007
preparing	0.000004
executing	0.000022
Sending data	0.000016
end	0.000003
query end	0.000003
freeing items	0.000004
closing tables	0.000002
removing tmp table	0.000007
closing tables	0.000005
logging slow query	0.000003
 
requête SQL
requête SQL:
EXPLAIN SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45
 Profilage [ Modifier ] [ Ne pas expliquer SQL ] [ Créer source PHP ]
 
 
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	T_poster	index	id_name	dater	8	NULL	61200	 
1	SIMPLE	FC_name	eq_ref	PRIMARY	PRIMARY	2	cfake.T_poster.id_name	1	Using where
1	SIMPLE	FC_ratings	eq_ref	id	id	3	cfake.T_poster.id	1

La même exécution une fois l'ajout de d'une entrée supplémentaire.

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Profilage
État	Durée
(initialization)	0.000016
Opening tables	0.000026
System lock	0.00001
Table lock	0.000013
init	0.000012
optimizing	0.000011
statistics	0.000012
preparing	0.000011
executing	0.000022
Sending data	0.000015
end	0.00001
query end	0.00001
freeing items	0.000011
closing tables	0.00001
removing tmp table	0.000012
closing tables	0.000012
logging slow query	0.00001
 
requête SQL
requête SQL:
EXPLAIN SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45
 Profilage [ Modifier ] [ Ne pas expliquer SQL ] [ Créer source PHP ]
 
 
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	FC_name	ALL	PRIMARY	NULL	NULL	NULL	3074	Using where; Using temporary; Using filesort
1	SIMPLE	T_poster	ref	id_name	id_name	2	cfake.FC_name.id	20	 
1	SIMPLE	FC_ratings	eq_ref	id	id	3	cfake.T_poster.id	1

[CinePhil]

Je pense qu'avec la donnée supplémentaire, MySQL, à l'aide des stats sur le volume de données à traiter, détecte qu'il ne pourra pas tout traiter en mémoire alors il décide de passer par une table temporaire et les perfs s'en ressentent.

[eratox]

Oui il semble que c'est ce qu'il se passe.

Qu'est ce qu'il est possible de faire :

- faire des modifs dans la conf de mysql
- updater le hardware
- ...

J'ai une DB qui fait 15Mo et 8Go de RAM!

[ionesco]

les profilages sont à donner pour les requêtes sans le MOT EXPLAIN !!!!

[eratox]

Alors voilà sans EXPLAIN.

Avant l'ajout :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Profilage
État	Durée
(initialization)	0.000014
Opening tables	0.000014
System lock	0.000003
Table lock	0.000005
init	0.000004
optimizing	0.000003
statistics	0.000005
preparing	0.000004
executing	0.000014
Sending data	0.000008
end	0.000003
query end	0.000003
freeing items	0.000004
closing tables	0.000003
removing tmp table	0.000005
closing tables	0.000004
logging slow query	0.000003
 
Affichage des enregistrements 0 - 29 (45 total, Traitement en 0.0016 sec.)
requête SQL:
SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45

Après l'ajout :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Profilage
État	Durée
(initialization)	0.000038
Opening tables	0.0000660
System lock	0.000014
Table lock	0.000017
init	0.000019
optimizing	0.000015
statistics	0.000021
preparing	0.000017
executing	0.000032
Sending data	0.0000900
end	0.000013
query end	0.000013
freeing items	0.000013
closing tables	0.000012
removing tmp table	0.000016
closing tables	0.000014
logging slow query	0.000012
 
Affichage des enregistrements 0 - 29 (45 total, Traitement en 3.8001 sec.)
requête SQL:
SELECT T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45

[ionesco]

tes profilages ne sont pas fiable car la requête est en cache.

recommence le profilage avec SELECT remplacé par SELECT SQL_NO_CACHE (toujours sans le explain)

[eratox]

Avant l'ajout :
mysqld %CPU 40 à 150 (variable)

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
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Profilage
État	Durée
(initialization)	0.000017
Opening tables	0.000019
System lock	0.000003
Table lock	0.000006
init	0.000005
optimizing	0.000004
statistics	0.000005
preparing	0.000005
executing	0.000019
Sending data	0.000009
end	0.000003
query end	0.000003
freeing items	0.000004
closing tables	0.000003
removing tmp table	0.000006
closing tables	0.000005
logging slow query	0.000003
 
Affichage des enregistrements 0 - 29 (45 total, Traitement en 0.0161 sec.)
requête SQL:
SELECT SQL_NO_CACHE T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45

Après l'ajout :
mysqld %CPU plus de 280 (variable)

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
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Profilage
État	Durée
(initialization)	0.000031
Opening tables	0.000043
System lock	0.000004
Table lock	0.000011
init	0.00001
optimizing	0.000005
statistics	0.000014
preparing	0.000011
executing	0.000023
Sending data	0.000107
end	0.000004
query end	0.000004
freeing items	0.000004
closing tables	0.000004
removing tmp table	0.000005
closing tables	0.000005
logging slow query	0.000003
 
Affichage des enregistrements 0 - 29 (45 total, Traitement en 0.4289 sec.)
requête SQL:
SELECT SQL_NO_CACHE T_poster.id, T_poster.file, T_poster.dater, FC_ratings.hits, FC_ratings.total_votes, FC_name.name, FC_name.country, FC_name.id AS IDName
FROM FC_poster AS T_poster
LEFT JOIN FC_name
FORCE INDEX ( 
PRIMARY ) ON ( T_poster.id_name = FC_name.id ) 
LEFT JOIN FC_ratings ON ( T_poster.id = FC_ratings.id ) 
WHERE FC_name.born <=  '01-01-1990'
ORDER BY T_poster.dater DESC 
LIMIT 0 , 45

[ionesco]

Il y a une chose que je en comprend pas c'est le profilage ne correspond pas au temps total de traitement! Normalement en additionnant tout on retombe sur le temps de traitement. Et là ce n'est pas le cas.

[eratox]

Je ne sais quoi te dire, j'exécute les requêtes via phpmyadmin dans la zone de texte prévu à l'exécution de requête et je coche l'option profilage rien de plus ou de moins.

Mais je pense pas que cela puisse venir de la requête.
Une requête qui à marcher de 1 enregistrement à 61200 enregistrement, sans broncher.

C'est quelque chose de structurelle, ou quelque chose qui fait que MySql essaye quelque chose qui bloque tout.

[ionesco]

je ne sais pas si c'est possible mais ce qui serait bien c'est de laisser tourner avec l'enregistrement supplémentaire pendant 24h ou 48h et ensuite faire un coup de primer_tunning.sh comme tu as posté dans ton autre poste. Je suis certain à 100% que ca changera radicalement de ton ancien poste et on pourra voir le tunning pointu qu'on devra mener.

[eratox]

Non, c'est pas possible après quelques minutes le swap et au max CPU max, l'accès ssh very low et après 5 à 10 min plus d'accès ssh "connexion close", impossible de reconnecter donc restart de la machine donc le resulta du tunning.sh va se réinitialisé quand mysql va restart avec la machine.

Le système ne supporte pas la montée en charge.