Tables temporaires, jointure, order by, group by

**ionesco** · 15/10/2009, 02h15

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** · 15/10/2009, 13h26

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** · 16/10/2009, 00h59

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** · 16/10/2009, 10h22

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** · 16/10/2009, 10h56

Bonjour Cinéphil

Envoyé par CinePhil

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)

Envoyé par CinePhil

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.

Envoyé par CinePhil

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** · 16/10/2009, 11h19

Autre piste à tout hasard : OPTIMIZE TABLE

Après je ne vois pas.

**ionesco** · 16/10/2009, 11h26

J'ai réussi à faire disparaitre le using filesort. Le GROUP BY effectue un ORDER BY implicitement, j'ai donc rajouter ORDER BY NULL après le GROUP BY, ce qui fait qu'il ne trie plus. Mais ca écrit toujours sur disque.....

C'est dingue quand même, sur 90% des requêtes qui ont une clause GROUP BY, ORDER BY ou un DISCTINCT, ca tape sur le disque.

J'ai pensé au OPTMIZE TABLE mais j'ai peur que ca prenne du temps dans l'environnement de prod. Je vais tester en dev.

En tout cas merci d'avoir essayer d'aider.

PS: J'ai MySQL Entreprise DASHBOARD en version platinium depuis ce matin. L'agent tourne bien, il commence à me remonter pas mal de chose. La premiere chose qui me frappe c'est l'activité de la BDD: on descend pas en dessous de 500 requete /s, avec des pic à 10 000 req/s

Je vais peut etre voir du coté du code si on ne pas réduire le nombre de requêtes.

**ionesco** · 16/10/2009, 12h58

Qu'est-ce qui est le moins impactant sur une perf, un full scan ou taper dans des table temporaires sur disque? J'ai réussi à forcer des indexes pour m'éviter la table temporaire, mais l'inconvéniant est que ca fait un full scan.

Merci!

**ionesco** · 19/10/2009, 13h09

Qu'est ce qui pourrait faire qu'il y a des processus en "sleep" (pas de jeu de mots) quand je fais un show processlist. Ca va du processus qui reste peu de temps et disparait, à ceux qui restent jusqu'au wait_timeout (28 800).

Sachant que l'appli n'utilise à aucun moment de connexion persistente.

Merci

**CinePhil** · 19/10/2009, 16h46

Je te conseille la lecture de ces deux articles de SQLPro pour optimiser les index et l'utilisation de la BDD.

C'est assez spectaculaire et ça peut te donner des idées, même si c'est écrit pour du SQL Server.

Ensuite, pour répondre à ta question :

Qu'est-ce qui est le moins impactant sur une perf, un full scan ou taper dans des table temporaires sur disque?

Je dirais qu'il faut faire des tests.
Il peut-être intéressant de faire une petite table temporaire avec un morceau de requête (potentiellement une sous-requête), ce qui peut permettre d'indexer cette table temporaire et d'accélérer les jointures.

Dans ton cas, si le nombre de voitures actives de catégorie 3 dans le site 1 n'est pas trop grand, il peut être intéressant de faire ceci :

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
CREATE TEMPORARY TABLE voitures_actives_site1_cat3
SELECT v.id_voiture, v.id_lieu
FROM table_voiture AS v
INNER JOIN voiture_categorie_voiture vcv ON vcv.id_voiture = v.id_voiture
WHERE v.active_voiture = '1'
  AND v.id_site = 1
  AND vcv.id_categ_voiture = 3 ;
 
ALTER TABLE voitures_actives_site1_cat3
ADD PRIMARY KEY (id_voiture);
 
CREATE INDEX ON voitures_actives_site1_cat3 (id_lieu);
 
SELECT l.id_lieu, COUNT( v.id_voiture ) , l.longitude_dd_lieu, l.latitude_dd_lieu
FROM table_lieu AS l
INNER JOIN voitures_actives_site1_cat3 AS v ON l.id_lieu = v.id_lieu
INNER JOIN langue_lieu AS ll ON ll.id_lieu = l.id_lieu
WHERE ll.id_lang = 1
  AND l.latitude_dd_lieu BETWEEN 48.436414885318 AND 49.155985114682
  AND l.longitude_dd_lieu BETWEEN - 2.1154721830172 AND - 1.0231278169828
GROUP BY l.id_lieu

**ionesco** · 19/10/2009, 17h04

Bonjour Cinéphil,

Pour le fait de passer par une table temporaire, j'y avais songé! Mais je vais vous raconter j'ai eu un problème: table_tmp_voiture ALREADY EXISTS.

