MySQL lent sur un serveur mais pas sur l'autre identique (Debian 11)

Version imprimable

14/10/2023, 01h55
Couin

MySQL lent sur un serveur mais pas sur l'autre identique (Debian 11)
Coucou :D

2 serveurs, un chez moi, un au boulot. Les machines sont physiquement les mêmes à part le volume de données (plus gros et en RAID sur le mien, vs un simple disque dur 80 Go + un autre pour sauvegarder chaque jour sur celui du boulot).
Config LAMP identique (infos récupérées dans PhpMyAdmin) :

Citation:

Serveur de base de données

Serveur : Localhost via UNIX socket
Type de serveur : MySQL
Connexion au serveur : SSL n'est pas utilisé Documentation
Version du serveur : 8.0.27 - MySQL Community Server - GPL
Version du protocole : 10
Utilisateur : root@localhost
Jeu de caractères du serveur : UTF-8 Unicode (utf8mb4)

Serveur Web

Apache/2.4.56 (Debian)
Version du client de base de données : libmysql - mysqlnd 7.4.33
Extension PHP : mysqli Documentation curl Documentation mbstring Documentation
Version de PHP : 7.4.33

Celui du boulot est lent sur certains requêtes alors que la même requête est exécutée rapidement sur le mien.

Exemple d'une requête, elle est quasi instantanée chez moi, alors qu'il faut 3 secondes pour obtenir son résultat :
Code:

1 2 3 SELECT archistory.song_id, archistory.id_subcat, subcategory.parentid FROM archistory LEFT JOIN subcategory ON archistory.id_subcat = subcategory.ID ORDER BY archistory.date_played DESC LIMIT 1
Afin d'écarter Php des hypothèses, je l'ai aussi exécuté en ligne de commande via SSH. Même résultat, environ 3 secondes.

J’avais pensé au disque dur HS mais Hard Disk Sentinel indique un état de 100%, aucun secteur défectueux.

HTOP n'indique pas de processus prenant tout le CPU ou RAM.

Si j'isole le serveur d'internet (via des régles firewall zeroshell en amont), pour écarter une éventuelle attaque extérieure (permanente ?), le résultat est pareil.

A noter que ce phénomène n'a pas été là dès le début de la configuration de ce serveur, mais seulement depuis quelques semaines.

Le disque dur a plus de la moitié de libre.

J'ai épuisé mes idées de recherches :(

Si quelqu'un a une zidée :D

Merkouiiin :)
14/10/2023, 10h22
Séb.

Citation:

Exemple d'une requête, elle est quasi instantanée chez moi, alors qu'il faut 3 secondes

Le "instantané" est de l'ordre de 0,01 sec. ou de 1 sec. ?

Le problème est-il systématique ?
Le problème est-il le même juste après un redémarrage du serveur MySQL ?
As-tu pu identifier si le problème survenait avec seulement certaines tables ?
Ou lorsque certaines colonnes sont présentes ?
Ou sur certaines opérations ? (ex. ORDER BY)

80 Go !? C'est quel genre de disque dur ? Les temps d'accès sont déterminants.
Une partition sur 1 et 1 seul disque dur physique ?
Ou une partition logique qui pourrait être sur plusieurs disques physiques ?

Les paramètres du serveur sont identiques également ?
Notamment les innodb_* et innodb_buffer_pool_size / innodb_sort_buffer_size ?
Et la quantité totale de RAM ?

L'exécution d'une requête peut changer selon les lignes en présence et les cardinalités d'index.
Les tables chez toi et au travail sont-elles identiques en terme de remplissage ?

Vérifie si un index n'aurait pas sauté ou passé en NO VISIBLE => SHOW INDEX FROM ta_table;
Vérifie si les plans d'exécution sont identiques => EXPLAIN (https://dev.mysql.com/doc/refman/8.0/en/explain.html)
14/10/2023, 17h05
Artemus24
Salut à tous.

Citation:

Envoyé par Couin

