Discussion :

[ionesco]

Bonsoir,
Je reprends une BDD assez grosse (mal concue, mal normalisé).
Le probleme est que quasi toutes les requêtes avec JOINTURE + GROUP BY (ou ORDER BY) font que MySQL écrit un resultat temporaire sur disque. A tel point que 60% des tables temporaires sont sur disque ( et non en mémoire donc...). Ca pose de gros probleme de perf.

J'ai vérifié les index, fait des EXPLAIN, tenté des optims, même forcé des index, mais il ne veut rien entendre: tables temporaires sur disque.

Quelqu'un aurait-il déjà connu ça dans sa carrière? D'où peut venir ce problème? Je vous donne une stat: environ 500 requetes/s en moyenne sur une semaine.

Merci.

[CinePhil]

Tu peux donner un exemple de requête et la structure des tables impliquées ainsi que ce qu'est censée faire la requête ?
Maintenant qu'on sait que la BDD est mal conçue on va essayer de ne pas trop crier !

[ionesco]

Bonsoir Cinéphil,

Je vais te donner un exemple concret.

J'ai des voitures. Ces voitures sont localisées dans des lieux et possèdent des catégories (une voiture peut etre de catégorie monospace, mais aussi de catégorie 5 places). L'application est multi-langues. Ainsi les lieux sont déclinable par langue.

table_voiture => les voitures
voiture_categorie_voiture => liens entre voitures et catégories de voitures
table_lieu => les lieux
langue_lieu => liens entre langues et lieux.

Les structures des tables sont:

