{"id":1044,"date":"2011-02-01T19:25:41","date_gmt":"2011-02-01T18:25:41","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1044"},"modified":"2011-02-01T19:25:41","modified_gmt":"2011-02-01T18:25:41","slug":"un-disque-ssd-comme-buffer-pour-innodb","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2011\/02\/01\/un-disque-ssd-comme-buffer-pour-innodb\/","title":{"rendered":"Un disque SSD comme buffer pour InnoDB"},"content":{"rendered":"

MySQL, la base de donn\u00e9es open source la plus populaire, inspire toujours autant les d\u00e9veloppeurs. David<\/a>, propose un patch qui permet de cr\u00e9er un buffer pool<\/a> suppl\u00e9mentaire pour InnoDB, qui est stock\u00e9 sur un disque SSD ou de la m\u00e9moire flash.<\/p>\n

Cette fonctionnalit\u00e9 cr\u00e9\u00e9e un thread qui en tache de fond r\u00e9cup\u00e8re les pages de donn\u00e9es vir\u00e9es du buffer pool pour les copier dans le buffer pool suppl\u00e9mentaire du SSD au lieu du disque classique. L’id\u00e9e \u00e9tant d’\u00e9viter les acc\u00e8s au disque classique ( beaucoup plus lents notamment lors d’acc\u00e8s al\u00e9atoires).<\/p>\n

De plus selon ses tests, les r\u00e9sultats restent\u00a0\u00e9galement\u00a0bien meilleurs avec une configuration SSD et un seul buffer pool.<\/p>\n

Sysbench OLTP benchmark<\/h1>\n

Server: Intel(R) Xeon Quad-Core E5405 2.00GHz X 2
\nDatabase: ~19G ( created by sysbench, row: 80000000 )
\ndisk: 4 disk RAID 10 flash storage: Intel X25-M 80G SSD
\ntest command:
\nsysbench –test=oltp –oltp-table-size=80000000 –oltp-read-only=off –init-rng=on –num-threads=16 –max-requests=0 –oltp-dist-type=uniform –max-time=7200 –mysql-user=root –mysql-socket=\/tmp\/mysql.sock –db-driver=mysql run<\/p>\n\n\n\n\n\n\n
innodb_buffer_pool<\/strong><\/td>\ninnodb_secondary_buffer_pool<\/strong><\/td>\nread-write(tps)<\/strong><\/td>\n<\/tr>\n
8G<\/td>\n0G<\/td>\n55.03<\/td>\n<\/tr>\n
8G(sbtest on SSD)<\/td>\n0G<\/td>\n60.8<\/td>\n<\/tr>\n
8G<\/td>\n30G<\/td>\n86.23<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

la suite…<\/a><\/p>\n

A tester \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

MySQL, la base de donn\u00e9es open source la plus populaire, inspire toujours autant les d\u00e9veloppeurs. David, propose un patch qui permet de cr\u00e9er un buffer pool suppl\u00e9mentaire pour InnoDB, qui est stock\u00e9 sur un disque SSD ou de la m\u00e9moire flash.<\/p>\n

Cette fonctionnalit\u00e9 cr\u00e9\u00e9e un thread qui en tache de fond r\u00e9cup\u00e8re les pages de donn\u00e9es vir\u00e9es du buffer pool pour les copier dans le buffer pool suppl\u00e9mentaire du SSD au lieu du disque classique. L’id\u00e9e \u00e9tant d’\u00e9viter les acc\u00e8s au disque classique ( beaucoup plus lents notamment lors d’acc\u00e8s al\u00e9atoir?es).<\/p>\n

De plus selon ses tests, ?????? les r\u00e9sultats restent \u00e9galement bien meilleurs avec une configuration SSD et un seul buffer pool.<\/p>\n

Sysbench <\/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,34],"tags":[24],"class_list":["post-1044","post","type-post","status-publish","format-standard","hentry","category-mysql","category-optimisation","tag-innodb"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-gQ","jetpack-related-posts":[{"id":1227,"url":"https:\/\/dasini.net\/blog\/2012\/03\/30\/mysql-5-6-rocks\/","url_meta":{"origin":1044,"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 "bench"","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":4644,"url":"https:\/\/dasini.net\/blog\/2021\/06\/14\/le-monde-de-mysql-3\/","url_meta":{"origin":1044,"position":1},"title":"Le Monde de MySQL – #3","author":"Olivier DASINI","date":"14 juin 2021","format":false,"excerpt":"Le Monde de MySQL est un vid\u00e9ocast, anim\u00e9 par deux v\u00e9t\u00e9rans MySQL (Lefred & Daz), qui a pour but de tenir inform\u00e9 la communaut\u00e9 francophone sur l\u2019actualit\u00e9 de la base de donn\u00e9es la plus populaire au monde. Dans ce troisi\u00e8me \u00e9pisode, les sujets abord\u00e9s sont (entre autres): + MySQL Operator\u2026","rel":"","context":"Dans "Cloud"","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/LeMondeDeMySQL_800x420.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/LeMondeDeMySQL_800x420.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/LeMondeDeMySQL_800x420.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/LeMondeDeMySQL_800x420.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1245,"url":"https:\/\/dasini.net\/blog\/2012\/04\/05\/mysql-5-6-rock-suite\/","url_meta":{"origin":1044,"position":2},"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 "bench"","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":1370,"url":"https:\/\/dasini.net\/blog\/2013\/01\/30\/full-table-scan-vs-full-index-scan-part2-2\/","url_meta":{"origin":1044,"position":3},"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 "optimisation"","block_context":{"text":"optimisation","link":"https:\/\/dasini.net\/blog\/category\/optimisation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":953,"url":"https:\/\/dasini.net\/blog\/2010\/07\/22\/vulnerabilites-mysql-5-1-47\/","url_meta":{"origin":1044,"position":4},"title":"Vuln\u00e9rabilit\u00e9s MySQL 5.1.47","author":"Olivier DASINI","date":"22 juillet 2010","format":false,"excerpt":"Les versions ant\u00e9rieurs \u00e0 5.1.47 MySQL souffrent de vuln\u00e9rabilit\u00e9s: * d\u00e9ni de service. Cet effet peut se produire lorsque le serveur de base de donn\u00e9es re\u00e7oit un paquet dont la taille est sup\u00e9rieure \u00e0 la taille maximale autoris\u00e9e. * d\u00e9bordement de m\u00e9moire (buffer overflow). Cet effet se produit lorsque l\u2019argument\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2959,"url":"https:\/\/dasini.net\/blog\/2019\/04\/08\/200\/","url_meta":{"origin":1044,"position":5},"title":"200 !!","author":"Olivier DASINI","date":"8 avril 2019","format":false,"excerpt":"Cet article est en fait mon 200 \u00e8me \\o\/ Pour marquer le coup, j'ai d\u00e9cid\u00e9 de faire une petite pause et de regarder dans le r\u00e9troviseur...","rel":"","context":"Dans "Divers"","block_context":{"text":"Divers","link":"https:\/\/dasini.net\/blog\/category\/divers\/"},"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\/1044","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=1044"}],"version-history":[{"count":3,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1044\/revisions"}],"predecessor-version":[{"id":1047,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1044\/revisions\/1047"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}