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.