Optimisation : fail , revert impossible

Version imprimable

Bonjour, j'ai eu l'idée saugrenue de vouloir optimiser mon serveur MySQL (5.5, Debian) vu que je suis passé de 32G à 64G de RAM.
Visiblement ce fut une très mauvaise idée car depuis mes requêtes sont horriblement lentes (CPU à 100%) et restaurer le my.cnf dans son état d'origine n'a rien changé ! Ni même rebooter le serveur (oui j'ai vraiment tout essayé …) Du coup je ne comprends pas.

Concrètement j'ai 2 bases, une Innodb d'environ 400Mo et une MyISAM d'environ 1.5Go. J'ai joué avec les variables suivantes (avant il y avait juste les valeurs par défaut):
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 slow_query_log=1 slow_query_log_file=/var/log/mysql/mysql-slow.log long_query_time = 2 innodb_buffer_pool_size=4G tmp_table_size = 256M max_heap_table_size = 256M join_buffer_size = 32M query_cache_limit = 10M query_cache_size = 100M key_buffer_size = 256M
Ensuite j'ai redémarré le service et pendant les premières heures tout allait bien. Puis les performances se sont rapidement dégradées alors que le serveur ne swap pas ! J'ai pu constater que c'était le CPU qui limitait certaines (grosses) requêtes : ces dernières prenaient 2s avant "optimisation", elles prennent maintenant quasiment 1 minute. Même après avoir restauré le fichier de conf dans son état d'origine et redémarré le serveur.

Précisions :
- Certaines requêtes font des jointures entre les deux bases
- les requêtes qui n'utilisent que la base innodb sont aussi touchées (base Redmine, avant elles n'avaient aucun soucis)
- mysqlcheck me donne "OK" partout
- les recommandations de mysqltuner n'ont rien changé

Voilà, si quelqu'un a une idée … Je vous remercie d'avance.

21/05/2017, 02h54
Artemus24

Salut Faya972.

Citation:

Envoyé par Faya972

j'ai eu l'idée saugrenue de vouloir optimiser mon serveur MySQL (5.5, Debian) vu que je suis passé de 32G à 64G de RAM.

Ce n'est pas nécessairement une mauvaise idée, mais cela se fait en analysant les performances de MySql et non en bidouillant à l'aveugle.

Citation:

Envoyé par Faya972

restaurer le my.cnf dans son état d'origine n'a rien changé !

Je ne voie pas pourquoi cela ne fonctionnerait pas. Mais qu'entendez-vous par "restaurer" ?
Voulez-vous dire remettre le fichier my.cnf d'avant vos bidouilles, c'est-à-dire la sauvegarde que vous avez faites ?
Ou bien remettre les valeurs par défaut ? Ce n'est pas exactement la même chose.

Est-ce que vous êtes à l'origine de l'ancien fichier "my.cnf" ?
Ou bien quelqu'un d'autre a déjà optimisé ce fichier avant votre venu ?

Citation:

Envoyé par Faya972

Ensuite j'ai redémarré le service et pendant les premières heures tout allait bien.

Qu'est-ce que vous entendez par "tout allait bien" ? Vous étiez seul à travailler sur votre serveur ?
Avez-vous désactivé votre fichier "mysql-slow.log", en mettant 0 dans "slow_query_log" ?

Pour retrouver un semblant de paramétrage d'origine, je vous conseille de lire la page "mysql" de mon site (ci-dessous, en bas de ce message).

Sinon, ne confondez pas les tirets "-" avec les soulignés "_" pour les variables dans le fichier "my.cnf". Ils n'ont pas le même effet.
Remplacez "key_buffer_size" par "key-buffer-size".

@+
21/05/2017, 16h30
faya972

Bonjour et merci pour votre réponse.

Je n'ai bien sûr pas optimisé "à l'aveugle", j'ai d'abord tenu compte des suggestions de mysqltuner puis ai choisi des valeurs qui me paraissaient raisonnables au regard de la taille des données et du (petit) nombre d'accès.

Citation:

Ou bien remettre les valeurs par défaut ? Ce n'est pas exactement la même chose.

J'ai fait les deux : d'abord remettre les valeurs par défaut puis remettre le fichier d'origine. Et ça n'a rien amélioré.
Le fichier d'origine est celui fourni par une installation "neuve " de Debian Jessie. Je suis le seul admin sur ce serveur.
Tout allait bien c'est à dire que les requêtes prenaient 2secondes pour s'exécuter et pas 60s.

Citation:

en mettant 0 dans "slow_query_log"

Ah non, j'ai commenté la ligne en question mais je testerai.

Merci pour la remarque sur les underscores, je vais vérifier ça.
22/05/2017, 11h20
SQLpro

Citation:

Envoyé par faya972

Bonjour, j'ai eu l'idée saugrenue de vouloir optimiser mon serveur MySQL (5.5, Debian) vu que je suis passé de 32G à 64G de RAM.
[...]
Concrètement j'ai 2 bases, une Innodb d'environ 400Mo et une MyISAM d'environ 1.5Go

Entre nous mettre 64 Go pour un volume global des données de moins de 2 Go en pensant que cela va magiquement optimiser, c'est aussi stupide que de vouloir se raser en utilisant un rasoir ayant une lame de 2500 mètres de long !!!!

A +
22/05/2017, 16h47
faya972

Citation:

Envoyé par SQLpro

Entre nous mettre 64 Go pour un volume global des données de moins de 2 Go en pensant que cela va magiquement optimiser, c'est aussi stupide que de vouloir se raser en utilisant un rasoir ayant une lame de 2500 mètres de long !!!!

A +

Cher SQLPro, où ai-je dit que les 64Go avaient pour but d'optimiser ces bases ? J'ai dit le contraire : le serveur a 64Go donc j'optimise.
Et le serveur a 64Go parce que ce n'est pas uniquement un serveur de bases de données.
Je vous remercie de votre aide.
22/05/2017, 16h57
escartefigue

@Faya972 : reconnaissez que votre formulation n'était pas très claire, j'avais compris comme SQLPro ;) dont la diplomatie est légendaire :D
Cela dit, l'image du rasoir à rallonge m'a bien amusé :P

Par contre, il est préférable dans la mesure du possible de ne pas mettre les applications sur le même serveur que la BDD.
22/05/2017, 17h07
faya972

Citation:

Envoyé par escartefigue

il est préférable dans la mesure du possible de ne pas mettre les applications sur le même serveur que la BDD.

C'est vrai. Mais je ne gère pas la bourse et les choix du client donc je suis malheureusement limité dans mes possibilité.