table_voiture:

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
CREATE TABLE IF NOT EXISTS `table_voiture` (
  `id_voiture` int(11) unsigned NOT NULL auto_increment,
  `libelle_voiture` varchar(200) NOT NULL default '',
  `code_voiture` varchar(100) NOT NULL default '',
  `date_ajout_voiture` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modification_voiture` datetime NOT NULL default '0000-00-00 00:00:00',
  `latitude_dd_voiture` float NOT NULL default '0',
  `longitude_dd_voiture` float NOT NULL default '0',
  `adresse_voiture` varchar(200) NOT NULL default '',
  `adresse2_voiture` varchar(200) NOT NULL default '',
  `code_postal_voiture` varchar(10) NOT NULL default '',
  `ville_voiture` varchar(120) NOT NULL default '',
  `active_voiture` enum('0','1') NOT NULL default '0',
  `id_lieu` int(11) unsigned NOT NULL default '0',
  `id_statut_voiture` tinyint(3) unsigned NOT NULL default '0',
  `id_lang` tinyint(3) unsigned NOT NULL default '0',
  `id_type_voiture` smallint(5) unsigned NOT NULL default '0',
  `id_site` smallint(5) unsigned NOT NULL default '0',
  `id_contact` int(11) unsigned NOT NULL default '0',
 
  PRIMARY KEY  (`id_voiture`),
  KEY `id_lieu` (`id_lieu`),
  KEY `id_statut_voiture` (`id_statut_voiture`),
  KEY `id_type_voiture` (`id_type_voiture`),
  KEY `id_site` (`id_site`),
  KEY `active_voiture` (`active_voiture`),
  KEY `date_ajout_voiture` (`date_ajout_voiture`),
  KEY `date_modification_voiture` (`date_modification_voiture`),
  KEY `code_voiture` (`code_voiture`),
  KEY `id_lang` (`id_lang`),
  KEY `id_contact` (`id_contact`)
) ENGINE=MyISAM;

voiture_categorie_voiture:

Code :

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

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `voiture_categorie_voiture` (
  `id_voiture` int(11) unsigned NOT NULL default '0',
  `id_categ_voiture` tinyint(3) unsigned NOT NULL default '0',
 
  PRIMARY KEY  (`id_voiture`,`id_categ_voiture`),
  KEY `id_categ_voiture` (`id_categ_voiture`)
) ENGINE=MyISAM

table_lieu:

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
CREATE TABLE IF NOT EXISTS `table_lieu` (
  `id_lieu` int(11) unsigned NOT NULL auto_increment,
  `libelle_lieu` varchar(120) NOT NULL default '',
  `code_postal_lieu` varchar(10) NOT NULL default '',
  `code_lieu` varchar(15) NOT NULL,
  `longitude_dd_lieu` float NOT NULL default '0',
  `latitude_dd_lieu` float NOT NULL default '0',
  `id_pays` smallint(5) unsigned NOT NULL default '0',
  `id_region` smallint(5) unsigned NOT NULL default '0',
 
  PRIMARY KEY  (`id_lieu`),
  KEY `id_pays` (`id_pays`),
  KEY `id_region` (`id_region`),
  KEY `code_postal_lieu` (`code_postal_lieu`),
  KEY `code_lieu` (`code_lieu`),
  KEY `libelle_lieu` (`libelle_lieu`),
  KEY `longitude_dd_lieu` (`longitude_dd_lieu`,`latitude_dd_lieu`)
) ENGINE=MyISAM;

langue_lieu:

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `langue_lieu` (
  `id_lieu` int(11) unsigned NOT NULL default '0',
  `id_lang` tinyint(3) unsigned NOT NULL default '0',
  `nom_lang_lieu` varchar(150) NOT NULL default '',
  `description_lang_lieu` varchar(2500) NOT NULL,
 
  PRIMARY KEY  (`id_lieu`,`id_lang`),
  KEY `id_lang` (`id_lang`)
) ENGINE=MyISAM

Maintenant la requête. La requête doit récupérer pour chaque lieu qui est présent dans table_voiture le nombre de voiture que contient ce lieu. Les dites voitures doivent être actives, appartenir au site 1, et être de catégorie 3. Parmis les lieux renvoyés, on ne veut que ceux qui sont dans un certain périmètre ( définit par des latitudes et longitudes) et qui existent pour la langue 1.
Voici la requête (le SQL_NO_CACHE c'est pour que mes tests ne soient pas erronés):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT SQL_NO_CACHE l.id_lieu, COUNT( v.id_voiture ) , l.longitude_dd_lieu, l.latitude_dd_lieu
 
FROM table_lieu l
JOIN table_voiture v ON ( v.id_lieu = l.id_lieu )
JOIN voiture_categorie_voiture vcv ON vcv.id_voiture = v.id_voiture AND vcv.id_categ_voiture = 3 
JOIN langue_lieu ll ON ll.id_lieu = l.id_lieu AND ll.id_lang =1
WHERE
(l.latitude_dd_lieu BETWEEN 48.436414885318 AND 49.155985114682)
AND (l.longitude_dd_lieu BETWEEN - 2.1154721830172 AND - 1.0231278169828)
AND v.active_voiture = '1'
AND v.id_site = 1
 
GROUP BY l.id_lieu

Voilà! Si tu as besoin de précisions demande moi et j'essaierai de te les apporter (moi même étant pas très documenté, le gars précédent a été viré et je n'ai quasi rien eu comme doc).

[CinePhil]

Pistes d'amélioration :
1) Tu as un index double (`longitude_dd_lieu`,`latitude_dd_lieu`). Je ne suis pas sûr qu'avec un BETWEEN sur chaque colonne l'index double soit opérationnel. A voir avec le EXPLAIN mais moi j'ajouterais un index sur la latitude.

2) Place des conditions

et être de catégorie 3

qui existent pour la langue 1

Ces deux conditions sont dans les conditions de jointure (JOIN ON) alors que ce sont des conditions de restriction (WHERE). As-tu essayé de les mettre dans le WHERE ?

Je sais que ce n'est pas facile à lire mais normalement, le EXPLAIN t'indique les index utilisés et les tables qui sont lues entièrement. C'est sur ces points là que tu peux chercher à améliorer ta requête.

Après bien sûr les performances de la requête dépendent aussi des caractéristiques du serveur, surtout si les tables sont grosses (centaines de milliers de lignes).

PS : Sur le morceau de BDD que tu montres, je ne vois pas de grossière erreur de conception a priori, à part le fait que l'utilisation du moteur MyISAM empêche l'utilisation des contraintes de clé étrangères.

[ionesco]

Bonjour Cinéphil

1) Tu as un index double (`longitude_dd_lieu`,`latitude_dd_lieu`). Je ne suis pas sûr qu'avec un BETWEEN sur chaque colonne l'index double soit opérationnel. A voir avec le EXPLAIN mais moi j'ajouterais un index sur la latitude.

Avec le EXPLAIN l'index double est dans les possible keys, mais il n'en utilise qu'une partie (longitude)

2) Place des conditions
Ces deux conditions sont dans les conditions de jointure (JOIN ON) alors que ce sont des conditions de restriction (WHERE). As-tu essayé de les mettre dans le WHERE ?

Ces conditions étaient bien dans le where auparavant mais l'EXPLAIN était le même.

PS : Sur le morceau de BDD que tu montres, je ne vois pas de grossière erreur de conception a priori, à part le fait que l'utilisation du moteur MyISAM empêche l'utilisation des contraintes de clé étrangères.

J'ai remanié les tables depuis mon arrivée. Avant c'était le souk j'ai remis de l'ordre au maximum que j'ai pu.
Avec le GROUP BY, MySQL écrit sur disque, sans le GROUP BY il n'écrit pas sur disque et c'est beaucoup plus rapide.

Le EXPLAIN avec le GROUP BY indique un using temporary et using filesort. Sans le GROUP BY la colonne extra est vide...
Par contre dans les deux cas, il fait un full scan sur la table voiture_catégorie_voiture.

Pour le serveur c'est un Quad CORE Opteron 8 proc à 8GB de mém.

Je ne vois plus quelle piste explorer. Le GROUP BY plombe tout.

[CinePhil]

Autre piste à tout hasard : OPTIMIZE TABLE

Après je ne vois pas.