J’avais pensé au disque dur HS mais Hard Disk Sentinel indique un état de 100%, aucun secteur défectueux.

Un disque dur en général est un disque mécanique et celui-ci est moins performant qu'un disque SSD.

Citation:

Envoyé par Couin

Si j'isole le serveur d'internet (via des règles firewall zeroshell en amont), pour écarter une éventuelle attaque extérieure (permanente ?), le résultat est pareil.

Il ne faudrait pas être paranoïaque et croire que si vous avez un problème de performance que cela vient nécessairement d'une attaque extérieure.

Citation:

Envoyé par Couin

Les machines sont physiquement les mêmes à part le volume de données ...

A priori, même si vos machines sont identiques physiquement, vous ne les utilisez pas de la même façon.
Chez vous, vous êtes seul à utiliser votre ordinateur, tandis qu'au boulot, vous n'êtes pas le seul utilisateur.
Il est possible que la dégradation des performances est due au nombres d'utilisateurs, mais cela reste à prouver.
Code:

1 2 3 SELECT archistory.song_id, archistory.id_subcat, subcategory.parentid FROM archistory LEFT JOIN subcategory ON archistory.id_subcat = subcategory.ID ORDER BY archistory.date_played DESC LIMIT 1
j'émets des hypothèses car vous ne nous avez rien donné des déclarations de vos tables.

a) on peut améliorer votre requête, car il me semble que vous récupérer une seule valeur de la colonne "date_played" qui est la plus récente.
b) il est aussi fort possible que vous n'ayez aucun index sur la colonne "date_played", ce qui dégrade le temps de recherche.
c) est-il nécessaire de trier sur la colonne "date_played" puisque de toute façon, vous ne récupérez qu'une seule valeur.
Je vous conseille de faire un "explain" sur cette requête et de voir si vous pouvez améliorer la performance.

Citation:

Envoyé par Couin

A noter que ce phénomène n'a pas été là dès le début de la configuration de ce serveur, mais seulement depuis quelques semaines.

C'est normal car votre volumétrie était très faible.
La désorganisation de votre base de données peut être une cause bien plus probable, mais il peut y en avoir d'autres.

Cordialement.
Artemus24.
@+
16/10/2023, 16h07
SQLpro

Dans tous les cas on ne met JAMAIS un SGBDR avec un serveur web dans la même machine. L'un dégrade les perfs de l'autre...

A +
17/10/2023, 06h51
Couin

Coucoutte !

Merci pour vos réponses :)

