{"id":1245,"date":"2012-04-05T13:34:55","date_gmt":"2012-04-05T12:34:55","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1245"},"modified":"2012-04-05T13:34:55","modified_gmt":"2012-04-05T12:34:55","slug":"mysql-5-6-rock-suite","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/04\/05\/mysql-5-6-rock-suite\/","title":{"rendered":"MySQL 5.6 rock suite"},"content":{"rendered":"<p>Voici la suite du post <a href=\"http:\/\/dasini.net\/blog\/2012\/03\/30\/mysql-5-6-rocks\/\" target=\"_blank\">MySQL 5.6 rock<\/a>, dans lequel je test MySQL 5.5 &amp; 5.6, MariaDB 5.3 &amp; 5.5 et Percona server 5.5.<\/p>\n<p>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%) <strong>et<\/strong> \u00e9criture (35%).<\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n<span style=\"text-decoration: underline;\">Les r\u00e8gles du bench<\/span><\/p>\n<p>sysbench, options <strong>read\/write<\/strong> complex, 128 connexions concurrentes<\/p>\n<p>Moteur InnoDB, 30 millions de lignes, 6.7 Go de donn\u00e9es<\/p>\n<p>Pas de warmup, le disque doit \u00eatre sollicit\u00e9 au maximum.<\/p>\n<p>Les tests ont \u00e9t\u00e9 lanc\u00e9 4 fois sur chaque version, avec restart du serveur entre chaque run.<\/p>\n<p>Les confs sont proche cad les param\u00e8tres communs \u00e0 toutes les versions ont les m\u00eames valeurs, les autres, leur valeur par d\u00e9faut<\/p>\n<p>La moyenne donne les r\u00e9sultats suivants:<\/p>\n<p><span style=\"text-decoration: underline;\">95 centile (en ms)<\/span><\/p>\n<ul>\n<ul>\n<li>Percona 5.5.12: <strong>182.135<\/strong><\/li>\n<li>MariaDB 5.3.5 *: <strong>132.3025<\/strong><\/li>\n<li>MariaDB 5.5.20: <strong>183.1275<\/strong><\/li>\n<li>MySQL 5.5.22: <strong>160.8525<\/strong><\/li>\n<li>MySQL 5.6.4: <strong>153.6175<\/strong><\/li>\n<\/ul>\n<\/ul>\n<p>&nbsp;<\/p>\n<div><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_95percentil.png\" target=\"_blank\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_95percentil.png?resize=430%2C282\" alt=\"dasini.net - 95 centile for R\/W i\/o bounds\" width=\"430\" height=\"282\" \/><\/a><\/div>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Nombre de transactions par seconde<\/span><\/p>\n<ul>\n<li>Percona 5.5.12: <strong>2170.5<\/strong><\/li>\n<li>MariaDB 5.3.5 *: <strong>2016.505<\/strong><\/li>\n<li>MariaDB 5.5.20: <strong>2134.9125<\/strong><\/li>\n<li>MySQL 5.5.22: <strong>1680.7525<\/strong><\/li>\n<li>MySQL 5.6.4: <strong>1738.4775<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<div><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_tps.png\" target=\"_blank\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_tps.png?resize=438%2C262\" alt=\"dasini.net - TPS for R\/W i\/o bounds\" width=\"438\" height=\"262\" \/><\/a><\/div>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Mes commentaires<\/span><\/p>\n<p>En mati\u00e8re de temps de r\u00e9ponse, MariaDB 5.3 (branche MySQL 5.1 + nombreuses am\u00e9liorations apport\u00e9es par Monty Program AB) est sans (trop de) surprises, celui qui donne les meilleurs temps de r\u00e9ponse sur 95 % des meilleurs requ\u00eates (95 centile). Mais il sera moins scalable du fait de son unique Buffer pool.<\/p>\n<p>La bonne surprise vient de MySQL 5.6 o\u00f9 le temps de r\u00e9ponse est significativement meilleur qu&rsquo;avec la branche 5.5 (de 5 \u00e0 15%).<\/p>\n<p>En ce qui concerne d\u00e9bit maximum, les TPS, MySQL est en retrait. Il plafonne aux environs des 1700 TPS alors que MariaDB &amp; Percona d\u00e9passent sans souci les 2000\u00a0!<\/p>\n<p>Cet \u00e9cart (20% tout de m\u00eame\u00a0!!!) s&rsquo;explique sans doute par de meilleurs performances d&rsquo;XtraDB (qui \u00e9quipe MariaDB &amp; Percona) en mati\u00e8re de d\u00e9bit, par rapport \u00e0 son cousin InnoDB (XtraDB \u00e9tant un fork d&rsquo;innoDB, d\u00e9velopp\u00e9 par Percona).<\/p>\n<p>Le produit s&rsquo;am\u00e9liore donc, et ce m\u00eame sans tuning particulier, c&rsquo;est une tr\u00e8s bonne chose\u00a0!<\/p>\n<p>Ceci dit, l&rsquo;optimisation du serveur MySQL risque de commencer d\u00e8s le choix de la distrib, en prenant en compte le workload (lecture seule, \u00e9criture seule, lecture+\u00e9criture), le besoin (temps de r\u00e9ponse, d\u00e9bit maximal), \u2026<\/p>\n<p>Avoir dans son \u00e9quipe un profile MySQL exp\u00e9riment\u00e9 devient de plus en plus n\u00e9cessaire. Et \u00e7a je trouve que c&rsquo;est plut\u00f4t une bonne chose \ud83d\ude09<\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Divers<\/span><\/p>\n<pre>sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=40000000 \\\r\n--oltp-test-mode=complex --max-requests=0 --max-time=600 \\\r\n--num-threads=128\r\n\r\ninnodb_buffer_pool_instances\u00a0\u00a0\u00a0 4 (1 pour MariaDB 5.3)\r\ninnodb_buffer_pool_size\u00a0\u00a0\u00a0 34359738368\r\ninnodb_doublewrite\u00a0\u00a0\u00a0 ON\r\ninnodb_file_per_table\u00a0\u00a0\u00a0 ON\r\ninnodb_flush_log_at_trx_commit\u00a0\u00a0\u00a0 2\r\ninnodb_log_buffer_size\u00a0\u00a0\u00a0 8388608\r\ninnodb_log_file_size\u00a0\u00a0\u00a0 152043520\r\ninnodb_log_files_in_group\u00a0\u00a0\u00a0 2\r\ninnodb_version\u00a0 1.1.8-24.1 \/ 1.2.4 (ou <span style=\"color: #000000;\"><span style=\"font-family: arial,sans,sans-serif;\"><span style=\"font-size: x-small;\">XtraDB 1.1.6-20.1<\/span><\/span><\/span>)\r\n\r\ngeneral_log\u00a0\u00a0\u00a0 OFF\r\nlog_bin\u00a0\u00a0\u00a0 OFF\r\nslow_query_log\u00a0\u00a0\u00a0 OFF<\/pre>\n<p>&nbsp;<\/p>\n<pre>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n           Name: sbtest\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Engine: InnoDB\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Version: 10\r\n\u00a0\u00a0\u00a0\u00a0 Row_format: Compact\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Rows: 30000237\r\n\u00a0Avg_row_length: 224\r\n\u00a0\u00a0\u00a0 Data_length: 6741295104\r\nMax_data_length: 0\r\n\u00a0\u00a0 Index_length: 477085696\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Data_free: 6291456\r\n\u00a0Auto_increment: 33800371\r\n\u00a0\u00a0\u00a0 Create_time: 2012-03-29 18:42:34\r\n\u00a0\u00a0\u00a0 Update_time: NULL\r\n\u00a0\u00a0\u00a0\u00a0 Check_time: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Collation: utf8_swedish_ci<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Voici la suite du post MySQL 5.6 rock, dans lequel je test MySQL 5.5 &#038; 5.6, MariaDB 5.3 &#038; 5.5 et Percona server 5.5.<\/p>\n<p>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 \u00e9criture (35%).<\/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":[81,167,8,181],"tags":[247,250,10,182,180],"class_list":["post-1245","post","type-post","status-publish","format-standard","hentry","category-bench","category-mariadb","category-mysql","category-percona-server-2","tag-bench","tag-mariadb","tag-mysql5","tag-mysql5-6","tag-percona-server"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-k5","jetpack-related-posts":[{"id":1227,"url":"https:\/\/dasini.net\/blog\/2012\/03\/30\/mysql-5-6-rocks\/","url_meta":{"origin":1245,"position":0},"title":"MySQL 5.6 rock !","author":"Olivier DASINI","date":"30 mars 2012","format":false,"excerpt":"Comme d'habitude, mon but n'est pas de conna\u00eetre les possibilit\u00e9s maximales du serveur (d'autres le font mieux que moi), mais plut\u00f4t d'avoir une id\u00e9e assez pr\u00e9cise de leurs comportements respectifs dans un environnement le plus proche possible de ma prod. pour ce test, les candidats sont, Percona 5.5, MariaDB 5.3\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","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/percentil.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1375,"url":"https:\/\/dasini.net\/blog\/2013\/02\/21\/mysql-5-6\/","url_meta":{"origin":1245,"position":1},"title":"MySQL 5.6","author":"Olivier DASINI","date":"21 f\u00e9vrier 2013","format":false,"excerpt":"Cela fait quelques jours maintenant que MySQL 5.6 est disponible pour la production. Un impressionnant travail a \u00e9t\u00e9 effectu\u00e9 par les \u00e9quipe d'Oracle, voici un petit r\u00e9sum\u00e9 des principales \u00e9volution vu par Peter Zaitsev. L'\u00e9v\u00e9nement dans l'\u00e9v\u00e9nement, c'est la \"pol\u00e9mique\" sur les performances de la 5.6, par rapport \u00e0 MySQL\u2026","rel":"","context":"Dans &quot;MariaDB&quot;","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1487,"url":"https:\/\/dasini.net\/blog\/2014\/01\/07\/update-db-set-age-age-1-where-productmysql\/","url_meta":{"origin":1245,"position":2},"title":"UPDATE db SET age = age + 1 WHERE product=&rsquo;MySQL&rsquo;","author":"Olivier DASINI","date":"7 janvier 2014","format":false,"excerpt":"2013 \u00e0 \u00e9t\u00e9 une ann\u00e9e tr\u00e8s riche en ce qui concerne l\u2019\u00e9cosyst\u00e8me MySQL \/ MariaDB \/ Percona Server. Voici un petit r\u00e9cap technique (incomplet) pour les 3 acteurs majeurs: MySQL @Oracle MariaDB & SkySQL Percona","rel":"","context":"Dans &quot;MariaDB&quot;","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1496,"url":"https:\/\/dasini.net\/blog\/2015\/09\/14\/meetup-mysql-a-paris\/","url_meta":{"origin":1245,"position":3},"title":"Meetup MySQL \u00e0 Paris","author":"Olivier DASINI","date":"14 septembre 2015","format":false,"excerpt":"Ce jeudi 17 septembre 2015, \u00e0 l'initiative du MySQL User Group France (lemug.fr), je vais pr\u00e9senter un retour d'exp\u00e9rience sur une migration MySQL 5.5 vers 5.6 dans les locaux de Dailymotion \u00e0 Paris.","rel":"","context":"Dans &quot;Conf\u00e9rence&quot;","block_context":{"text":"Conf\u00e9rence","link":"https:\/\/dasini.net\/blog\/category\/conference\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1501,"url":"https:\/\/dasini.net\/blog\/2015\/09\/25\/slides-du-meetup\/","url_meta":{"origin":1245,"position":4},"title":"Slides du meetup","author":"Olivier DASINI","date":"25 septembre 2015","format":false,"excerpt":"Le 17 septembre j'ai \u00e9t\u00e9 invit\u00e9 par le MySQL User Group Fr pour parler d'un retour d'exp\u00e9rience sur une migration MySQL 5.5 vers 5.6. Voici ma pr\u00e9sentation: http:\/\/www.slideshare.net\/freshdaz\/upgrade-to-mysql-56-without-downtime","rel":"","context":"Dans &quot;Conf\u00e9rence&quot;","block_context":{"text":"Conf\u00e9rence","link":"https:\/\/dasini.net\/blog\/category\/conference\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1425,"url":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","url_meta":{"origin":1245,"position":5},"title":"Utiliser une sous requ\u00eate c\u2019est mal ? (suite) part 1-3","author":"Olivier DASINI","date":"27 mai 2013","format":false,"excerpt":"Comme promit, voici la suite de l\u2019article Utiliser une sous-requ\u00eate c\u2019est mal ? L\u2019id\u00e9e ici est de r\u00e9pondre aux interrogations de svar et d\u2019en profiter pour explorer les nouvelles possibilit\u00e9s de la variante stable de MySQL qui poss\u00e8de l\u2019optimiseur le plus avanc\u00e9, c\u2019est \u00e0 dire MariaDB 5.5. Pr\u00e9ambule En pr\u00e9-requis,\u2026","rel":"","context":"Dans &quot;MariaDB&quot;","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA","width":350,"height":200,"srcset":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1x, https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1.5x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1245","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=1245"}],"version-history":[{"count":4,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1245\/revisions"}],"predecessor-version":[{"id":1249,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1245\/revisions\/1249"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1245"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}