dbnewz

Le prochain article de notre série consacrée aux index MySQL approche et j’ai besoin pour ce prochain épisode de générer une table de test de la forme suivante :

CREATE TABLE t (
id mediumint(8) unsigned NOT NULL auto_increment,
date timestamp(14) NOT NULL,
flag tinyint(4) NOT NULL default ‘0′,
PRIMARY KEY  (id),
KEY flag (flag)
) TYPE=MyISAM;

La structure est définie, reste à alimenter la table, disons 1 million d’enregistrements.

La valeur du champ “flag” est importante pour nos tests ultérieurs, sa valeur doit pour le moment être comprise entre 0 et 1, le tout à peu près uniformément distribué.

La requête sera la suivante :

INSERT INTO test.t (flag) VALUES (ROUND(RAND()));

Il faut maintenant exécuter celle-ci 1 million de fois.
Voyons ce que cela donne en utilisant le shell, mysqlslap ou bien encore une procédure stockée.

(more…)

Le but de ce billet est d’installer rapidement une configuration permettant de tester le cluster MySQL.
Il existe plusieurs façons de faire, en voici une basée sur les binaires de la dernière version à ce jour, la cluster 6.2.15. Rappelez-vous que depuis la version 5.1.25 de MySQL, les binaires du cluster ne sont plus inclus mais disposent de leur propre branche.

Haute disponibilité (redondance), montée en charge et haute performance (données en mémoire vive), une architecture en “shared-nothing” (aucun élément du cluster ne partage le même hardware) et un serveur mysql doté d’un nouveau moteur de stockage (NDB encore appelé NDBCLUSTER), voici quelques caractéristiques du cluster MySQL. Voyons comment l’installer en un minimum d’étapes.

Vue d’ensemble des éléments composant un cluster

Ce schéma issu de la documentation MySQL expose les différents élements qui composent un cluster MySQL, passons-les rapidement en revue.

(more…)

Je n’avais pas prévu d’écrire un billet sur mysql_secure_installation, c’est en préparant le prochain article (installation du cluster MySQL) que m’est venue l’idée d’écrire quelques lignes sur le sujet.

Ce script, présent dans le répertoire “bin” de votre installation de MySQL, a pour but de “sécuriser” votre base une fois celle-ci installée. Il vous est d’ailleurs conseillé de l’exécuter une fois mysql_install_db lancé ou à défaut de passer par mysqladmin pour au moins modifier le mot de passe associé à l’utilisateur “root”.

Lors de l’installation du cluster, je pars du principe qu’un autre serveur MySQL est susceptible de tourner sur le SQL node, je choisis donc d’opter pour un port différent de celui assigné par défaut au serveur MySQL (3306). Je m’aperçois alors que je n’ai aucun moyen de faire pointer mysql_secure_installation vers mon serveur MySQL en 3307, le script pointant automatiquement vers le port par défaut…

Face à ce problème, au moins deux solutions, taper manuellement les requêtes automatisées de mysql_secure_installation, ou modifier le script.

(more…)

Il est parfois reproché au serveur MySQL de ne pas fournir suffisamment d’outils de benchmark / profiling concernant les requêtes ou le fonctionnement du serveur lui-même. Des commandes telles que SHOW STATUS (affiche l’état du serveur à un instant t), ou bien encore EXPLAIN (plan d’exécution de la requête SQL) permettent néanmoins d’obtenir de précieuses informations.

Ceci étant dit, comment s’assurer que son serveur MySQL tiendra la charge ? 1000 requêtes /s en insertion sont prévues le jour de la sortie de votre prochain service internet : votre serveur sera t-il capable d’y faire face  ?

Les deux outils présentés aujourd’hui permettent de simuler la charge reçue par le serveur MySQL en fonction de différentes paramètres dont le nombre de connexions simultanées et le nombre de requêtes par utilisateurs. Avec de tels outils, vous pouvez par exemple tester mysqlslap sur une de vos requêtes clé, visualiser comment celle-ci réagit sous différentes configurations, et visualiser un “score” à base de temps d’exécution. Une fois ce “score” récupéré, comparez-le avec celui que vous obtiendriez avec la même requête modifiée par vos soins, avez-vous progressé ?
(more…)

Disposer d’un MCD (modèle conceptuel de données) lorsqu’on travaille sur une requête SQL impliquant différentes tables représente un gain de temps.
Il est en effet plus rapide de jeter un coup d’oeil sur un MCD afin de repérer quels sont les champs qui lient une table à une autre plutôt que d’enchaîner les “DESC ma_table”, puis repérer la clé primaire et les éventuelles clés étrangères, et rebolote sur la ou les tables de destination…

La prochaine série d’articles sur les index MySQL va nous amener à enchaîner quelques requêtes sur une des deux bases d’exemple disponibles sur le site de MySQL : world et sakila, le prétexte est donc tout trouvé pour évoquer ici la solution que j’ai retenu pour obtenir le MCD de ces tables : DBDesigner 4.

Cet outil n’est pas nouveau, son successeur officiel est même déjà connu, il s’agit de MySQL Workbench. Celui-ci n’étant pas encore disponible sous linux, nous utiliserons son ancêtre et plus particulièrement sa fonctionnalité de “reverse engineering” : une fois connecté à votre base, DBDesigner 4 va générer sous forme graphique vos tables, leurs descriptions, et si tout se passe bien, les relations entre vos tables.

(more…)

