{"id":362,"date":"2009-01-04T00:04:44","date_gmt":"2009-01-03T23:04:44","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=362"},"modified":"2009-01-03T21:23:14","modified_gmt":"2009-01-03T20:23:14","slug":"les-nouveautes-de-mysql-51-part-55","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/01\/04\/les-nouveautes-de-mysql-51-part-55\/","title":{"rendered":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 5\/5)"},"content":{"rendered":"<p><!-- \t \t --><br \/>\n<em>(<a title=\"Les nouveaut\u00e9s de MySQL 5.1 -- (part 4\/5)\" href=\"http:\/\/dasini.net\/blog\/2008\/12\/13\/les-nouveautes-de-mysql-51-part-45\/\">&lt;- pr\u00e9c\u00e9dent<\/a>)<\/em><br \/>\n<!-- \t \t --><\/p>\n<h2>mysqlslap : un utilitaire de test de charge et de performance<\/h2>\n<p>Disponible depuis MySQL 5.1.4, <em>mysqlslap<\/em> permet d&rsquo;effectuer des tests de stress et de charge sur votre serveur MySQL. Vous pourrez alors tester les performances de votre SGBDR pr\u00e9f\u00e9r\u00e9, apr\u00e8s, par exemple, une nouvelle installation, un changement d&rsquo;architecture ou apr\u00e8s avoir modifi\u00e9 des param\u00e8tres de configuration.<\/p>\n<p>Cr\u00e9\u00e9 pour devenir le meilleur ami des administrateurs de bases de donn\u00e9es et des d\u00e9veloppeurs, le client <em>mysqlslap<\/em> envoie des requ\u00eates au serveur MySQL en cr\u00e9ant plusieurs connexions simultan\u00e9es. A la fin de la simulation, un rapport de diagnostic est cr\u00e9e sur la sortie standard. Vous avez aussi la possibilit\u00e9 de l&rsquo;\u00e9crire dans un fichier au format CSV utilisable directement avec Calc d&rsquo;OpenOffice ou Excel de Microsoft.<\/p>\n<p>Exemple d&rsquo;utilisation de <em>mysqlslap<\/em> avec les options suivantes :<\/p>\n<ul>\n<li><strong>user<\/strong>, <strong>password<\/strong>, <strong>socket <\/strong>: autentification classique d&rsquo;un client MySQL,<\/li>\n<li><strong>concurrency <\/strong>: nombre de \tclients effectuant simultan\u00e9ment une requ\u00eate SELECT,<\/li>\n<li><strong>iterations <\/strong>: nombre \td&rsquo;it\u00e9rations du test \u00e0 faire,<\/li>\n<li><strong>number-of-queries<\/strong> : \tnombre total de requ\u00eates effectu\u00e9es lors du test,<\/li>\n<li><strong>engine <\/strong>: moteur(s) \u00e0 \tutiliser,<\/li>\n<li><strong>auto-generate-sql<\/strong> : \t<em>mysqlslap<\/em> g\u00e9n\u00e8re automatiquement un jeu de tests,<\/li>\n<li><strong>number-int-cols<\/strong> : nombre \tde colonnes de type INT \u00e0 cr\u00e9er,<\/li>\n<li><strong>number-char-cols<\/strong> : \tnombre de colonnes de type CHAR \u00e0 cr\u00e9er,<\/li>\n<li><strong>auto-generate-sql-load-type<\/strong> : permet de sp\u00e9cifier le type de requ\u00eates (lecture, insertion, \tlecture sur la cl\u00e9 primaire, mise \u00e0 jour ou mixte)<\/li>\n<\/ul>\n<p><!-- \t \t --><\/p>\n<p>Tests de performances avec des tables MyISAM et innoDB:<\/p>\n<pre>shell&gt; mysqlslap --user=daz --password --socket=\/tmp\/mysql51.sock\r\n--concurrency=1,100   --iterations=10  --number-of-queries=1000\r\n--engine=myisam,innodb   --auto-generate-sql  --number-int-cols=2\r\n--number-char-cols=3   --auto-generate-sql-load-type=mixed\r\n\r\n<!-- \t \t -->\r\nBenchmark\r\n   Running for engine myisam\r\n   Average number of seconds to run all queries: 1.576 seconds\r\n   Minimum number of seconds to run all queries: 1.539 seconds\r\n   Maximum number of seconds to run all queries: 1.631 seconds\r\n   Number of clients running queries: 1\r\n   Average number of queries per client: 1000\r\nBenchmark\r\n   Running for engine myisam\r\n   Average number of seconds to run all queries: 1.792 seconds\r\n   Minimum number of seconds to run all queries: 1.543 seconds\r\n   Maximum number of seconds to run all queries: 2.107 seconds\r\n   Number of clients running queries: 100\r\n   Average number of queries per client: 10\r\nBenchmark\r\n   Running for engine innodb\r\n   Average number of seconds to run all queries: 2.477 seconds\r\n   Minimum number of seconds to run all queries: 2.430 seconds\r\n   Maximum number of seconds to run all queries: 2.663 seconds\r\n   Number of clients running queries: 1\r\n   Average number of queries per client: 1000\r\nBenchmark\r\n   Running for engine innodb\r\n   Average number of seconds to run all queries: 2.704 seconds\r\n   Minimum number of seconds to run all queries: 0.725 seconds\r\n   Maximum number of seconds to run all queries: 5.272 seconds\r\n   Number of clients running queries: 100\r\n   Average number of queries per client: 10<\/pre>\n<p><em><\/em><br \/>\n<!-- \t \t --><\/p>\n<p><!-- \t \t --><\/p>\n<h2>Conclusion<\/h2>\n<p>Toujours aussi performant, fiable et robuste, certes un peu moins simple \u00e0 administrer mais tellement plus riche, cette nouvelle version de MySQL devrait faire le bonheur des administrateurs de bases de donn\u00e9es et des d\u00e9veloppeurs. Nous esp\u00e9rons que cet article vous a mis l&rsquo;eau \u00e0 la bouche et vous donnons d&rsquo;ors et d\u00e9j\u00e0 rendez-vous au prochain num\u00e9ro pour explorer plus en d\u00e9tail le partitionnement de MySQL.<\/p>\n<p><!-- \t \t --><\/p>\n<p><em><a title=\"Les nouveaut\u00e9s de MySQL 5.1 -- (part 1\/5)\" href=\"http:\/\/dasini.net\/blog\/2008\/11\/28\/les-nouveautes-de-mysql-51-part-15\/\">Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 1\/5)<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>mysqlslap : un utilitaire de test de charge et de performance<\/p>\n<p>Disponible depuis MySQL 5.1.4, mysqlslap permet d&rsquo;effectuer des tests de stress et de charge sur votre serveur MySQL. Vous pourrez alors tester les performances de votre SGBDR pr\u00e9f\u00e9r\u00e9, apr\u00e8s, par exemple, une nouvelle installation, un changement d&rsquo;architecture ou apr\u00e8s avoir modifi\u00e9 des param\u00e8tres de configuration.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[247,241,59,80],"class_list":["post-362","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-bench","tag-mysql","tag-mysql-51","tag-mysqlslap"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-5Q","jetpack-related-posts":[{"id":334,"url":"https:\/\/dasini.net\/blog\/2008\/12\/13\/les-nouveautes-de-mysql-51-part-45\/","url_meta":{"origin":362,"position":0},"title":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 4\/5)","author":"Olivier DASINI","date":"13 d\u00e9cembre 2008","format":false,"excerpt":"Le moteur de stockage CSV n'est pas une vraie nouveaut\u00e9. Il est disponible depuis MySQL 4.1.4 (MySQL 5.1 pour M.S. Windows). Sa particularit\u00e9 est de stocker les donn\u00e9es dans un fichier texte au format CSV (Comma Separated Values) o\u00f9 les donn\u00e9es sont s\u00e9par\u00e9es par une virgule. Les avantages sont multiples,\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":474,"url":"https:\/\/dasini.net\/blog\/2009\/03\/02\/presentation-vue-densemble-de-mysql-51\/","url_meta":{"origin":362,"position":1},"title":"Pr\u00e9sentation: Vue d&rsquo;ensemble de MySQL 5.1","author":"Olivier DASINI","date":"2 mars 2009","format":false,"excerpt":"Num\u00e9ro un pour les applications en ligne, MySQL se positionne maintenant sur le secteur des applications d\u2019entrep\u00f4ts de donn\u00e9es et d\u2019informatique d\u00e9cisionnelle\u2026 http:\/\/dasini.net\/blog\/presentations\/?#presentation_mysql51","rel":"","context":"Dans &quot;Pr\u00e9sentation&quot;","block_context":{"text":"Pr\u00e9sentation","link":"https:\/\/dasini.net\/blog\/category\/presentation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1370,"url":"https:\/\/dasini.net\/blog\/2013\/01\/30\/full-table-scan-vs-full-index-scan-part2-2\/","url_meta":{"origin":362,"position":2},"title":"Full table scan vs Full index scan part2-2","author":"Olivier DASINI","date":"30 janvier 2013","format":false,"excerpt":"2\/ FTS ou FIS Avant de r\u00e9pondre explicitement \u00e0 la question, un petit zoom sur l\u2019une des nombreuses nouveaut\u00e9s de MySQL 5.6. La commande EXPLAIN s\u2019est enrichie de la clause format=json. Elle permet d\u2019avoir une version un peu plus d\u00e9taill\u00e9e que l\u2019EXPLAIN classique. Query 1\/ EXPLAIN format=json SELECT d,avg(price) FROM\u2026","rel":"","context":"Dans &quot;optimisation&quot;","block_context":{"text":"optimisation","link":"https:\/\/dasini.net\/blog\/category\/optimisation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":343,"url":"https:\/\/dasini.net\/blog\/2008\/12\/15\/presentation-architectures-haute-disponibilite-avec-mysql\/","url_meta":{"origin":362,"position":3},"title":"Pr\u00e9sentation: Architectures haute disponibilit\u00e9 avec MySQL","author":"Olivier DASINI","date":"15 d\u00e9cembre 2008","format":false,"excerpt":"La haute disponibilit\u00e9 consiste \u00e0 faire en sorte qu\u2019un service ou une architecture soit le moins souvent indisponible\u2026 http:\/\/dasini.net\/blog\/?#Haute_dispo_avec_MySQL","rel":"","context":"Dans &quot;Pr\u00e9sentation&quot;","block_context":{"text":"Pr\u00e9sentation","link":"https:\/\/dasini.net\/blog\/category\/presentation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1256,"url":"https:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/","url_meta":{"origin":362,"position":4},"title":"Jointure vs sous-requ\u00eate","author":"Olivier DASINI","date":"15 mai 2012","format":false,"excerpt":"MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n'est pas faux, et d'ailleurs c'est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure. C'est le cas de SELECT a FROM T1\u2026","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1163,"url":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","url_meta":{"origin":362,"position":5},"title":"Am\u00e9liorations de l&rsquo;optimiseur dans MariaDB","author":"Olivier DASINI","date":"9 janvier 2012","format":false,"excerpt":"Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l'optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates. Voici un effet visuel de ces optimisations: Avec MySQL 5.5, l'utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d'un SELECT), donne le plan d'ex\u00e9cution suivant:","rel":"","context":"Dans &quot;bench&quot;","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/362","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=362"}],"version-history":[{"count":15,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/362\/revisions"}],"predecessor-version":[{"id":380,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/362\/revisions\/380"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}