MYSQL : Premier SELECT très long

**re12** · 16/05/2012, 10h59

Bonjour

J'ai une table avec 400 000 enregistrements.
Elle est en INNODB.

Le première requête executé et correspondant à l'index est très long.
Une fois exécuté il est plus rapide, sauf si je ne l'exécute pas de quelque temps.

Comment remedier à cela ?

Voici la structure de la table

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE IF NOT EXISTS `product` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(7,2) NOT NULL DEFAULT '0.00',
  `tva` decimal(4,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=473580 ;

Le SELECT :

Code :

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

SELECT `name` FROM `product` WHERE `ID` = 80

Merci

**punkoff** · 16/05/2012, 11h17

Bonjour,

- que veut dire très long ?
- par 1er qu'entendez-vous ? après un arrêt / relance du serveur ?
- combien de Go/Mo fait votre bdd ? vos indexes ?
- qu'avez-vous comme valeur pour le paramètre : innodb_buffer_pool_size ?

**CinePhil** · 16/05/2012, 11h33

Normalement, et vu la structure de la table, la réponse devrait être immédiate.
Ce temps très long est-il constaté directement sur le serveur MySQL ou via une application ?

**re12** · 16/05/2012, 11h33

Il faut bien compter 70 secondes.

Cela correspond a un arrêt relance effectivement.

La base fait 21 Mo.

innodb_buffer_pool_size fait 8 Mio

La requête est exécutée via PHPMyAdmin

**CinePhil** · 16/05/2012, 11h38

Envoyé par re12

Il faut bien compter 70 secondes.

Il y a quelque chose qui ne va pas quelque part !

Un détail que je viens de constater :

Code :

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

CREATE TABLE IF NOT EXISTS `product`

Code :

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

SELECT `name` FROM `bo_product`

tu n'interroges pas la table dont tu nous as donné la structure !

**re12** · 16/05/2012, 11h42

Effectivement j'ai corrigé cette partie dans le premier post.

Pensez vous qu'il y a une mise en cache qui est faite la première fois? étonnant qu'elle soit si longue?

**punkoff** · 16/05/2012, 11h43

La piste que j'aurai par rapport à ca c'est que MYSql met 70 sec pour soit (ou un mix des deux) :
- charger en mémoire votre index.
- retrouver sur disque les informations liées à votre ligne (et les mettre en mémoire).

Donc ...
- est-ce un serveur dédié
- y a-t-il d'autre database MySql qui tourne sur la même instance qui pourrai utiliser ces 8mo de innodb_buffer_pool_size
- vos disques sont-ils ok
- y aurai-t-il un lock par une appli de votre table production lors du lancement du serveur ?
- etc ...

**re12** · 16/05/2012, 11h51

Alors c'est une machine virtuelle pour le moment (l'application est en cours de développement)

Il n'y a pas de soucis de perf sur cette machine elle fonctionne plutôt bien.

C'est la seule application sur la machine.

**CinePhil** · 16/05/2012, 16h37

L'index étant une clé primaire entière, Même avec plus de 400 000 lignes, il doit être petit.
Même sur un simple PC de base, une telle requête devrait s'exécuter en moins de temps qu'il faut pour lever le nez du clavier vers l'écran après avoir tapé la touche Entrée !

J'ai fait des requêtes autrement plus complexes sur un simple portable d'il y a 4 ans et sur plusieurs tables jointes contenant des millions et dizaines de millions de lignes avec une réponse inférieure à 5 secondes.

Il y a soit un problème de config, soit un problème matériel. 70 secondes, c'est une éternité pour un SGBD !

**re12** · 16/05/2012, 20h54

Bonsoir,

Effectivement après recherche ce n'est pas cette requête qui posait problème...

Voici la fautive :

Code :

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

1
2
3
4
5
6
7
 
SELECT COUNT(1) as "total" FROM 
(
	SELECT `ID` as "ID",`name` as "name",`price` as "price",`tva` as "tva"
	FROM `product` 
	WHERE 1 = 1 
) deriv;

Le but de cette requête est de compter le nombre total de ligne qu'aurai pu retourner la sous requête si il n'y avais pas eu de LIMIT.