Alors analysons. d'après mes maigres mais grandissantes connaissances sur MySQL (j'ai fais du ORACLE 5 ans en cours, jamais du MySQL merci pour la variété de l'enseignement), une table temporaire peut avoir le même nom, car elle est créer pour une connexion si je ne me trompe pas. Et vu que moi j'appelle la même requête deux fois de suite, peut-être MySQL suppose-t-il que j'éxécute cette requête avec la même connexion? Vous n'y voyez pas un lien avec le problème que j'ai remonté plus haut (des proccess sleep à foison) ?

**CinePhil** · 19/10/2009, 18h01

C'est possible. Je n'ai jamais été confronté à ce problème.

Une autre solution consisterait à créer une vue mais je crois malheureusement que MySQL ne permet pas d'indexer les vues donc ça ne changerait probablement rien !

A essayer quand même car le moteur de BDD se débrouille peut-être tout seul avec la vue pour optimiser son utilisation en jointure avec autre chose. Et cette fois la vue est créée de manière permanente et peut être appelée par plusieurs process à la fois je pense.

**ionesco** · 19/10/2009, 18h14

Je penses que MySQL ne gère pas les vues materialisées, il stocke la requête qui sert à créer la vue puis la ré-éxécute à chaque fois qu'on veut consulter la vue. Donc la vue n'est pas physiquement présente comme une table.

Sinon pour mon problème global je suis entrain de réduire le nombre de requêtes par page (il m'est arrivé de trouver 10 fois la même requête dans une même page ...). Aussi pour toutes les requêtes qui font du ORDER BY, je le supprime et le fait en php via une fonction ( à condition que ca ne dépasse pas les 1000 enregistrement pour pas pourrir le tri en PHP et qu'il n'y ait pas de pagination à gérer).

**ionesco** · 21/10/2009, 15h18

Re-Bonjour

Existe-t-il un moyen de dire à MySQL de ne pas enregistrer certaines reqûêtes en cache?

En effet, j'ai activé le cache des requête MySQL, cependant l'activité de la base est telle que le turn over est horrible => une requête ne reste en cache que quelques secondes puis est ejectée de la "file"....

SELECT SQL_NO_CACHE permet-il de dire de ne pas aller chercher si la requête est présente dans le cache, ou bien permet-il de dire ne pas l'inscrire dans le cache?

Merci.

**CinePhil** · 21/10/2009, 15h37

Si j'en crois la doc MYSQL :

SQL_NO_CACHE
The query result is not cached.

C'est le résultat de la requête qui n'est pas mis en cache.
D'un autre côté, je ne vois pas quel serait l'intérêt de garder la requête en cache sans son résultat !

**ionesco** · 21/10/2009, 15h41

C'est clair.

Moi j'aimerais que mes requêtes les plus optimisées et leur résultat ne figurent jamais dans le cache. Je souhaiterai arriver à ne faire tenir dans le cache que les requêtes qui entrainent une écriture lecture sur disque.

Est-ce une bonne idée?

Je ne sais pas comment dire à MySQL pour telle reqûete ne l'inscris pas dans le cache.

**CinePhil** · 21/10/2009, 16h22

Envoyé par ionesco

Je ne sais pas comment dire à MySQL pour telle reqûete ne l'inscris pas dans le cache.

Ben apparemment, c'est SQL_NO_CACHE.
Mais as-tu vu ce détail de la doc :

SQL_CACHE The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.

**ionesco** · 21/10/2009, 16h28

Bien vu.

Il faut donc que query_cache_size soit sur ON pour permettre le choix comme je le désire. (j'ai mis une taille de 512M pour le cache).

Ca va me faire avancer un peu j'espère.

Merci Cinéphil.
Je vais tunner le serveur maintenant. 2 fois 4 Core à 8GB de ram. Il ne faut pas que je me plante ca peut se retourner contre moi.

**ionesco** · 27/10/2009, 17h37

Bonjour,

C'est encore moi.

Je me demandais. Dans l'affreuse appli que je suis entrain de redresser, il y a une fonctionnalité que j'aimerai revoir. Il s'agit d'enregistrement de statistiques concernant les internautes. A chaque fois qu'un internaute clique sur un lien, on insert en base une stat.

Ma question: connaissez-vous une façon de mettre en tampon ces insertions répétées. Le but est que je ne fasse qu'un seul insert par heure (par exemple). Regrouper 10 000 inserts puis les balancer, au lieu de le faire 10 000 fois pendant l'heure.

Il y a le INSERT DELAYED mais est-il contrôlable à souhait? je pense que non.