dbnewz

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é ?

mysqlslap

mysqlslap est un “client” du serveur MySQL au même titre que “mysql” ou “mysqladmin” (autre client). Officiellement disponible depuis la version 5.1.4 de MySQL, il est néanmoins possible de faire fonctionner mysqlslap avec des versions moins avancées. Plusieurs astuces pour cela, en voici une, suivie d’une autre. Il y’a peut-être encore plus simple : sur ma version d’ubuntu la version 5.0.51 de MySQL est installée, le binaire de mysqlslap est absent mais il m’a suffit d’installer les binaires de la 5.1.25 dans un autre répertoire pour exécuter mysqlslap via le serveur MySQL 5.0.51 en fonctionnement.

mysqlslap est très simple d’utilisation, parmi ses options les plus notables on trouve :

–auto-generate-sql : mysqlslap peut générer pour vous des requêtes SQL aléatoires. Pratique pour des tests rapides, il vaut mieux néanmoins soumettre vos propres requêtes à mysqlslap.
–query : permet d’utiliser vos propres requêtes.
–concurrency : simule un nombre de clients qui se connectent simultanément.
–iterations : … pour répéter votre test n fois.
--number-of-queries : permet d’attribuer à chaque client un nombre de requêtes approximatifs.
–engine : spécifie le moteur de stockage à utiliser lorsque vous laissez mysqlslap générer ses propres requêtes.
–csv : pour générer les résultats de vos tests au format csv.

Notes :
- Il est possible de spécifier plusieurs tests à la suite en séparant par une virgule vos valeurs pour les paramètres “concurrency”, “iterations”, “engines”.
- mysqlslap recherche par défaut une base du nom de “mysqlslap”, créez là. Ma base mysqlslap étant vide, j’ai utilisé l’instruction “USE world” afin de ne pas avoir à deplacer mes tables de test existant dans la base “world”. Pour des benchmarks plus précis, évitez d’utiliser cette commande et déplacez vos tables dans la base mysqlslap.

mysqlslap en pratique

undercat@u200:/usr/local/mysql5125$ ./bin/mysqlslap –user=mysql –concurrency=200 –number-of-queries=1000 –iterations=10 –query=”USE world;SELECT SQL_NO_CACHE ci.name, co.name from City ci, Country co where ci.countrycode = co.code and ci.name like ‘c%’”


On obtient :

Benchmark
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.015 seconds
Maximum number of seconds to run all queries: 1.265 seconds
Number of clients running queries: 200
Average number of queries per client: 5

Nous avons ici 200 clients concurrents dont on limite le nombre de requêtes à 1000 au total, ce qui nous donne environ 5 requêtes par client.

En rajoutant la clause –csv et une concurrence variable :

undercat@u200:/usr/local/mysql5125$ ./bin/mysqlslap --user=mysql –csv=/tmp/slap.csv –concurrency=1,10,50,100,200 –iterations=10 --query="USE world;SELECT SQL_NO_CACHE ci.name, co.name from City ci, Country co where ci.countrycode = co.code and ci.name like 'c%'"

J’utilise ici SQL_NO_CACHE afin de ne pas placer la requête dans le query cache, ce qui fausserait les tests.

L’option –csv couplé à openoffice / impress par exemple, permet de créer rapidement un graphique issu de nos tests. Ci-dessous le csv généré et le graphique  :

undercat@u200:/tmp$ cat slap.csv
,mixed,0.002,0.000,0.004,1,1
,mixed,0.015,0.011,0.027,10,1
,mixed,0.063,0.061,0.068,50,1
,mixed,0.133,0.122,0.205,100,1
,mixed,0.245,0.105,0.298,200,1



supersmack

Disponible sur http://vegan.net/tony/supersmack/, cet outil n’évolue plus depuis trois ans mais il reste intéressant ! Initialement développé par Sasha Pachev (un ancien de chez MySQL), puis retouché par plusieurs autre contributeurs dont Jéremy Zawodny, supersmack est moins intuitif que mysqlslap mais il se rattrape par ailleurs puisqu’il est plus largement disponible (pas seulement sur MySQL 5) et offre des réglages plus fins. Il est notamment possible de construire dynamiquement une requête à partir d’un “dictionnaire”… supersmack peut en effet “piocher” dans un fichier texte une liste d’identifiants par exemple, nous verrons comment.

Note : en plus des avantages suscités, supersmack peut également être interfacé avec PostgreSQL et Oracle.

Installation de supersmack

Le binaire n’étant pas distribué avec mysql, nos tests sous supersmack vont nécessiter quelques étapes supplémentaires.

Première étape, récupérer les sources :

wget http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz

puis

./configure --with-mysql=/usr/local/mysql5125

A ce stade vous devriez voir s’afficher ceci :

Building with the following options:

MySQL Support………………… yes
PostgreSQL Support……………. no
Oracle Support……………….. no

Thanks for using super-smack!

Faites suivre d’un make, et make install : supersmack devait être opérationnel.

Si ça n’est pas le cas, veillez à avoir installé les paquets suivants (ubuntu) :

apt-get install libmysqlclient15-dev
apt-get install build-essential

supersmack : fonctionnement

Le répertoire /smacks contient deux exemples : select-key.smack et update-select.smack.
Prenons comme base le premier fichier et voyons comment le configurer.

