OVH Community, votre nouvel espace communautaire.

Anticiper la charge mysql


AIexis
25/11/2014, 15h46
Je ne connaissais pas la formule de calcul de table_cache.
J'ai simplement suivi les directives proposées en augmentant graduellement la valeur de table_cache (qui apparemment est devenu table_open_cache dans les dernières versions de mysql), jusqu'à ce qu'apparaisse ce message abscons signifiant que la valeur que j'ai mise est inférieure à 524288
D'après ce que j'ai vu après, 524288 est la valeur max, donc je ne peux pas mettre plus.
Je ne comprends donc pas pourquoi mysqltuner me demande de l'augmenter...

Mais effectivement, avec l'unité, mysql ne doit pas comprendre ... un message d'erreur dans les logs aurait été le bienvenu ! (y'a rien)
Je vais remettre une valeur plus conforme, par exemple 5000, et je verrai bien.
Merci

TBC_Ly0n
25/11/2014, 15h35
Alors là, va falloir m'expliquer comment tu fais pour avoir besoin d'un aussi gros table_cache.
Le table_cache se calcule de la manière suivante : N x C
Où, au maximum, nous avons :
- C est le nombre de connexions maximales
- N est le nombre maximal de tables jointes dans une seule requête

Certains champs acceptent des unités, d'autres non. table_cache fait partie des paramètres qui n'acceptent pas d'unités.

tuning-primer.sh + mysqltuner.pl pour y voir plus clair. Je trouve mysqltuner moins pertinent que tuning-primer.sh

AIexis
24/11/2014, 19h41
Je remonte ce sujet parce que j'ai un petit souci avec mysqltuner.
Dans les propositions, ça dit:
table_cache (>524288)

or dans mon my.ini j'ai
table_cache=30M
table_open_cache=30M

Il me semble que 30M est bien supérieur à 524288 pourtant...
J'ai suivi toutes les recommandations précédentes, et tout a fonctionné, sauf ça.
Une idée ?

cybaloo
14/11/2014, 14h48
pas forcément en get , tu as des webservice en post.
Ton webservice va vérifier que ce que tu envois est bon, gérer la sécurité à son niveau, rajouter ce qu'il faut en log ( je ne sais le niveau de log de mysql mais là c'est toi qui rédige tes logs) et taper en localhost peinard

AIexis
14/11/2014, 12h13
Je garde précieusement ton post, même si j'ai pas tout compris
Si le terminal est volé ou perdu, je pensais simplement bloquer son IP (qui est fixe) sur le serveur. Je conçois que c'est pas l'idéal mais ça m'a semblé un bon début, déjà... (après, j'ai pas idée de comment on fait ça, si c'est sur la config mysql, ou windows, mais je me suis pas trop inquiété).

Le principe d'un webservice, ça serait quoi plus précisément ? au lieu d'accéder au serveur directement par le port 3306, j'envoie des données par exemple en ouvrant une url avec des données en GEt sur un serveur HTTP qui rebalance sur le serveur MYSQL?

Ça me semble plus délicat à débugguer si ça congestionne quelque part, mais bon... vu de loin ça parait plus "safe" en effet.

TBC_Ly0n
14/11/2014, 10h39
MyISAM utilise des verrouillages par table. InnoDB utilise des verrouillage par ligne.
Des paramètres comme concurrent_insert te permettront de mieux gérer les flux d'écritures.
Au fur et à mesure de l'évolution, il faudra retailler les ib_logfiles, changer le nombre de threads concurrents et la mise à jour des journaux d'activité.
Evidemment, innodb_file_per_table obligatoire

Dans quelle mesure peux-tu faire confiance à un terminal :
- Perte / Vol : du terminal, du compte MySQL...
- Configuration : Terminal mal configuré

En partant du postulat qu'on ne peut pas faire totalement confiance à un terminal distant (ce qui est la vraie vie d'une application de gestion à distance d'équipements), il va être nécessaire de rajouter une intelligence conséquente à un niveau pour valider la cohérence de tes données. Soit tu le fais par des webservices qui te permettront de gérer l'intégrité de tes données par une couche d'isolation sans divulguer de compte MySQL, soit tu ajoutes les triggers / procédures stockées nécessaires et suffisantes pour faire le même boulot en partant du principe qu'il faudra être en mesure de gérer la perte d'un terminal et par là-même la révocation de son accès à la base de données. Ce qui m'amène à penser qu'il faudrait que les terminaux n'aient accès qu'à des procédures stockées.

