stratégies pour les index

**venomelektro** · 24/01/2011, 16h31

Bonjour,

j ai une question pour un projet qui commence a avoir une base assez conséquente (> 100.000 entrées dans plusieurs tables et des jointures)

il s agit d un projet utilisant un moteur de recherche

on peut rechercher des inscrits a un concours par ville, prenom, nom ou genre (garcon/fille) + quelques autres criteres

dans le moteur aucun champ n est obligatoire

faut il mieux créer un index avec ville+nom+prenom+genre ou des index séparés sur nom/prenom/ville/genre

voire plusieurs combinaisons pour accélérer les performances?

**CinePhil** · 24/01/2011, 16h50

Un index sur genre ne sera probablement pas utilisé par l'optimiseur car il ne comporte que trop peu de valeurs distinctes.

Pour le reste, je te renvoie chez SQLPro qui a donné des principes sur l'optimisation grâce aux index :
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/optimis...ntenanceIndex/

Et aussi un exemple frappant :
http://sqlpro.developpez.com/optimisation/indexation/

**venomelektro** · 25/01/2011, 11h55

Bonjour,

Merci pour la réponse.

j ai réussi à optimiser les temps en rajoutant des index sur les champs de recherche, mais je sèche sur des optimisations supplémentaires

pour info , ma db est sur un serveur assez costaud avec 24Go de RAM , ce que je ne comprends pas est que le serveur est presque IDLE (conso de RAM et proc base alors que MySQL mets parfois 2 minutes a venir a bout de certaines requêtes.

suite aux remaniements des index, c est mieux, mais j ai toujours des requêtes a 5-6 secondes alors .

visiblement les requêtes, dont voici un exemple plus bas , utilisent une table temporaire.

J ai reussi a optimiser en indiquant a mysql d utiliser la ram (avec tmp_table_size, max_heap_table_size)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT nom_participant, prenom_participant, ville_participant, 
  libre1_participant, libre2_participant, libre3_participant, libre4_participant, 
  categorie_participant, commentaire_participant, id_relooking_participant, 
  langue_participant, categorie_participant, id_participant_etape, 
  PARTICIPANT.id_participant, id_etat_participant, 
  PARTICIPANT_ETAPE.nombre_votes  
FROM PARTICIPANT 
INNER JOIN PARTICIPANT_ETAPE ON PARTICIPANT.id_participant = PARTICIPANT_ETAPE.id_participant  
WHERE PARTICIPANT_ETAPE.id_etape = 2 
  AND PARTICIPANT_ETAPE.supprime = 0  
  AND avec_photos_participant=1 
ORDER BY PARTICIPANT_ETAPE.date_creation_participant_etape DESC 
LIMIT 520,8;

visiblement ce genre de requêtes demande a mysql une analyse d un tres grand nombre de lignes

# Query_time: 5 Lock_time: 0 Rows_sent: 8 Rows_examined: 118783

ce que je ne comprends pas ,c est la valeur elevé de "Rows_examined", vu que des index sont présent sur chacune des conditions.

Quelles seraient a votre avis les optimisations possibles ? création d'une VIEW ? utilisation de FORCE INDEX ? autre suggestions?

Merci d avance.

**Feng-Huang** · 25/01/2011, 12h17

Mon expérience dans les VLDB est pauvre (max de 300k tuples), mais as-tu songé à utiliser un autre support que MySQL pour la recherche ? Un Lucene peut-être ?
Ok c'est autre chose à mettre en place mais ça te permettrait de fournir un outil plus complet d'une part, et de laisser ton SGBD souffler d'autre part.

Sinon je rejoins CinePhil pour le genre.
En outre si toutes les recherches tu as au minimum ville+nom+prenom , alors oui tu devrais créer un index composite, sinon un index "au détail"

**venomelektro** · 25/01/2011, 12h25

je ne connais pas lucene, mais je vais me renseigner dessus.

Sinon pour info, j utilise deja memcache pour laisser mysql souffler, mais les critieres de recherche étant nombreux (et non obligatoire) , impossible de cacher toutes les recherches.

C est par contre les efficace pour requetes de base (ex : affichage de la HP avec les derniers participants, qui sont memcachés 5 minutes)

**venomelektro** · 25/01/2011, 12h47

il y a tout de meme un probleme que je ne comprends pas, mysql semble mal se débrouiller pour utiliser les ressources de la machine

il est dans les choux vite alors que la machine (serveur ovh mg ssd avec 24 Go de ram et Bi Xeon E5504 en proc) semble etre IDLE

Code :

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

1
2
3
4
5
6
7
8
9
 
#free -m
             total       used       free     shared    buffers     cached
Mem:         24148       2078      22069          0        197       1054
-/+ buffers/cache:        826      23321
 
# uptime
 
 12:47:06 up 19:01,  1 user,  load average: 0.18, 0.07, 0.01

sur quel parametres puis je agir pour forcer mysql a se mettre plus a l aise ?

j ai deja mis des valeurs importantes sur les points suivants:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
tmp_table_size = 5000M
max_length_for_sort_data=4096
max_heap_table_size = 5000M
#
# * Query Cache Configuration
#
query_cache_limit       = 1024M
query_cache_size        = 10192M

**CinePhil** · 25/01/2011, 13h48

Que donne un EXPLAIN de ta requête ?

Y a t-il un index sur PARTICIPANT_ETAPE.id_etape ?

Je suppose que PARTICIPANT_ETAPE.supprime et avec_photos_participant sont des booléens ?
Si oui, inutile de mettre un index dessus je pense.

Ça ne coûte rien et ça économise un petit temps de recherche pour le SGBD : précise de quelle table vient la colonne avec_photos_participant.

Bien sûr, il y a des index sur les colonnes de la condition de jointure PARTICIPANT.id_participant et PARTICIPANT_ETAPE.id_participant ?

Un index sur PARTICIPANT_ETAPE.date_creation_participant_etape peut aussi être utile puisque c'est la colonne à ordonner.

Mais effectivement, avec 24 Go de RAM sur le serveur et seulement 118 783 lignes apparemment traitées, la requête devrait être instantanée.

Combien y a t-il réellement de lignes dans les deux tables de cette requête ?

Ce qui peut ralentir peut-être, c'est le LIMIT 520, 8.

**venomelektro** · 25/01/2011, 14h20

resultat de explain

Code :

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

1
2
3
4
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	PARTICIPANT_ETAPE 	ref 	id_participant_2,id_participant,id_etape,supprime,... 	id_etape 	4 	const 	25456 	Using where; Using filesort
1 	SIMPLE 	PARTICIPANT 	eq_ref 	PRIMARY,avec_photos_participant 	PRIMARY 	4 	kiabi.PARTICIPANT_ETAPE.id_participant 	1 	Using where

il y a bien des index sur les deux id_participant et sur date_creation_participant_etape (ainsi que sur ville, nom et prenom)

le limit est utilisé dans le cas d une navigation par page.

nombre de lignes :

PARTICIPANT : 51 213

PARTICIPANT_ETAPE : 102 043

**CinePhil** · 25/01/2011, 14h35

Ce n'est vraiment pas beaucoup, même sur un simple PC !
Et le EXPLAIN montre que l'index permet de ne traiter que 25457 lignes.

La réponse evrait donc être instantanée. Je pense qu'il y a un problème de config serveur.

La requête directement exécutée sur le serveur en mode console, si tu peux y avoir accès, ou via phpMyAdmin donne quel temps de réponse ?

**venomelektro** · 25/01/2011, 14h47

je soupçonne en effet un problème serveur , j ai notamment cette erreur un peu louche dans mon syslog

Code :

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

1
2
 
mysqld[18107]: 110125 14:18:45 [ERROR] /usr/sbin/mysqld: Sort aborted

je ne pense pas que ca soit le hardware ou le système qui faute, mais plutôt mysql, malheureusement ma config est tout ce qu il y a de plus normale, j ai grosso modo la même config sur plusieurs autres machines sans soucis.

j ai monté le sort_buffer_size pour voir si les choses s arrangent.

Edit : est ce que ca te semble pas beaucoup avec entre 200 et 300 connections ouvertes (réseaux) en simultanée ?

**skuatamad** · 25/01/2011, 18h45

Il faut peut être recalculer les stats de la table avec ANALYSE.
Sinon oui je pense que c'est le ORDER BY qui plombe, y a t il un index sur PARTICIPANT_ETAPE.date_creation_participant_etape DESC ?

**venomelektro** · 25/01/2011, 18h55

je comptais faire des analyse/optimize dans la nuit (il me semble que ca bloque en lecture seule les tables donc je prefere le faire quand le trafic sera faible)

oui, il y a bien un index sur PARTICIPANT_ETAPE.date_creation_participant_etape

**skuatamad** · 25/01/2011, 20h20

Pour info créer des index lock les tables aussi.

Je ne sais pas si mysql gère la lecture d'index à l'envère (DESC) de toute façon si la plus part des requêtes trient en DESC c'est intéressant d'avoir l'index déjà trié dans le bon sens, idem pour récupérer des données récentes.

Mais je ne t'encourrage pas forcément à le créer direct en prod car je ne suis pas du tout sûr qu'il soit utilisable dans la requête en question.

Dis nous ce qu'il en est après la maintenance de cette nuit.

**CinePhil** · 25/01/2011, 21h56

Edit : est ce que ca te semble pas beaucoup avec entre 200 et 300 connections ouvertes (réseaux) en simultanée ?

Euh... ou ton site a un très fort traffic et tu utilises une connexion par utilisateur, ou ton site ne referme pas les sessions qu'il ouvre. Elle sont à quel état ces connexions ?

**venomelektro** · 27/01/2011, 12h57

en effet, tres fort trafic

mais je pensais pas que mysql serait submergé a ce point

ca fait deux jours qu on est sur les opti , on a meme changé de serveur dans le doute et les perf sont toujours autant mauvaises..

soit les developpeurs se sont plantés dans certains choix (pour l instant je ne vois que le choix d innodb qui pourrait expliquer cela) soit mysql n arrive pas a tirer parti de la machine

Code :

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

1
2
3
4
5
6
7
8
9
 
~# uptime
 12:49:17 up 20:12,  1 user,  load average: 0.25, 0.09, 0.06
 
~# free -m
             total       used       free     shared    buffers     cached
Mem:         24148       3301      20846          0        194       1761
-/+ buffers/cache:       1345      22803
Swap:         1027          0       1027

je n ai jamais vu une machine avec un tel stats etant tant submergée.

je ne sais vraiment plus quoi faire de plus, je vais rajouter un second server en slave pour les requetes en lecture, mais de toute facon , les temps de reponse etant tres long malgré les index, le probleme sera moindre mais restera ;(

**CinePhil** · 27/01/2011, 13h42

Je repose cette question qui pourrait inculper ou disculper MySQL :

La requête directement exécutée sur le serveur en mode console, si tu peux y avoir accès, ou via phpMyAdmin donne quel temps de réponse ?

Je crois que la mauvaise réputation de MySQL en matière de performances face à des utilisateurs simultanés en grand nombre n'est plus à faire.

Parmi ses principaux défauts rédhibitoires notons : la pauvreté de la gestion de la concurrence (en pratique MySQL se vautre à plus de 5 utilisateurs en concurrence, alors que tous les autres SGBDR acceptent des centaines d'utilisateurs en parallèle sans broncher)

SQLPro est très virulent contre MySQL mais je crois avoir lu ce genre de remarque ailleurs que chez lui.

Par contre je ne pense pas que le moteur InnoDB soit en cause.

**venomelektro** · 27/01/2011, 13h45

je te confirme : ca rame autant connecté en root en local

**venomelektro** · 27/01/2011, 14h42

ce qui m etonne c est que je n en suis pas a mon premier projet mysql , ni a mon premier a fort trafic, je comprends pas pourquoi il y a un tel carnage.

je viens de rajouter un serveur slave pour les lectures, on verra si les choses s arrangent avec le moindre volume

**SQLpro** · 27/01/2011, 23h28

MySQL n'a jamais été conçu pour un grand nombre de connexion en transactionnel. A partir de 5 utilisateurs effectuant des transactions, il commence à plonger, là ou du PostGreSQL, du SQL Server ou de l'Oracle sont stable jusqu'à quelques dizaines de milliers d'utilisateurs suivant config hard.

Vous ne verrez jamais du MySQL sur des gros sites web marchand.

Exemples :
TGV.com => Oracle,
Fnac.com => SQL Server
...

C'est de part sa conception qu'il ne supporte pas la charge transactionnelle.
En revanche il est plus à l'aise sur de la lecture ou il n'y a pas de concurrence réelle (pas de verrous bloquant).

A lire :
http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/

Bref, vous n'avez pas beaucoup d'autres possibilité que de gonfler encore le serveur au niveau physique ou changer de SGBDR !

A +

**skuatamad** · 28/01/2011, 00h11

Oui mais venomelektro semble déjà avoir des problèmes avec juste un select (sûrement à cause de l'ORDER BY) et rien n'indique dans ses messages que les 300 connexions sont concurrentes, c'est peut être 299 select et 1 update.

stratégies pour les index

Requêtes MySQL

Discussions similaires

Partager

Partager