J'ai vu qu'il était possible d'utiliser SQL_CALC_FOUND_ROWS pour récupérer cette valeur, seulement elle retourne la valeur pour la dernière requête exécutée, et cela est risquée pour mon application qui as plusieurs utilisateurs connectés en même temps.

Une idée?

Bonne soirée à vous

**CinePhil** · 16/05/2012, 22h05

Euh... on n'est plus du tout avec la même requête qu'au début là !

Ta sous requête ne fait rien d'autre que ramener toutes les lignes de la table puis que WHERE 1 = 1 est toujours vrai.

Du coup le COUNT de la requête principale ne fait que compter les lignes de la table, ce qui est équivalent à cette requête beaucoup plus simple :

Code :

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

1
2
SELECT COUNT(*) AS nb_lignes
FROM product

**ericd69** · 17/05/2012, 06h35

salut,

faut pas passer par ça pour savoir le nombre total de ligne sans limit, y a une syntaxe spéciale pour ça...found_rows()

en plus, comme le dit cinephil autan faire un count direct sur l'id de ta table vu ce que tu fais comme requête...

**re12** · 17/05/2012, 09h48

Alors effectivement je n'ai pas expliqué la raison d'être de cette requête.

Mais en reprenant ça je pense pouvoir faire autrement effectivement.

Je reviens vers vous des que j'ai trouvé la solution.
Merci pour vos réponses.

**re12** · 21/05/2012, 14h34

Bonjour,

En faisant un

Code :

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

1
2
SELECT COUNT(*) AS nb_lignes
FROM product

sur une base en INNODB avec 20 millions de ligne la requête met 20 secondes à s'exécuter.

Pensez vous qu'il y a moyen d'optimiser cela?
J'ai vu qu'en INNODB cette requête allait lire tout les enregistrements comparé à MyISAM qui va chercher les stats de la table

**CinePhil** · 21/05/2012, 14h42

J'ai vu qu'en INNODB cette requête allait lire tout les enregistrements comparé à MyISAM qui va chercher les stats de la table

Tu as vu ça où ?

Si ta table est normalement constituée, elle possède une clé primaire qui, par définition, est indexée. En faisant un COUNT(*) sur une table, sans autre condition, le SGBD doit, normalement, se contenter de calculer la profondeur de l'index de clé primaire. Il ne va pas parcourir toute la table pour en compter une à une les lignes.

Avec cette méthode, compter même 20 millions de lignes doit se faire en une fraction de seconde.

**re12** · 21/05/2012, 14h53

http://www.mysqlperformanceblog.com/...innodb-tables/

Qu'en penses-tu?

**punkoff** · 21/05/2012, 14h55

J'penses que MySql a le même problème que postGresql a ce stade là.

http://dev.mysql.com/doc/refman/5.5/...trictions.html

•InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.

**re12** · 21/05/2012, 15h04

pensez vous que je puisse utiliser un autre moteur de stockage ?

Sachant que j'utilise INNODB pour les transactions.

Merci

**ericd69** · 21/05/2012, 15h08

c'est bizarre en effet... tu as accès à la configuration de mysql?

regarde la taille du buffer pour les index de base c'est: key_buffer_size = 16M

fait un:

Code sql :

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

show table status from ta_bd

et regarde "index length" dans la ligne correspondant au nom de ta table

si tu as beaucoup d'insertion/suppression, il faut faire plus ou moins fréquemment un:

Code :

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

optimize table ta_table;

pour compacter la table et l'index et éviter leur fragmentation

**CinePhil** · 21/05/2012, 15h11

Envoyé par re12

http://www.mysqlperformanceblog.com/...innodb-tables/

Qu'en penses-tu?

J'en pense que ce billet date de 2006 et j'espérais que le problème fut corrigé depuis.

Avec le lien et la citation donnés par Punkoff, il semble que ce ne soit pas le cas.
Cependant, la citation confirme ma supposition :

To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table

Donc je persiste à dire que même avec 20 millions de lignes, ça devrait être très rapide.

Applique la procédure de ericd69 et réessaie le comptage.