{"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":1245,"url":"https:\/\/dasini.net\/blog\/2012\/04\/05\/mysql-5-6-rock-suite\/","url_meta":{"origin":362,"position":4},"title":"MySQL 5.6 rock suite","author":"Olivier DASINI","date":"5 avril 2012","format":false,"excerpt":"Voici la suite du post MySQL 5.6 rock, dans lequel je test MySQL 5.5 & 5.6, MariaDB 5.3 & 5.5 et Percona server 5.5. Pour cet article, toujours un bench. Le contexte est assez proche, \u00e0 la diff\u00e9rence pr\u00e8s que cette fois les serveurs sont test\u00e9s en lecture (65%) et\u2026","rel":"","context":"Dans &quot;bench&quot;","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"dasini.net - 95 centile for R\/W i\/o bounds","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_95percentil.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":362,"position":5},"title":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 2\/5)","author":"Olivier DASINI","date":"3 d\u00e9cembre 2008","format":false,"excerpt":"Pouvoir automatiser ses t\u00e2ches de mani\u00e8re fiable et simple est le r\u00eave de tout administrateur de base de donn\u00e9es. Le programmateur d'\u00e9v\u00e8nements (Event Scheduler) est un planificateur de t\u00e2ches (CRON-like) embarqu\u00e9 dans MySQL 5.1.","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":[]}],"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}]}}