Désolé pour le temps de retour, panne embrayage donc démontage ce week end, encore du couin sur la planche :(

Sur les deux serveurs, il y a une copie du site de la radio (signature), je m'en sers pour développer le site (chez moi) et avoir une copie ailleurs que chez moi.

Citation:

Envoyé par Séb.

Le "instantané" est de l'ordre de 0,01 sec. ou de 1 sec. ?

Je dirais 0.1 sec, le résultat apparait de suite.

Citation:

Envoyé par Séb.

Le problème est-il systématique ?
Le problème est-il le même juste après un redémarrage du serveur MySQL ?
As-tu pu identifier si le problème survenait avec seulement certaines tables ?
Ou lorsque certaines colonnes sont présentes ?
Ou sur certaines opérations ? (ex. ORDER BY)

Je commence récement à me pencher sur le problème (car pas mal de boulot quand même à coté lol), je dois faire des tests avec d'autres tables et requêtes. Je regarderais aussi après un reboot.

Citation:

Envoyé par Séb.

80 Go !? C'est quel genre de disque dur ? Les temps d'accès sont déterminants.

Le disque dur est un vieux (forcément, pour qu'il fasse 80 go lol), Seagate Barracuda 7200.10 . Sur mon serveur, c'est le même, il n'y a que le le volume RAID en plus car j'ai beaucoup plus à stocker qu'au boulot.

Citation:

Envoyé par Séb.

Une partition sur 1 et 1 seul disque dur physique ?
Ou une partition logique qui pourrait être sur plusieurs disques physiques ?

C'est un disque physique. Pour les partitions, j'avais laissé l'installeur Debian partitionner le disque. un fdisk me donne ceci :
Périphérique Amorçage Début Fin Secteurs Taille Id Type
/dev/sda1 * 2048 154300415 154298368 73,6G 83 Linux
/dev/sda2 154302462 156301311 1998850 976M 5 Étendue
/dev/sda5 154302464 156301311 1998848 976M 82 partition d'échange

Citation:

Envoyé par Séb.

Les paramètres du serveur sont identiques également ?
Notamment les innodb_* et innodb_buffer_pool_size / innodb_sort_buffer_size ?

Je regarderais mais logiquement, ça doit être identique vu que je n'ai pas modifié de ce coté là et que mon serveur est une copie de celui du boulot (avec le RAID en plus mais l'install est la même).

Citation:

Envoyé par Séb.

Et la quantité totale de RAM ?

Le serveur (le mien comme le même au boulot, Xeon 3 GHz, 4 Go de RAM).

Citation:

Envoyé par Séb.

L'exécution d'une requête peut changer selon les lignes en présence et les cardinalités d'index.
Les tables chez toi et au travail sont-elles identiques en terme de remplissage ?

Les deux serveurs ont sensiblement la même base de données (en terme de quantité de données) car j'importe depuis une sauvegarde de la DB du site.

Citation:

Envoyé par Séb.

Vérifie si un index n'aurait pas sauté ou passé en NO VISIBLE => SHOW INDEX FROM ta_table;
Vérifie si les plans d'exécution sont identiques => EXPLAIN (https://dev.mysql.com/doc/refman/8.0/en/explain.html)

Je regarderais également.

Citation:

Envoyé par Artemus24

Un disque dur en général est un disque mécanique et celui-ci est moins performant qu'un disque SSD.

Je suis tout à fait d'accord. Cependant, dans le cas présent, avec le disque dur, jusqu'il y a peu, cela marchait sans souci.

Citation:

Envoyé par Artemus24

Il ne faudrait pas être paranoïaque et croire que si vous avez un problème de performance que cela vient nécessairement d'une attaque extérieure.

Je n'étais effectivement pas convaincu par cette hypothèse, mais au moins, elle est écartée.

Citation:

Envoyé par Artemus24

A priori, même si vos machines sont identiques physiquement, vous ne les utilisez pas de la même façon.
Chez vous, vous êtes seul à utiliser votre ordinateur, tandis qu'au boulot, vous n'êtes pas le seul utilisateur.
Il est possible que la dégradation des performances est due au nombres d'utilisateurs, mais cela reste à prouver.

Pour détailler un peu plus le contexte, le serveur au boulot, est sur un réseau indépendant du boulot, et sert pour quelques scripts permettant de faire des graphiques à partir d’extractions de logs de la machine de production du boulot. Donc à peu près moins de 5 fois par jour.

Citation:

Envoyé par Artemus24

j'émets des hypothèses car vous ne nous avez rien donné des déclarations de vos tables.

Quelles infos (déclarations) seraient à donner ? Que je puisse regarder ça :)

Citation:

Envoyé par Artemus24

a) on peut améliorer votre requête, car il me semble que vous récupérer une seule valeur de la colonne "date_played" qui est la plus récente.
b) il est aussi fort possible que vous n'ayez aucun index sur la colonne "date_played", ce qui dégrade le temps de recherche.
c) est-il nécessaire de trier sur la colonne "date_played" puisque de toute façon, vous ne récupérez qu'une seule valeur.
Je vous conseille de faire un "explain" sur cette requête et de voir si vous pouvez améliorer la performance.

a) A vrai dire, elle me parait déjà simple, comment pourrais-je l'améliorer ?
b) Le coup des index m'est aussi venu à l'idée, j'en avais rajouté mais cela n'a rien changé. Dans tous les cas, étant donné que chez moi c'est la même base de données, j'aurais le même problème aussi. C'est là que je cale lol
c) En gros je veux récupérer l'enregistrement le plus récent de la table (qui compte environ 510000 lignes à ce jour, pour 3 ans et 9 mois), d'où le tri par date décroissante.
Pour l'explain, oui, je regarderais ça :)