Evoquée en introduction, supersmack dispose d’une fonctionnalité très pratique dans certains cas, il s’agit du “dictionnaire”. Ce dernier permet de construire des requêtes dynamiques, comme si votre requête était directement issue de votre script. Exemple :

SELECT Capital FROM Country WHERE Code = “$code_pays”

Difficile d’exécuter une telle requête avec mysqlslap… Il faudrait s’arranger pour récupérer le code pays via une jointure mais dans certains cas cette information n’est tout simplement pas disponible. Il se peut que vous souhaitiez exécuter une série de requête construites dynamiquement à partir d’informations contenues dans un fichier.

Construisons à titre d’exemple un tel dictionnaire à partir de l’une de nos tables (la fameuse base “world” est encore une fois mise à contribution).

mysql> select distinct(countrycode) into outfile '/tmp/code_pays_pop_under_2M.txt' from City where population < 2000000;

On a ainsi exporté dans /tmp/code_pays_under_2M.txt la liste des codes pays qui possèdent des villes inférieures à 2 000 000 d’habitants. Le fichier contient des lignes stockés sous la forme suivante :

AFG
NLD
ANT
…

Notre “dictionnaire” désormais construit, supersmack pourra y piocher les informations requises et construire dynamiquement nos requêtes.

Voyons maintenant à quoi ressemble un fichier .smack. Nous partons ici du fichier select-key.smack pour construire un dbnewz.smack allégé pour notre exemple :

//define a dictionary
dictionary “code_pays”
{
type “rand”; // autres valeurs possibles : “seq” ou “unique”
source_type “file”; // nous utiliserons le fichier contenant nos codes pays
source “/tmp/code_pays_pop_under_2M.txt”; // emplacement du fichier “dictionnaire”
delim “,”; // take the part of the line before ,
file_size_equiv “45000″; // if the file is greater than this
//divide the real file size by this value obtaining N and take every Nth
//line skipping others. This is needed to be able to target a wide key
// range without using up too much memory with test keys
} 

//define a query
query “find_capital”
{
query “SELECT SQL_NO_CACHE ci.Name, ci.Population
FROM City ci
INNER JOIN Country co ON ci.CountryCode = co.Code
WHERE co.Code = ‘$code_pays’”;
type “req_code_pays”; // libellé de votre requete, permet de s’y retrouver dans les resultats
has_result_set “y”; // On attend des resultats (a la difference d’un UPDATE par ex).
parsed “y”; // signale que supersmack doit construire dynamiquement la requete a partir du dictionnaire
}

// define database client type
client “dbnewz”
{
user “root”; // connect as this user
pass “XXXXXXX”; // use this password
host “localhost”; // connect to this host
db “world”; // switch to this database
#socket ” /var/run/mysqld/mysqld.sock”;  // j’utilise ici la valeur de SHOW VARIABLES LIKE ’socket’;
query_barrel “1 find_capital”; // Execute 1 fois la requete “find_capital” a chaque iteration.
}

main
{
dbnewz.init(); // initialize the client
dbnewz.set_num_rounds($2); // le 2eme argument recu par supersmack est le nombre d’iterations.
dbnewz.create_threads($1); // permet de definir combien de clients simultanés l’on souhaite.
dbnewz.connect();
dbnewz.unload_query_barrel(); // for each client fire the query barrel
dbnewz.collect_threads();
dbnewz.disconnect();
}

Il est également possible pour supersmack de créer automatiquement les tables dans lesquelles se dérouleront les tests, ouvrez le fichier select-key.smack pour davantage d’informations, il fournit la syntaxe relative à cette fonctionnalité.

Nous pouvons désormais lancer le test, ici 50 clients concurrents et 1000 itérations :

root@u200:/opt/super-smack-1.3/smacks# super-smack dbnewz.smack 50 1000

Query Barrel Report for client dbnewz
connect: max=888ms  min=7ms avg= 213ms from 50 clients
Query_type    num_queries    max_time    min_time    q_per_s
req_code_pays    50000    41    1    1327.08

Nous avons ici obtenu en moyenne 1327 requêtes / seconde pour cette requêtes et 50 clients concurrents entre le début et la fin de nos tests.

Connecté sur le serveur MySQL en question, on observe via un SHOW FULL PROCESSLIST des requêtes du type :

SELECT SQL_NO_CACHE ci.Name, ci.Population
FROM City ci
INNER JOIN Country co ON ci.CountryCode = co.Code
WHERE co.Code = ‘LKA’

Le code ‘LKA’ a été remplacé par supersmack lui-même, le dictionnaire a donc bien fonctionné.

Vous venez de faire connaissance avec ces deux outils de benchmark (si ça n’était pas déjà fait), pensez à les utiliser lors de vos prochaines évaluations de performance (serveur / requêtes). Il est en effet toujours appréciable de pouvoir qualifier la situation de départ afin de mesurer l’étendue des progrès effectués par la suite.

---

Autres billets susceptibles de vous intéresser :

mysql_secure_installation, utile mais non paramétrable

DBDesigner 4 : générer son MCD par reverse engineering

Les covering index, de la théorie à la pratique avec MyISAM et InnoDB

15 secondes pour installer une réplication MySQL avec MySQL Sandbox, pari tenu ?

Cardinalité, sélectivité et distributivité d’un index MySQL : quel impact sur le plan d’exécution ?

Cet article a été publié le Monday 7 July 2008 à 12:34 am et est classé dans MySQL, Oracle. Vous pouvez suivre les réponses reçues par cet article grâce au fil RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre site.