Voici mon problème du jour : Comment lancer un script (de maintenance par exemple) qui fait appel à mysql, sans stocker en dur le nom de l’utilisateur et le mot de passe (ce qui est mal, très très mal). Le but est que seul un utilisateur privilégié (je n’ai pas forcément dit root ! je pense plutôt à un compte système comme mysql par exemple) puisse lancer ce script.

C’est plutôt facile, et je fournis trois solutions pour la peine :

1. Avoir un fichier de configuration pour le script accessible seulement par l’utilisateur privilégiéOn définit dans le fichier de configuration des variables d’environnement, une pour le user, une autre pour le mot de passe. Dans le script il ne reste qu’à utiliser la commande source pour récuperer ses variables (seul l’utilisateur privilégié pourra lire le fichier). Simple et efficace.
2. Avoir un fichier de configuration pour mysql accessible seulement par l’utilisateur privilégiéVariante de la précédente : on crée un fichier de configuration spécifique pour mysql (que l’on pourra mettre par exemple dans /etc/mysql mais il n’y a aucune obligation). Et on utilise l’option –defaults-file pour que mysql lise le contenu du fichier (il lit notamment les sections [client] et [mysql]). Exemple de fichier:
   
[client]
host = localhost
user = votre_user
password = votre_mot_de_passe
socket = /var/run/mysqld/mysqld.sock

   Bonus : on peut spécifier d’autres options pour influer sur mysql (en vrac, le nom de serveur, le jeu de caractères…).
3. Pour ceux qui ont plusieurs scripts mais qui ont des options qui diffèrent légèrementVous avez des scripts quasiment identiques mais les options diffèrent légèrement (ou vous voulez centraliser tout en un seul fichier) : c’est possible. C’est une variante du cas précedent : on écrit un fichier de configuration mysql que l’on découpe en plusieurs sections. Ensuite, on fait appel au programme my_print_defaults ! my_print_defaults examine le fichier de configuration (option -c pour spécifier le votre…) et donne en sortie les paramètres à passer à mysql sous forme d’argument. Exemple :
   
$ my_print_defaults -c /etc/mysql/maconf.cnf client
--host=localhost
--user=votre_user
--password=votre_mot_de_passe
--socket=/var/run/mysqld/mysqld.sock

   Il ne reste plus qu’à passer cela à mysql en récuperant la sortie dans une variable (ou en utilisant cette petite merveille de xargs).

J’ai eu un petit problème sympa à résoudre. En résumé, nous avons 2 tables t1 et t2.

mysql> select * from t1;
+——+——+
| id | name |
+——+——+
| 1 | a |
| 2 | b |
| 3 | ab |
| 4 | bc |
+——+——+

mysql> select * from t2;
+————–+———+
| dependent_id | base_id |
+————–+———+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 3 | 2 |
| 4 | 2 |
+————–+———+

Nous ouvrons une transaction (T1) et nous exécutons la requête suivante.

SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;

+——+——+————–+———+
| id | name | dependent_id | base_id |
+——+——+————–+———+
| 1 | a | 1 | 1 |
| 3 | ab | 3 | 1 |
+——+——+————–+———+

Nous ouvrons une transaction (T2) et nous exécutons la requête suivante.

SELECT * FROM roles WHERE id=2 FOR UPDATE;

Et alors que les 2 requêtes ne sont pas sensées travailler sur les même lignes, T2 attend la fin de T1? Comment est ce possible?

Pour comprendre vous devez analyser le plan d’exécution.

mysql> explain SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |

Voila l’erreur, MySQL fait un full table scan (type ALL) pour exécuter cette requête et ainsi bloque toutes les lignes. T2 est ainsi obliger d’attendre la fin de T1. Pourquoi?

Ayant seulement 4 entrées dans cette table, l’optimiseur de MySQL préfère un faire un full table scan plutot qu’utiliser un index. De la j’ ai inséré d’autre entrée dans la table t1, et le plan d’exécution a changé.

| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 217 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.dependent_id | 1 | |

MySQL bloque ainsi seulement la PK et fait bien du ‘row level locking’. T2 peut aboutir sans problème. CQFD!

Dans une architecture MySQL nous retrouvons un maître (M) pour les écritures et plusieurs esclaves (S) pour la lecture. MySQL ne permet pas l’extensibilité en écriture mais en lecture, d’ou la notion de “Shards”, un ensemble de plusieurs blocks (M-S). L’idée est de partitionner vos données. Mais ceci est une autre histoire. Pour accéder à vos esclaves, nous utilisons une VIP/Load Balancer en “round robin”. Vos applications étant ainsi re-dirigées vers un esclave de façon aléatoire.
La question est maintenant: Comment puis je maintenant enlever un esclave de la rotation?
Tout va dépendre de ce qui vous utilisez comme HW / SW.

En tout cas, ce que nous voulons:

• Ne pas arrêter l’esclave: pour pouvoir travailler dessus sans pour autant le laisser en rotation
• Ne pas utiliser skip-networking: pour ne pas avoir à redémarrer MySQL

Donc nous allons devoir couper la vérification en 2 parties:

• 1. Server OK
• 2. MySQL OK

Votre VIP devra sortir le serveur MySQL de la rotation si (1) ou si (1) ET (2). Un simple stratagème est de mettre un serveur http avec un fichier X.html. Si le fichier X.html est présent alors le serveur est ok.
Ensuite vous pouvez vérifier la connectivité MySQL en elle même.

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.