[QUOTE=Artemus24;11978006]C'est normal car votre volumétrie était très faible.
A vrai dire la table n'est pas partie de 0 (ou volumétrie très faible) car c'est un import de la DB site, tant sur mon serveur que sur celui du boulot.

Citation:

Envoyé par Artemus24

La désorganisation de votre base de données peut être une cause bien plus probable, mais il peut y en avoir d'autres.

C'est surtout la différence de temps de réponse entre les deux serveurs qui me fait tiquer.

Citation:

Envoyé par SQLpro

Dans tous les cas on ne met JAMAIS un SGBDR avec un serveur web dans la même machine. L'un dégrade les perfs de l'autre...

Comment cela ? Vu la très faible utilisation des deux serveurs, ça devrait quand même passer ?
17/10/2023, 17h24
SQLpro
Citation:

Envoyé par Couin

....Comment cela ? Vu la très faible utilisation des deux serveurs, ça devrait quand même passer ?

Non.... Un SGBDR doit être installé sur un serveur dédié et aucun autre service applicatif ne doit tourner dessus.

Les SGBDR sont très consommateurs de tout un tas de types de ressources : mémoire, CPU et disque. Lorsque des ressources sont volées au SGBDR par d'autres applicatif, le SGBDR doit attendre la restitution de ces ressources pour pouvoir continuer son travail. Deux exemples :
- Si le disque est occupé par une autre application alors le SGBDR devra attendre son tour pour accéder au disque. La requête reste en attente et occupe un thread inutilement.
- Si le cœur sur lequel s’exécutait la requête n'est plus disponible alors le SGBDR devra attendre con tour pour accéder au ce cœur et la requête reste en attente et occupe un thread inutilement.
4 Go de RAM sur un serveur 64 bits c'est extrêmement faible pour un SGBDR dont les opérations sont faites en mémoire... L'OS consomme déjà pas loin de 2 Go avec l'ensemble des services connexes, l'exécutable pas loin de 1 Go, et la VM a aussi besoin de place en mémoire pour orchestrer et contrôler et en plus vous avez un applicatif dessus... il vous reste donc moins de 1 Go pour mettre en mémoire les données des tables et index et les sessions de tous les utilisateurs. Chez vous un seul utilisateur... Mais en prod ???
Personnellement j'exige un strict minimum de 16 Go pour une VM ayant un SGBDR même si la base ne fait que quelques GO, chaque utilisateur consommant aussi pas mal de RAM...

Autre problématique, les statistiques de l'optimiseur pas à jour. En effet, je suppose que votre serveur de prod produit de la data... Cela veut dire que des transactions (INSERT, UPDATE, DELETE...) sont effectuées tout au long de la journée, ce qui conduit les stats de l'optimiseur à diverger, car elle sont statifiées au moment ou :
- on a créé l'index
- on a réalisé une opération de maintenance d'index ou de statistiques
La question est donc, quand le DBA a t-il fait ce travail pour la dernière fois ?

A +
17/10/2023, 19h02
Couin

Je crois qu'on ne parle pas de la même chose. Il ne s'agit pas d'un serveur de prod mais d'un simple serveur LAMP qui sert essentiellement pour le dev et sur lequel j'accède et seulement un ou deux collègue pour utiliser 2 /3 scripts PHP (et même pas en même temps que moi). Le reste du temps le serveur pourrait même dormir :lol: Il n'y a ni VM ni requêtes tout au long de la journée.
D'ailleurs j'essaierais aussi ces scripts pour voir si lents aussi mais je pense que non, le collègue m'en aurait parlé. Ceci dit il y a moins de lignes sur les DB de ces scripts.
après ce dit pas pourquoi même si c’était qu'une question de nombre de lignes important, ça marchez sans souci chez moi avec le même serveur.