Lenteur d'une requete sur une table INNODB avec un auto_increment

Version imprimable

03/02/2009, 13h47
mls

Lenteur d'une requete sur une table INNODB avec un auto_increment

Bonjour,

J'ai un petit problème (non bloquant mais quand même...) concernant le temps de réponse d'une table INNODB qui possède un champ en clé primaire et auto-incrémenté. Je m'explique :
Depuis 3 semaine, l'équipe Système et Réseau de ma boite exécute un script sur le serveur MySQL afin de l'arrêter et de le redémarrer toute les nuits. Le lendemain, lorsque je lance une requête du type SELECT sur cette table (et 2 autres qui sont en relation avec elle), le résultat n'apparait qu'au bout de 2 min (même en l'exécutant plusieurs fois de suite). Alors qu'en temps normal et avant que le serveur ne soit redémarré, cette, même requête qui existe depuis maintenant 2 ans, s'exécutait en 5 sec.

Ensuite, lorsque j'exécute cette requête : "ALTER TABLE nom_table AUTO_INCREMENT=0", le temps de réponse redevient à nouveau correct, c'est à dire 5 sec. Mais dès que le serveur MySQL redémarre (pour maintenance, ou autre...), le problème revient systématiquement. Même après destruction et reconstruction de la table...

Mais, après test, même en essayant de ne plus arrêter le serveur MySQL pendant un week-end, le problème revient alors qu'aucune "activité" ne s'est passée sur cette table durant ce laps de temps.

Quelqu'un saurait-il pourquoi une telle lenteur se produit sur cette table après redémarrage du serveur sachant que la base de données possède plusieurs tables de ce type (avec champ primaire et auto-incrémenté) et que je n'ai aucun problème avec ces dernières ?

Merci beaucoup d'avance pour votre aide...

Caractéristiques :

Système : UNIX
Base de données : MySQL 4.1.22 standard
Client d'intérogation : MySQL Query Browser
03/02/2009, 14h05
SQLpro

Citation:

Depuis 3 semaine, l'équipe Système et Réseau de ma boite exécute un script sur le serveur MySQL afin de l'arrêter et de le redémarrer toute les nuits.

Ceci est une imbécilité catastrophique résultat de l'ignorance crasse en matière de SGBDR.
Les SGBDR sont doté d'un mécanisme de cache qui met en mémoire les données des tables les plus utilisées. Ce qui permet de faire en sorte que 80 à 99% des données requêtées soient trouvées en mémoire (1000 fois plus rapide que la lecture disque), par un algorithme généralement LRU.
Chaque fois que vous arrêtez votre serveur toute la mise en cache est perdu et votre serveur fait des requêtes à froid qui donc consomment énormément d'accès disque jusqu'à une certaine stabilité qui pour des grosses bases fortement sollicitées peut prendre plusieurs heures...
Ne cherchez pas ailleurs les problèmes de performances....

En matière de SGBDR, une règle d'or est que l'on arrête JAMAIS un serveur de bases de données...
Certains étant conçu pour fonctionner avec une dipos de 99,999 % du temps (soit moins de 6 minutes d'interruption par an), toutes les opérations pouvant être entreprise à chaud (déplacement des données, partitionnement, indexation...).

Je suppose que votre équipe SI et réseaux n'a jamais eu de formation de DBA... Navrant !!

A +
03/02/2009, 15h07
mls

Effectivement, je pense exactement la même chose que vous. Mais eux, ont dans la tête que ça ne peut pas faire de mal de l'arrêter (ils ne pensent pas effectivement au cache).

Mais maintenant que le serveur ne s'arrête plus, ça ne résoud quand même pas mon problème de lenteur qui perdure toujours sans ça...:cry:
04/02/2009, 17h56
Sivrît
Citation:

Envoyé par mls

Ensuite, lorsque j'exécute cette requête : "ALTER TABLE nom_table AUTO_INCREMENT=0", le temps de réponse redevient à nouveau correct, c'est à dire 5 sec. Mais dès que le serveur MySQL redémarre (pour maintenance, ou autre...), le problème revient systématiquement. Même après destruction et reconstruction de la table...

Pour vérifier que c'est bien un problème de cache, il faudrait essayer d'alimenter le cache avant de lancer la requête. Par exemple :
Code:

1 2 3 4 create tamporary table nom_table_tmp like nom_table; alter table nom_table_tmp engine=BLACKHOLE; INSERT INTO nom_table_tmp SELECT * FROM nom_table;
Et éventuellement pour les index (je ne sais pas si c'est la meilleure méthode mais ça devrait le faire) :

Code:

SELECT count(*) FROM nom_table GROUP BY colonne_avec_index

En un sens, c'est plus ou moins ce que fait un "ALTER" (en plus violant) qui va copier la table.

A la limite ça peut être un script lancé au démarrage du serveur.

Citation:

Envoyé par mls

Mais, après test, même en essayant de ne plus arrêter le serveur MySQL pendant un week-end, le problème revient alors qu'aucune "activité" ne s'est passée sur cette table durant ce laps de temps.

C'est probablement justement parce qu'il n'y a eu aucune activité sur la table. Il y a plus de données que de cache, et les données des autres tables plus utilisées ont chassés celles-ci. Si c'est possible, il faudrait peut-être augmenter innodb_buffer_pool_size.

Citation:

Envoyé par mls

Quelqu'un saurait-il pourquoi une telle lenteur se produit sur cette table après redémarrage du serveur sachant que la base de données possède plusieurs tables de ce type (avec champ primaire et auto-incrémenté) et que je n'ai aucun problème avec ces dernières ?

Celle-ci est peut-être plus grosse que les autres, et/ou moins utilisée en temps normal.
04/02/2009, 19h09
mls

Bonjour,

Merci Sivrît de m'avoir répondu.

Citation:

Pour vérifier que c'est bien un problème de cache, il faudrait essayer d'alimenter le cache avant de lancer la requête

Ce que je trouve bizarre, c'est que c'est venu d'un seul coups alors que la base à toujours eu le même comportement...

Citation:

C'est probablement justement parce qu'il n'y a eu aucune activité sur la table. Il y a plus de données que de cache, et les données des autres tables plus utilisées ont chassés celles-ci.

En fait, je me suis mal exprimé : il n'y avait aucune activité sur toute la base de données et non sur cette table seulement.

Citation:

Si c'est possible, il faudrait peut-être augmenter innodb_buffer_pool_size.

Effectivement, j'essaierai de voir ça avec l'équipe Système et Réseau de ma boite.

Citation:

Celle-ci est peut-être plus grosse que les autres, et/ou moins utilisée en temps normal.

Celle-ci est moins grosse que les autres pas contre elle est moins utilisée.
Mais bon, elle l'a toujours été et maintenant ça "rame".

Sinon, j'ai continué de fouiller aujourd'hui sur mon problème et j'ai découvert 2 choses :

La première : lorsque j'utilise MySQL Administrator, cette requête (entre autres)

Code:

SHOW TABLES STATUS

est exécutée automatiquement et plusieurs fois vers la base de données notamment lorsque que je souhaite effectuer un backup mais sans le lancé. Après ça, la table a de nouveau le problème.

Je la "répare" avec

Code:

ALTER TABLE nom_table AUTO_INCREMENT=0

Si dans MySQL Query Browser, j'exécute au moins 2 fois de suite cette requête, la table a de nouveau le problème. Si je vais cette requête alternativement avec celle qui est censé s'effectuer en 5 sec, le problème ne revient pas.:evilred: Je ne comprend vraiment pas...

La deuxième : ma table comporte 2 index distinct c'est à dire un sur chaque champ (en plus de la clé primaire auto-incrémentée). Je les ai supprimé puis j'ai recréé un seul index basé sur ces 2 champs.
Résultat, après exécution de la requête "SHOW TABLES STATUS" pour la faire "planter", je ne retrouve pas (pour l'instant) mon problème de lenteur !!! :yaisse2: Mais je vais attendre un peu pour en être sûr.

En tout cas, je ne comprend vraiment pas le sens de tout ça...:roll:
Je trouve qu'il n'y a rien de logique...