MySQL sait gérer l'authentification par certificat. Evidemment, en SSL, parce que faire transiter des données en clair, c'est mal, d'autant plus que tu ne maitrises pas la couche réseau.

Si tu veux, je te montre pourquoi avoir un port 3306 ouvert est une très mauvaise idée en termes de disponibilité MySQL n'est pas fait pour résister aux aggressions "réseau".

Je vais te le charger, moi, ce serveur MySQL

AIexis
14/11/2014, 10h03
Citation Envoyé par TBC_Ly0n
Bonjour,

La capacité du serveur à monter en charge dépend :
  • de la concurrence des accès en écriture aux mêmes jeux de données
  • du schéma de données (et en particulier l'indexation mise en place)
  • la quantité de données à manipuler
  • de la configuration du système


Normalement, il faudrait le faire dans cet ordre-là :
  1. Analyse fine du modèle de données
  2. Analyse des requêtes une par une (à coups d'EXPLAIN) qui permettra de valider le modèle de données.
  3. Choix du moteur (InnoDB / MyISAM) en fonction de l'analyse


Dans les faits, on embauche des développeurs, pas des analystes ! Donc, comme tout le monde, tu vas suivre le même cheminement... A l'envers !

D'un point de vue personnel
  • je n'exposerais pas le serveur MySQL directement sur le réseau. Je passerais par des webservices qui pourront se charger de garantir la sécurité (authentification, qualité des données...)
  • J'utiliserais InnoDB sur une version de MySQL > 5.1 pour utiliser le format Compressed d'InnoDB
  • Ca peut encaisser de très fortes charges... Sauf que sous Windows, les outils de tuning sont rares et pas aussi efficaces
  • En fonction des cas d'utilisation, on peut aller à plusieurs dizaines de milliers d'équipements... ou beaucoup moins !
Merci pour ta réponse et tes conseils
En effet, je ne suis pas analyste (j'ai même aucun souvenir de mes cours de SGBD, Merise et tout le bordel, pourtant je suis passé par cette case là dans ma formation, à un moment... ), et je ne suis même pas développeur à la base
J'essaye d'être rigoureux mais forcément quand on n'a pas toutes les connaissances, c'est compliqué.
Pour répondre point par point:
1- chaque terminal écrit dans la même table. ça pourrait être un problème si ça correspond au même "jeux de données"
2- L'indexation a été faite consciencieusement, autant que je sache.
3- la quantité de données remontée chaque minute est d'environ 2Ko. Faible ou pas faible, c'est surement une question de point de vue
4- la configuration matérielle est largement surdimensionnée à l'heure actuelle. La configuration de mysql est celle par défaut ou quasiment (juste fait de l'ajout de query-cache hier)

Pour le reste, passer par un serveur tiers m'a traversé l'esprit, mais ça m'a semblé ajouter des contraintes (+ de risque de panne, délais). Certes c'est peut-être un peu violent que chaque terminal attaque le port 3306 du serveur, cash. Mais ça doit dépendre du nombre.
Question sécurité, il faudra bien que le mot de passe du user mysql transite vers le webservice de toute façon, non ?
Concernant la version de mysql: Version du logiciel: 5.5.29 - MySQL Community Server (GPL), tout en innoDB. Le format compressed ? je ne connais pas, je vais chercher.
Windows... oui.... pour l'instant je suis obligé, mais ça changera surement si besoin. Cela dit l'outil mysqltuner compilé sous windows donne des résultats.
Encore merci

AIexis
14/11/2014, 09h48
En modifiant un peu mon fichier my.conf (grâce aux analyses de mysqltuner), la charge CPU de mysqld est maintenant toujours inférieure à 0.6%.
Je ne sais pas si c'est une coïncidence, ou le fait que j'ai relancé depuis moins de 24h, mais c'est plutôt encourageant tout ça
Par défaut, il n'y a pas d'option de cache, et ça fait grandement travailler le serveur mysql. Je comprends pas trop comment c'est possible d'avoir un cache (et donc le risque d'avoir des résultats de requêtes faux si les tables ont changé entre temps ? non?) mais si ça marche je ne me plains pas

TBC_Ly0n
14/11/2014, 09h46
Bonjour,

La capacité du serveur à monter en charge dépend :
  • de la concurrence des accès en écriture aux mêmes jeux de données
  • du schéma de données (et en particulier l'indexation mise en place)
  • la quantité de données à manipuler
  • de la configuration du système


Normalement, il faudrait le faire dans cet ordre-là :
  1. Analyse fine du modèle de données
  2. Analyse des requêtes une par une (à coups d'EXPLAIN) qui permettra de valider le modèle de données.
  3. Choix du moteur (InnoDB / MyISAM) en fonction de l'analyse


Dans les faits, on embauche des développeurs, pas des analystes ! Donc, comme tout le monde, tu vas suivre le même cheminement... A l'envers !

D'un point de vue personnel
  • je n'exposerais pas le serveur MySQL directement sur le réseau. Je passerais par des webservices qui pourront se charger de garantir la sécurité (authentification, qualité des données...)
  • J'utiliserais InnoDB sur une version de MySQL > 5.1 pour utiliser le format Compressed d'InnoDB
  • Ca peut encaisser de très fortes charges... Sauf que sous Windows, les outils de tuning sont rares et pas aussi efficaces
  • En fonction des cas d'utilisation, on peut aller à plusieurs dizaines de milliers d'équipements... ou beaucoup moins !

ValentinC
13/11/2014, 19h52
Citation Envoyé par AIexis
Y'a une version mise à jour dans mon édition du post au dessus
En effet, on à posté en même temps.

AIexis
13/11/2014, 19h26
Citation Envoyé par ValentinC
Si ton système est bien développé et donc stable avec une règle de trois tu peux très vulgairement te donner une estimation en laissant une marge de 15/20% sur la capacité total du serveur (c'est pas ultra pro mais ça peux te donner un ordre d'idée).

Pour MySQLTuner sous windows il y à bien une version, je l'utilise et ça fonctionne très bien (http://www.kevinfatkin.net/mysql-tun...t-for-windows/).
Y'a une version mise à jour dans mon édition du post au dessus

ValentinC
13/11/2014, 19h17
Si ton système est bien développé et donc stable avec une règle de trois tu peux très vulgairement te donner une estimation en laissant une marge de 15/20% sur la capacité total du serveur (c'est pas ultra pro mais ça peux te donner un ordre d'idée).

Pour MySQLTuner sous windows il y à bien une version, je l'utilise et ça fonctionne très bien (http://www.kevinfatkin.net/mysql-tun...t-for-windows/).

AIexis
13/11/2014, 18h31
Apparemment mysqltuner tourne pas sous win, tant pis.
Ah si, ici: https://mysqltuner.codeplex.com/

AIexis
13/11/2014, 17h35
Le serveur fournit d'autres services (IIS avec php) mais c'est dérisoire, c'est pour héberger des potes avec 3 visites par jour, aucun souci avec ça.
Les optimisations mysql, je voudrais bien, mais je ne connais pas assez bien ce dossier :/
Le nombre max par défaut est de 100, et il va donc falloir que je l'augmente, mais quelles conséquences ça peut avoir ?
Merci pour mysqltuner.pl, je vais voir si je peux installer perl et faire tourner ça sous win.

captainadmin
13/11/2014, 16h27
Bonjour,

La question est assez vague.

Est-ce que ton serveur héberge uniquement le serveur mysql ou fournit-il d'autres services?
Prévois tu de faire des optimisations pour mysql, qu'il puisse accepter le maximum de connexion possible en fonction de la puissance allouée ?

Tu as des paramètres à ajuster, comme le nombre max de connexion à ton serveur, le cache ou encore pleins de paramètres sympathiques.
Pour t'aider tu as des scripts comme mysqltuner.pl

Le mieux est de faire des tests de capacité de ton serveur mysql


Bon courage
http://www.captainadmin.com

AIexis
13/11/2014, 14h24
Bonjour
j'ai conscience que je vais poser une question compliquée
Le dédié: Xeon E3-1225 V2 @3.2Ghz
RAM: 16Go
Windows server 2012

Des terminaux se connectent une fois par minute à se serveur pour remonter des infos basiques dans une base de données mysql (IP, uptime, charge cpu, température.. bref quelques champs dans une table).
La question c'est: combien de ces terminaux le serveur mysql pourrait "supporter" avant qu'il y ait des problèmes ?
100 ? 1000 ? un million ?
Actuellement, il y en a 65, et le process mysqld tourne entre 0.5 et 1.2%, et la RAM environ 500Mo
Ces infos pourraient me suffire à faire une estimation avec une bête règle de trois, mais j'ai l'intuition que ça n'est pas aussi simple
Merci