dbnewz

Aujourd’hui fut la journée des ‘too many connections’. En effet pas loin de 3 applications ont planté du fait que le nombre maximum de connexions MySQL avait été atteint. Le message d’erreur est très parlant. Comment est ce possible? mysqld autorise max_connections+1 clients à se connecter. Le ‘+1′ est une extra connexion réservée aux comptes ayant le privilège SUPER. Donc si votre user applicatif à ce privilège, vous vous retrouvez bloqués. Prenez comme principe d’avoir les privilèges minimaux pour vos utilisateurs, INSERT, UPDATE, DELETE et SELECT suffisent largement.

La valeur par défaut du paramètre est de 100.


mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+


Donc à vous de connaître vos besoins et d’allouer un nombre maximum suffisant pour votre application. Cela peut être utile pour vous montrer une erreur logicielle si le nombre est trop important.

Maintenant quelle est le problème si j’autorise trop de connexions simultanées. Tout simplement d’ utiliser trop de mémoire et de planter votre serveur. Il faut garder en tête cette rapide équation:

Un connexion ( MySQL thread ) utilise en RAM au maximum:
( thread_stack + net_buffer_length + max_allowed_packet + read_buffer_size + join_buffer_size + tmp_table_size + myisam_sort_buffer_size )

Sachant que vous avez déjà alloué de la mémoire à votre buffer pool / key cache, restez vigilant à ne pas dépasser le total de votre serveur.

Avec MySQL 5.0, une nouvelle variable est apparue, ‘max_user_connections’ pour limiter le nombre de connexions concurrentes pour un même utilisateur. C’est une variable globale pour TOUS les utilisateurs et activée pour les valeurs > 0.


mysql> show variables like 'max_user_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_user_connections | 0 |
+----------------------+-------+


Lorsque vous avez besoin de créer un identifiant unique, la plupart des SGBD vous propose ce que l’on appelle une séquence. Je traduirais ça par une suite, une liste ordonnée d’objets. A chaque fois que tu avais besoin d’un identifiant unique vous incrémentez la séquence de 1. En général la syntaxe est plus ou moins la même, select (sequence).NEXTVAL et le tour est joué. Maintenant MySQL ne fournit pas un mécanisme de ce genre pour l’instant, donc soit vous simulez vous même ce comportement ( en créant une table réservée à la génération d’identifiant unique) soit vous utilisez une colonne AUTO_INCREMENT.

CREATE TABLE `mytable` (
`id` tinyint unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
)

Un limitation déjà, l’AUTO_INCREMENT doit faire parti de la clé primaire. A chaque fois que vous ajoutez une ligne sans cette table la valeur sera incrémenté de 1 par rapport à la dernière valeur ajouté dans la table. Et c’est la que le bas blesse. J’ai eu cette semaine toute un chaîne de serveurs bloqué pendant quelques heures à cause de cet AUTO_INCREMENT.

Même si vous remettez l’AUTO_INCREMENT à 1 (ALTER TABLE tbl AUTO_INCREMENT = 1;) sur le prochain INSERT la valeur sera toujours MAX(id) + 1.

Il y a des limites à l’utilisation de cette fonctionnalité, principalement si votre architecture repose sur des serveurs en DUAL master. En résumé, l’AUTO_INCREMENT n’est PAS une séquence.

Qui n’a jamais utilisé MySQL sans la réplication? La réplication permet à votre application de supporter un nombre de lecture beaucoup plus important. Les updates se font sur une base de donnée “maître” (master) et sont ensuite répliquées sur plusieurs “esclaves” (slaves). Le maître n’a pas à supporter les requêtes venant de clients qui sont gérées par les esclaves. Un autre jour je rentrerai plus en détails la dessus mais en résumé, le maître écrit dans des fichiers toutes les requêtes qui ont modifié ses informations (insert, update et delete). Les slaves viennent à leur tour récupérer ces fichiers (binary logs) et les “copie” localement (relay logs) avant de les rejouer.

Il est conseillé en général de dédier un disque pour ces fichiers car beaucoup d’écriture entraîne une baisse de vos performances. Dans le cas qui nous intéresse, l’activité du CPU m’a très étonné. En effet jusqu’à 60% du temps CPU étaient utilisé par ‘USER’ juste en activant la réplication.
(more…)